ODBC scrollable cursor example
This ODBC program is an example of how a scrollable cursor can be used to move backward and forward through a result set.
/******************************************************************/
/* Include the 'C' include files */
/******************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlcli1.h"
/******************************************************************/
/* Variables */
/******************************************************************/
#ifndef NULL
#define NULL 0
#endif
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHDBC hstmt= SQL_NULL_HSTMT;
SQLRETURN rc = SQL_SUCCESS;
SQLINTEGER i,j,id;
SQLCHAR name[51];
SQLINTEGER namelen, intlen, colcount;
struct sqlca sqlca;
SQLCHAR server[18];
SQLCHAR uid[30];
SQLCHAR pwd[30];
SQLCHAR sqlstmt[500];
SQLINTEGER H1INT4;
SQLCHAR H1CHR10[11];
SQLINTEGER LNH1INT4;
SQLINTEGER LNH1CHR10;
SQLINTEGER output_nts,autocommit,cursor_hold;
// scrollable cursors
#define ROWSET_SIZE 10
SQLUINTEGER numrowsfetched;
SQLUSMALLINT rowStatus[ROWSET_SIZE];
static char ROWSTATVALUE[][26] = { "SQL_ROW_SUCCESS", \
"SQL_ROW_SUCCESS_WITH_INFO", \
"SQL_ROW_ERROR", \
"SQL_ROW_NOROW" };
// column-wise binding
SQLINTEGER SH1INT4[ROWSET_SIZE];
SQLCHAR SH1CHR10[ROWSET_SIZE][11];
SQLINTEGER SLNH1CHR10[ROWSET_SIZE];
SQLRETURN check_error(SQLSMALLINT,SQLHANDLE,SQLRETURN,int,char *);
SQLRETURN print_error(SQLSMALLINT,SQLHANDLE,SQLRETURN,int,char *);
SQLRETURN prt_sqlca(void);
#define CHECK_HANDLE( htype, hndl, rc ) if ( rc != SQL_SUCCESS ) \
{check_error(htype,hndl,rc,__LINE__,__FILE__);goto dberror;}
/******************************************************************/
/* Main Program */
/******************************************************************/
int main()
{
printf("APDLX INITIALIZATION\n");
//*********************************************************************
printf("APDLX SQLAllocHandle-Environment\n");
henv=0;
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );
printf("APDLX-henv=%i\n",henv);
//*********************************************************************
printf("APDLX SQLAllocHandle-Connection\n");
hdbc=0;
rc=SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
printf("APDLX-hdbc=%i\n",hdbc);
//*********************************************************************
printf("APDLX SQLConnect\n");
strcpy((char *)uid,"sysadm");
strcpy((char *)pwd,"sysadm");
strcpy((char *)server,"stlec1"); //uwo setting
rc=SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
printf("APDLX successfully issued a SQLconnect\n");
//*********************************************************************
printf("APDLX SQLAllocHandle-Statement\n");
hstmt=0;
rc=SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX hstmt=%i\n",hstmt);
printf("APDLX successfully issued a SQLAllocStmt\n");
/* Set the number of rows in the rowset */
printf("APDLX SQLSetStmtAttr\n");
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER) ROWSET_SIZE,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Set the cursor type */
printf("APDLX SQLSetStmtAttr\n");
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_STATIC,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Set the pointer to the variable numrowsfetched: */
printf("APDLX SQLSetStmtAttr\n");
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
&numrowsfetched,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Set pointer to the row status array */
printf("APDLX SQLSetStmtAttr\n");
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER) rowStatus,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX SQLExecDirect\n");
strcpy((char *)sqlstmt,"SELECT INT4,CHR10 FROM TABLE2A");
printf("APDLX sqlstmt=%s\n",sqlstmt);
rc=SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
printf("APDLX rc=%i\n",rc);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX SQLColAttributes\n");
colcount=-1;
rc=SQLColAttributes(hstmt,
0,
SQL_COLUMN_COUNT,
NULL,
0,
NULL,
&colcount);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
if(colcount!=2)
{
printf("\nAPDLX colcount=%i\n",colcount);
goto dberror;
}
printf("APDLX SQLBindCol\n");
H1INT4=-1;
LNH1INT4=-1;
rc=SQLBindCol(hstmt,
1,
SQL_C_LONG,
(SQLPOINTER) SH1INT4,
(SQLINTEGER)sizeof(H1INT4),
(SQLINTEGER *) &LNH1INT4);
if( rc != SQL_SUCCESS ) goto dberror;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX SQLBindCol\n");
strcpy(H1CHR10,"garbage");
LNH1CHR10=-1;
rc=SQLBindCol(hstmt,
2,
SQL_C_DEFAULT,
(SQLPOINTER) SH1CHR10,
11,
SLNH1CHR10 );
if( rc != SQL_SUCCESS ) goto dberror;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("\nUse Column-Wise Binding to demonstrate SQLFetchScroll():\n");
printf("\nINT4 CHAR10 \n");
printf("-------- -------------- \n");
printf("APDLX SQLFetchScroll FIRST \n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***\n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s\n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:\n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s\n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll NEXT \n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***\n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s\n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:\n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s\n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll SQL_FETCH_ABSOLUTE 3 \n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 3);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***\n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s\n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:\n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s\n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll SQL_FETCH_RELATIVE -1 \n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***\n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s\n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the R;ow Status Array:\n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s\n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll SQL_FETCH_FIRST again \n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
printf("rc=%d\n", rc);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***\n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %14s\n", SH1INT4[i], SH1CHR10[i]);
}
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:\n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s\n", i, ROWSTATVALUE[rowStatus[i]]);
}
printf("APDLX SQLFetchScroll SQL_FETCH_NEXT to EOF \n");
for (j = 0; j < 2; j++) {
printf("APDLX SQLFetchScroll NEXT \n");
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
printf("rc=%d\n", rc);
/* Indicate how many rows were in the result set. */
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("(%i rows in rowset). ***\n", numrowsfetched);
for (i = 0; i < numrowsfetched; i++)
printf("%8ld %14s\n", SH1INT4[i], SH1CHR10[i]);
/* Output the Row Status Array if the complete rowset was not returned. */
if (numrowsfetched != ROWSET_SIZE) {
printf(" Previous rowset was not full, here is the Row Status Array:\n");
for (i = 0; i < ROWSET_SIZE; i++)
printf(" Row Status Array[%i] = %s\n", i, ROWSTATVALUE[rowStatus[i]]);
}
} // end for
printf("APDLX SQLFreeHandle-Statement\n");
rc=SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
hstmt=0;
printf("APDLX successfully issued a SQLFreeStmt\n");
printf("APDLX SQLEndTran-Commit\n");
rc=SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("APDLX successfully issued a SQLTransact\n");
/******** SQLDisconnect ******************************************/
printf("APDLX SQLDisconnect\n");
rc=SQLDisconnect(hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
printf("APDLX successfully issued a SQLDisconnect\n");
/******** SQLFreeConnect *****************************************/
printf("APDLX SQLFreeHandle-Connection\n");
rc=SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
hdbc=0;;
printf("APDLX successfully issued a SQLFreeConnect\n");
/******** SQLFreeEnv *********************************************/
printf("APDLX SQLFreeHandle-Environment\n");
rc=SQLFreeHandle(SQL_HANDLE_ENV,henv);
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );
henv=0;
printf("APDLX successfully issued a SQLFreeEnv\n");
pgmend:
printf("APDLX pgmend: Ending sample\n");
if (rc==0)
printf("APDLX Execution was SUCCESSFUL\n");
else
{
printf("APDLX***************************\n");
printf("APDLX Execution FAILED\n");
printf("APDLX rc = %i\n", rc );
printf("APDLX ***************************\n");
}
return(rc);
dberror:
printf("APDLX dberror: entry dberror rtn\n");
printf("APDLX dberror: rc=%d\n",rc);
printf("APDLX dberror: environment cleanup attempt\n");
printf("APDLX dberror: cleanup SQLFreeEnv\n");
rc=SQLFreeEnv(henv);
printf("APDLX dberror: cleanup SQLFreeEnv rc =%d\n",rc);
rc=12;
printf("APDLX dberror: setting error rc=%d\n",rc);
goto pgmend;
} /*END MAIN*/
/******************************************************************/
/* check_error */
/******************************************************************/
/* RETCODE values from sqlcli.h ***************************/
/*#define SQL_SUCCESS 0 ***************************/
/*#define SQL_SUCCESS_WITH_INFO 1 ***************************/
/*#define SQL_NO_DATA_FOUND 100 ***************************/
/*#define SQL_NEED_DATA 99 ***************************/
/*#define SQL_NO_DATA SQL_NO_DATA_FOUND **************/
/*#define SQL_STILL_EXECUTING 2 not currently returned*/
/*#define SQL_ERROR -1 ***************************/
/*#define SQL_INVALID_HANDLE -2 ***************************/
/******************************************************************/
SQLRETURN check_error( SQLSMALLINT htype, /* A handle type */
SQLHANDLE hndl, /* A handle */
SQLRETURN frc, /* Return code */
int line, /* Line error issued */
char * file /* file error issued */
) {
SQLCHAR cli_sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER cli_sqlcode;
SQLSMALLINT length;
printf("APDLX entry check_error rtn\n");
switch (frc) {
case SQL_SUCCESS:
break;
case SQL_INVALID_HANDLE:
printf("APDLX check_error> SQL_INVALID HANDLE \n");
break;
case SQL_ERROR:
printf("APDLX check_error> SQL_ERROR\n");
break;
case SQL_SUCCESS_WITH_INFO:
printf("APDLX check_error> SQL_SUCCESS_WITH_INFO\n");
break;
case SQL_NO_DATA_FOUND:
printf("APDLX check_error> SQL_NO_DATA_FOUND\n");
break;
default:
printf("APDLX check_error> Received rc from api rc=%i\n",frc);
break;
} /*end switch*/
print_error(htype,hndl,frc,line,file);
printf("APDLX SQLGetSQLCA\n");
rc = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
if( rc == SQL_SUCCESS )
prt_sqlca();
else
printf("APDLX check_error SQLGetSQLCA failed rc=%i\n",rc);
printf("APDLX exit check_error rtn\n");
return (frc);
} /* end check_error */
/******************************************************************/
/* print_error */
/* calls SQLGetDiagRec()displays SQLSTATE and message */
/******************************************************************/
SQLRETURN print_error( SQLSMALLINT htype, /* A handle type */
SQLHANDLE hndl, /* A handle */
SQLRETURN frc, /* Return code */
int line, /* error from line */
char * file /* error from file */
) {
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1] ;
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1] ;
SQLINTEGER sqlcode ;
SQLSMALLINT length, i ;
SQLRETURN prc;
printf("APDLX entry print_error rtn\n");
printf("APDLX rc=%d reported from file:%s,line:%d ---\n",
frc,
file,
line
) ;
i = 1 ;
while ( SQLGetDiagRec( htype,
hndl,
i,
sqlstate,
&sqlcode,
buffer,
SQL_MAX_MESSAGE_LENGTH + 1,
&length
) == SQL_SUCCESS ) {
printf( "APDLX SQLSTATE: %s\n", sqlstate ) ;
printf( "APDLX Native Error Code: %ld\n", sqlcode ) ;
printf( "APDLX buffer: %s \n", buffer ) ;
i++ ;
}
printf( ">--------------------------------------------------\n" ) ;
printf("APDLX exit print_error rtn\n");
return( SQL_ERROR ) ;
} /* end print_error */
/******************************************************************/
/* prt_sqlca */
/******************************************************************/
SQLRETURN
prt_sqlca()
{
int i;
printf("APDLX entry prt_sqlca rtn\n");
printf("\r\rAPDLX*** Printing the SQLCA:\r");
printf("\nAPDLX SQLCAID .... %s",sqlca.sqlcaid);
printf("\nAPDLX SQLCABC .... %d",sqlca.sqlcabc);
printf("\nAPDLX SQLCODE .... %d",sqlca.sqlcode);
printf("\nAPDLX SQLERRML ... %d",sqlca.sqlerrml);
printf("\nAPDLX SQLERRMC ... %s",sqlca.sqlerrmc);
printf("\nAPDLX SQLERRP ... %s",sqlca.sqlerrp);
for (i = 0; i < 6; i++)
printf("\nAPDLX SQLERRD%d ... %d",i+1,sqlca.sqlerrd??(i??));
for (i = 0; i < 10; i++)
printf("\nAPDLX SQLWARN%d ... %c",i,sqlca.sqlwarn[i]);
printf("\nAPDLX SQLWARNA ... %c",sqlca.sqlwarn[10]);
printf("\nAPDLX SQLSTATE ... %s",sqlca.sqlstate);
printf("\nAPDLX exit prt_sqlca rtn\n");
return(0);
} /* End of prt_sqlca */