Click here to Skip to main content
Click here to Skip to main content

Asynchronous Query Execution

, 8 Dec 1999
Rate this:
Please Sign up or sign in to vote.
Two classes that provide support for asynchronous SQL execution.
  • Download source files - 7 Kb
  • <!-- Article Starts -->

    Introduction

    Because of multithreading support is available, the MFC ODBC classes no longer use asynchronous processing. By default, drivers execute ODBC functions synchronously; that is, the application calls a function and the driver does not return control to the application until it has finished executing the function. However, some functions can be executed asynchronously; that is, the application calls the function, and the driver, after minimal processing, returns control to the application. The application can then call other functions while the first function is still executing.

    Asynchronous execution is supported for most functions that are largely executed on the data source, such as the functions to prepare and execute SQL statements, retrieve metadata, and fetch data. It is most useful when the task being executed on the data source takes a long time, such as a complex query against a large database.

    In MFC version 4.2 and higher, CDatabase::SetSynchronousMode() function which allows asynchronous execution has become obsolete. The MFC ODBC classes now use only synchronous processing.

    Two classes CAsyncDatabase and CAsyncRecordset provide support for asynchronous execution. This allows an application perform other tasks while a complex query is being executed.

    Usage is very simple. Copy CAsyncDatabase.* and CAsyncRecordset.* files into the project. Do not forget

    #include "AsyncDatabase.h"
    #include "AsyncRecordset.h"
    

    Examine the following samples:

    For CAsyncDatabase:

    • Open database as usual;
    • Call ExecuteSQLAsync(<sql_query_text_here>);
    • Call CAsyncDatabase's SQLStillExecuting() to determine whether the query is still executing;
    • Call CDatabase's Cancel() function with the HSTMT returned by ExecuteSQLAsync() to cancel the query execution.

    CAsyncDatabase Sample

    // asynchronous query execution
    void ExecuteLongQuery(strQuery)
    {
    	CAsyncDatabase DB;
    	DB.OpenEx("");
    
    	HSTMT hstmt = DB.ExecuteSQLAsync(strQuery);	// complex query
    	while(DB.SQLStillExecuting(hstmt))
    	{
    		// check for Cancel command, for exmpl
    		_GET_MESSAGE_
    		if(Cancelled()) 	// by Cancel button, for example
    		{
    			DB.Cancel(hstmt);
    			return;
    		}
    	}
    }
    

    For CAsyncRecordset:

    • Open recordset using OpenAsync(...). (parameters are the same as for Open());
    • Call CAsyncRecordset's StillExecuting() to determine whether the query is still executing;
    • Call CRecordset's Cancel() function to cancel the recordset opening (i.e. SELECT query execution).

    CAsyncRecordset Sample

    // Open Asynchronous Recordset
    {
    	// DB is a CDatabase
    	CAsyncRecordset rs(&DB);
    
    	rs.OpenAsync(CRecordset::snapshot, strQuery, CRecordset::executeDirect);
    	
    	while(rs.StillExecuting())
    	{
    		_GET_MESSAGE_
    		if(Cancelled())
    		{
    			rs.Cancel();
    			rs.Close();
    			return;
    		}
    	}
    	
    	if(!rs.IsOpen())
    		return;
    	else
    		ContinueTask();	// perform some task
    	rs.Close();
    }
    

    A Few Words About OnSetOptions() Virtual

    The framework calls this member function to set initial options for the recordset or database. CRecordset and CDatabase's OnSetOptions() determines the data source's support for scrollable cursors and for cursor concurrency and sets the recordset's options accordingly.

    You can override OnSetOptions() to set additional options specific to the driver or the data source. For example, if your data source supports opening for exclusive access, you might override OnSetOptions() to take advantage of that ability.

    CAsyncDatabase and CAsyncRecordset classes use OnSetOptions() to determine the data source's support for asynchronous function execution and set options accordingly. (If data source does not support asynchronous execution both CAsyncDatabase and CAsyncRecordset execute functions synchronously without warning user.) So when You override OnSetOptions() be sure you call CAsyncDatabase::OnSetOptions() or CAsyncRecordset::OnSetOptions() from derived class OnSetOptions().

    Note:According to MFC documentation asynchronous processing slows performance.

    License

    This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

    A list of licenses authors might use can be found here

    Share

    About the Author

    Vachik Hovhannisyan

    United States United States
    No Biography provided

    Comments and Discussions

     
    GeneralAsynchronous Query execution in .Net Pingroupelizas8-Feb-10 2:43 
    GeneralDynamicRecordset.h PinmemberDo Manh Hung1-Apr-03 17:19 
    GeneralRe: DynamicRecordset.h PinmemberRolando E. Cruz-Marshall23-Dec-08 8:38 
    GeneralAsynchronous query execution vs SQLCancel PinmemberTom Tempelaere, aka TiTi3-Sep-01 2:00 
    OK, your CAsyncRecordset is very nice. The method you used is correct, but there is one flaw: canceling the query. The method CRecordset::Cancel() is not enough. If you cancel the query like that, and you try to open another CRecordset (or CAsyncRecordset) using the same connection (eg a CDatabase object), it would raise an exception telling that the connection is still busy processing the results of the query that was previously canceled. So what went wrong during CRecordset::Cancel()?
     
    Well, to find the solution take a look at the following VC++ documentation pages:
    1.SQLSetStmtAttr, option -> SQL_ATTR_ASYNC_ENABLE (ODBC API)
    2.SQLCancel (ODBC API)
    Let's look at the relevant text in these documents:
     
    The first (1) states the following:
    "Once a function has been called asynchronously, only the original function, SQLCancel, SQLGetDiagField, or SQLGetDiagRec can be called on the statement, and only the original function, SQLAllocHandle (with a HandleType of SQL_HANDLE_STMT), SQLGetDiagField, SQLGetDiagRec, or SQLGetFunctions can be called on the connection associated with the statement, until the original function returns a code other than SQL_STILL_EXECUTING. Any other function called on the statement or the connection associated with the statement returns SQL_ERROR with an SQLSTATE of HY010 (Function sequence error)"
     
    The second (2) states the following:
    "After any call to the function that returns SQL_STILL_EXECUTING, an application can call SQLCancel to cancel the function. If the cancel request is successful, the driver returns SQL_SUCCESS. This message does not indicate that the function was actually canceled; it indicates that the cancel request was processed. When or if the function is actually canceled is driver-dependent and data source – dependent. The application must continue to call the original function until the return code is not SQL_STILL_EXECUTING. If the function was successfully canceled, the return code is SQL_ERROR and SQLSTATE HY008 (Operation canceled). If the function completed its normal processing, the return code is SQL_SUCCESS or SQL_SUCCESS_WITH_INFO if the function succeeded or SQL_ERROR and a SQLSTATE other than HY008 (Operation canceled) if the function failed"
     
    OK, put together we know what the problem is; after calling SQLCancel on the statement (CRecordset::m_hstmt), we should execute the same function that was canceled until it returns something other than SQL_STILL_EXECUTING; only then is the query actually canceled, and can the query be closed.
    Sadly, the CRecordset::Cancel() method is non-virtual. You could handle this in CRecordset::Close() which is, but to be sure I wrote an extra method CancelPendingQuery, which is written like:
     
    void CMyRecSet::CancelPendingQuery()
    {
    ASSERT_VALID(this);
    ASSERT(m_hstmt!=SQL_NULL_HSTMT);
    if(StillExecuting())
    if(Check(::SQLCancel(m_hstmt))
    {
    USES_CONVERSION;
    LPCSTR lpszWSQL = T2CA(m_strSQL);
    while(::SQLExecDirect(m_hstmt,(UCHAR*)lpszWSQL,SQL_NTS)==
    SQL_STILL_EXECUTING)
    ::Sleep(5); // poll every 5 ms.
    }
    }
     
    TiTi

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

    | Advertise | Privacy | Mobile
    Web02 | 2.8.140827.1 | Last Updated 9 Dec 1999
    Article Copyright 1999 by Vachik Hovhannisyan
    Everything else Copyright © CodeProject, 1999-2014
    Terms of Service
    Layout: fixed | fluid