Site navigation
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
/**********************************************************************
* FILENAME : UsingParameterArraysAndKeySetCursors.c
*
* DESCRIPTION :
* Example uses a parameter array with a keyset driven cursor to retrieve
* rowsets with SQLFetchScroll.
*
* Also utilises a ParamOperationsArray which can be used to
* tell the driver which parameter sets in the parameter array
* to use or to ignore.
*
* It illustrates what SQL_PARAM_ARRAY_SELECTS = SQL_PAS_BATCH
* and SQL_NO_PAS_BATCH actually mean. The SQL_PARAM_ARRAY_SELECTS
* option indicates whether a result set is available for each set
* of parameters (SQL_PAS_BATCH) or whether only one result set
* is available (SQL_PAS_NO_BATCH).
*
* In other words, if the driver indicates SQL_PAS_BATCH, effectively
* individual parameter sets are processed one at a time irrespective
* of any rowset size indicated. Here the parameter status array,
* where a select is successful, will indicate one record returned
* in the first element, followed by NO ROW in the rest.
*
*
* ODBC USAGE :
* Uses parameterised SELECT statement on TestTBL1
* Set Statement Attributes for SQLFetchScroll:
* SQLSetStmtAttr - SQL_ATTR_CURSOR_TYPE - SQL_CURSOR_KEYSET_DRIVEN
* SQL_ATTR_ROW_BIND_TYPE - ROW-WISE Binding
* SQL_ATTR_ROW_ARRAY_SIZE - 3 rows
* SQL_ATTR_USE_BOOKMARKS - SQL_UB_VARIABLE
* SQL_ATTR_ROW_STATUS_PTR - Row Status Array Addr
* SQL_ATTR_ROW_BIND_OFFSET_PTR - Bind Offset Addr
* SQL_ATTR_ROWS_FETCHED_PTR - Rows Fetched Addr
* SQL_ATTR_CONCURRENCY - SQL_CONCUR_LOCK
* Set Parameter Array related Statement Attributes
* SQLSetStmtAttr - SQL_ATTR_PARAMSET_SIZE - size of parameter array
* SQL_ATTR_PARAM_STATUS_PTR - status array addr
* SQL_ATTR_PARAMS_PROCESSED_PTR - params processed addr
* SQL_ATTR_PARAM_OPERATION_PTR - ignore/proceed flags
* SQLExecDirect - to execute the select statement
* Loop using SQLFetchScroll and SQLMoreResults to retrieve data
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define ROWSET_SIZE 3 // How many rows at a time
#define PARAM_ARRAY_SIZE 15 // Number of params in total
#define DATA_ARRAY_SIZE 3 // Buffers for rowsets
#define BOOKMARK_LEN 10
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
// Select statements to return person details from TestTBL1
SQLCHAR stmt[] = "SELECT PersonID, FirstName, LastName, Address, City "
"FROM TestTBL1 WHERE PersonID=?";
// Array of person IDs to get list of all Record IDs for
SQLINTEGER PersonIDs[] = {1,2,3,4,5,6,7,118,119,10,111,112,113,114,15};
// Define structure for data
typedef struct tagCustStruct {
SQLCHAR Bookmark[BOOKMARK_LEN];
SQLLEN BookmarkLen;
SQLUINTEGER PersonID;
SQLLEN PersonIDInd;
SQLCHAR FirstName[255];
SQLLEN FirstNameLenOrInd;
SQLCHAR LastName[255];
SQLLEN LastNameLenOrInd;
SQLCHAR Address[255];
SQLLEN AddressLenOrInd;
SQLCHAR City[255];
SQLLEN CityLenOrInd;
} CustStruct;
CustStruct CustArray[DATA_ARRAY_SIZE];
SQLUSMALLINT ParamStatusArray[PARAM_ARRAY_SIZE];
// Operation Array used to indicate whether a parameter from the parameter
// array should be ignored or used. Here the first 2 parameters are ignored
// and will not appear in the results.
SQLUSMALLINT ParamOperationsArray[PARAM_ARRAY_SIZE] = {
SQL_PARAM_IGNORE, SQL_PARAM_IGNORE, SQL_PARAM_PROCEED,
SQL_PARAM_PROCEED, SQL_PARAM_PROCEED, SQL_PARAM_PROCEED,
SQL_PARAM_PROCEED, SQL_PARAM_PROCEED, SQL_PARAM_PROCEED,
SQL_PARAM_PROCEED, SQL_PARAM_PROCEED, SQL_PARAM_PROCEED,
SQL_PARAM_PROCEED, SQL_PARAM_PROCEED, SQL_PARAM_PROCEED,
};
SQLLEN ParamsProcessed=0;
int i;
SQLUSMALLINT RowStatusArray[DATA_ARRAY_SIZE], Action, RowNum;
SQLLEN NumUpdates = 0, NumInserts = 0, NumDeletes = 0;
SQLLEN BindOffset = 0;
SQLLEN RowsFetched = 0;
SQLLEN Concurrency = SQL_CONCUR_LOCK;
SQLLEN rowCount;
//
// Column-wise binding
//
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLCHAR *)(void*)SQL_OV_ODBC3, -1);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQLAllocHandle)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)", hdbc, SQL_HANDLE_DBC);
retcode = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
hstmt, SQL_HANDLE_STMT);
// Setup for SQLFetchScroll and SQLMoreResults
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE,
(SQLPOINTER)sizeof(CustStruct), 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER)3, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER)SQL_UB_VARIABLE, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR,
RowStatusArray, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_OFFSET_PTR,
&BindOffset, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR,
&RowsFetched,0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY,
(SQLPOINTER)SQL_CONCUR_LOCK ,0);
// Setup for parameter array processing
retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMSET_SIZE,
(SQLPOINTER) PARAM_ARRAY_SIZE, 0);
retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAM_STATUS_PTR,
ParamStatusArray, PARAM_ARRAY_SIZE);
retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR,
&ParamsProcessed, 0);
retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAM_OPERATION_PTR,
ParamOperationsArray, PARAM_ARRAY_SIZE);
// Bind array values of parameter 1 data in
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, PersonIDs, 0, NULL);
// Bind columns for data out
retcode = SQLBindCol(hstmt, 1, SQL_C_LONG,
&CustArray[0].PersonID, 0,
&CustArray[0].PersonIDInd);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR,
(SQLPOINTER) CustArray[0].FirstName, 255,
(SQLLEN *) &CustArray[0].FirstNameLenOrInd);
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR,
(SQLPOINTER) CustArray[0].LastName, 255,
(SQLLEN *) &CustArray[0].LastNameLenOrInd);
retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR,
(SQLPOINTER) CustArray[0].Address, 255,
(SQLLEN *) &CustArray[0].AddressLenOrInd);
retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR,
(SQLPOINTER) CustArray[0].City, 255,
(SQLLEN *) &CustArray[0].CityLenOrInd);
retcode = SQLExecDirect(hstmt, stmt, SQL_NTS);
CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);
do {
retcode = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
if (retcode!=SQL_NO_DATA) {
printf("\nParams Processed : %i", (int)ParamsProcessed);
printf("\nNext : %i Row ", (int)RowsFetched);
printf ("\nRowset Status Array : ");
for (i=0;i<DATA_ARRAY_SIZE;i++) {
switch (RowStatusArray[i]) {
case SQL_ROW_SUCCESS_WITH_INFO:
case SQL_ROW_SUCCESS:
printf ("\n %i - ROW SUCCESS", i);
break;
case SQL_ROW_NOROW:
printf ("\n %i - NO ROW", i);
break;
default:
printf ("\n %i - ?", (int)RowStatusArray[i]);
}
}
printf ("\nData : \n");
for (i = 0; i < RowsFetched; i++) {
printf ("Rowset Row %i Data : ", i);
printf("%i ", (int)CustArray[i].PersonID);
printf("%.10s ", CustArray[i].FirstName);
printf("%.10s ", CustArray[i].LastName);
printf("%.10s ", CustArray[i].Address);
printf("%.10s \n", CustArray[i].City);
}
} else {
printf("\nParams Processed : %i", (int)ParamsProcessed);
printf("\nNext : No Row\n");
}
} while (SQLMoreResults(hstmt) == SQL_SUCCESS);
exit:
printf ("\nComplete.\n");
// Free handles
// Statement
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
// Connection
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// Environment
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.