Site navigation
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
/**********************************************************************
* FILENAME : UsingKeysetCursors.c
*
* DESCRIPTION :
* Shows use of SQLSetPos with keyset driven cursor type and
* SQLFetchScroll() to read and update data in a rowset.
*
* Illustrates the use of SQL_FETCH_NEXT, SQL_FETCH_FIRST,
* SQL_FETCH_LAST, SQL_FETCH_PRIOR, SQL_FETCH_ABSOLUTE and
* SQL_FETCH_RELATIVE to retrieve data with UPDATE, DELETE and INSERT
* operations to change data in the rowset.
*
* A rowset array size of 10 is used (+1 extra for an insert record)
*
* ODBC USAGE :
* Sets the statement handle attributes ...
*
* SQLSetStmtAttr() with - SQL_ATTR_CURSOR_TYPE = SQL_CURSOR_KEYSET_DRIVEN
* - SQL_ATTR_ROW_BIND_TYPE = SQL_BIND_BY_COLUMN
* - SQL_ATTR_ROW_ARRAY_SIZE = 10
* - SQL_ATTR_ROW_STATUS_PTR = pointer to row status
* array
* - SQL_ATTR_CONCURRENCY = SQL_CONCUR_LOCK
* SQLBindCol() - to bind data array for current rowset
* SQLExecuteDirect() - execute SELECT from TestTBL1
* SQLFetchScroll() with - to retrieve rowset according to action requested
* (SQL_FETCH_FIRST, etc)
* SQLSetPos() - to set the cursor position in the rowset
* (for update, delete and insert)
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define QUIT 0
#define INSERT 0
#define UPDATE 1
#define DELETE 2
#define LAST 3
#define UPDATE_ROW 100
#define DELETE_ROW 101
#define INSERT_ROW 102
#define REFRESH_ROW 103
#define QUIT 0
#define TRUE 1
#define FALSE 0
#define PERSONID_LEN 2
#define LASTNAME_LEN 255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN 255
#define DATA_ARRAY_SIZE 11
// Data and size arrays NOTE difference with Bulk Operations
// where structure is used
SQLUINTEGER PersonID[DATA_ARRAY_SIZE];
SQLCHAR FirstName[DATA_ARRAY_SIZE][255];
SQLCHAR LastName[DATA_ARRAY_SIZE][255];
SQLCHAR Address[DATA_ARRAY_SIZE][255];
SQLCHAR City[DATA_ARRAY_SIZE][255];
SQLLEN PersonIDLenArr[DATA_ARRAY_SIZE];
SQLLEN FirstNameLenArr[DATA_ARRAY_SIZE];
SQLLEN LastNameLenArr[DATA_ARRAY_SIZE];
SQLLEN AddressLenArr[DATA_ARRAY_SIZE];
SQLLEN CityLenArr[DATA_ARRAY_SIZE];
// RowStatusArray values (for reference)
// SQL_ROW_SUCCESS 0
// SQL_ROW_DELETED 1
// SQL_ROW_UPDATED 2
// SQL_ROW_NOROW 3
// SQL_ROW_ADDED 4
// SQL_ROW_ERROR 5
// (ODBCVER >= 0x0300)
// SQL_ROW_SUCCESS_WITH_INFO 6
// SQL_ROW_PROCEED 0
// SQL_ROW_IGNORE 1
// SQLFetchScroll() actions (for reference)
//#define SQL_FETCH_NEXT 1 - fetch next rowset
//#define SQL_FETCH_FIRST 2 - fetch first rowset
//#define SQL_FETCH_LAST 3 - fetch last rowset
//#define SQL_FETCH_PRIOR 4 - fetch rowset prior to current rowset
//#define SQL_FETCH_ABSOLUTE 5 - fetch rowset starting at row within
// the table
//#define SQL_FETCH_RELATIVE 6 - fetch rowset starting at row within
// current rowset
SQLUSMALLINT RowStatusArray[DATA_ARRAY_SIZE], Action, RowNum;
SQLUSMALLINT NumUpdates = 0, NumInserts = 0, NumDeletes = 0;
SQLLEN BindOffset = 0;
SQLLEN RowsFetched = 0;
SQLLEN Concurrency = SQL_CONCUR_LOCK;
SQLHENV henv = SQL_NULL_HENV; // Environment handle
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
SQLLEN rowCount;
//
// Display row status array and rowset data array
//
void DisplayCustData() {
int i;
// Display RowStatusArray
for (i=0; i<DATA_ARRAY_SIZE; i++) {
printf ("%i, ", (int)RowStatusArray[i]);
}
printf ("\n");
for (i=0; i<DATA_ARRAY_SIZE; i++) {
printf ("Record %i - ", i);
printf ("%i ,", (int)PersonID[i]);
printf ("%.10s ,", (char *)FirstName[i]);
printf ("%.10s ,", (char *)LastName[i]);
printf ("%.10s ,", (char *)Address[i]);
printf ("%.10s\n", (char *)City[i]);
}
return;
}
//
// Function to get next action
//
int GetAction(SQLUSMALLINT *pStatusArray,
SQLUSMALLINT *pAction,
SQLUSMALLINT* pRowNum) {
// Display cust data array
printf ("Current Data: \n");
DisplayCustData();
printf ("QUIT(0), \n");
printf ("LIST : NEXT(1), FIRST(2), LAST(3), PRIOR(4), ABS(5), REL(6)\n");
printf ("OR : UPDATE(100) DELETE(101) INSERT(102) REFRESH(103)\n");
getInt ("Select Action", (int *) pAction, 'N', 0);
// Row number only used for SQL_FETCH_ABSOLUTE and SQL_FETCH_RELATIVE
if (*pAction == SQL_FETCH_ABSOLUTE || *pAction == SQL_FETCH_RELATIVE) {
getInt ("Select ABS/REL Row Number", (int *) pRowNum, 'N', 0);
} else {
*pRowNum=0;
}
return 1;
}
//
// Clear a number of rows in the rowset data array
//
void clearBuffers (int index, int rows) {
int i;
for (i=index;i<index+rows;i++) {
RowStatusArray[i]=SQL_ROW_NOROW;
}
printf ("Clear Buffers : %i, %i\n", index, rows);
for (i=index;i<index+rows;i++) {
PersonID[i]=0;
memset(FirstName[i], ' ', FIRSTNAME_LEN);
memset(LastName[i], ' ', LASTNAME_LEN);
memset(Address[i], ' ', ADDRESS_LEN);
memset(City[i], ' ', CITY_LEN);
}
}
//
// If performing an Update or Delete, prompts for record number/index of
// record to process in the data array. Requests new data for update or
// insert. Data for inserts is returned in an extra row at the end of
// the rowset data array.
//
SQLUSMALLINT GetNewCustData(base) {
int srcOffset;
SQLCHAR strFirstName[FIRSTNAME_LEN];
SQLCHAR strLastName[LASTNAME_LEN];
SQLCHAR strAddress[ADDRESS_LEN];
SQLCHAR strCity[CITY_LEN];
char reply=' ';
// Update record
if (base==UPDATE) {
getInt ("Enter Record No (0 - 9)", &srcOffset, 'N', 0);
if (srcOffset<DATA_ARRAY_SIZE) {
// Ask for new FirstName
printf ("Current First Name : %.20s\n", FirstName[srcOffset]);
memset(strFirstName, ' ', FIRSTNAME_LEN);
reply=getStr ("New First Name ", strFirstName, FIRSTNAME_LEN, 'N');
// Move new FirstName into Update area and remove NULL
memset(FirstName[srcOffset], ' ', FIRSTNAME_LEN);
strFirstName[strlen(strFirstName)]= ' ';
memcpy(FirstName[srcOffset], strFirstName, FIRSTNAME_LEN);
// Set lengths for Update to lengths of fields
PersonIDLenArr[srcOffset]=SQL_COLUMN_IGNORE;
FirstNameLenArr[srcOffset]=FIRSTNAME_LEN;
LastNameLenArr[srcOffset]=LASTNAME_LEN;
AddressLenArr[srcOffset]=ADDRESS_LEN;
CityLenArr[srcOffset]=CITY_LEN;
// Return row number of update record
return srcOffset+1;
}
}
// Insert record
if (base==INSERT) {
// Clear insert buffer
clearBuffers (DATA_ARRAY_SIZE-1, 1);
// Ask for new FirstName
memset(strFirstName, ' ', FIRSTNAME_LEN);
reply=getStr ("New First Name ", strFirstName, FIRSTNAME_LEN, 'N');
memset(FirstName[DATA_ARRAY_SIZE-1], ' ', FIRSTNAME_LEN);
strFirstName[strlen(strFirstName)]= ' ';
memcpy(FirstName[DATA_ARRAY_SIZE-1], strFirstName, FIRSTNAME_LEN);
// Ask for new LastName
memset(strLastName, ' ', LASTNAME_LEN);
reply=getStr ("New Last Name ", strLastName, LASTNAME_LEN, 'N');
memset(LastName[DATA_ARRAY_SIZE-1], ' ', LASTNAME_LEN);
strLastName[strlen(strLastName)]= ' ';
memcpy(LastName[DATA_ARRAY_SIZE-1], strLastName, LASTNAME_LEN);
// Ask for new Address
memset(strAddress, ' ', ADDRESS_LEN);
reply=getStr ("New Address ", strAddress, ADDRESS_LEN, 'N');
memset(Address[DATA_ARRAY_SIZE-1], ' ', ADDRESS_LEN);
strAddress[strlen(strAddress)]= ' ';
memcpy(Address[DATA_ARRAY_SIZE-1], strAddress, ADDRESS_LEN);
// Ask for new City
memset(strCity, ' ', CITY_LEN);
reply=getStr ("New City ", strCity, CITY_LEN, 'N');
memset(City[DATA_ARRAY_SIZE-1], ' ', CITY_LEN);
strCity[strlen(strCity)]= ' ';
memcpy(City[DATA_ARRAY_SIZE-1], strCity, CITY_LEN);
// Set lengths for Insert to lengths of fields
PersonIDLenArr[DATA_ARRAY_SIZE-1]=SQL_COLUMN_IGNORE;
FirstNameLenArr[DATA_ARRAY_SIZE-1]=FIRSTNAME_LEN;
LastNameLenArr[DATA_ARRAY_SIZE-1]=LASTNAME_LEN;
AddressLenArr[DATA_ARRAY_SIZE-1]=ADDRESS_LEN;
CityLenArr[DATA_ARRAY_SIZE-1]=CITY_LEN;
// Return row number of update record
return DATA_ARRAY_SIZE;
}
// Delete record
if (base==DELETE) {
getInt ("Enter Record No (0 - 9)", &srcOffset, 'N', 0);
if (srcOffset<DATA_ARRAY_SIZE) {
// return row number of record to delete
printf ("Deleting row %i\n", srcOffset+1);
return srcOffset+1;
}
}
return -1;
}
// Display row being inserted, updated or deleted
void DisplayRow(int action, int RowNum) {
if (action==UPDATE) {
printf ("Updating Row %i\n", RowNum);
}
if (action==INSERT) {
printf ("Inserting Row %i\n", RowNum);
}
if (action==DELETE) {
printf ("Deleting Row %i\n", RowNum);
}
printf ("Record : %.10s,", FirstName[RowNum-1]);
printf ("'%.10s',", FirstName[RowNum-1]);
printf ("'%.10s',", LastName[RowNum-1]);
printf ("'%.10s',", Address[RowNum-1]);
printf ("'%.10s'\n", City[RowNum-1]);
}
int main () {
int i;
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,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 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);
// Set to autocommit
retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)TRUE,0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Set the following statement attributes:
// SQL_ATTR_CURSOR_TYPE: Keyset-driven
// SQL_ATTR_ROW_BIND_TYPE: SQL_BIND_BY_COLUMN
// SQL_ATTR_ROW_ARRAY_SIZE: 10
// SQL_ATTR_ROW_STATUS_PTR: Points to RowStatusArray
// SQL_ATTR_CONCURRENCY: Sets Concurrency
// (because default is READ ONLY)
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE,
SQL_BIND_BY_COLUMN, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER)10, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR,
RowStatusArray, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY,
(SQLPOINTER)SQL_CONCUR_LOCK ,0); // not in Microsoft example
// Bind arrays to the PersonID, FirstName, LastName, Address,
// and City columns.
retcode = SQLBindCol(hstmt, 1, SQL_C_ULONG,
&PersonID, 0,
PersonIDLenArr);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR,
FirstName, sizeof(FirstName[0]),
FirstNameLenArr);
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR,
LastName, sizeof(LastName[0]),
LastNameLenArr);
retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR,
Address, sizeof(Address[0]),
AddressLenArr);
retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR,
City, sizeof(City[0]),
CityLenArr);
// Execute a statement to retrieve rows from the Persons table.
retcode = SQLExecDirect(hstmt, (SQLCHAR*)
"SELECT PersonID, FirstName, LastName, Address, City "
"FROM TestTBL1", SQL_NTS);
// Fetch and display the first 3 rows.
retcode = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
if (retcode == SQL_NO_DATA) {
printf ("SQL_NO_DATA\n");
}
// Call GetAction to get an action and a row number from the user.
while (GetAction(RowStatusArray, &Action, &RowNum)) {
if (Action==0) break;
if (Action==REFRESH_ROW) {
Action=SQL_FETCH_FIRST;
}
switch (Action) {
case SQL_FETCH_NEXT:
case SQL_FETCH_PRIOR:
case SQL_FETCH_FIRST:
case SQL_FETCH_LAST:
case SQL_FETCH_ABSOLUTE:
case SQL_FETCH_RELATIVE:
clearBuffers (0, DATA_ARRAY_SIZE);
// Fetch and display the requested data.
retcode = SQLFetchScroll(hstmt, Action, RowNum);
if (retcode == SQL_NO_DATA) {
printf ("SQL_NO_DATA\n");
}
break;
// SQLRowCount returns the number of rows affected by an
// UPDATE, INSERT, or DELETE statement;
// an SQL_ADD, SQL_UPDATE_BY_BOOKMARK, or SQL_
// DELETE_BY_BOOKMARK operation in SQLBulkOperations;
// or an SQL_UPDATE or SQL_DELETE operation in SQLSetPos.
case UPDATE_ROW:
RowNum = GetNewCustData(UPDATE);
DisplayRow(UPDATE, RowNum);
SQLSetPos(hstmt, RowNum, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
SQLRowCount (hstmt, &rowCount);
printf ("Rows Effected %i\n", (int) rowCount);
break;
case DELETE_ROW:
RowNum = GetNewCustData(DELETE);
DisplayRow(DELETE, RowNum);
SQLSetPos(hstmt, RowNum, SQL_DELETE, SQL_LOCK_NO_CHANGE);
SQLRowCount (hstmt, &rowCount);
printf ("Rows Effected %i\n", (int) rowCount);
clearBuffers (RowNum-1, 1);
break;
case INSERT_ROW:
RowNum = GetNewCustData(INSERT);
DisplayRow(INSERT, 11);
retcode = SQLSetPos(hstmt, 11, SQL_ADD, SQL_LOCK_NO_CHANGE);
if (retcode != SQL_SUCCESS &&
retcode != SQL_SUCCESS_WITH_INFO) {
extract_error("SQLSetPos - INSERT ROW",
hstmt, SQL_HANDLE_STMT);
} else {
SQLRowCount (hstmt, &rowCount);
printf ("Rows Effected %i\n", (int) rowCount);
}
clearBuffers (10, 1);
break;
}
}
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.