Asynchronous ADO 1

[boggg1]

I'm part way through converting my asynchronous DAO's to ADO's and I cannot get the asynchronous part of the ADO to work...

This line works...
rstHistory.Open SQL1_, conPRS, adOpenStatic, adLockReadOnly

But this one does not...
rstHistory.Open SQL1_, conPRS, adOpenStatic, adLockReadOnly, adAsyncExecute

Here is the error...

Error Number: 0
IM002:[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified
Error Source: ODBC.Workspace

SQL1_ is valid SQL, conPRS is a valid connection (otherwise the synchronous query would not work).

In the past I have found the Oracle drivers to be better so I am using: Oracle ODBC Driver
The only reference I am using is: Microsoft ActiveX Data Objects 2,5 Library
I have Windows 2000 reading Oracle 8i data.

Note that asynchronous queries worked with DAO so I guess this is an ADO problem not a database problem.

Any ideas where to look ?

Boggg1

 

[ArtieChoke]

I believe you need to use a client-side cursor to use asyncrhonous recordsets. Here's how I do it:

rst.CursorLocation = adUseClient
rst.Properties("Initial Fetch Size") = 1
rst.Open Source, con.Connection, adOpenKeyset, , adAsyncFetch

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[boggg1]

This does not work on my system, it still runs synchronously.

I notice you use adAsyncFetch (rather than adAsyncExecute). Help does not work on these variables on my system and the net has not given me any clues, yet. I got adAsyncExceute from a Microsoft example but it gives an error where adAsyncFetch at least runs (albeit synchronously). Any idea what the difference is ?

Thanks for your help.

Boggg1

 

[boggg1]

Aha,

Itcould be a bug...

http://support.microsoft.com/kb/q224332/

More when I have sorted this out...

Boggg1

 

[boggg1]

No, still no resolution. The bug can be circumvented by using the lines given by ArtieChoke. But they do not work for me, the query runs synchronously. Any more ideas ?

Boggg1

 

[ArtieChoke]

Be aware that while the db server is processing the query and until it returns the first record set, control will not be returned to the program. If you have query that takes a long time to get the collection, it will seem like it's running synchronously. Once the first n fetch size records are returned to the recordset, the rest of them will fill asynchronously.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[boggg1]

ArtieChoke, thanks for returning to help.

I have two questions...

1) How do I detect that the asynchronous part is complete. I have tried...
Do While CommandName.State = adAsyncFetch
but the routine never goes into the "Do" part. CommandName is the ADODB.Command associated withe the connection. Similarly I always get 0 when I put the value CommandName.State in a text box

2) The program "hangs" in an unpredictable way while the data is being retrieved. I saw this as another sign that the query was running synchronously. DAO used to do that until I discovered asynchronous operation. DAO behaved beautifully when I went asynchronous, as if it was truely asynchronous with the program proceding while the query was underway. While it proceded it regularly ran Dummy = DoEvents to ensure response to the user. Apparently DAO instantly ran truely asynchronously but ADO waits for the first fetch until it behaves asynchronously. Is this right ?

This seems an incredible disadvantage of ADO.

Boggg1

 

[boggg1]

I think I'm beginning to sort this. Solution shortly...

Boggg1

 

[boggg1]

The basic problem was that I started the query using rst.Open... but I was checking cmd.State for evidence that it was complete. You have to check rst.State if you start using rst.Open.

Here rst is the recordset and cmd is the related command object.

However, it did not help that I had copied the recordset to a new variable but used the old command object. A self induced problem, I think.

Finally the relevant constants here are...
adStateExecuting = 4 (when the asynchronous query is still underway)
adStateOpen = 1 (when the recordset is still open but not executing)
rstState = 0 (when the recordset is closed).

Thanks again ArtieChoke, both posts helped me in understanding and tracking the problems. Have a star or two.

Boggg1

 

[ArtieChoke]

Glad you worked it out. Thx for the star.

"I think we're all Bozos on this bus!" - Firesign Theatre