Asynchronous Execution via ODBC

[Former Member]

I'm using the ODBC API to execute SQL. I want to make the call asynchronous so I use the ODBC API to set the following attribute:

SQLSetStmtAttr(ll_dbhandle, SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON, 0)

This appears to have no effect. The Microsoft documentation states that vendors may not implement this which I'm suspecting is the case here.

Has anyone else come across this?

I notice that the ODBC API doesn't work well with SQL Anywhere. For example, traditional methods to get the results of PRINT statements do no work.

[MarkCulp]

Please explain what you mean by your last statement - What do you mean by "traditional methods to get results of PRINT statements" ?

[Former Member]

The ODBC API has two functions: SQLGetDiagRec and SQLGetDiagField. They are traditional ways to get messages from the database; whether it's print messages or error messages. SQL Anywhere doesn't seem to respond well to these function calls.

[MarkCulp · Accepted solution]

SQL Anywhere's ODBC driver does not support asynchronous statements - you will get a error when you attempt to turn ON the SQL_ATTR_ASYNC_ENABLE attribute. (Are you checking the return code from the call?) The reason is that (the current implementation is that) each statement that is sent to the database server from the client blocks until the server returns a response to the request.

An alternative to using SQL_ATTR_ASYNC_ENABLE is to create a separate thread (and connection) and execute the SQL on the separate thread.

[Breck_Carter]

To expand on Mark's answer: One way to create a separate thread and connection is (in PowerBuilder) to call SharedObjectRegister and SharedObjectGet. Another way is to call a stored procedure which triggers a server-side EVENT to perform the actual task; events run on their own connection, and TRIGGER EVENT is a fire-and-forget operation. I've used the former technique to make up to 1,000 connections to SQL Anywhere for stress-testing purposes. The latter technique (TRIGGER EVENT) is at the heart of Foxhound.

[VolkerBarth]

There are at least three reasons to use asynchronous calls to ODBC:

1. Your application should be responsive during longer-taking queries. - That is primarily a historical need, methinks: Starting with the introduction of 32 bit OSes, one should implement that requirement with a separate thread doing a synchronous ODBC call instead of an asynchronous ODBC call in the main thread.

2. You want to be able to cancel a longer-taking query. - AFAIK, in order to cancel a running ODBC query in SA, you will have to use a second thread and call SQLCancel() with the statement handle from the first request. That is a side-effect of not-supporting asynchronous calls.

3. You want to display progress messages during longer-taking queries. - That seems to be your particular concern. - In SA, you can use MESSAGE TO CLIENT statements in your SQL code and use a particular callback to receive those messages. You may have a look at SA_REGISTER_MESSAGE_CALLBACK for that usage.

Please not: These hints are taken from my personal memory, so I'd like to invite all SA insiders to verify my statements:)