Easysoft ODBC-SQL Server Driver

How do I create a threaded ODBC application that runs a SQL Server workload in parallel?

Article:
01067
Last Reviewed:
9th April 2014
Revision:
1

The following application creates 256 threads. Each thread connects to a SQL Server data source and executes a stored procedure.

We used this sample when testing the in-memory tables feature intoduced in SQL Server 2014, and the procedure the sample uses is created when Microsoft's In-Memory OLTP Sample is run. To control whether the procedure inserts records into an on-disk (0) or in-memory table (1), change the value of the procedure's second parameter.

#include <stdio.h>
#include <pthread.h>
#include <unistd.h>

#include <sql.h>
#include <sqlext.h>

#define THREAD_COUNT    256

void *runRequest(void *arg);

SQLHENV hEnv = NULL;

int main() {
    int i;
    int numSize = THREAD_COUNT;
    pthread_t thrArray[ THREAD_COUNT ];

    printf("Starting test...");

    SQLRETURN rcSql;

    rcSql = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);

    if (hEnv == NULL || rcSql != SQL_SUCCESS) {
        printf("SQLAllocHandle hEnv error\n");
        return -1;
    }

    rcSql = SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0 );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLSetEnvAttr hEnv error\n");
        return -1;
    }

    for ( i = 0; i != numSize; i++) {
        pthread_create(&thrArray[i], NULL, &runRequest, i );
    }

    for ( i = 0; i != numSize; i++) {
        pthread_join(thrArray[i], NULL);
    }

    rcSql = SQLFreeHandle( SQL_HANDLE_ENV, hEnv );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLFreeHandle hEnv error\n");
        return -1;
    }

    printf("Finished test\n");

}

void *runRequest(void *arg) {

    SQLHDBC hCon = NULL;
    SQLHSTMT hStmt = NULL;
    SQLRETURN rcSql;

    // Errors
    SQLCHAR    retMessage[ 256 ];
    SQLCHAR    retState[ 7 ];
    SQLINTEGER retNativeErrNum;
    SQLSMALLINT retMessageLen;
    SQLINTEGER val1, val2;
    SQLLEN len1, len2;

    rcSql = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hCon );
    if (rcSql != SQL_SUCCESS) {

        rcSql = SQLGetDiagRec( SQL_HANDLE_DBC, hCon, 0, retState,
&retNativeErrNum, retMessage, sizeof(retMessage), &retMessageLen );

        printf("SQLAllocHandle hCon error : %s \n", retMessage );
        return NULL;
    }


    // Replace this value with a SQL Server ODBC driver DSN that connects
    // to a SQL Server 2014 Enterprise instance in which the
    // SQL Server 2014 RTM In-Memory OLTP Sample has been created.
    // (http://msftdbprodsamples.codeplex.com/releases/view/114491)
    const char *myDSN = "SQLSERVER_2014_ENTERPRISE_DSN";

    // Replace these values with a valid user name and password
    // for this instance.
    const char *myUser = "sa";
    const char *myPassword = "password" ;

    rcSql = SQLConnect( hCon,
                        ( SQLCHAR * ) myDSN, SQL_NTS,
                        ( SQLCHAR * ) myUser, SQL_NTS,
                        ( SQLCHAR * ) myPassword, SQL_NTS );

    if ( !SQL_SUCCEEDED(rcSql)) {

        rcSql = SQLGetDiagRec( SQL_HANDLE_DBC, hCon, 1, retState,
&retNativeErrNum, retMessage, sizeof(retMessage), &retMessageLen );

        rcSql = SQLFreeHandle( SQL_HANDLE_DBC, hCon );

        printf("SQLConnect hCon error %s\n", retMessage );
        return NULL;
    }

    rcSql = SQLAllocHandle( SQL_HANDLE_STMT, hCon, &hStmt );
    if (rcSql != SQL_SUCCESS) {

        rcSql = SQLDisconnect( hCon );
        rcSql = SQLFreeHandle( SQL_HANDLE_DBC, hCon );
        printf("SQLAllocHandle hStmt error\n");
        return NULL;
    }

    // This procedure is created when you install the SQL Server 2014 RTM In-Memory OLTP Sample.
    rcSql = SQLPrepare( hStmt, "{call Demo.usp_DemoInsertSalesOrders (?, ?)}", SQL_NTS );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLPrepare hStmt error\n");
        return NULL;
    }

    rcSql = SQLBindParameter( hStmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &val1, 0, &len1 );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLBindParameter hStmt error\n");
        return NULL;
    }
    rcSql = SQLBindParameter( hStmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &val2, 0, &len2 );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLBindParameter hStmt error\n");
        return NULL;
    }

    len1 = len2 = sizeof( SQLINTEGER );

    // Whether to insert records into an on-disk (0) or in-memory (1) version of the
    // SalesOrderDetail table.
    val1 = 0;

    // The numbers of orders to insert.
    val2 = 100;

    rcSql = SQLExecute( hStmt );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLExecute hStmt error\n");
        return NULL;
    }

    rcSql = SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLFreeHandle hCon error\n");
        return NULL;
    }

    rcSql = SQLDisconnect( hCon );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLDisconnect hCon error \n");
        return NULL;
    }

    rcSql = SQLFreeHandle( SQL_HANDLE_DBC, hCon );
    if (rcSql != SQL_SUCCESS) {
        printf("SQLFreeHandle hCon error\n");
        return NULL;
    }

    pthread_exit(NULL);

    return NULL;
}
Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)