A SELECT statement is used to retrieve data that meets a given set of specifications. For example, SELECT * FROM EMPLOYEE WHERE EMPNAME = "Jones" is used to retrieve all columns of all rows in EMPLOYEE where the employee’s name is Jones. ODBC extension functions also can retrieve data. For example, SQLColumns retrieves data about columns in the data source. These sets of data, called result sets, can contain zero or more rows.
Note that other SQL statements, such as GRANT or REVOKE, do not return result sets. For these statements, the return code from SQLExecute or SQLExecDirect is usually the only source of information as to whether the statement was successful. (For INSERT, UPDATE, and DELETE statements, an application can call SQLRowCount to return the number of affected rows.)
The steps an application takes to process a result set depends on what is known about it.
- Known result set The application knows the exact form of the SQL statement, and therefore the result set, at compile time. For example, the query SELECT EMPNO, EMPNAME FROM EMPLOYEE returns two specific columns.
- Unknown result set The application does not know the exact form of the SQL statement, and therefore the result set, at compile time. For example, the ad hoc query SELECT * FROM EMPLOYEE returns all currently defined columns in the EMPLOYEE table. The application may not be able to predict the format of these results prior to execution.
An application can assign storage for results before or after it executes an SQL statement. If an application prepares or executes the SQL statement first, it can inquire about the result set before it assigns storage for results. For example, if the result set is unknown, the application must retrieve the number of columns before it can assign storage for them.
To associate storage for a column of data, an application calls SQLBindCol and passes it the following information:
- The data type to which the data is to be converted. For more information, see "Converting Data from SQL to C Data Types" in Appendix D, "Data Types."
- The address of an output buffer for the data. The application must allocate this buffer and it must be large enough to hold the data in the form to which it is converted.
- The length of the output buffer. This value is ignored if the returned data has a fixed width in C, such as an integer, real number, or date structure.
- The address of a storage buffer in which to return the number of bytes of available data.
To determine the characteristics of a result set, an application can:
- Call SQLNumResultCols to determine how many columns a request returned.
- Call SQLColAttributes or SQLDescribeCol to describe a column in the result set.
If the result set is unknown, an application can use the information returned by these functions to bind the columns in the result set. An application can call these functions at any time after a statement is prepared or executed. Note that, although SQLRowCount can sometimes return the number of rows in a result set, it is not guaranteed to do so. Few data sources support this functionality and interoperable applications should not rely on it.
Note For optimal performance, an application should call SQLColAttributes, SQLDescribeCol, and SQLNumResultCols after a statement is executed. In data sources that emulate statement preparation, these functions sometimes execute more slowly before a statement is executed because the information returned by them is not readily available until after the statement is executed.
To retrieve a row of data from the result set, an application:
- 1. Calls SQLBindCol to bind the columns of the result set to storage locations if it has not already done so.
- 2. Calls SQLFetch to move to the next row in the result set and retrieve data for all bound columns.
To keep track of its position in the result set, a driver maintains a cursor. The cursor is so named because it indicates the current position in the result set, just as the cursor on a CRT screen indicates current position.
Each time an application calls SQLFetch, the driver moves the cursor to the next row and returns that row. The cursor supported by the core ODBC functions only scrolls forward, one row at a time. (To reretrieve a row of data that it has already retrieved from the result set, the application must close the cursor by calling SQLFreeStmt with the SQL_CLOSE option, reexecute the SELECT statement, and fetch rows with SQLFetch until the target row is retrieved.)
Important Committing or rolling back a transaction, either by calling SQLTransact or by using the SQL_AUTOCOMMIT connection option, can cause the data source to close the cursors for all hstmts on an hdbc. For more information, see the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR information types in SQLGetInfo.
ODBC extends the X/Open and SQL Access Group Call Level Interface to provide additional functions related to retrieving results. The remainder of this chapter describes these functions. To determine if a driver supports a specific function, an application calls SQLGetFunctions.
To retrieve data from unbound columns — that is, columns for which storage has not been assigned with SQLBindCol — an application uses SQLGetData. The application first calls SQLFetch or SQLExtendedFetch to position the cursor on the next row. It then calls SQLGetData to retrieve data from specific unbound columns.
An application may retrieve data from both bound and unbound columns in the same row. It calls SQLBindCol to bind as many columns as desired. It calls SQLFetch or SQLExtendedFetch to position the cursor on the next row of the result set and retrieve all bound columns. It then calls SQLGetData to retrieve data from unbound columns.
If the data type of a column is character, binary, or data source–specific and the column contains more data than can be retrieved in a single call, an application may call SQLGetData more than once for that column, as long as the data is being transferred to a buffer of type SQL_C_CHAR or SQL_C_BINARY. For example, data of the SQL_LONGVARBINARY and SQL_LONGVARCHAR types may need to be retrieved in several parts.
For maximum interoperability, an application should only call SQLGetData for columns to the right of the rightmost bound column and then only in left-to-right order. To determine if a driver can return data with SQLGetData for any column (including unbound columns before the last bound column and any bound columns) or in any order, an application calls SQLGetInfo with the SQL_GETDATA_EXTENSIONS option.
In addition to binding individual rows of data, an application can call SQLBindCol to assign storage for a rowset (one or more rows of data). By default, rowsets are bound in column-wise fashion. They can also be bound in row-wise fashion.
To specify how many rows of data are in a rowset, an application calls SQLSetStmtOption with the SQL_ROWSET_SIZE option.
Column-Wise Binding
To assign storage for column-wise bound results, an application performs the following steps for each column to be bound:
- 1. Allocates an array of data storage buffers. The array has as many elements as there are rows in the rowset.
- 2. Allocates an array of storage buffers to hold the number of bytes available to return for each data value. The array has as many elements as there are rows in the rowset.
- 3. Calls SQLBindCol and specifies the address of the data array, the size of one element of the data array, the address of the number-of-bytes array, and the type to which the data will be converted. When data is retrieved, the driver will use the array element size to determine where to store successive rows of data in the array.
Row-Wise Binding
To assign storage for row-wise bound results, an application performs the following steps:
- 1. Declares a structure that can hold a single row of retrieved data and the associated data lengths. (For each column to be bound, the structure contains one field to contain data and one field to contain the number of bytes of data available to return.)
- 2. Allocates an array of these structures. This array has as many elements as there are rows in the rowset.
- 3. Calls SQLBindCol for each column to be bound. In each call, the application specifies the address of the column’s data field in the first array element, the size of the data field, the address of the column’s number-of-bytes field in the first array element, and the type to which the data will be converted.
- 4. Calls SQLSetStmtOption with the SQL_BIND_TYPE option and specifies the size of the structure. When the data is retrieved, the driver will use the structure size to determine where to store successive rows of data in the array.
Before it retrieves rowset data, an application calls SQLSetStmtOption with the SQL_ROWSET_SIZE option to specify the number of rows in the rowset. It then binds columns in the rowset with SQLBindCol. The rowset may be bound in column-wise or row-wise fashion. For more information, see "Assigning Storage for Rowsets (Binding)" previous in this chapter.
To retrieve rowset data, an application calls SQLExtendedFetch.
For maximum interoperability, an application should not use SQLGetData to retrieve data from unbound columns in a block (more than one row) of data that has been retrieved with SQLExtendedFetch. To determine if a driver can return data with SQLGetData from a block of data, an application calls SQLGetInfo with the SQL_GETDATA_EXTENSIONS option.
As originally designed, cursors in SQL only scroll forward through a result set, returning one row at a time. However, interactive applications often require forward and backward scrolling, absolute or relative positioning within the result set, and the ability to retrieve and update blocks of data, or rowsets.
To retrieve and update rowset data, ODBC provides a block cursor attribute. To allow an application to scroll forwards or backwards through the result set, or move to an absolute or relative position in the result set, ODBC provides a scrollable cursor attribute. Cursors may have one or both attributes.
Block Cursors
An application calls SQLSetStmtOption with the SQL_ROWSET_SIZE option to specify the rowset size. The application can call SQLSetStmtOption to change the rowset size at any time. Each time the application calls SQLExtendedFetch, the driver returns the next rowset size rows of data. After the data is returned, the cursor points to the first row in the rowset. By default, the rowset size is one.
Scrollable Cursors
Applications have different needs in their ability to sense changes in the tables underlying a result set. For example, when balancing financial data, an accountant needs data that appears static; it is impossible to balance books when the data is continually changing. When selling concert tickets, a clerk needs up-to-the minute, or dynamic, data on which tickets are still available. Various cursor models are designed to meet these needs, each of which requires different sensitivities to changes in the tables underlying the result set.
Static Cursors
At one extreme are static cursors, to which the data in the underlying tables appears to be static. The membership, order, and values in the result set used by a static cursor are generally fixed when the cursor is opened. Rows updated, deleted, or inserted by other users (including other cursors in the same application) are not detected by the cursor until it is closed and then reopened; the SQL_STATIC_SENSITIVITY information type returns whether the cursor can detect rows it has updated, deleted, or inserted.
Static cursors are commonly implemented by taking a snapshot of the data or locking the result set. Note that in the former case, the cursor diverges from the underlying tables as other users make changes; in the latter case, other users are prohibited from changing the data.
Dynamic Cursors
At the other extreme are dynamic cursors, to which the data appears to be dynamic. The membership, order, and values in the result set used by a dynamic cursor are ever-changing. Rows updated, deleted, or inserted by all users (the cursor, other cursors in the same application, and other applications) are detected by the cursor when data is next fetched. Although ideal for many situations, dynamic cursors are difficult to implement.
Keyset-Driven Cursors
Between static and dynamic cursors are keyset-driven cursors, which have some of the attributes of each. Like static cursors, the membership and ordering of the result set of a keyset-driven cursor is generally fixed when the cursor is opened. Like dynamic cursors, most changes to the values in the underlying result set are visible to the cursor when data is next fetched.
When a keyset-driven cursor is opened, the driver saves the keys for the entire result set, thus fixing the membership and order of the result set. As the cursor scrolls through the result set, the driver uses the keys in this keyset to retrieve the current data values for each row in the rowset. Because data values are retrieved only when the cursor scrolls to a given row, updates to that row by other users (including other cursors in the same application) after the cursor was opened are visible to the cursor.
If the cursor scrolls to a row of data that has been deleted by other users (including other cursors in the same application), the row appears as a hole in the result set, since the key is still in the keyset but the row is no longer in the result set. Updating the key values in a row is considered to be deleting the existing row and inserting a new row; therefore, rows of data for which the key values have been changed also appear as holes. When the driver encounters a hole in the result set, it returns a status code of SQL_ROW_DELETED for the row.
Rows of data inserted into the result set by other users (including other cursors in the same application) after the cursor was opened are not visible to the cursor, since the keys for those rows are not in the keyset.
The SQL_STATIC_SENSITIVITY information type returns whether the cursor can detect rows it has deleted or inserted. Because updating key values in a keyset-driven cursor is considered to be deleting the existing row and inserting a new row, keyset-driven cursors can always detect rows they have updated.
Mixed (Keyset/Dynamic) Cursors
If a result set is large, it may be impractical for the driver to save the keys for the entire result set. Instead, the application can use a mixed cursor. In a mixed cursor, the keyset is smaller than the result set, but larger than the rowset.
Within the boundaries of the keyset, a mixed cursor is keyset-driven, that is, the driver uses keys to retrieve the current data values for each row in the rowset. When a mixed cursor scrolls beyond the boundaries of the keyset, it becomes dynamic, that is, the driver simply retrieves the next rowset size rows of data. The driver then constructs a new keyset, which contains the new rowset.
For example, assume a result set has 1000 rows and uses a mixed cursor with a keyset size of 100 and a rowset size of 10. When the cursor is opened, the driver (depending on the implementation) saves keys for the first 100 rows and retrieves data for the first 10 rows. If another user deletes row 11 and the cursor then scrolls to row 11, the cursor will detect a hole in the result set; the key for row 11 is in the keyset but the data is no longer in the result set. This is the same behavior as a keyset-driven cursor. However, if another user deletes row 101 and the cursor then scrolls to row 101, the cursor will not detect a hole; the key for the row 101 is not in the keyset. Instead, the cursor will retrieve the data for the row that was originally row 102. This is the same behavior as a dynamic cursor.
Specifying the Cursor Type
To specify the cursor type, an application calls SQLSetStmtOption with the SQL_CURSOR_TYPE option. The application can specify a cursor that only scrolls forward, a static cursor, a dynamic cursor, a keyset-driven cursor, or a mixed cursor. If the application specifies a mixed cursor, it also specifies the size of the keyset used by the cursor.
Note To use the ODBC cursor library, an application calls SQLSetConnectOption with the SQL_ODBC_CURSORS option before it connects to the data source. The cursor library supports block scrollable cursors. It also supports positioned update and delete statements.
Unless the cursor is a forward-only cursor, an application calls SQLExtendedFetch to scroll the cursor backwards, forwards, or to an absolute or relative position in the result set. The application calls SQLSetPos to refresh the row currently pointed to by the cursor.
Specifying Cursor Concurrency
Concurrency is the ability of more than one user to use the same data at the same time. A transaction is serializable if it is performed in a manner in which it appears as if no other transactions operate on the same data at the same time. For example, assume one transaction doubles data values and another adds 1 to data values. If the transactions are serializable and both attempt to operate on the values 0 and 10 at the same time, the final values will be 1 and 21 or 2 and 22, depending on which transaction is performed first. If the transactions are not serializable, the final values will be 1 and 21, 2 and 22, 1 and 22, or 2 and 21; the sets of values 1 and 22, and 2 and 21, are the result of the transactions acting on each value in a different order.
Serializability is considered necessary to maintain database integrity. For cursors, it is most easily implemented at the expense of concurrency by locking the result set. A compromise between serializability and concurrency is optimistic concurrency control. In a cursor using optimistic concurrency control, the driver does not lock rows when it retrieves them. When the application requests an update or delete operation, the driver or data source checks if the row has changed. If the row has not changed, the driver or data source prevents other transactions from changing the row until the operation is complete. If the row has changed, the transaction containing the update or delete operation fails.
To specify the concurrency used by a cursor, an application calls SQLSetStmtOption with the SQL_CONCURRENCY option. The application can specify that the cursor is read-only, locks the result set, uses optimistic concurrency control and compares row versions to determine if a row has changed, or uses optimistic concurrency control and compares data values to determine if a row has changed. The application calls SQLSetPos to lock the row currently pointed to by the cursor, regardless of the specified cursor concurrency.
A bookmark is a 32-bit value that an application uses to return to a row. The application does not request that the driver places a bookmark on a row; instead, the application requests a bookmark that it can use to return to a row. For example, if a bookmark is a row number, an application requests the row number of a row and stores it. Later, the application passes this row number back to the driver and requests that the driver return to the row.
Before opening the cursor, an application must call SQLSetStmtOption with the SQL_USE_BOOKMARKS option to inform the driver it will use bookmarks. After opening the cursor, the application retrieves bookmarks either from column 0 of the result set or by calling SQLGetStmtOption with the SQL_GET_BOOKMARK option. To retrieve a bookmark from the result set, the application either binds column 0 and calls SQLExtendedFetch or calls SQLGetData; in either case, the fCType argument must be set to SQL_C_BOOKMARK. To return to the row specified by a bookmark, the application calls SQLExtendedFetch with a fetch type of SQL_FETCH_BOOKMARK.
If a bookmark requires more than 32 bits, such as when it is a key value, the driver maps the bookmarks requested by the application to 32-bit binary values. The 32-bit binary values are then returned to the application. Because this mapping may require considerable memory, applications should only bind column 0 of the result set if they will actually use bookmarks for most rows. Otherwise, they should call SQLGetStmtOption with the SQL_BOOKMARK statement option or call SQLGetData for column 0.
Before an application opens a cursor with which it will use bookmarks, it:
- Retrieves the value from column 0 of the rowset. The application can either call SQLBindCol to bind column 0 before it calls SQLExtendedFetch or call SQLGetData to retrieve the data after it calls SQLExtendedFetch. In either case, the fCType argument must be SQL_C_BOOKMARK.
Note To determine whether it can call SQLGetData for a block (more than one row) of data and whether it can call SQLGetData for a column before the last bound column, an application calls SQLGetInfo with the SQL_GETDATA_EXTENSIONS information type.
- – Or –
- Calls SQLSetPos with the SQL_POSITION option to position the cursor on the row and calls SQLGetStmtOption with the SQL_BOOKMARK option to retrieve the bookmark.
To return to the row specified by a bookmark (or a row a certain number of rows from the bookmark), an application:
- Calls SQLExtendedFetch with the irow argument set to the bookmark and the fFetchType argument set to SQL_FETCH_BOOKMARK. The driver returns the rowset starting with the row identified by the bookmark.
ODBC provides two ways to modify data in the result set. Positioned update and delete statements are similar to such statements in embedded SQL. Calls to SQLSetPos allow an application to update, delete, or add new data without executing SQL statements.
Executing Positioned Update and Delete Statements
An application can update or delete the row in the result set currently pointed to by the cursor. This is known as a positioned update or delete statement. After executing a SELECT statement to create a result set, an application calls SQLFetch one or more times to position the cursor on the row to be updated or deleted. Alternatively, it fetches the rowset with SQLExtendedFetch and positions the cursor on the desired row by calling SQLSetPos with the SQL_POSITION option. To update or delete the row, the application then executes an SQL statement with the following syntax on a different hstmt:
UPDATE table-name
SET column-identifier = {expression | NULL}
[, column-identifier = {expression | NULL}]...
WHERE CURRENT OF cursor-name DELETE FROM table-name WHERE CURRENT OF cursor-name
Positioned update and delete statements require cursor names. An application can name a cursor with SQLSetCursorName. If the application has not named the cursor by the time the driver executes a SELECT statement, the driver generates a cursor name. To retrieve the cursor name for an hstmt, an application calls SQLGetCursorName.
To execute a positioned update or delete statement, an application must follow these guidelines:
- The SELECT statement that creates the result set must use a FOR UPDATE clause.
- The cursor name used in the UPDATE or DELETE statement must be the same as the cursor name associated with the SELECT statement.
- The application must use different hstmts for the SELECT statement and the UPDATE or DELETE statement.
- The hstmts for the SELECT statement and the UPDATE or DELETE statement must be on the same connection.
To determine if a data source supports positioned update and delete statements, an application calls SQLGetInfo with the SQL_POSITIONED_STATEMENTS option. For an example of code that performs a positioned update in a rowset, see SQLSetPos in Chapter 13, "Function Reference."
Note In ODBC 1.0, positioned update, positioned delete, and SELECT FOR UPDATE statements were part of the core SQL grammar; in ODBC 2.0, they are part of the extended grammar. Applications that use the SQL conformance level to determine whether these statements are supported also need to check the version number of the driver to correctly interpret the information. In particular, applications that use these features with ODBC 1.0 drivers need to explicitly check for these capabilities in ODBC 2.0 drivers.
Modifying Data with SQLSetPos
To add, update, and delete rows of data, an application calls SQLSetPos and specifies the operation, the row number, and how to lock the row. Where new rows of data are added to the result set, and whether they are visible to the cursor is data source–defined.
The row number determines both the number of the row in the rowset to update or delete and the index of the row in the rowset buffers from which to retrieve data to add or update. If the row number is 0, the operation affects all of the rows in the rowset.
SQLSetPos retrieves the data to update or add from the rowset buffers. It only updates those columns in a row that have been bound with SQLBindCol and do not have a length of SQL_IGNORE. However, it cannot add a new row of data unless all of the columns in the row are bound, are nullable, or have a default value.
Note The rowset buffers are used both to send and retrieve data. To avoid overwriting existing data when it adds a new row of data, an application can allocate an extra row at the end of the rowset buffers to use as an add buffer.
- 1. Places the data for each column the rgbValue buffers specified with SQLBindCol. To avoid overwriting an existing row of data, the application should allocate an extra row of the rowset buffers to use as an add buffer.
- 2. Places the length of each column in the pcbValue buffer specified with SQLBindCol; this only needs to be done for columns with an fCType of SQL_C_CHAR or SQL_C_BINARY. To use the default value for a column, the application specifies a length of SQL_IGNORE.
Note To add a new row of data to a result set, one of the following two conditions must be met:
All columns in the underlying tables must be bound
with SQLBindCol.
All unbound columns and all bound columns for
which the specified length is SQL_IGNORE must
accept NULL values or have default values.
- To determine if a row in a result set accepts NULL values, an application calls SQLColAttributes. To determine if a data source supports non-nullable columns, an application calls SQLGetInfo with the SQL_NON_NULLABLE flag.
- 3. Calls SQLSetPos with the fOption argument set to SQL_ADD. The irow argument determines the row in the rowset buffers from which the data is retrieved. For information about how an application sends data for data-at-execution columns, see SQLSetPos in Chapter 13, "Function Reference."
- After the row is added, the row the cursor points to is unchanged.
Note Columns for long data types, such as SQL_LONGVARCHAR and SQL_LONGVARBINARY, are generally not bound. However, if an application uses SQLSetPos to send data for these columns, it must bind them with SQLBindCol. Unless the driver returns the SQL_GD_BOUND bit for the SQL_GETDATA_EXTENSIONS information type, the application must unbind them before calling SQLGetData to retrieve data from them.
- 1. Modifies the data of each column to be updated in the rgbValue buffer specified with SQLBindCol.
- 2. Places the length of each column to be updated in the pcbValue buffer specified with SQLBindCol. This only needs to be done for columns with an fCType of SQL_C_CHAR or SQL_C_BINARY.
- 3. Sets the value of the pcbValue buffer for each bound column that is not to be updated to SQL_IGNORE.
- 4. Calls SQLSetPos with the fOption argument set to SQL_UPDATE. The irow argument specifies the number of the row in the rowset to modify and the index of row in the rowset buffer from which to retrieve the data. The cursor points to this row after it is updated.
For information about how an application sends data for data-at-execution columns, see SQLSetPos in Chapter 13, "Function Reference."
- Calls SQLSetPos with the fOption argument set to SQL_DELETE. The irow argument specifies the number of the row in the rowset to delete. The cursor points to this row after it is deleted.
Note The application cannot perform any positioned operations, such as executing a positioned update or delete statement or calling SQLGetData, on a deleted row.
To determine what operations a data source supports for SQLSetPos, an application calls SQLGetInfo with the SQL_POS_OPERATIONS flag.
SELECT statements return result sets. UPDATE, INSERT, and DELETE statements return a count of affected rows. If any of these statements are batched, submitted with arrays of parameters, or in procedures, they can return multiple result sets or counts.
To process a batch of statements, statement with arrays of parameters, or procedure returning multiple result sets or row counts, an application:
- 1. Calls SQLExecute or SQLExecDirect to execute the statement or procedure.
- 2. Calls SQLRowCount to determine the number of rows affected by an UPDATE, INSERT, or DELETE statement. For statements or procedures that return result sets, the application calls functions to determine the characteristics of the result set and retrieve data from the result set.
- 3. Calls SQLMoreResults to determine if another result set or row count is available.
- 4. Repeats steps 2 and 3 until SQLMoreResults returns SQL_NO_DATA_FOUND.
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.