SQLTools and Delete Query

[George Bleck]

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?

[Michael Mattias]

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

[Eric Pearson]

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.

[George Bleck]

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.

[Michael Mattias]

Hmmmmm.....simply A-MAZE-ING what you can accomplish when you simply "give it a try," huh?

[George Bleck]

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.

[Michael Mattias]

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

[Neil J Hosgood]

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.