Announcement

Collapse
No announcement yet.

SQLTools and Delete Query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLTools and Delete Query

    I apologize if this is a redundant question but I was not quite sure on the phrasing as I am weak on SQL and Database development.

    I am using SQLTools Pro against an local Access 2003 MDB. I want to be able to run a Delete Query I created within the database itself (query is called "Delete Old Duplicates") . I'm assuming that would be considered a stored procedure?

    Can I do this in SQLTools or must I do it using a COM method?
    Last edited by George Bleck; 2 Oct 2009, 09:15 AM.
    <b>George W. Bleck</b>
    <img src='http://www.blecktech.com/myemail.gif'>

  • #2
    I know there is a syntax to call stored procedures from an SQL statement, but I also know it varies with the DBMS.

    It might be something like "Execute" or maybe "call" or maybe "run" or something like that.

    Try a search engine with "execute stored procedure Access" and see what you find.

    >I'm assuming that would be considered a stored procedure

    Generally those are created with a "CREATE PROCEDURE " or similar syntax, but those can be DBMS-specific, too. (eg Oracle can use 'CREATE OR REPLACE PROCEDURE xxxxx' ..., which syntax I know MS-Jet does not support)

    Basically anything you can submit via 'command line' can be executed thru the ODBC interface, and therefore thru an ODBC-helper product (SQL Tools).

    Darned if I would know how to get back any output parameters of the procedure via SQL Tools, though.

    MCM
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      George --

      > Can I do this in SQLTools or must I do it using a COM method?

      You can definitely do it with SQL Tools Pro. There's very little that COM can do that ODBC can't. As Michael said, the SQL-statement syntax varies from DBMS to DBMS but the entire "stored procedure" system is available in SQL Tools Pro, including input and output parameters (if you need them). You can list the available procedures, etc. etc. etc.

      It has been a while since I used a Stored Procedure with Microsoft Access, but IIRC the syntax -- assuming that the procedure doesn't require any params -- is simply "call <name of stored procedure>". I see that your proc name contains spaces, so I suspect that you'll need to use brackets like "call [Delete Old Duplicates]". If that SQL statement doesn't work, please post the results (exact error message etc.) and we'll figure it out.

      -- Eric Pearson, Perfect Sync, Inc.
      "Not my circus, not my monkeys."

      Comment


      • #4
        Just a follow-up on this thread...

        I decided not to go the route of a Delete Query. My original problem was I was afraid I would not be able to handle the fast stream of data that was coming into my program. I just logged the data into the database as fast as it came in and had planned on running a nightly cleaup query when the load was low or non-existant.

        Well as it turns out, I am pleasantly surprised at the sheer speed of my app, SQL Tools, and the Jet Engine. I switched, after a little madness teaching myself SQL, to a hybrid function that will UPDATE/INSERT based on existence in the database. I don't think the entire system even blinks at the data rate.

        Very happy camper here.
        Last edited by George Bleck; 6 Oct 2009, 11:42 AM.
        <b>George W. Bleck</b>
        <img src='http://www.blecktech.com/myemail.gif'>

        Comment


        • #5
          Hmmmmm.....simply A-MAZE-ING what you can accomplish when you simply "give it a try," huh?
          Michael Mattias
          Tal Systems (retired)
          Port Washington WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            I agree 100%, just always a little rough when you don't know the lingo and have to learn it on your feet and under the gun

            I've used SQL perhaps 2-3 in my 32 year IT career so far and the extent of it was a simple "SELECT * FROM table". Granted the stuff I used this time out was still just scratching the surface of what SQL is capable of I am still wildly impressed.

            I just need to get myself a good SQL primer book now to read when I have time. I bought SQLTools standard a few years ago, and SQLTools Pro a few months ago, neither of which I have had need for until now. Each of the products I needed the original purchases for fell through so I never felt the urge or need to delve into them. Until now.

            I had to merge a lot of unfamiliar territories together for this particular product I worked on (I had to build a client component distributed to about 15K PC's generating XML data which was sent via TCP/IP to a central server component I also created that sits in my system tray and writes to a SQL database). Love that feeling you get when all these "speedbumps" are worked out and the product actually does what you envisioned it to do.
            Last edited by George Bleck; 6 Oct 2009, 04:40 PM.
            <b>George W. Bleck</b>
            <img src='http://www.blecktech.com/myemail.gif'>

            Comment


            • #7
              You know, one of the greatest things about Win/32 is the process isolation. You can screw up big time and the worst that happens is you have to click on "no, don't send a report to Microsoft."

              Under MS/DOS of course, you could only run one process; but Ctrl+ALT+Delete was often required to completely recover from an error. (e.g. from current thread: file locks).

              Win/16 was a step in the right direction in that it supported multi-tasking; but the shared memory made it a virtual certainty that if you 'made a stupid' in one process, it was not going down alone and would take one or more other processes with it.

              Win/32? Close it out, atone for your sin, run again. No muss, no fuss, no bother.

              MCM
              Michael Mattias
              Tal Systems (retired)
              Port Washington WI USA
              [email protected]
              http://www.talsystems.com

              Comment


              • #8
                Hi George,
                if you just want the SQL basics then try something like
                http://www.amazon.com/Sams-Teach-You.../dp/0672325675
                it gives you query examples and the different syntax for DB2, Access, SQL Sever,MYSQL,Oracle,PostgreSQL and Sybase.

                Couple this with MCM's http://www.powerbasic.com/support/pb...130#post301130

                and you will find SQl with COM easy. Sorry Eric

                I have also used MCM's ADO code with the VFPOLEDB COM interface to convert VFP9 dbf data, works a treat.

                N.

                Comment

                Working...
                X