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

Brad%20Wery's gravatar image

Brad Wery
382192126
accept rate: 0%

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

(09 Apr '10, 23:43) Mark Culp

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.

(12 Apr '10, 14:41) Brad Wery

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.

permanent link

answered 09 Apr '10, 23:40

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274
accept rate: 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.

permanent link

answered 10 Apr '10, 09:44

Breck%20Carter's gravatar image

Breck Carter
26.9k437609883
accept rate: 21%

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:)

permanent link

answered 14 Apr '10, 11:48

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125

question asked: 09 Apr '10, 23:00

question was seen: 3,000 times

last updated: 14 Apr '10, 11:48