Clever Geek Handbook

How to cancel SQL on stream A from stream B using SQLCancel

I need to run a SQL command synchronously on one thread with the ability to cancel the request from another thread.

As far as I can tell, I need an ODBC SQLCancel function and a connection object descriptor to accomplish this task.

However, as a newbie to the field, it feels like a tough battle to figure it out.

Can anyone provide me with some sample code for this in Delphi 2006?

Setting Description:

Topic A

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

Topic B

[some magic code that cancels the SQL statement in Thread A with SQLCancel]

Note. I have a current solution where I am using an ADOCommand object to make an asynchronous request. This allows the Command object's Cancel method to be used. But this is slow because I have a while loop with a Sleep function to poll the status of the command to see when it has completed. This is why I want a solution based on synchronous execution. I have hundreds of requests, so performance is important.

Additional information: I am using SQL Server as a backend, so this is the main solution I am looking for. The advice on handling Oracle and other databases is of course also interesting, but secondary in my case.

+3


source to share


2 answers


It all depends on the database, the database client and the DAL used.

The database server and client must support access to the same underlying connection and command statements from different threads.



The DAL should give you access to it.

  • DAL: I know what AnyDAC does , but have never tried ODBC. Your question indicates that the ODBC API supports it, but I'm not sure if the Delphi ODBC wrapper covers it.
  • Database Server: At least Oracle and SQL Server support this.
+1


source


According to the ODBC specification, SQLCancel can override the following processing in a statement:

  • A function to execute asynchronously in a statement (which you seem to have done already)
  • A function in a statement that needs data (an example is SQLExecute, which returns SQL_NEED_DATA)
  • A function to execute in a statement on a different thread (which is similar to what you want)


I personally did the first 2, but never the last. You also need a thread-safe ODBC driver (some don't even think there should be an ODBC specification). As for how you do it in Delphi, I have no idea.

0


source






More articles:


All Articles