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.
asked 09 Apr '10, 23:00
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.
answered 09 Apr '10, 23:40
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.
answered 10 Apr '10, 09:44
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:)
answered 14 Apr '10, 11:48