SQLFetch

Conformance

Version Introduced:ODBC 1.0
Standards Compliance:ISO 92

Summary

SQLFetch fetches the next rowset of data from the result set and returns data for all bound columns.

Syntax

SQLRETURN SQLFetch(
SQLHSTMTStatementHandle);

Arguments

StatementHandle

[Input]
Statement handle.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLFetch returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by SQLFetch and explains each one in the context of this function; the notation “(DM)” precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise. If an error occurs on a single column, SQLGetDiagField can be called with a DiagIdentifier of SQL_DIAG_COLUMN_NUMBER to determine the column the error occurred on; and SQLGetDiagField can be called with a DiagIdentifier of SQL_DIAG_ROW_NUMBER to determine the row containing that column.

For all those SQLSTATEs that can return SQL_SUCCESS_WITH_INFO or SQL_ERROR (except 01xxx SQLSTATEs), SQL_SUCCESS_WITH_INFO is returned if an error occurs on one or more, but not all, rows of a multirow operation, and SQL_ERROR is returned if an error occurs on a single-row operation.

Comments

SQLFetch returns the next rowset in the result set. It can be called only while a result set exists — that is, after a call that creates a result set and before the cursor over that result set is closed. If any columns are bound, it returns the data in those columns. If the application has specified a pointer to a row status array or a buffer in which to return the number of rows fetched, SQLFetch returns this information as well. Calls to SQLFetch can be mixed with calls to SQLFetchScroll but cannot be mixed with calls to SQLExtendedFetch. For more information, see “Fetching a Row of Data” in Chapter 10, “Retrieving Results (Basic).”

If an ODBC 3.x application works with an ODBC 2.x driver, the Driver Manager maps SQLFetch calls to SQLExtendedFetch for an ODBC 2.x driver that supports SQLExtendedFetch. If the ODBC 2.x driver does not support SQLExtendedFetch, the Driver Manager maps SQLFetch calls to SQLFetch in the ODBC 2.x driver, which can only fetch a single row.

For more information, see “Block Cursors, Scrollable Cursors, and Backward Compatibility” in Appendix G, “Driver Guidelines for Backward Compatibility.”

Positioning the Cursor

When the result set is created, the cursor is positioned before the start of the result set. SQLFetch fetches the next rowset. It is equivalent to calling SQLFetchScroll with FetchOrientation set to SQL_FETCH_NEXT. For more information about cursors, see “Cursors” in Chapter 10, “Retrieving Results (Basic)” and “Block Cursors” in Chapter 11, “Retrieving Results (Advanced).”

The SQL_ATTR_ROW_ARRAY_SIZE statement attribute specifies the number of rows in the rowset. If the rowset being fetched by SQLFetch overlaps the end of the result set, SQLFetch returns a partial rowset. That is, if S + R – 1 is greater than L, where S is the starting row of the rowset being fetched, R is the rowset size, and L is the last row in the result set, then only the first L – S+1 rows of the rowset are valid. The remaining rows are empty and have a status of SQL_ROW_NOROW.

After SQLFetch returns, the current row is the first row of the rowset.

The following rules describe cursor positioning after a call to SQLFetch:

where:

For example, suppose a result set has 100 rows and the rowset size is 5. The following table shows the rowset and return code returned by SQLFetch for different starting positions.

Returning Data in Bound Columns

As SQLFetch returns each row, it places the data for each bound column in the buffer bound to that column. If no columns are bound, SQLFetch does not return any data but does move the block cursor forward. The data can still be retrieved with SQLGetData. If the cursor is a multirow cursor (that is, the SQL_ATTR_ROW_ARRAY_SIZE is greater than 1), SQLGetData can be called only if SQL_GD_BLOCK is returned when SQLGetInfo is called with an InfoType of SQL_GETDATA_EXTENSIONS. (For more information, see SQLGetData.)

For each bound column in a row, SQLFetch does the following:

1. Sets the length/indicator buffer to SQL_NULL_DATA and proceeds to the next column if the data is NULL. If the data is NULL and no length/indicator buffer was bound, SQLFetch returns SQLSTATE 22002 (Indicator variable required but not supplied) for the row and proceeds to the next row. For information about how to determine the address of the length/indicator buffer, see “Buffer Addresses” in SQLBindCol.

   If the data for the column is not NULL, SQLFetch proceeds to step 2.

