/**********************************************************************
* FILENAME :	AsyncExecution.c
*
* DESCRIPTION :
*       	Example shows use of parameter array but with Asyncronous
*               operation on SQLExecDirect()
*
* ODBC USAGE :
*		NOTE For SELECT statements, the SQL_PARAM_ARRAY_SELECTS
*               option indicates whether a result set is available for
*               each set of parameters (SQL_PAS_BATCH) or whether only
*               one result set is available (SQL_PAS_NO_BATCH). In the
*               case of SQL_PAS_BATCH, the SELECT is split up into a
*               number of selects, on per parameter set.
*
* Uses TestTBL1,
*
*               PersonID int NOT NULL IDENTITY(1,1),
*               FirstName varchar(255) NOT NULL,
*               LastName varchar(255),
*               Address varchar(255),
*               City varchar(255)
*/

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"



#define PARAM_ARRAY_SIZE 30

#define FIRSTNAME_LEN 256
#define LASTNAME_LEN 256
#define ADDRESS_LEN 256
#define CITY_LEN 256

int main () {

    SQLHENV   henv  = SQL_NULL_HENV;   // Environment
    SQLHDBC   hdbc  = SQL_NULL_HDBC;   // Connection handle
    SQLHSTMT  hstmt = SQL_NULL_HSTMT;  // Statement handle
    SQLRETURN retcode, AsyncRetCode;

    // Select statements to return person details from TestTBL1
    SQLCHAR *stmt = "SELECT PersonID, FirstName, LastName, Address, City FROM TestTBL1 WHERE PersonID=?";

    // Array of person IDs to get list of all Record IDs for
    SQLINTEGER  PersonIDs[] = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,
                                            20,21,22,23,24,25,26,27,28,29,30};

    // Column binding variables
    SQLLEN  PersonID;
    SQLLEN  PersonID_l;
    SQLCHAR FirstName [FIRSTNAME_LEN];
    SQLLEN  FirstName_l;
    SQLCHAR LastName [LASTNAME_LEN];
    SQLLEN  LastName_l;
    SQLCHAR Address [ADDRESS_LEN];
    SQLLEN  Address_l;
    SQLCHAR City [CITY_LEN];
    SQLLEN  City_l;

    int i;

    SQLUSMALLINT ParamStatusArray[PARAM_ARRAY_SIZE];
    SQLLEN       ParamsProcessed=0;

    // Allocate the ODBC environment and save handle.
    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)", henv,
                SQL_HANDLE_ENV);

    // Notify ODBC that this is an ODBC 3.0 app.
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (SQLPOINTER) SQL_OV_ODBC3, 0);
    CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_HANDLE_ENV)", henv,
                SQL_HANDLE_ENV);

    // Allocate ODBC connection handle and connect.
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)", hdbc,
                SQL_HANDLE_DBC);

    // Connect to DATASOURCE
    retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;",
                             SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE)", hdbc,
                SQL_HANDLE_DBC);

    // Allocate statement handle.
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)", hstmt,
                SQL_HANDLE_STMT);

    // Set parameter set size, status array and params processed pointers
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMSET_SIZE,
                              (SQLPOINTER) PARAM_ARRAY_SIZE, 0);
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAM_STATUS_PTR,
                              ParamStatusArray, PARAM_ARRAY_SIZE);
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR,
                              &ParamsProcessed, 0);

    // Enable Async Operation - if ASYNC is not allowed, SQLSetStmtAttr
    // will return an error
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_ASYNC_ENABLE,
                              (SQLPOINTER) SQL_ASYNC_ENABLE_ON, 0);
    CHECK_ERROR(retcode, "SQLSetStmtAttr(SQL_ATTR_ASYNC_ENABLE)",
                hstmt, SQL_HANDLE_STMT);

    // Bind array values of parameter 1
    retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG,
                               SQL_INTEGER, 0, 0, PersonIDs, 0, NULL);

    /* Bind columns to the Record_num Field of the first row in the array */
    retcode = SQLBindCol(hstmt, 1, SQL_C_LONG,   &PersonID, 0,
                         &PersonID_l);
    retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR,   FirstName, FIRSTNAME_LEN,
                         &FirstName_l);
    retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR,   LastName,  LASTNAME_LEN,
                         &LastName_l);
    retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR,   Address,   ADDRESS_LEN,
                         &Address_l);
    retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR,   City,      CITY_LEN,
                         &City_l);

    // Execute SELECT whilst status SQL_STILL_EXECUTING
    retcode = SQLExecDirect (hstmt, stmt, SQL_NTS);
	while (retcode==SQL_STILL_EXECUTING) {
		printf ("\nStill executing ...");
		printf ("\nSnooze ..");
		sleep(1);
		printf ("\nAwake ..");
		retcode = SQLExecDirect (hstmt, stmt, SQL_NTS);
	}
    CHECK_ERROR(retcode, "SQLExecDirect(SQL_HANDLE_STMT)", hstmt,
                SQL_HANDLE_STMT);

    // Use SQLFetch and SQLMoreResults to retrieve the records.
    do {
        retcode = SQLFetch (hstmt);
        if (retcode != SQL_NO_DATA) {
            printf ("\n  - %i, %.10s %.10s %.10s %.10s",
                    (int) PersonID, FirstName, LastName, Address, City);
        }
        // SQLMoreResults decides whether there are any more array params to
        // process
    } while (SQLMoreResults(hstmt) == SQL_SUCCESS);

exit:

    printf ("\nComplete.\n");

    // Free handles
    // Statement
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.