| Some SQL statements, like SELECT, can return a table of results that must be retrieved by the program. The IBM manuals refer to these as result tables. Stored procedures invoked by the SQL CALL statement may return multiple result tables, and each is referred to as a result set. In this chapter we'll use the term result set to refer to either a result table from a query or a result set from a stored procedure, because the way they are handled in application programs is indistinguishable. In DB2 ODBC, as in embedded SQL, result sets are retrieved using cursors . Similar to SQLBindParameter() for parameter markers in the SQL statement, result set columns are associated with application variables . The SQLBindCol() function handles this for cursors. SQLRETURN SQLBindCol (SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLSMALLINT CDataType, SQLPOINTER Buffer, SQLINTEGER BufferMaxSize, SQLINTEGER *BufferDataSize); SQLBindCol() is called for every result set column the program wishes to retrieve. Unlike SQLBindParameter(), columns bound to result sets can have gaps. Any column that does not have a variable bound to it is not returned to the application, though it would be more efficient to omit the column from the result set in the first place. There are fewer parameters on SQLBindCol() because DB2 already knows the SQL data type. The C data type must still be specified, and DB2 will perform any necessary data conversion. After the row is fetched , BufferDataSize will contain the length of the data placed in the buffer for this column. If the column contains a null value, this field will be set to SQL_NULL_DATA. The associations for bound columns remain in effect until the statement handle is deallocated or another SQLBindCol() call is made for the same ColumnNumber. Once the columns have been bound to application variables, the result rows may be fetched using either SQLFetch() or SQLExtendedFetch(). These different fetch methods may not be intermixed. SQLExtendedFetch() has been deprecated in ODBC 3.0, so its use is not recommended. SQLRETURN SQLFetch (SQLHSTMT StatementHandle); SQLRETURN SQLExtendedFetch (SQLHSTMT StatementHandle, SQLUSMALLINT FetchType, SQLINTEGER irow, SQLUINTEGER *pcrow, SQLUSMALLINT *rgfRowStatus); The SQLFetch() function retrieves a single row and places the column values in the host variables associated with them in the preceding SQLBindCol() calls. SQLExtendedFetch() retrieves one or more rows from the result set (called a rowset ) as an array for each bound column. The number of rows in the rowset is determined using the SQL_ROWSET_SIZE statement attribute, which can be set using the SQLSetStmtAttr() function. FetchType should contain SQL_FETCH_NEXT to retrieve the next row or rowset. Other values available for FetchType are discussed later under the SQLFetchScroll() function when we discuss scrollable cursors. Getting Column Metadata If the SQL statement isn't known at the time the program is written, you can find out what the result set looks like at runtime. Use SQLNumResultCols() to find out how many columns were returned, then use SQLDescribeCol() and/or SQLColAttribute() to get the details about each column. SQLRETURN SQLNumResultCols(SQLHSTMT StatementHandle, SQLSMALLINT *pccol); SQLRETURN SQLDescribeCol (SQLHSTMT StatementHandle, SQLUSMALLINT ColNumber, SQLCHAR *ColName, SQLSMALLINT ColNameMaxLen, SQLSMALLINT *ColNameLen, SQLSMALLINT *SqlDataType, SQLUINTEGER *ColLen, SQLSMALLINT *Scale, SQLSMALLINT *Nullable); SQLRETURN SQLColAttribute (SQLHSTMT StatementHandle, SQLSMALLINT ColumnNumber, SQLSMALLINT FieldId, SQLPOINTER CharAttrPtr, SQLSMALLINT BufferLength, SQLSMALLINT *StringLengthPtr, SQLPOINTER NumericAttributePtr); Multiple Result Sets There are times when an SQL statement may return multiple result sets. This may be from a stored procedure or a parameterized query with an array of input parameter values. When there are multiple result sets, they must be retrieved sequentially. To begin processing the next result set, use the SQLMoreResults() function. SQLRETURN SQLMoreResults (SQLHSTMT StatementHandle); If the SQLMoreResults() call is successful, the application can begin fetching rows for the new cursor. Don't forget to do SQLBindCol() calls if the result columns are different from the prior result set. When all the result sets have been processed , SQLMoreResults() returns SQL_NO_DATA_FOUND. Scrollable Cursors As of version 7, DB2 for OS/390 and z/OS does include support for scrollable cursors. The manuals do not include information on how to use scrollable cursors in ODBC applications, but we will discuss how to use them. Scrollable cursors let you move forward, backward, or randomly through a result set. Scrollable cursors cannot be used in applications that use DB2 private protocol access. Scrollable cursors are also discussed in more detail in Chapter 7, "Application Program Features." Programs use three new functions to use scrollable cursors. Fetching from Scrollable Cursors SQLFetchScroll() fetches the specified number of rows from the result set. The cursor can be moved to a relative or absolute position within the result set. SQLRETURN SQLFetchScroll (SQLHSTMT StatementHandle, SQLUSMALLINT FetchType, SQLINTEGER FetchOffset); The statement uses the FetchType and FetchOffset values to decide where the cursor is to be positioned within the result set. The function can return multiple rows from a single execution. The number of rows to return is set in the SQL_ATTR_ROW_ARRAY_SIZE statement attribute. Allowable FetchType values include the following: -
SQL_FETCH_NEXT returns the next set of rows following the current cursor position. -
SQL_FETCH_PRIOR returns the set number of rows starting at the row preceding the current cursor position. -
SQL_FETCH_RELATIVE returns the set number of rows starting at FetchOffset from the current cursor position. -
SQL_FETCH_ABSOLUTE returns the set number of rows starting at FetchOffset from the start of the result set. -
SQL_FETCH_FIRST returns the set number of rows starting at the beginning of the result set. -
SQL_FETCH_LAST returns the set number of rows before the last row in the result set. -
SQL_FETCH_BOOKMARK returns the set number of rows from the position identified by the bookmark specified by the SQL_ATTR_FETCH_BOOKMARK_PTR statement attribute. Positioning Scrollable Cursors SQLSetPos() positions the cursor on a specific row of the result set. It can also be used to refresh, update, or to delete the entire row. SQLRETURN SQLSetPos (SQLHSTMT StatementHandle, SQLUSMALLINT RowNumber, SQLUSMALLINT Operation, SQLSMALLINT LockType); The RowNumber value specifies the absolute position within the result set. Use 1 to position at the first row. If 0 is specified the Operation parameter is performed for the entire result set. The Operation parameter can have the following values: -
SQL_POSITION for positioning the cursor at RowNumber without refreshing any data. -
SQL_REFRESH for positioning the cursor at RowNumber and refreshing the data. The WHERE clause is not reapplied, so any modified rows in the result set may still show up. -
SQL_UPDATE for positioning the cursor at RowNumber and updating the row with values from the buffer. -
SQL_DELETE for positioning the cursor at RowNumber and deleting that row. The only value that DB2 ODBC supports for the LockType parameter is SQL_LOCK_TYPE_CHANGE. This retains the locks taken prior to the SQLSetPos() call. Bulk Operations on Scrollable Cursors SQLBulkOperations() allows mass operations against the result set of a scrollable cursor query. New rows can be added. Mass updates or deletes can be performed on bookmarked rows. SQLRETURN SQLBulkOperations (SQLHSTMT StatementHandle, SQLSMALLINT Operation); | 