2. If the SQL_ATTR_MAX_LENGTH statement attribute is set to a nonzero value and the column contains character or binary data, the data is truncated to SQL_ATTR_MAX_LENGTH bytes.

   Note The SQL_ATTR_MAX_LENGTH statement attribute is intended to reduce network traffic. It is generally implemented by the data source, which truncates the data before returning it across the network. Drivers and data sources are not required to support it. Therefore, to guarantee that data is truncated to a particular size, an application should allocate a buffer of that size and specify the size in the cbValueMax argument in SQLBindCol.

3. Converts the data to the type specified by TargetType in SQLBindCol.
   
   
4. If the data was converted to a variable-length data type, such as character or binary, SQLFetch checks whether the length of the data exceeds the length of the data buffer. If the length of character data (including the null-termination character) exceeds the length of the data buffer, SQLFetch truncates the data to the length of the data buffer less the length of a null-termination character. It then null-terminates the data. If the length of binary data exceeds the length of the data buffer, SQLFetch truncates it to the length of the data buffer. The length of the data buffer is specified with BufferLength in SQLBindCol.

   SQLFetch never truncates data converted to fixed-length data types; it always assumes that the length of the data buffer is the size of the data type.

5. Places the converted (and possibly truncated) data in the data buffer. For information about how to determine the address of the data buffer, see “Buffer Addresses” in SQLBindCol.
   
   
6. Places the length of the data in the length/indicator buffer. If the indicator pointer and the length pointer were both set to the same buffer (as a call to SQLBindCol does), the length is written in the buffer for valid data and SQL_NULL_DATA is written in the buffer for NULL data. If no length/indicator buffer was bound, SQLFetch does not return the length.
   • For character or binary data, this is the length of the data after conversion and before truncation due to the data buffer being too small. If the driver cannot determine the length of the data after conversion, as is sometimes the case with long data, it sets the length to SQL_NO_TOTAL. If data was truncated due to the SQL_ATTR_MAX_LENGTH statement attribute, the value of this attribute — as opposed to the actual length — is placed in the length/indicator buffer. This is because this attribute is designed to truncate data on the server before conversion, so the driver has no way of figuring out what the actual length is.
     
     
   • For all other data types, this is the length of the data after conversion; that is, it is the size of the type to which the data was converted.

   For information about how to determine the address of the length/indicator buffer, see “Buffer Addresses” in SQLBindCol.

7. If the data is truncated during conversion without a loss of significant digits (for example, the real number 1.234 is truncated when converted to the integer 1) and SQLFetch returns SQLSTATE 01S07 (Fractional truncation) and SQL_SUCCESS_WITH_INFO: If the data is truncated because the length of the data buffer is too small (for example, the string “abcdef” is placed in a 4-byte buffer), SQLFetch returns SQLSTATE 01004 (Data truncated) and SQL_SUCCESS_WITH_INFO. If data is truncated due to the SQL_ATTR_MAX_LENGTH statement attribute, SQLFetch returns SQL_SUCCESS and does not return SQLSTATE 01S07 (Fractional truncation) or SQLSTATE 01004 (Data truncated). If data is truncated during conversion with a loss of significant digits (for example, if an SQL_INTEGER value greater than 100,000 were converted to an SQL_C_TINYINT), SQLFetch returns SQLSTATE 22003 (Numeric value out of range) and SQL_ERROR (if the rowset size is 1) or SQL_SUCCESS_WITH_INFO (if the rowset size is greater than 1).

The contents of the bound data buffer and the length/indicator buffer are undefined if SQLFetch or SQLFetchScroll does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO.

Row Status Array

The row status array is used to return the status of each row in the rowset. The address of this array is specified with the SQL_ATTR_ROW_STATUS_PTR statement attribute. The array is allocated by the application and must have as many elements as are specified by the SQL_ATTR_ROW_ARRAY_SIZE statement attribute. Its values are set by SQLFetch, SQLFetchScroll, and SQLBulkOperations or SQLSetPos (except when they have been called after the cursor has been positioned by SQLExtendedFetch). If the value of the SQL_ATTR_ROW_STATUS_PTR statement attribute is a null pointer, these functions do not return the row status.

