| Team-Fly | | | DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson | Table of Contents | | Chapter 11. ODBC/CLI Programming | Sample Program /* Standard C/C++ Headers */ #include <stdio.h> #include <stdlib.h> #include <string.h> /* DB2 ODBC Header */ #include <sqlcli1.h> /* DB2 SQLCA */ #include <sqlca.h> /* Local Function Definitions */ int ErrorHandler(SQLRETURN rc, char* errstring, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt); /* Main Program */ int main(int argc, char** argv) { /* Handles */ SQLHENV henv = SQL_NULL_HENV; SQLHDBC hdbc = SQL_NULL_HDBC; SQLHSTMT hstmt= SQL_NULL_HSTMT; /* Local variables */ SQLRETURN rc; SQLCHAR statement[32761]; SQLINTEGER totaltaken = 0; SQLINTEGER totaltakenlen = sizeof(totaltaken); SQLCHAR testout[7]; SQLINTEGER testoutlen = 0; SQLCHAR nameout[51]; SQLINTEGER nameoutlen = 0; char* errstring; errstring="Allocate Environment Handle"; rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Allocate Connection Handle"; rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Null Connect"; rc = SQLConnect(hdbc, 0, 0, 0, 0, 0, 0); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Allocate Statement Handle"; rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Insert some data"; strcpy(statement,"INSERT INTO DB2USER1.TEST "); strcat(statement," (NUMBER, NAME, TYPE, LENGTH)"); strcat(statement," VALUES('910','ODBC Test 1','B',90);"); rc = SQLExecDirect(hstmt, statement, SQL_NTS); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Insert more data"; strcpy(statement,"INSERT INTO DB2USER1.TEST "); strcat(statement," (NUMBER, NAME, TYPE, LENGTH)"); strcat(statement," VALUES('912','ODBC Test 2','B',90);"); rc = SQLExecDirect(hstmt, statement, SQL_NTS); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Set up for retrieval"; strcpy(statement,"SELECT NUMBER, NAME"); strcat(statement," FROM DB2USER1.TEST"); strcat(statement," WHERE TOTALTAKEN = ?"); strcat(statement," ORDER BY NUMBER;"); rc = SQLPrepare(hstmt, statement, strlen(statement)); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Input Parameter for Where Clause"; rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, sizeof(totaltaken), 0, &totaltaken, sizeof(totaltaken), &totaltakenlen); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); /* Set Value for Where Clause */ totaltaken = 0; errstring="Run the Statement"; rc = SQLExecute(hstmt); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Result Columns"; rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, testout, sizeof(testout), &testoutlen); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, nameout, sizeof(nameout), &nameoutlen); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Fetch Results"; while((rc = SQLFetch(hstmt)) == SQL_SUCCESS rc == SQL_SUCCESS_WITH_INFO) { /* Print data */ printf("Test %s: %s\n", testout, nameout); } if(rc != SQL_NO_DATA_FOUND) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Close Cursor"; rc = SQLCloseCursor(hstmt); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Free Statement Resources"; rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Free Statement Handle"; rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Disconnect"; rc = SQLDisconnect(hdbc); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Free Connection Handle"; rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Free Environment Handle"; rc = SQLFreeHandle(SQL_HANDLE_ENV, henv); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); return 0; } /* ErrorHandler() */ /* - Print Diagnostics */ int ErrorHandler(SQLRETURN rc, char* errstring, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt) { SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1]; SQLCHAR msqlstate[SQL_SQLSTATE_SIZE + 1]; SQLINTEGER msqlcode; SQLSMALLINT length, i; SQLRETURN prc; struct sqlca sqlca; memset(&sqlca, ' /* Standard C/C++ Headers */ #include <stdio.h> #include <stdlib.h> #include <string.h> /* DB2 ODBC Header */ #include <sqlcli1.h> /* DB2 SQLCA */ #include < sqlca .h> /* Local Function Definitions */ int ErrorHandler(SQLRETURN rc, char* errstring, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt); /* Main Program */ int main(int argc, char** argv) { /* Handles */ SQLHENV henv = SQL_NULL_HENV; SQLHDBC hdbc = SQL_NULL_HDBC; SQLHSTMT hstmt= SQL_NULL_HSTMT; /* Local variables */ SQLRETURN rc; SQLCHAR statement[32761]; SQLINTEGER totaltaken = 0; SQLINTEGER totaltakenlen = sizeof(totaltaken); SQLCHAR testout[7]; SQLINTEGER testoutlen = 0; SQLCHAR nameout[51]; SQLINTEGER nameoutlen = 0; char* errstring; errstring="Allocate Environment Handle"; rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Allocate Connection Handle"; rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Null Connect"; rc = SQLConnect(hdbc, 0, 0, 0, 0, 0, 0); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Allocate Statement Handle"; rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Insert some data"; strcpy(statement,"INSERT INTO DB2USER1.TEST "); strcat(statement," (NUMBER, NAME, TYPE, LENGTH)"); strcat(statement," VALUES('910','ODBC Test 1','B',90);"); rc = SQLExecDirect(hstmt, statement, SQL_NTS); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Insert more data"; strcpy(statement,"INSERT INTO DB2USER1.TEST "); strcat(statement," (NUMBER, NAME , TYPE, LENGTH)"); strcat(statement," VALUES('912','ODBC Test 2','B',90);"); rc = SQLExecDirect(hstmt, statement, SQL_NTS); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Set up for retrieval"; strcpy (statement,"SELECT NUMBER, NAME"); strcat(statement," FROM DB2USER1.TEST"); strcat(statement," WHERE TOTALTAKEN = ?"); strcat(statement," ORDER BY NUMBER;"); rc = SQLPrepare(hstmt, statement, strlen(statement)); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Input Parameter for Where Clause"; rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, sizeof(totaltaken), 0, &totaltaken, sizeof(totaltaken), &totaltakenlen); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); /* Set Value for Where Clause */ totaltaken = 0; errstring="Run the Statement"; rc = SQLExecute(hstmt); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Result Columns"; rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, testout, sizeof(testout), &testoutlen); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, nameout, sizeof(nameout), &nameoutlen); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Fetch Results"; while((rc = SQLFetch(hstmt)) == SQL_SUCCESS rc == SQL_SUCCESS_WITH_INFO) { /* Print data */ printf("Test %s: %s\n", testout, nameout); } if(rc != SQL_NO_DATA_FOUND) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Close Cursor"; rc = SQLCloseCursor(hstmt); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Free Statement Resources"; rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Free Statement Handle"; rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Disconnect"; rc = SQLDisconnect(hdbc); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Free Connection Handle"; rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); errstring="Free Environment Handle"; rc = SQLFreeHandle(SQL_HANDLE_ENV, henv); if(rc != SQL_SUCCESS) ErrorHandler(rc, errstring, henv, hdbc, hstmt); return 0; } /* ErrorHandler() */ /* - Print Diagnostics */ int ErrorHandler(SQLRETURN rc, char* errstring, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt) { SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1]; SQLCHAR msqlstate[SQL_SQLSTATE_SIZE + 1]; SQLINTEGER msqlcode; SQLSMALLINT length, i; SQLRETURN prc; struct sqlca sqlca; memset (&sqlca, '\0', sizeof(sqlca)); printf("\nError: %s\n\n", errstring); /* Environment Messages */ if(henv != SQL_NULL_HENV) { i = 1; while(SQLGetDiagRec(SQL_HANDLE_ENV, henv, i, msqlstate, &msqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS) { printf("Env SQLSTATE: %s\n", msqlstate) ; printf("Env Native Error Code: %ld\n", msqlcode) ; printf("Env buffer: %s \n", buffer) ; i++ ; } } /* Connection Messages */ if(hdbc != SQL_NULL_HDBC) { i = 1; while(SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, i, msqlstate, &msqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS) { printf("Dbc SQLSTATE: %s\n", msqlstate) ; printf("Dbc Native Error Code: %ld\n", msqlcode) ; printf("Dbc buffer: %s \n", buffer) ; i++ ; } } /* Statement Messages */ if(hstmt != SQL_NULL_HSTMT) { i = 1; while(SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, msqlstate, &msqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS) { printf("Stmt SQLSTATE : %s\n", msqlstate) ; printf("Stmt Native Error Code: %ld\n", msqlcode) ; printf("Stmt buffer: %s \n", buffer) ; i++ ; } } if(SQLGetSQLCA(henv, hdbc, hstmt, &sqlca) == SQL_SUCCESS) { printf("SQLCA SQLCODE = %d\n", SQLCODE); } if(rc != SQL_SUCCESS_WITH_INFO) { abort(); } return 0; } ', sizeof(sqlca)); printf("\nError: %s\n\n", errstring); /* Environment Messages */ if(henv != SQL_NULL_HENV) { i = 1; while(SQLGetDiagRec(SQL_HANDLE_ENV, henv, i, msqlstate, &msqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS) { printf("Env SQLSTATE: %s\n", msqlstate) ; printf("Env Native Error Code: %ld\n", msqlcode) ; printf("Env buffer: %s \n", buffer) ; i++ ; } } /* Connection Messages */ if(hdbc != SQL_NULL_HDBC) { i = 1; while(SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, i, msqlstate, &msqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS) { printf("Dbc SQLSTATE: %s\n", msqlstate) ; printf("Dbc Native Error Code: %ld\n", msqlcode) ; printf("Dbc buffer: %s \n", buffer) ; i++ ; } } /* Statement Messages */ if(hstmt != SQL_NULL_HSTMT) { i = 1; while(SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, msqlstate, &msqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS) { printf("Stmt SQLSTATE: %s\n", msqlstate) ; printf("Stmt Native Error Code: %ld\n", msqlcode) ; printf("Stmt buffer: %s \n", buffer) ; i++ ; } } if(SQLGetSQLCA(henv, hdbc, hstmt, &sqlca) == SQL_SUCCESS) { printf("SQLCA SQLCODE = %d\n", SQLCODE); } if(rc != SQL_SUCCESS_WITH_INFO) { abort(); } return 0; } |