Receiving Multiple Result Sets in a C or C Application | Intelligent Enterprises of the 21st Century

Receiving Multiple Result Sets in a C or C++ Application

DB2 CLI has a similar function as the getMoreResults() JDBC method for sequential result set processing. The function is SQLMoreResults(). Additionally, DB2 CLI supports another function called SQLNextResult() that allows you to access and process more than one result set at the same time. Both functions are demonstrated in Figure H.9.

Figure H.9. PrintSalary.cAn example of receiving multiple result sets from the CLI application.

 #include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlcli1.h> #include <sqlca.h> #define MAX_SERVER_LENGTH   10 #define MAX_UID_LENGTH      10 #define MAX_PWD_LENGTH      10 #define MAX_STMT_LENGTH    200 int main(int argc, char *argv[]) {     SQLRETURN     sqlrc = SQL_SUCCESS;     SQLHANDLE     henv;  /* environment handle */     SQLHANDLE     hdbc;  /* connection handle */     SQLHANDLE     hstmt1; /* statement handle */     SQLHANDLE     hstmt2; /* statement handle */     SQLCHAR       server[MAX_SERVER_LENGTH + 1] ;     SQLCHAR       uid[MAX_UID_LENGTH + 1] ;     SQLCHAR       pwd[MAX_PWD_LENGTH + 1] ;     SQLCHAR       stmt[MAX_STMT_LENGTH + 1];     SQLDOUBLE     salary, bonus, comm;     /* allocate an environment handle */     sqlrc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv );     if ( sqlrc != SQL_SUCCESS )         printf( "\n--ERROR while allocating the environment handle.\n" );     /* allocate a database connection handle */     sqlrc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc );     if ( sqlrc != SQL_SUCCESS )         printf( "\n--ERROR while allocating the connection handle.\n" );     /* connect to sample database */     strcpy( (char *)server, "sample" );     strcpy( (char *)uid, "db2admin" );     strcpy( (char *)pwd, "db2admin" );     sqlrc = SQLConnect( hdbc,                         server, SQL_NTS,                         uid,    SQL_NTS,                         pwd,    SQL_NTS                       );     if ( sqlrc != SQL_SUCCESS )         printf( "\n--ERROR while connecting to database.\n" );     /* allocate statement handles */     sqlrc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt1);     if ( sqlrc != SQL_SUCCESS )         printf( "\n--ERROR while allocating the statement handle1.\n" );     sqlrc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt2);     if ( sqlrc != SQL_SUCCESS )         printf( "\n--ERROR while allocating the statement handle2.\n" );     /* calling the SQL procedure */     strcpy( (char *)stmt, "CALL DB2ADMIN.READ_EMP_MULTI()" );     sqlrc = SQLExecDirect( hstmt1, stmt, SQL_NTS );     if ( sqlrc != SQL_SUCCESS )         printf( "\n--ERROR while calling the SQL procedure.\n" );     /* fetch first two result sets */     sqlrc = SQLBindCol(hstmt1, 1, SQL_C_DOUBLE, &salary, 0, NULL);     if ( sqlrc != SQL_SUCCESS )         printf( "\n--ERROR while binding salary column.\n" );     sqlrc = SQLNextResult(hstmt1, hstmt2);                            /*--(1)*/     if ( sqlrc != SQL_SUCCESS )         printf( "\n--ERROR while opening the second result set.\n" );     sqlrc = SQLBindCol(hstmt2, 1, SQL_C_DOUBLE, &bonus, 0, NULL);     if ( sqlrc != SQL_SUCCESS )        printf( "\n--ERROR while binding bonus column.\n" );     sqlrc = SQLFetch(hstmt1);     if ( sqlrc != SQL_SUCCESS && sqlrc != SQL_NO_DATA_FOUND )        printf( "\n--ERROR while fetching the result set.\n" );     sqlrc = SQLFetch(hstmt2);     if ( sqlrc != SQL_SUCCESS && sqlrc != SQL_NO_DATA_FOUND )        printf( "\n--ERROR while fetching the result set.\n" );     while (sqlrc != SQL_NO_DATA_FOUND)     {         printf("%.2f, %.2f\n", salary, bonus);         sqlrc = SQLFetch(hstmt1);         if ( sqlrc != SQL_SUCCESS && sqlrc != SQL_NO_DATA_FOUND )            printf( "\n--ERROR while fetching the result set.\n" );         sqlrc = SQLFetch(hstmt2);         if ( sqlrc != SQL_SUCCESS && sqlrc != SQL_NO_DATA_FOUND )            printf( "\n--ERROR while fetching the result set.\n" );     }     /* fetch the third result set */     sqlrc = SQLMoreResults(hstmt1);                                   /*--(2)*/     sqlrc = SQLBindCol(hstmt1, 1, SQL_C_DOUBLE, &comm, 0, NULL);     if ( sqlrc != SQL_SUCCESS )        printf( "\n--ERROR while binding comm column.\n" );     sqlrc = SQLFetch(hstmt1);     printf("\nThe comissions are:\n");     while (sqlrc != SQL_NO_DATA_FOUND)     {         printf("%.2f\n", comm);         sqlrc = SQLFetch(hstmt1);         if ( sqlrc != SQL_SUCCESS && sqlrc != SQL_NO_DATA_FOUND )            printf( "\n--ERROR while fetching the result set.\n" );     }     /* free the statement handles */     sqlrc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);     if ( sqlrc != SQL_SUCCESS )        printf( "\n--ERROR while freeing the statement handle1.\n" );     sqlrc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);     if ( sqlrc != SQL_SUCCESS )        printf( "\n--ERROR while freeing the statement handle2.\n" );     /* disconnect from the database */     sqlrc = SQLDisconnect( hdbc ) ;     if ( sqlrc != SQL_SUCCESS )        printf( "\n--ERROR while disconnecting from database.\n" );     /* free the connection handle */     sqlrc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;     if ( sqlrc != SQL_SUCCESS )        printf( "\n--ERROR while freeing the connection handle.\n" );     /* free the environment handle */     sqlrc = SQLFreeHandle( SQL_HANDLE_ENV, henv );     if ( sqlrc != SQL_SUCCESS )        printf( "\n--ERROR while freeing the environment handle.\n" );     printf("Completed(0).\n");     return( 0 ); } /* main */

The C/CLI version of the PrintSalary.c program is similar to its Java version. However, because of the SQLNextResult() function, data from different result sets can be accessed at the same time. The example in Figure H.6 prints the salary and bonus columns on the same line.

In order to use SQLNextResult(), you need to declare and allocate an additional statement handle. With the SQLMoreResults() function on Line (2), the first result set is closed to allow the next result set to be accessed. With the SQLNextResult() function on Line (1), the additional statement handle is associated with the next result set without closing the first result set, allowing access to both result sets at the same time. Once the result sets are associated with different statement handles, you can fetch and use the result sets in any order you want.

SQLMoreResults() and SQLNextResult() can be used together in any order. When either function is called, a result set from the SQL procedure is transferred to a statement handle at the client and is removed from the queue of remaining result sets to be processed by that procedure, so you do not have to worry about accessing the same data twice.