Receiving Result Sets in a C or C Application
|
Receiving Result Sets in a C or C++ ApplicationWhen writing a database application using C or C++ to process the result sets from SQL procedures, you have to use DB2 Call Level Interface (CLI) instead of embedded SQL. Figure H.4 performs the equivalent data processing logic as the previous Java sample. Figure H.4. TotalRaise.cAn example of receiving single result set 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 hstmt; /* 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; double raise, totalRaise; double min_raise, max_raise; /* process the input parameters */ if ( argc != 3 ) { printf("Usage: TotalRaise Min_Raise_Percentage Max_Raise_Percentage\n"); printf(" Both percentages have to be integers.\n"); return -1; } min_raise = atoi(argv[1])/100.0; max_raise = atoi(argv[2])/100.0; /* 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 a statement handle */ sqlrc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); /* --(1)*/ if ( sqlrc != SQL_SUCCESS ) printf( "\n--ERROR while allocating the statement handle.\n" ); /* calling the SQL procedure */ strcpy( (char *)stmt, "CALL DB2ADMIN.READ_EMP()" ); sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ); /*--(2)*/ if ( sqlrc != SQL_SUCCESS ) printf( "\n--ERROR while calling the SQL procedure.\n" ); /* bind columns to variables */ sqlrc = SQLBindCol(hstmt, 1, SQL_C_DOUBLE, &salary, 0, NULL); /*--(3)*/ if ( sqlrc != SQL_SUCCESS ) printf( "\n--ERROR while binding salary column.\n" ); sqlrc = SQLBindCol(hstmt, 2, SQL_C_DOUBLE, &bonus, 0, NULL); if ( sqlrc != SQL_SUCCESS ) printf( "\n--ERROR while binding bonus column.\n" ); sqlrc = SQLBindCol(hstmt, 3, SQL_C_DOUBLE, &comm, 0, NULL); if ( sqlrc != SQL_SUCCESS ) printf( "\n--ERROR while binding comm column.\n" ); /* fetch result set returned from SQL procedure */ sqlrc = SQLFetch(hstmt); /*--(4)*/ if ( sqlrc != SQL_SUCCESS && sqlrc != SQL_NO_DATA_FOUND ) printf( "\n--ERROR while fetching the result set.\n" ); totalRaise = 0; while (sqlrc != SQL_NO_DATA_FOUND) { raise = min_raise; /* calculate raise */ if ( bonus >= 600 ) raise += 0.04; if ( comm < 2000 ) raise += 0.03; else if ( comm < 3000 ) raise += 0.02; else raise += 0.01; if ( raise > max_raise ) raise = max_raise; totalRaise += salary * raise; sqlrc = SQLFetch(hstmt); if ( sqlrc != SQL_SUCCESS && sqlrc != SQL_NO_DATA_FOUND ) printf( "\n--ERROR while fetching the result set.\n" ); } printf("The total cost of the raise is: %.2f\n", totalRaise); /* free the statement handle */ sqlrc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); if ( sqlrc != SQL_SUCCESS ) printf( "\n--ERROR while freeing the statement handle.\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 */ In DB2 CLI, you need to allocate a statement handle before you can invoke SQL procedures (or execute any SQL statement, for that matter). The function is SQLAllocHandle() on Line (1) with appropriate parameters. In this case, because the SQL procedure requires no input parameters, you can invoke the procedure by calling the SQLExecDirect() function on Line (2) without going through SQLPrepare() and SQLBindParameter() calls, which would allow you to bind the host variables with input and output parameters. After execution, the result sets are returned through the statement handle. To access the data, you need to bind the host variables to the result set columns using the SQLBindCol() function on Line (3). Another useful function, SQLNumResultCols() (which is not used in our example), allows you to check the number of columns returned by the cursor. This function would be useful if you need to write more dynamic code to deal with cases in which the result set columns are unknown. Once the variables are bound, you can fetch one row at a time using the SQLFetch() function on Line (4) in a while loop. The condition of the loop checks the return code of SQLFetch() against a defined constant SQL_NO_DATA_FOUND. For more information on CLI/ODBC programming, refer to DB2 Call Level Interface Guide and Reference. When finished with the cursor, don't forget to free all your allocated handles. |
|
EAN: 2147483647
Pages: 205