/**********************************************************************
* 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 MS 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;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.