Scrollable Cursors
The ODBC driver supports scrollable cursors through SQLFetchScroll() and SQLSetPos().
The driver supports static (read-only) and keyset-driven (updatable) cursor types. These cursor types allow the cursor to be positioned in any direction within a result set.
Static and keyset-driven cursors support the position directives described in
SQLFetchScroll()--Fetch from a Scrollable Cursor. In contrast, forward-only cursors support only SQL_FETCH_NEXT.
Note: Static and keyset-driven cursors can be used only if the target database is Ingres 9.2 and later. For Ingres databases prior to 9.2, the Cursor Library can be used to simulate these types of cursors.
Static Scrollable Cursors
Static cursors fetch rows as they are materialized from the current transaction isolation level. The result set is not updated if other sessions change data that applies to the result set. Static cursors are read-only.
Keyset-driven Scrollable Cursors
Keyset-driven cursors allow updates to selected records in the result set. Keyset-driven cursors require the target tables to include unique primary keys. If an attempt is made to update or delete records in a result set, and the corresponding records in the target table have been deleted, an error may be returned, depending on the transaction isolation level.
Keyset-driven cursors can be used within a read-only context, but do not perform as well as static cursors.
Scrollable Cursor Programming Considerations
The ODBC DSN definition or connection string must specify cursor loops, or name a cursor using the SQLSetCursorName() function. Select loops cannot be used with static or keyset-driven cursors.
Keyset-driven cursors require the cursor to be named. The cursor name must be included in the WHERE CURRENT OF clause in the update or delete query.
The SQLFetchScroll() function fetches records in the result set according to the directive specified in the FetchOrientation argument.
Cursor types can be specified in the SQLSetStmtAttr() function and queried by the SQLGetStmtAttr() function.
Use the SQLSetConnectvAttr() function to specify that the ODBC Driver is to be used for scrollable cursor functions.
SQLFetchScroll()--Fetch from a Scrollable Cursor
SQLFetchScroll() positions the cursor according to the specified fetch orientation and then retrieves data.
SQLFetchScroll has the following syntax:
SQLFetchScroll( StatementHandle, FetchOrientation, FetchOffset )
where:
FetchOrientation
Specifies the fetch orientation as one of the following:
SQL_FETCH_NEXT
Fetch the next record in the result set
SQL_FETCH_FIRST
Fetch the first record in the result set
SQL_FETCH_LAST
Fetch the last record in the result set
SQL_FETCH_PRIOR
Fetch the previous record in the result set
SQL_FETCH_ABSOLUTE
Fetch a record based on the position in the result set
SQL_FETCH_RELATIVE
Fetch relative to n rows from the current position in the result set
SQLSetPos()--Scroll Cursor to Absolute Position
SQLSetPos() allows the ODBC application to scroll the cursor to an absolute position within the result set and perform updates or deletes on the selected record.
Note: The SQLSetPos() function works for keyset-driven (updatable) cursors only.
SQLSetPos() has the following syntax:
SQLSetPos( StatementHandle, RowNumber, Operation, LockType )
where:
StatementHandle
Specifies the statement handle
RowNumber
Specifies the position of the record in the result set
Operation
Specifies the operation to perform: SQL_POSITION, SQL_UPDATE, or SQL_DELETE.
SQL_REFRESH is not supported.
LockType
(Not supported) Specifies the type of table lock. SQLSetPos() ignores all settings for the LockType argument.
Static Scrollable Cursor Example
The following code demonstrates the use of static scrollable cursors:
/*
** Specify that the Ingres ODBC Driver is used.
*/
SQLSetConnectAttr(hdbc, SQL_ATTR_ODBC_CURSORS,
SQL_CUR_USE_DRIVER,SQL_IS_INTEGER );
/* Set the cursor name. */
SQLSetCursorName(hstmt, "C1", SQL_NTS);
/* Set the number of rows in the rowset */
SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER) ROWSET_SIZE,
0);
/* Set the cursor type */
SQLSetStmtAttr( hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_STATIC,
0);
/* Set the pointer to the variable numrowsfetched: */
SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
&numrowsfetched,
0);
/* Set pointer to the row status array */
SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER) rowStatus,
0);
/* Execute the select query. */
strcpy((char *)sqlstmt,"SELECT y,x FROM myTable");
SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
/* Fetch last full result set. */
SQLFetchScroll(hstmt, SQL_FETCH_LAST, 0);
/* Fetch first result set. */
SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
/* Fetch next row. */
SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
/* Fetch the result set starting from the third row. */
SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 3);
/* Fetch the result set starting after moving up one row */
SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1);
Keyset-driven Scrollable Cursor Example
The following code demonstrates keyset-driven cursors:
/* Set the cursor name. */
SQLSetCursorName(hstmt, "CUPD1", SQL_NTS);
/* Set the cursor type */
SQLSetStmtAttr( hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_KEYSET,
0);
/* Execute select query */
SQLExecDirect(hstmtS, "SELECT x, y FROM keyset_cursor", SQL_NTS);
/* Fetch scrollable cursor */
SQLFetchScroll(hstmtS, SQL_FETCH_NEXT, 0)) != SQL_ERROR)
/* Move cursor to record 4 */
SQLSetPos(hstmtS, 4, SQL_POSITION, SQL_LOCK_NO_CHANGE);
/* Bind a string parameter */
rc = SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR,
TXT_LEN, 0, x[irow-1], 0, NULL);
/* Update the record */
SQLExecDirect(hstmtU,
"UPDATE keyset_cursor SET x=? WHERE CURRENT OF CUPD", SQL_NTS);