How to cancel SQL in thread A from thread B using SQLCancel

Question

I need to run an SQL command synchronously in one thread with the ability to cancel the query from another thread.

As far as I can tell I need the ODBC function SQLCancel and a handle to the connection object to accomplish this task.

However, being a rookie in this area it feels like an uphill battle to figure this out.

Can someone provide me sample code to achieve this in Delphi 2006?

Description of the setup:

Thread A

ODBC_Connection.Execute('SELECT a,b,c INTO ##MyTable FROM LongRunningQuery')

Thread B

[some magical code that cancels the SQL statement in Thread A using SQLCancel]

Note: I have a current solution where I utilize the ADOCommand object to execute a query asynchronously. This makes it possible to use the Command object's Cancel method. But this is slow because I'm having a while loop with a Sleep function to poll the status of the command to see when it is complete. That's why I want a solution based on synchronous execution. I have hundreds of queries so performance is important.

Additional information: I'm using SQL Server as backend so this is the primary solution I'm looking for. Advice on handling Oracle and other databases is of course also interesting, but secondary in my case.

Answer 1

It totally depends on your database back-end, database client and the DAL you use.

The database server and client must support accessing the same underlying connection and statement handles from different threads.

The DAL needs to give you access to it.

• DAL: I know that AnyDAC does, but never tried ODBC. Your question indicates the ODBC API supports it, but I'm not sure the Delphi ODBC wrapper surfaces it.
• Database Server: at least Oracle and SQL Server support this.