none
ADO program not able to run Asynchronously

    Question

  • Hi,

    I have an Real-Time application written in C++ (VS 2008) that must not wait around to finish the execution of a Stored Procedure executing Synchronously via ADO. So i have been trying to modify my application to use ADO's Asynchronous calls so that i can prepare the next piece of information and send that as soon as it is ready. I want this so i do not need to buffer or cause any delays, as the code is fast enough to send to the Database again before the previous send has completed.

    However, whenever i try to use the adAsyncExecute i  do not get the raw_ExecuteComplete event. I only see this when i close the connection, which what i think is wrong as ity is not the same without the adAsyncExecute option.

    Also when i run the program i get one of the following errors:

    • Operation cannot be performed while connecting asynchronously.
    • The connection cannot be used to perform this operation. It is either closed or invalid in this context.

    I have search around on the Web and through ADO Guide (which i found only in the MDAC 2.8 SDK), but have found related information but absolutely no answers to make it work Asynchonously.

    So can anyone please shed any light at all for me to successfully work with ADO Asynchronously.

    Many thanks!

    Rich.

    Wednesday, February 08, 2012 5:07 AM

All replies

  • Hi,

    I have an Real-Time application written in C++ (VS 2008) that must not wait around to finish the execution of a Stored Procedure executing Synchronously via ADO. So i have been trying to modify my application to use ADO's Asynchronous calls so that i can prepare the next piece of information and send that as soon as it is ready. I want this so i do not need to buffer or cause any delays, as the code is fast enough to send to the Database again before the previous send has completed.

    However, whenever i try to use the adAsyncExecute i  do not get the raw_ExecuteComplete event. I only see this when i close the connection, which what i think is wrong as ity is not the same without the adAsyncExecute option.

    Also when i run the program i get one of the following errors:

    • Operation cannot be performed while connecting asynchronously.
    • The connection cannot be used to perform this operation. It is either closed or invalid in this context.

    I have search around on the Web and through ADO Guide (which i found only in the MDAC 2.8 SDK), but have found related information but absolutely no answers to make it work Asynchonously.

    So can anyone please shed any light at all for me to successfully work with ADO Asynchronously.

    Many thanks!

    Rich.

    You should look at "Open Method (ADO Recordset)":

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms675544(v=vs.85).aspx   

    If you have set the CursorLocation property to adUseClient, you can retrieve rows asynchronously in one of two ways. The recommended method is to set Optionsto adAsyncFetch. Alternatively, you can use the "Asynchronous Rowset Processing" dynamic property in the Properties collection, but related retrieved events can be lost if you do not set the Options parameter to adAsyncFetch.

    See also http://support.microsoft.com/kb/268797/e


    • Edited by Sergey Chepurin Wednesday, February 08, 2012 5:58 AM
    • Proposed as answer by Helen Zhao Wednesday, February 15, 2012 1:57 AM
    Wednesday, February 08, 2012 5:57 AM
  • Hi Sergey,

    Thanks for your fast response, i have just checked your links and i see that they are for a recordset.

    In my application i am only inserting which is why i want to do this without blocking in any way. So because i am only inserting, i should not be returning any kind of recordset.

    Is this the correct way to work ADO, if i only want to Insert and not wait in the code until SQL completes?

    Thanks,

    Rich.

    Wednesday, February 08, 2012 7:01 AM
  • Hi Sergey,

    Thanks for your fast response, i have just checked your links and i see that they are for a recordset.

    In my application i am only inserting which is why i want to do this without blocking in any way. So because i am only inserting, i should not be returning any kind of recordset.

    Is this the correct way to work ADO, if i only want to Insert and not wait in the code until SQL completes?

    Thanks,

    Rich.

    MSDN says (http://msdn.microsoft.com/en-us/library/windows/desktop/ms675544(v=vs.85).aspx): To perform a query that does not return records, such as a SQL INSERT statement, call the Execute method of a Command object or the Execute method of a Connection object instead.

    Guess, you should also look at this link "ADO, adAsyncExecute...":

    http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/68e23681-f6b5-4ed5-b963-e63e34eeac2f

    Wednesday, February 08, 2012 11:41 AM
  • Hi,

    Actually i have already been over it, but only stopping it returning any records does not make it happen any faster as it will still block until SQL is finished. Also because i am inserting i dont have any rows to return and i dont think opening a recordset is the correct approach?

    These last few days i have been trying to get the adAsyncExecute to work, but this Async just does not want to work me or the ADO documentation does not cover it properly. I have properly read on how to setup an Async command, but not found enough on how to handle one? I have even tried implementing the ADO Events to receive notification of when the Async command has finished, because i need to perform some cleanup of the used parameters to a stored proc before reusing the command object for another SQL call to the same stored proc. I found that if i dont do this cleanup, the parameters will double up for the next insert and the stored proc will fail to insert anyway.

    This seems like my only option so far, unless you or anyone can suggest something else?

    Thanks.

    Wednesday, February 08, 2012 2:27 PM
  • Hi,

    Actually i have already been over it, but only stopping it returning any records does not make it happen any faster as it will still block until SQL is finished. Also because i am inserting i dont have any rows to return and i dont think opening a recordset is the correct approach?

    These last few days i have been trying to get the adAsyncExecute to work, but this Async just does not want to work me or the ADO documentation does not cover it properly. I have properly read on how to setup an Async command, but not found enough on how to handle one? I have even tried implementing the ADO Events to receive notification of when the Async command has finished, because i need to perform some cleanup of the used parameters to a stored proc before reusing the command object for another SQL call to the same stored proc. I found that if i dont do this cleanup, the parameters will double up for the next insert and the stored proc will fail to insert anyway.

    This seems like my only option so far, unless you or anyone can suggest something else?

    Thanks.

    I am not sure if i understand you correctly. The ADO was developed to make database programmers' life much easier at the time, introducing objects and methods for database access operations. If MSDN says that To perform a query that does not return records, such as a SQL INSERT statement, call the Execute method of a Command object or the Execute method of a Connection object instead. - i don't think you can do much about it. This is a price you pay for "easy way" to do something. If you want more freedom and productivity, you probably should consider ODBC, which is more difficult to program (and that is why ADO was introduced in the first place). 
    Wednesday, February 08, 2012 3:01 PM
  • Yes you may be right, but how do you interpret the MS Documentation regarding an Asynchronous call? Also for that matter, i have not seen anyone on the web who says that they have successfully done it with ADO.

    ADO.NET 2.0 is a different matter though as i have already proven that it works.

    So i think the ADO library should be able to do it, its just knowing how it should be done and this is what i am stuck on. Do you know how to process an Asynchronous call in ADO?

    Thanks.

    Wednesday, February 08, 2012 4:44 PM
  • Yes you may be right, but how do you interpret the MS Documentation regarding an Asynchronous call? Also for that matter, i have not seen anyone on the web who says that they have successfully done it with ADO.

    ADO.NET 2.0 is a different matter though as i have already proven that it works.

    So i think the ADO library should be able to do it, its just knowing how it should be done and this is what i am stuck on. Do you know how to process an Asynchronous call in ADO?

    Thanks

    I don't know what to interpret in documentation. The best way to interpret programming documentation is to implement it in code. MSDN documentation says about everything what you can do using ADO objects and methods (and that is not so much). There are also issues with some ADO methods as pointed in this thread, for example -  http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/68e23681-f6b5-4ed5-b963-e63e34eeac2f

    Other than that, i think you can search on Internet. Hope that someone here will help you more.

     

    Wednesday, February 08, 2012 5:00 PM
  • Hi,

    I have an Real-Time application written in C++ (VS 2008) that must not wait around to finish the execution of a Stored Procedure executing Synchronously via ADO. So i have been trying to modify my application to use ADO's Asynchronous calls so that i can prepare the next piece of information and send that as soon as it is ready. I want this so i do not need to buffer or cause any delays, as the code is fast enough to send to the Database again before the previous send has completed.

    However, whenever i try to use the adAsyncExecute i  do not get the raw_ExecuteComplete event. I only see this when i close the connection, which what i think is wrong as ity is not the same without the adAsyncExecute option.

    Also when i run the program i get one of the following errors:

    • Operation cannot be performed while connecting asynchronously.
    • The connection cannot be used to perform this operation. It is either closed or invalid in this context.

    I have search around on the Web and through ADO Guide (which i found only in the MDAC 2.8 SDK), but have found related information but absolutely no answers to make it work Asynchonously.

    So can anyone please shed any light at all for me to successfully work with ADO Asynchronously.

    Many thanks!

    Rich.

    ADO is apartment-threaded. First thing you need to make sure is that you obey apartment rules. That means that you can't just take interface pointer (I am imagining ICommand*) and use it cross-thread at will. I used global interface table to share ADO objects cross-thread with success.

    If you do have these issues sorted, then... Indeed, you still can't call an ADO from more than one thread at a time. I am not sure why, but I am guessing that ADO spins a message loop while executing, and so COM is able to enter into it's apartment and call an ADO object, but ADO is not made re-entrant.

    I see three ways out:

    • use OLEDB
    • create ADO connections / commands per thread. First connect is expensive, rest, less so ;-)
    • serialize access to ADO objects (I did that, it worked). I guess that's not acceptable for you.

    HTH.

    Wednesday, February 08, 2012 5:47 PM
  • Hi Goran,

    Yes i have heard that ADO is apartment-threaded, but i am not trying to do any multi-threading currently. I was using a worker thread previously which offset the job of all the SQL Inserts to this worker thread where some buffering was taking place, but the buffering was flawed and causing unacceptable delays. So the design was properly re-thought and the buffering was simply a band-aid to fixing the real issue which was to Insert into SQL as fast as the data arrived, thus shifting any kind of buffering over to SQL where it should be.

    I hear your suggestion about using OLEDB as i did read about some Async commands with this library.

    Could you explain more about serializing access to ADO objects? This sounds like more threads with some control to limit access.

    Basically i am interested in any concept that will achieve better throughput than a single process/thread while Inserting Sychronously. Only that i prefer to keep everything simple and thought that the Asynchronous approach would be the best fix. Especially when i had ADO.NET (in C#) working well when in Async mode.

    Thanks.

    Wednesday, February 08, 2012 6:40 PM
  • Hi Goran,

    Yes i have heard that ADO is apartment-threaded, but i am not trying to do any multi-threading currently. I was using a worker thread previously which offset the job of all the SQL Inserts to this worker thread where some buffering was taking place, but the buffering was flawed and causing unacceptable delays. So the design was properly re-thought and the buffering was simply a band-aid to fixing the real issue which was to Insert into SQL as fast as the data arrived, thus shifting any kind of buffering over to SQL where it should be.

    I hear your suggestion about using OLEDB as i did read about some Async commands with this library.

    Could you explain more about serializing access to ADO objects? This sounds like more threads with some control to limit access.

    Yes. I had a connection and a bunch of commands to run, all in one apartment. I was accessing them from my own MTA (that's where GIT played). Basically, I used a mutex (critical section in windows parlance) to make sure that I don't attack any ADO objects concurrently.

    HTH.

    • Proposed as answer by Helen Zhao Wednesday, February 15, 2012 1:57 AM
    Thursday, February 09, 2012 11:32 AM