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. |
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. |
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. |
There are at least three reasons to use asynchronous calls to ODBC:
Please not: These hints are taken from my personal memory, so I'd like to invite all SA insiders to verify my statements:) |
Please explain what you mean by your last statement - What do you mean by "traditional methods to get results of PRINT statements" ?
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.