SQLFetch

SQLFetch

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

The user can separately get columns by directly receiving the data for the variables specified in SQLBindCol() using or by calling SQLGetData() to fetch. In case SQLFetch() is called while conversion is set upon binding of the column, the data will be converted.

Syntax

SQLRETURN  SQLFetch (
    SQLHSTMT    stmt);

Argument

Data Type	Argument	In/Output	Description
SQLHSTMT	stmt	Input	Statement handle

Return Values

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR 
SQL_NO_DATA

Description

SQLFetch() can be called only when the recently executed command in stmt is a SELECT statement.

SQLBindCol() and the number of binding variables of the application must not exceed the number of columns in the result set. Otherwise, SQLFetch() will fail.

Positioning the Cursor

When the result set is created, the cursor is positioned before the start of the result set. SQLFetch() returns the next row set in the result set. The SQL_ATTR_ROW_ARRAY_SIZE statement attribute specifies the number of rows in the rowset. For example, if the number of rows is 5 when the result set has total 100 rows, the result row set of initial SQLFetch() is from 1 to 5. Also, if the result set is from the 52nd row to 56th row of the current row set, 57th rows to 61st rows will be returned by SQLFetch(). SQL_SUCCESS will be returned, and the number of released rows will be 5. The following table shows the row set and returns the code that was returned by SQLFetch().

Current row set	Returned code	New row set	Number of the fetched rows
Before start	SQL_SUCCESS	1 to 5	5
1 to 5	SQL_SUCCESS	6 to 10	5
52 to 56	SQL_SUCCESS	57 to 61	5
91 to 95	SQL_SUCCESS	96 to 100	5
93 to 97	SQL_SUCCESS	98 to 100. SQL_ROW_NOROW is set on rows 4 and 5	3
96 to 100	SQL_NO_DATA	None	0
99 to 100	SQL_NO_DATA	None	0
After end	SQL_NO_DATA	None	0

After SQLFetch() is returned, the current row will become the first row of the row set.

Returning the 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.

For each bound column, SQLFetch() does the following:

1. . When the data is NULL, set SQL_NULL_DATA in the length/indicator buffer and go to the next column. If the data for the column is not NULL, SQLFetch proceeds to step 2.
2. Converts the data of the type specified in the type argument of SQLBindCol().
3. If the data is converted into the flexible length data type, SQLFetch() will inspect whether the data length (including NULL-terminatior when converted into SQL_C_CHAR) exceeds the data buffer length. If the character data length exceeds the data buffer length, SQLFetch() will cut the NULL-terminatior according to the data buffer length. In this way, finish the data composed of NULL characters. If the binary data length exceeds the data buffer length, SQLFetch() will cut the data according to the data buffer. The length of the data buffer is specified in SQLBindCol() length.
4. Positions the converted data in the data buffer.
5. Positions the data length in the length/indicator buffer. If the indicator pointer and the length pointer are set as the same buffer, the length will be recorded for the valid data and SQL_NULL_DATA will be recorded for the NULL data. If there is no bound length/indicator buffer, SQLFetch() will not return the length.

The contents of the bound data buffer and the length/indicator buffer are not defined unless SQLFetch() returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. When the result of SQLFetch() is SQL_ERROR, it is invalid value.

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. If the value of SQL_ATTR_ROW_STATUS_PTR is a NULL pointer, SQLFetch() will not return the row status.

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. This buffer is allocated by an application and set by SQLFetch(). If SQL_ATTR_ROWS_FETCHED_PTR statement attribute is a NULL pointer, SQLFetch() will not return the number of the fetched rows.

If SQLFetch() does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO except SQL_NO_DATA, the contents of the row fetched buffer will not be determined. In this case, the row fetched buffer will be set to 0.

Error Handling

Errors and warnings can apply to individual rows or to the entire function.

Errors and warnings for the entire function.

If a random warning is applied to the entire function, SQLFetch() will return SQL_SUCCESS_WITH_INFO and proper SQLSTATE. The warning status records applied to the function must be returned before the status records are applied to each row.

Diagnosis

SQLSTATE	Description	Comments
01004	String data, right truncated	String or binary data returned for a column resulted in the truncation of nonblank character or non-NULL binary data. If it was a string value, it was right-truncated.
07006	Restricted data type attribute violation	The column data within the result set must not be converted to the data type expressed in cType of SQLBindCol().
08S01	Communication channel error	Communication channel failure before the function processing is completed between the Altibase CLI driver and the database
HY000	General error

Related Functions

SQLBindCol
SQLDescribeCol  
SQLExecDirect
SQLExecute
SQLFreeStmt
SQLGetData
SQLNumResultCols
SQLPrepare

Example

< Refer to: $ALTIBASE_HOME/sample/SQLCLI/demo_ex2.cpp >

See example of SQLBindCol()