/*******************************************************************************
**
** Source File Name = tbread.c 1.7
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** Shows how to read tables.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "utilcli.h" /* Header file for CLI sample code */
/* methods to perform select */
int TbBasicSelectUsingFetchAndBindCol( SQLHANDLE) ;
int TbBasicSelectUsingFetchAndGetData( SQLHANDLE) ;
int SysTbSelect( SQLHANDLE) ;
int TbSelectWithParam( SQLHANDLE) ;
int TbSelectWithUnknownOutCols( SQLHANDLE) ;
#define ROWSET_SIZE 5
int TbSelectUsingFetchScrollColWise( SQLHANDLE) ;
int TbSelectUsingFetchScrollRowWise( SQLHANDLE) ;
int TbSelectUsingSQLBulkOperations( SQLHANDLE) ;
/* types of select */
int TbSubSelect( SQLHANDLE) ;
int TbJoinSelect( SQLHANDLE) ;
int TbGroupingSetsSelect( SQLHANDLE) ;
int TbCubeSelect( SQLHANDLE) ;
int TbRollupSelect( SQLHANDLE) ;
int TbRecursiveSelect( SQLHANDLE) ;
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] )
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handles */
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
if ( rc != 0 ) return( rc ) ;
printf("\n\nTABLES: HOW TO READ TABLES.\n");
/* initialize the CLI application */
rc = CLIAppInit( dbAlias, user, pswd, &henv, &hdbc,
(SQLPOINTER)SQL_AUTOCOMMIT_ON);
if ( rc != 0 ) return( rc ) ;
/*****************************************************************/
printf("\n****** METHODS TO PERFORM SELECT *******************\n");
/*****************************************************************/
/* two basic selects */
rc = TbBasicSelectUsingFetchAndBindCol( hdbc) ;
rc = TbBasicSelectUsingFetchAndGetData( hdbc) ;
/* select on system tables */
rc = SysTbSelect( hdbc) ;
/* select with parameters */
rc = TbSelectWithParam( hdbc) ;
/* select with unknown output columns */
rc = TbSelectWithUnknownOutCols( hdbc) ;
/* select using SQLFetchScroll with column-wise binding */
rc = TbSelectUsingFetchScrollColWise( hdbc) ;
/* select using SQLFetchScroll with row-wise binding */
rc = TbSelectUsingFetchScrollRowWise( hdbc) ;
/* select using SQLBulkOperations */
/*
rc = TbSelectUsingSQLBulkOperations( hdbc) ;
*/
/*****************************************************************/
printf("\n****** TYPES OF SELECT *****************************\n");
/*****************************************************************/
/* complete subselect */
/* rc = TbSubSelect( hdbc) ; */
/* recursive select */
/* rc = TbRecursiveSelect( hdbc) ; */
/* terminate the CLI application */
rc = CLIAppTerm( &henv, &hdbc, dbAlias);
return( rc ) ;
} /* end main */
/******************************************************************************
** TbBasicSelectUsingFetchAndBindCol - no more comments
******************************************************************************/
int TbBasicSelectUsingFetchAndBindCol( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * ) "SELECT deptnumb, location FROM org";
struct
{ SQLINTEGER ind ;
SQLSMALLINT val ;
} deptnumb ; /* variable to be bound to the DEPTNUMB column */
struct
{ SQLINTEGER ind ;
SQLCHAR val[15] ;
} location ; /* variable to be bound to the LOCATION column */
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLBindCol\n");
printf("-SQLFetch\n-SQLFreeHandle\n");
printf("TO PERFORM A BASIC SELECT:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the statement */
printf("\n Execute directly the statement.\n");
printf(" %s\n", stmt);
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0, &deptnumb.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR, location.val, 15, &location.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch each row, and display */
printf(" Fetch each row and dispaly.\n");
printf(" DEPTNUMB LOCATION \n" ) ;
printf(" -------- -------------\n" ) ;
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while (sqlrc != SQL_NO_DATA_FOUND)
{ printf( " %-8d %-14.14s \n", deptnumb.val, location.val ) ;
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbBasicSelectUsingFetchAndGetData - no more comments
******************************************************************************/
int TbBasicSelectUsingFetchAndGetData( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * ) "SELECT deptnumb, location FROM org";
struct
{ SQLINTEGER ind ;
SQLSMALLINT val ;
} deptnumb ; /* variable to get data from the DEPTNUMB column */
struct
{ SQLINTEGER ind ;
SQLCHAR val[15] ;
} location ; /* variable to get data from the LOCATION column */
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLFetch\n");
printf("-SQLGetData\n-SQLFreeHandle\n");
printf("TO PERFORM A BASIC SELECT:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the statement */
printf("\n Execute directly the statement.\n");
printf(" %s\n", stmt);
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch each row, and display */
printf(" Fetch each row and dispaly.\n");
printf(" DEPTNUMB LOCATION \n" ) ;
printf(" -------- -------------\n" ) ;
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while (sqlrc != SQL_NO_DATA_FOUND)
{ /* use SQLGetData to get the results */
sqlrc = SQLGetData( hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0,
&deptnumb.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLGetData( hstmt, 2, SQL_C_CHAR, location.val, 15,
&location.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* display */
printf( " %-8d %-14.14s \n", deptnumb.val, location.val ) ;
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** SysTbSelect - perform select on system tables
******************************************************************************/
int SysTbSelect( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
struct
{ SQLINTEGER ind ;
SQLCHAR val[129] ;
} tbQualifier, tbSchema, tbName, tbType;
struct
{ SQLINTEGER ind ;
SQLCHAR val[255] ;
} tbRemarks;
SQLCHAR tbSchemaPattern[] = "%";
SQLCHAR tbNamePattern[] = "ST%"; /* all the tables starting with ST */
printf("\nThere are some CLI functions that basically \n");
printf("perform SELECT on SYSTEM TABLES.\n");
printf("They return info stored in the system tables,\n");
printf("about objects in the database.\n");
printf("They are:\n");
printf("-SQLColumnPrivileges\n-SQLColumns\n-SQLForeignKeys\n");
printf("-SQLPrimaryKeys\n-SQLProcedureColumns\n-SQLProcedures\n");
printf("-SQLSpecialColumns\n-SQLStatistics\n");
printf("-SQLTablePrivileges\n-SQLTables\n");
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLTables\n-SQLBindCol\n");
printf("-SQLFetch\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO USE THE CLI FUNCTION SQLTables, FOR INSTANCE:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* call SQLTables */
printf("\n Call SQLTables.\n");
sqlrc = SQLTables( hstmt, NULL, 0,
tbSchemaPattern, SQL_NTS,
tbNamePattern, SQL_NTS,
NULL, 0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_CHAR, tbQualifier.val, 129,
&tbQualifier.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR, tbSchema.val, 129,
&tbSchema.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 3, SQL_C_CHAR, tbName.val, 129,
&tbName.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 4, SQL_C_CHAR, tbType.val, 129,
&tbType.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 5, SQL_C_CHAR, tbRemarks.val, 255,
&tbRemarks.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch each row, and display */
printf(" Fetch each row and dispaly.\n");
printf(" TABLE SCHEMA TABLE_NAME TABLE_TYPE\n");
printf(" -------------- -------------- ----------\n");
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while (sqlrc != SQL_NO_DATA_FOUND)
{ printf(" %-14s %-14s %-11s\n",
tbSchema.val, tbName.val, tbType.val);
if (tbRemarks.ind > 0)
{ printf(" (Remarks ) %s\n", tbRemarks.val);
}
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbSelectWithParam - how to perform select with parameters
******************************************************************************/
int TbSelectWithParam( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"SELECT deptnumb, location FROM org WHERE division = ?";
char divisionParam[15] ;
struct
{ SQLINTEGER ind ;
SQLSMALLINT val ;
} deptnumb ; /* variable to be bound to the DEPTNUMB column */
struct
{ SQLINTEGER ind ;
SQLCHAR val[15] ;
} location ; /* variable to be bound to the LOCATION column */
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameter\n");
printf("-SQLExecute\n-SQLBindCol\n");
printf("-SQLFetch\n-SQLFreeHandle\n");
printf("TO PERFORM A SELECT WITH PARAMETERS:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf("\n Prepare the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind divisionParam to the statement */
printf(" Bind divisionParam to the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 15, 0, divisionParam, 15, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute the statement for divisionParam = Eastern */
printf(" Execute the prepared statement for\n");
printf(" divisionParam = 'Eastern'\n");
strcpy( divisionParam, "Eastern");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0, &deptnumb.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR, location.val, 15, &location.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch each row, and display */
printf(" Fetch each row and dispaly.\n");
printf(" DEPTNUMB LOCATION \n" ) ;
printf(" -------- -------------\n" ) ;
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while (sqlrc != SQL_NO_DATA_FOUND)
{ printf( " %-8d %-14.14s \n", deptnumb.val, location.val ) ;
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbSelectWithUnknownOutCols - select with unknown output columns
** at compile time
******************************************************************************/
int TbSelectWithUnknownOutCols( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * ) "SELECT * FROM org";
SQLSMALLINT i ; /* index */
SQLSMALLINT nResultCols ; /* variable for SQLNumResultCols */
SQLCHAR colName[32] ; /* variables for SQLDescribeCol */
SQLSMALLINT colNameLen ;
SQLSMALLINT colType ;
SQLUINTEGER colSize ;
SQLSMALLINT colScale ;
SQLINTEGER colDataDisplaySize ; /* the max. size of the data */
SQLINTEGER colDisplaySize[MAX_COLUMNS] ; /* the max. size of the column */
struct
{ SQLCHAR * buff;
SQLINTEGER len;
SQLINTEGER buffLen;
} outData[MAX_COLUMNS]; /* var. to read the results */
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n-SQLExecDirect\n");
printf("-SQLNumResultCols\n-SQLDescribeCol\n-SQLSetColAttribute\n");
printf("-SQLBindCol\n-SQLFetch\n-SQLFreeHandle\n");
printf("TO PERFORM A SELECT WITH UNKNOWN OUTPUT COLUMNS\n");
printf("AT COMPILE TIME:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the statement */
printf("\n Execute directly the statement\n");
printf(" %s.\n", stmt);
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Identify the output columns, then \n");
printf(" fetch each row and display.\n");
/* identify the output columns */
sqlrc = SQLNumResultCols( hstmt, &nResultCols ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf("\n ");
for ( i = 0; i < nResultCols; i++ )
{ sqlrc = SQLDescribeCol( hstmt,
( SQLSMALLINT ) ( i + 1 ),
colName,
sizeof(colName),
&colNameLen,
&colType,
&colSize,
&colScale,
NULL ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* get display size for column */
sqlrc = SQLColAttribute( hstmt,
( SQLSMALLINT ) ( i + 1 ),
SQL_DESC_DISPLAY_SIZE,
NULL,
0,
NULL,
&colDataDisplaySize ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* set "column display size" to max of "column data display size",
and "column name length". Plus at least one space between
columns.
*/
colDisplaySize[i] = max( colDataDisplaySize,
colNameLen
) + 1 ;
/* print the column name */
printf( "%-*.*s",
(int) colDisplaySize[i],
(int) colDisplaySize[i],
colName ) ;
/* set "output data buffer length" to "column data display size".
Plus one byte for null terminator.
*/
outData[i].buffLen = colDataDisplaySize + 1;
/* allocate memory to bind column */
outData[i].buff = ( SQLCHAR * ) malloc( (int) outData[i].buffLen ) ;
/* bind columns to program vars, converting all types to CHAR */
sqlrc = SQLBindCol( hstmt,
( SQLSMALLINT ) ( i + 1 ),
SQL_C_CHAR,
outData[i].buff,
outData[i].buffLen,
&outData[i].len ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
printf( "\n" ) ;
/* fetch each row and display */
sqlrc = SQLFetch( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\nData not found.\n");
}
while ( sqlrc != SQL_NO_DATA_FOUND )
{ printf(" ");
for ( i = 0; i < nResultCols; i++ )
{ /* check for NULL data */
if ( outData[i].len == SQL_NULL_DATA )
{ printf( "%-*.*s",
(int) colDisplaySize[i],
(int) colDisplaySize[i],
"NULL" ) ;
}
else
{ /* print outData for this column */
printf( "%-*.*s",
(int) colDisplaySize[i],
(int) colDisplaySize[i],
outData[i].buff ) ;
}
} /* for all columns in this row */
printf("\n");
sqlrc = SQLFetch( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
} /* while rows to fetch */
/* free data buffers */
for ( i = 0; i < nResultCols; i++ )
{ free( outData[i].buff ) ;
}
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** TbSelectUsingFetchScrollColWise - no more comments
******************************************************************************/
int TbSelectUsingFetchScrollColWise( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLHANDLE hstmtTable ; /* to create a test table */
SQLINTEGER rowNb;
SQLCHAR stmtInsert[100];
SQLUINTEGER rowsFetchedNb;
SQLUSMALLINT rowStatus[ROWSET_SIZE];
static char ROWSTATVALUE[][26] = { "SQL_ROW_SUCCESS", \
"SQL_ROW_SUCCESS_WITH_INFO", \
"SQL_ROW_ERROR", \
"SQL_ROW_NOROW" };
int i;
struct
{ SQLINTEGER ind[ROWSET_SIZE] ;
SQLCHAR val[ROWSET_SIZE][15] ;
} col1, col2 ; /* variables to be bound to columns */
struct
{ SQLINTEGER ind ;
SQLINTEGER val[4] ;
} bookmark ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLSetStmtAttr\n-SQLBindCol\n");
printf("-SQLFetchScroll\n-SQLSetPos\n");
printf("-SQLGetData\n-SQLFreeHandle\n");
printf("TO DEMONSTRATE SCROLLABLE CURSORS\n");
printf("USING COLUMN-WISE BINDING:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtTable ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* create a table with 30 rows */
printf("\n Create a table with 2 columns and 30 rows.\n");
sqlrc = SQLExecDirect( hstmtTable,
(UCHAR*)"CREATE TABLE fetchScrollTable ( col1 CHAR(14), col2 CHAR(14))",
SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtTable, sqlrc);
for( rowNb = 1; rowNb <= 30; rowNb++)
{ sprintf((char*) stmtInsert,
"INSERT INTO fetchScrollTable VALUES ( 'row%d_col1', 'row%d_col2')",
rowNb, rowNb);
sqlrc = SQLExecDirect( hstmtTable, stmtInsert, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtTable, sqlrc);
}
/* allocate a statement handle for FetchScroll */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* set the required statement attributes */
printf(" Set the required statement attributes.\n");
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER) ROWSET_SIZE,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_BIND_TYPE,
SQL_BIND_BY_COLUMN,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
&rowsFetchedNb,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_STATIC,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER) SQL_UB_VARIABLE,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER) rowStatus,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* select everything from the test table */
printf(" Select all 30 rows from the test table.\n");
sqlrc = SQLExecDirect( hstmt,
(SQLCHAR*)"SELECT * FROM fetchScrollTable",
SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_CHAR, col1.val, 15, col1.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR, col2.val, 15, col2.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_ABSOLUTE at row 15 */
printf("\n SQLFetchScroll with SQL_FETCH_ABSOLUTE at row 15.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", col1.val[i], col2.val[i]);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_NEXT */
printf(" SQLFetchScroll with SQL_FETCH_NEXT.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", col1.val[i], col2.val[i]);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_PRIOR */
printf(" SQLFetchScroll with SQL_FETCH_PRIOR.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_PRIOR, 0 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", col1.val[i], col2.val[i]);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_FIRST */
printf(" SQLFetchScroll with SQL_FETCH_FIRST.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_FIRST, 0 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", col1.val[i], col2.val[i]);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_LAST */
printf(" SQLFetchScroll with SQL_FETCH_LAST.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_LAST, 0 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", col1.val[i], col2.val[i]);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_RELATIVE offset 3 */
printf(" SQLFetchScroll with SQL_FETCH_RELATIVE offset 3.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_RELATIVE, 3 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", col1.val[i], col2.val[i]);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* set the bookmark at the row 17 */
printf("\n Set the bookmark at the row 17.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* 1->15, 2->16, 3->17*/
sqlrc = SQLSetPos( hstmt, 3, SQL_POSITION, SQL_LOCK_NO_CHANGE);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLGetData(hstmt, 0, SQL_C_LONG, bookmark.val, 4, &bookmark.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_FETCH_BOOKMARK_PTR,
(SQLPOINTER) bookmark.val,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_BOOKMARK offset 4 */
printf(" SQLFetchScroll with SQL_FETCH_BOOKMARK offset 4.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_BOOKMARK, 4 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", col1.val[i], col2.val[i]);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* drop the test table */
printf("\n Drop the test table.\n");
sqlrc = SQLExecDirect( hstmtTable,
(SQLCHAR *)"DROP TABLE fetchScrollTable ",
SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtTable, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtTable ) ;
STMT_HANDLE_CHECK( hstmtTable, sqlrc);
return(rc);
}
/******************************************************************************
** TbSelectUsingFetchScrollRowWise - no more comments
******************************************************************************/
int TbSelectUsingFetchScrollRowWise( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLHANDLE hstmtTable ; /* to create a test table */
SQLINTEGER rowNb;
SQLCHAR stmtInsert[100];
SQLUINTEGER rowsFetchedNb;
SQLUSMALLINT rowStatus[ROWSET_SIZE];
static char ROWSTATVALUE[][26] = { "SQL_ROW_SUCCESS", \
"SQL_ROW_SUCCESS_WITH_INFO", \
"SQL_ROW_ERROR", \
"SQL_ROW_NOROW" };
int i;
struct
{ SQLINTEGER col1_ind ;
SQLCHAR col1_val[15] ;
SQLINTEGER col2_ind ;
SQLCHAR col2_val[15] ;
} rowset[ROWSET_SIZE]; /* variables to be bound to columns */
struct
{ SQLINTEGER ind ;
SQLINTEGER val[4] ;
} bookmark ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLSetStmtAttr\n-SQLBindCol\n");
printf("-SQLFetchScroll\n-SQLSetPos\n");
printf("-SQLGetData\n-SQLFreeHandle\n");
printf("TO DEMONSTRATE SCROLLABLE CURSORS\n");
printf("USING ROW-WISE BINDING:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtTable ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* create a table with 30 rows */
printf("\n Create a table with 2 columns and 30 rows.\n");
sqlrc = SQLExecDirect( hstmtTable,
(SQLCHAR*)"CREATE TABLE fetchScrollTable ( col1 CHAR(14), col2 CHAR(14))",
SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtTable, sqlrc);
for( rowNb = 1; rowNb <= 30; rowNb++)
{ sprintf( (char *)stmtInsert,
"INSERT INTO fetchScrollTable VALUES ( 'row%d_col1', 'row%d_col2')",
rowNb, rowNb);
sqlrc = SQLExecDirect( hstmtTable, stmtInsert, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtTable, sqlrc);
}
/* allocate a statement handle for FetchScroll */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* set the required statement attributes */
printf(" Set the required statement attributes.\n");
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER) ROWSET_SIZE,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_BIND_TYPE,
(SQLPOINTER) ( sizeof(rowset)/ROWSET_SIZE),
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
&rowsFetchedNb,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_STATIC,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER) SQL_UB_VARIABLE,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER) rowStatus,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* select everything from the test table */
printf(" Select all 30 rows from the test table.\n");
sqlrc = SQLExecDirect( hstmt,
(SQLCHAR*)"SELECT * FROM fetchScrollTable",
SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_CHAR,
(SQLPOINTER) rowset[0].col1_val, 15,
&rowset[0].col1_ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR,
(SQLPOINTER) rowset[0].col2_val, 15,
&rowset[0].col2_ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_ABSOLUTE at row 15 */
printf("\n SQLFetchScroll with SQL_FETCH_ABSOLUTE at row 15.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", rowset[i].col1_val, rowset[i].col2_val);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_NEXT */
printf(" SQLFetchScroll with SQL_FETCH_NEXT.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", rowset[i].col1_val, rowset[i].col2_val);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_PRIOR */
printf(" SQLFetchScroll with SQL_FETCH_PRIOR.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_PRIOR, 0 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", rowset[i].col1_val, rowset[i].col2_val);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_FIRST */
printf(" SQLFetchScroll with SQL_FETCH_FIRST.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_FIRST, 0 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", rowset[i].col1_val, rowset[i].col2_val);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_LAST */
printf(" SQLFetchScroll with SQL_FETCH_LAST.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_LAST, 0 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", rowset[i].col1_val, rowset[i].col2_val);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* fetch the rowset: row15, row16, row17, row18, row19 */
printf("\n Fetch the rowset: row15, row16, row17, row18, row19.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_RELATIVE offset 3 */
printf(" SQLFetchScroll with SQL_FETCH_RELATIVE offset 3.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_RELATIVE, 3 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", rowset[i].col1_val, rowset[i].col2_val);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* set the bookmark at the row 17 */
printf("\n Set the bookmark at the row 17.\n");
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_ABSOLUTE, 15 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* 1->15, 2->16, 3->17*/
sqlrc = SQLSetPos( hstmt, 3, SQL_POSITION, SQL_LOCK_NO_CHANGE);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLGetData(hstmt, 0, SQL_C_LONG, bookmark.val, 4, &bookmark.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_FETCH_BOOKMARK_PTR,
(SQLPOINTER) bookmark.val,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* SQLFetchScroll with SQL_FETCH_BOOKMARK offset 4 */
printf(" SQLFetchScroll with SQL_FETCH_BOOKMARK offset 4.\n");
printf(" COL1 COL2 \n" ) ;
printf(" ------------ -------------\n" ) ;
sqlrc = SQLFetchScroll( hstmt, SQL_FETCH_BOOKMARK, 4 );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
for ( i = 0; i < rowsFetchedNb; i++)
{ printf(" %-14s%-14s\n", rowset[i].col1_val, rowset[i].col2_val);
}
/* output the Row Status Array if the complete rowset was not returned. */
if ( rowsFetchedNb != ROWSET_SIZE)
{ printf(" Previous rowset was not full:\n");
for (i = 0; i < ROWSET_SIZE; i++)
{ printf(" Row Status Array[%i] = %s\n",
i, ROWSTATVALUE[rowStatus[i]]);
}
}
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* drop the test table */
printf("\n Drop the test table.\n");
sqlrc = SQLExecDirect( hstmtTable,
(SQLCHAR*)"DROP TABLE fetchScrollTable ",
SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtTable, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtTable ) ;
STMT_HANDLE_CHECK( hstmtTable, sqlrc);
return(rc);
}