3

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.

Nikolaj Henrichsen
  • 39
  • 4
  • It's questionable if it's even possible. For Oracle it is explicitly stated in the docs that OCIBreak can be used from parallel thread. While mySQL or PostgreSQL use 2nd session to cancel task on the 1st one. – ibre5041 Feb 16 '13 at 03:45
  • @Ivan: According to ODBC documentation and many comments I've found on the net this is not only possible - it's the way to do it. I just wish I could figure out how to do it myself. It is not supported with Delphi's native components. It should be done from another thread (session) as you state and as I wrote in my question. – Nikolaj Henrichsen Feb 16 '13 at 13:58
  • Well as I stated before, at least mySQL and PostreSQL do not support it natively. You must execute "KILL PROCESS " from the 2nd session. – ibre5041 Feb 17 '13 at 10:54
  • @Ivan: Can you provide an example (code) for that? – Nikolaj Henrichsen Feb 17 '13 at 11:27
  • I was wrong in the prev answer. For PostreSQL you can use PQcancel which is possibly mapped to SQLCancel. Or you can use pg_backend_pid()/pg_cancel_backend() from the 2nd session as described [here](http://stackoverflow.com/questions/3508627/stop-long-running-sql-query-in-postgresql-when-session-or-requests-no-longer-e). For MySQL you have to execute `SELECT CONNECTION_ID(); ` store it aside first and the execute the `KILL PROCESS/QUERY ;` from the second session. – ibre5041 Feb 18 '13 at 17:42
  • 1
    For [MS SQL](http://stackoverflow.com/questions/1092433/how-can-you-cancel-a-sql-server-execution-process-programmatically) – ibre5041 Feb 18 '13 at 18:00

2 Answers2

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.
Jeroen Wiert Pluimers
  • 23,965
  • 9
  • 74
  • 154
0

According to the ODBC spec SQLCancel can cancel the following types of processing on a statement:

  • A function running asynchronously on the statement (which you sound like you've already done)
  • A function on a statement that needs data (an example would be SQLExecute returning SQL_NEED_DATA)
  • A function running on the statement on another thread (which sounds like this is what you want)

I've personally done the first 2 above but never the last. You'd also need a thread-safe ODBC driver (some are not even though the ODBC spec says they should be). As to how you do this in Delphi, I've no idea.

bohica
  • 5,932
  • 3
  • 23
  • 28