SQLFetch--Fetch next row

Purpose

SQLFetch() advances the cursor to the next row of the result set and retrieves any bound columns.

Columns can be bound to application storage.

When SQLFetch() is called, the appropriate data transfer is performed, along with any data conversion if conversion is indicated when the column is bound. The columns can also be received individually after the fetch, by calling SQLGetData().

SQLFetch() can be called only after a result set is generated (using the same statement handle) by executing a query.

Syntax

SQLRETURN   SQLFetch         (SQLHSTMT          StatementHandle);  /* hstmt */

Function arguments

Table 48. SQLFetch arguments

Usage

SQLFetch() can be called only after a result set is generated on the same statement handle. Before SQLFetch() is called the first time, the cursor is positioned before the start of the result set.

The number of application variables bound with SQLBindCol() must not exceed the number of columns in the result set, or SQLFetch() fails.

If SQLBindCol() has not been called to bind any columns, then SQLFetch() does not return data to the application, but just advances the cursor. In this case SQLGetData() could be called to obtain all of the columns individually. Data in unbound columns is discarded when SQLFetch() advances the cursor to the next row.

Columns can be bound to application storage. SQLBindCol() is used to bind application storage to the column. Data is transferred from the database to the application at fetch time. The length of the available data to return is also set.

If any bound storage buffer is not large enough to hold the data returned by SQLFetch(), the data is truncated. If character data is truncated, SQL_SUCCESS_WITH_INFO is returned, and an SQLSTATE is generated indicating truncation. The SQLBindCol() deferred output argument pcbValue contains the actual length of the column data retrieved from the server. The application should compare the actual output length to the input buffer length (pcbValue and cbValueMax arguments from SQLBindCol()) to determine which character columns were truncated.

Truncation of numeric data types is reported as a warning if the truncation involves digits to the right of the decimal point. If truncation occurs to the left of the decimal point, an error is returned (refer to the diagnostics section).

When all the rows are retrieved from the result set, or the remaining rows are not needed, call SQLFreeStmt() to close the cursor and discard the remaining data and associated resources.

DB2 Everyplace fetches at most one row at a time, instead of using a row set. DB2 Everyplace does not support statement descriptors.

SQLFetch() determines whether the application 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.

Positioning the cursor

When the result set is created, the cursor is positioned before the start of the result set. SQLFetch() fetches the next row.

Return codes

• SQL_SUCCESS
• SQL_SUCCESS_WITH_INFO
• SQL_ERROR
• SQL_INVALID_HANDLE
• SQL_NO_DATA_FOUND

SQL_NO_DATA_FOUND is returned if there are no rows in the result set, or previous SQLFetch() calls have fetched all the rows from the result set.

If all the rows have been fetched, the cursor is positioned after the end of the result set.

Diagnostics

Table 49. SQLFetch SQLSTATEs

Restrictions

None.

Related reference

• Key to DB2 CLI function descriptions
• DB2 CLI function summary
• SQLBindCol--Bind a column to an application variable
• SQLExecDirect--Execute a statement directly
• SQLGetData--Get data from a column