The contents of the row status array buffer are undefined if SQLFetch or SQLFetchScroll does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO.

The following values are returned in the row status array.

Rows Fetched Buffer

The rows fetched buffer is used to return the number of rows fetched, including those rows for which no data was returned because an error occurred while they were being fetched. In other words, it is the number of rows for which the value in the row status array is not SQL_ROW_NOROW. The address of this buffer is specified with the SQL_ATTR_ROWS_FETCHED_PTR statement attribute. The buffer is allocated by the application. It is set by SQLFetch and SQLFetchScroll. If the value of the SQL_ATTR_ROWS_FETCHED_PTR statement attribute is a null pointer, these functions do not return the number of rows fetched. To determine the number of the current row in the result set, an application can call SQLGetStmtAttr with the SQL_ATTR_ROW_NUMBER attribute.

The contents of the rows fetched buffer are undefined if SQLFetch or SQLFetchScroll does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, except when SQL_NO_DATA is returned, in which case the value in the rows fetched buffer is set to 0.

Error Handling

Errors and warnings can apply to individual rows or to the entire function. For more information about diagnostic records, see Chapter 15, “Diagnostics,” and SQLGetDiagField.

Errors and Warnings on the Entire Function

If an error applies to the entire function, such as SQLSTATE HYT00 (Timeout expired) or SQLSTATE 24000 (Invalid cursor state), SQLFetch returns SQL_ERROR and the applicable SQLSTATE. The contents of the rowset buffers are undefined and the cursor position is unchanged.

If a warning applies to the entire function, SQLFetch returns SQL_SUCCESS_WITH_INFO and the applicable SQLSTATE. The status records for warnings that apply to the entire function are returned before the status records that apply to individual rows.

Errors and Warnings in Individual Rows

If an error (such as SQLSTATE 22012 (Division by zero)) or a warning (such as SQLSTATE 01004 (Data truncated)) applies to a single row, SQLFetch:

• Sets the corresponding element of the row status array to SQL_ROW_ERROR for errors or SQL_ROW_SUCCESS_WITH_INFO for warnings.
  
  
• Adds zero or more status records containing SQLSTATEs for the error or warning.
  
  
• Sets the row and column number fields in the status records. If SQLFetch cannot determine a row or column number, it sets that number to SQL_ROW_NUMBER_UNKNOWN or SQL_COLUMN_NUMBER_UNKNOWN, respectively. If the status record does not apply to a particular column, SQLFetch sets the column number to SQL_NO_COLUMN_NUMBER.

SQLFetch continues fetching rows until it has fetched all of the rows in the rowset. It returns SQL_SUCCESS_WITH_INFO unless an error occurs in every row of the rowset (not counting rows with status SQL_ROW_NOROW), in which case it returns SQL_ERROR. In particular, if the rowset size is 1 and an error occurs in that row, SQLFetch returns SQL_ERROR.

SQLFetch returns the status records in row number order. That is, it returns all status records for unknown rows (if any), then all status records for the first row (if any), then all status records for the second row (if any), and so on. The status records for each individual row are ordered according to the normal rules for ordering status records; for more information, see “Sequence of Status Records” in SQLGetDiagField.

Descriptors and SQLFetch

The following sections describe how SQLFetch interacts with descriptors.

Argument Mappings

The driver does not set any descriptor fields based on the arguments of SQLFetch.

Other Descriptor Fields

The following descriptor fields are used by SQLFetch.

All descriptor fields can also be set through SQLSetDescField.

Separate Length and Indicator Buffers

Applications can bind a single buffer or two separate buffers to be used to hold length and indicator values. When an application calls SQLBindCol, the driver sets the SQL_DESC_OCTET_LENGTH_PTR and SQL_DESC_INDICATOR_PTR fields of the ARD to the same address, which is passed in the StrLen_or_IndPtr argument. When an application calls SQLSetDescField or SQLSetDescRec, it can set these two fields to different addresses.

SQLFetch determines whether the application has specified separate length and indicator buffers. In this case, when the data is not NULL, SQLFetch sets the indicator buffer to 0 and returns the length in the length buffer. When the data is NULL, SQLFetch sets the indicator buffer to SQL_NULL_DATA and does not modify the length buffer.

Code Example

See SQLBindCol, SQLColumns, SQLGetData, and SQLProcedures.

Related Functions