Sample Program

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; }

Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net