/******************************************************************************
**
** Source File Name = sfetch.c 1.3
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
**
** A modification of the CLI sample xfetch.c to demonstrate scrollable
** cursors. Demonstrates the use of the following types of fetch:
** o SQL_FETCH_FIRST
** o SQL_FETCH_LAST
** o SQL_FETCH_NEXT
** o SQL_FETCH_ABSOLUTE
** o SQL_FETCH_RELATIVE
** o SQL_FETCH_BOOKMARK
**
** The same set of SQLFetchScroll() statements are issued twice, once
** using column-wise binding, then once using row-wise binding.
**
** For more information about these samples see the README file.
**
** 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 "sqlcli.h"
#include "sqlcli1.h"
#include "samputil.h" /* Header file for CLI sample code */
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
#define MAX_STMT_LEN 255
#define ROWSET_SIZE 5
/* Global Variables for user id and password.
* To keep samples simple, not a recommended practice.
*/
SQLCHAR server[SQL_MAX_DSN_LENGTH + 1];
SQLCHAR uid[MAX_UID_LENGTH + 1];
SQLCHAR pwd[MAX_PWD_LENGTH + 1];
/*******************************************************************
** main
*******************************************************************/
int
main( int argc, char * argv[] )
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN rc;
SQLCHAR stmt[] =
"SELECT deptnumb, deptname, id, name FROM staff, org \
WHERE dept=deptnumb AND job = 'Mgr' \
ORDER BY name";
/* Column-Wise */
SQLINTEGER deptnumb[ROWSET_SIZE];
SQLCHAR deptname[ROWSET_SIZE][15];
SQLINTEGER deptname_l[ROWSET_SIZE];
SQLSMALLINT id[ROWSET_SIZE];
SQLCHAR name[ROWSET_SIZE][10];
SQLINTEGER name_l[ROWSET_SIZE];
/* New for sfetch.c, A variable to store the number of rows fetched */
SQLUINTEGER numrowsfetched;
SQLUSMALLINT rowStatus[ROWSET_SIZE];
static char ROWSTATVALUE[][26] = { "SQL_ROW_SUCCESS", \
"SQL_ROW_SUCCESS_WITH_INFO", \
"SQL_ROW_ERROR", \
"SQL_ROW_NOROW" };
/* Row-Wise (Includes buffer for both column data and length) */
struct {
SQLINTEGER deptnumb_l; /* length */
SQLINTEGER deptnumb; /* value */
SQLINTEGER deptname_l;
SQLCHAR deptname[15];
SQLINTEGER id_l;
SQLSMALLINT id;
SQLINTEGER name_l;
SQLCHAR name[10];
} R[ROWSET_SIZE];
SQLUSMALLINT Row_Stat[ROWSET_SIZE];
SQLUINTEGER pcrow;
int i;
/* New for sfetch.c */
SQLINTEGER abookmark[4];
SQLINTEGER outlen;
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Before making any API calls for 68K environment, need to initialize the
Library Manager */
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
/* macro to initalize server, uid and pwd */
INIT_UID_PWD;
/* allocate an environment handle */
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS)
return (terminate(henv, rc));
/* allocate a connect handle, and connect */
rc = DBconnect(henv, &hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
/*--> SQLL1X62.SCRIPT */
/*********************************************/
/* Column-Wise Binding */
/*********************************************/
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
/* Set the number of rows in the rowset */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER) ROWSET_SIZE,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* New for sfetch.c: */
/* Set the SQL_ATTR_ROW_BIND_TYPE statement attribute */
/* to use column-wise binding: */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_BIND_TYPE,
SQL_BIND_BY_COLUMN,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* New for sfetch.c: */
/* Set the SQL_ATTR_ROWS_FETCHED_PTR statement attribute to */
/* point to the variable numrowsfetched: */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
&numrowsfetched,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* New for sfetch.c: */
/* Set the SQL_ATTR_CURSOR_TYPE statement attribute */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_STATIC,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* New for sfetch.c: */
/* Indicate that we will use bookmarks by setting the */
/* SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE: */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER) SQL_UB_VARIABLE,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* New for sfetch.c: */
/* Point the statement handle to the row status array */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER) rowStatus,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/*<-- */
rc = SQLExecDirect(hstmt, stmt, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLBindCol(hstmt, 1, SQL_C_LONG, (SQLPOINTER) deptnumb, 0, NULL);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) deptname, 15, deptname_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLBindCol(hstmt, 3, SQL_C_SSHORT, (SQLPOINTER) id, 0, NULL);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) name, 10, name_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("\nUse Column-Wise Binding to demonstrate SQLFetchScroll():\n");
printf("\nDEPTNUMB DEPTNAME ID NAME\n");
printf("-------- -------------- -------- ---------\n");
/* (1.) Fetch the first ROWSET_SIZE rows. */
printf("*** (1.) SQLFetchScroll with SQL_FETCH_FIRST ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[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]]);
}
/* (2.) Fetch the next ROWSET_SIZE rows. */
printf("*** (2.) SQLFetchScroll with SQL_FETCH_NEXT ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[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]]);
}
/* (3.) Fetch ROWSET_SIZE rows starting at row 3. */
printf("*** (3.) SQLFetchScroll with SQL_FETCH_ABSOLUTE at row 3 ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 3);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[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]]);
}
/* Set a bookmark to come back to later. */
/* Will use SQLGetData() for column 0 to get the bookmark value. */
printf("+++ Storing the 1st row of the previous result set as a bookmark. +++\n");
/* Use SQLSetPos to position the row within the rowset. */
rc = SQLSetPos(
hstmt,
1, /* Position at the 1st row of the rowset. */
SQL_POSITION,
SQL_LOCK_NO_CHANGE); /* Do not change the lock state. */
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* The bookmark value is stored in column 0; get its value. */
rc = SQLGetData(hstmt, 0, SQL_C_LONG, abookmark, 255, &outlen);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Now that we have the bookmark value we must set it. */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_FETCH_BOOKMARK_PTR,
(SQLPOINTER) abookmark,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* (4.) Fetch the last ROWSET_SIZE rows. */
printf("*** (4.) SQLFetchScroll with SQL_FETCH_LAST ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_LAST, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[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]]);
}
/* (5.) Fetch ROWSET_SIZE rows using SQL_FETCH_RELATIVE */
printf("*** (5.) SQLFetchScroll with SQL_FETCH_RELATIVE at row -3 ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -3);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[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]]);
}
/* (2. again) Fetch the next ROWSET_SIZE rows. */
printf("*** (2. again) SQLFetchScroll with SQL_FETCH_NEXT ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[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]]);
}
/* (6.) Fetch ROWSET_SIZE rows starting from the saved bookmark. */
printf("*** (6.) SQLFetchScroll with SQL_FETCH_BOOKMARK offset 0 ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_BOOKMARK, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[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]]);
}
/* (6. again) Fetch ROWSET_SIZE rows starting from the saved bookmark. */
printf("*** (6.again) SQLFetchScroll with SQL_FETCH_BOOKMARK offset 2 ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_BOOKMARK, 2);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[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]]);
}
/* (7.) Use SQLFetch() which is the same as calling SQLFetchScroll() */
/* with the FetchOrientation set to SQL_FETCH_NEXT. */
printf("*** (7) Use SQLFetch() to fetch the next rowset ");
rc = SQLFetch( hstmt );
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", deptnumb[i], deptname[i], id[i], name[i]);
}
/* Output 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]]);
}
/* Finished with column-wise binding, close the statement handle. */
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
/*********************************************/
/* Row-Wise Binding */
/*********************************************/
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Set maximum number of rows to receive with each extended fetch */
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ROWSET_SIZE, 0);
if (rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/*
* Set vparam to size of one row, used as offset for each bindcol
* rgbValue
*
* ie. &(R[0].deptnumb) + vparam = &(R[1].deptnum)
*/
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_ROW_BIND_TYPE,
(SQLPOINTER) (sizeof(R) / ROWSET_SIZE),
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* New for sfetch.c: */
/* Set the SQL_ATTR_ROWS_FETCHED_PTR statement attribute to */
/* point to numrowsfetched so that the results are stored there. */
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &numrowsfetched, 0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* New for sfetch.c: */
/* Set the SQL_ATTR_CURSOR_TYPE statement attribute. */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_STATIC,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* New for sfetch.c: */
/* Indicate that bookmarks will be used by setting the */
/* SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE. */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER) SQL_UB_VARIABLE,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLExecDirect(hstmt, stmt, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLBindCol(hstmt, 1, SQL_C_LONG, (SQLPOINTER) & R[0].deptnumb, 0,
&R[0].deptnumb_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) R[0].deptname, 15,
&R[0].deptname_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLBindCol(hstmt, 3, SQL_C_SSHORT, (SQLPOINTER) & R[0].id, 0,
&R[0].id_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) R[0].name, 10, &R[0].name_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Fetch ROWSET_SIZE rows at a time, and display */
printf("\nNow do same as above using Column-Wise Binding:\n");
printf("\nDEPTNUMB DEPTNAME ID NAME\n");
printf("-------- -------------- -------- ---------\n");
/* (1.) Fetch the first ROWSET_SIZE rows. */
/* Same as Column-wise binding above. Only the printf is different. */
printf("*** (1.) SQLFetchScroll with SQL_FETCH_FIRST ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* 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]]);
}
/* (2.) Fetch the next ROWSET_SIZE rows. */
/* Same as Column-wise binding above. Only the printf is different. */
printf("*** (2.) SQLFetchScroll with SQL_FETCH_NEXT ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* 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]]);
}
/* (3.) Fetch ROWSET_SIZE rows starting at row 3. */
/* Same as Column-wise binding above. Only the printf is different. */
printf("*** (3.) SQLFetchScroll with SQL_FETCH_ABSOLUTE at row 3 ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 3);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* 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]]);
}
/* Set a bookmark to come back to later. */
/* Will use SQLGetData() for column 0 to get the bookmark value. */
/* Exactly the same as Column-wise binding above. */
printf("+++ Storing the 1st row of the previous result set as a bookmark. +++\n");
/* Use SQLSetPos to position the row within the rowset. */
rc = SQLSetPos(
hstmt,
1, /* Position at the 1st row of the rowset. */
SQL_POSITION,
SQL_LOCK_NO_CHANGE); /* Do not change the lock state. */
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* The bookmark value is stored in column 0; get its value. */
rc = SQLGetData(hstmt, 0, SQL_C_LONG, abookmark, 255, &outlen);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* Now that we have the bookmark value we must set it. */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_FETCH_BOOKMARK_PTR,
(SQLPOINTER) abookmark,
0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
/* (4.) Fetch the last ROWSET_SIZE rows. */
/* Same as Column-wise binding above. Only the printf is different. */
printf("*** (4.) SQLFetchScroll with SQL_FETCH_LAST ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_LAST, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* 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]]);
}
/* (5.) Fetch ROWSET_SIZE rows using SQL_FETCH_RELATIVE */
/* Same as Column-wise binding above. Only the printf is different. */
printf("*** (5.) SQLFetchScroll with SQL_FETCH_RELATIVE at row -3 ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -3);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* Output 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]]);
}
/* (2. again) Fetch the next ROWSET_SIZE rows. */
/* Same as Column-wise binding above. Only the printf is different. */
printf("*** (2. again) SQLFetchScroll with SQL_FETCH_NEXT ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* Output 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]]);
}
/* (6.) Fetch ROWSET_SIZE rows starting from the saved bookmark. */
/* Same as Column-wise binding above. Only the rintf is different. */
printf("*** (6.) SQLFetchScroll with SQL_FETCH_BOOKMARK offset 0 ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_BOOKMARK, 0);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* 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]]);
}
/* (6. again) Fetch ROWSET_SIZE rows starting from the saved bookmark. */
/* Same as Column-wise binding above. Only the printf is different. */
printf("*** (6.again) SQLFetchScroll with SQL_FETCH_BOOKMARK offset 2 ");
rc = SQLFetchScroll(hstmt, SQL_FETCH_BOOKMARK, 2);
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* 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]]);
}
/* (7.) Use SQLFetch() which is the same as calling SQLFetchScroll() */
/* with the FetchOrientation set to SQL_FETCH_NEXT. */
printf("*** (7) Use SQLFetch() to fetch the next rowset ");
rc = SQLFetch( hstmt );
/* Indicate how many rows were in the result set. */
printf("(%i rows). ***\n", numrowsfetched);
if (rc != SQL_NO_DATA && rc != SQL_SUCCESS)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
for (i = 0; i < numrowsfetched; i++) {
printf("%8ld %-14s %8d %-9s\n", R[i].deptnumb, R[i].deptname,
R[i].id, R[i].name);
}
/* 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]]);
}
/* Done. */
/* Free handles, commit, exit */
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
printf("\n>Disconnecting .....\n");
rc = SQLDisconnect(hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
if (rc != SQL_SUCCESS)
return (terminate(henv, rc));
return (SQL_SUCCESS);
} /* end main */