SQL FETCH ROW Statement

SQL FETCH ROW
Statement

< > Query Statements Example Flow Chart Table of Contents

The SQL FETCH ROW statement fetches the next available row from a result set of an InstantSQL query. An option to close the query cursor or drop the query may also be specified.

QueryHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the query for which the next row of the result set is to be fetched. This value must have been returned from a successful SQL PREPARE QUERY statement or any of the browseInstantSQLBrowseStatements statements.

Option (input). This argument must specify a numeric integer value. The value identifies whether the query is to be preserved after the row is fetched. The query may be preserved (sql-None), dropped (sql-Drop) or only the cursor for the query closed (sql-Close). This argument is optional; when the argument is omitted, the default is as if sql-None had been specified.

An SQL FETCH ROW statement can only be successfully executed when the query status has been set to either:

sql-StatExecuting by an SQL START QUERY statement or by one of the InstantSQL browse statements, which implicitly start the specified browse query; or

sql-StatFetching by a prior SQL FETCH ROW statement.

The current status of a query can be obtained using the SQL DESCRIBE QUERY statement.

If there are no more rows available in the result set of the query, the SQL FETCH ROW statement sets the status of the query identified by the value of the QueryHandle argument to sql-StatEndOfQuery and sets sql-EndOfData to true for the return code of the statement. When the end of the result set is reached, InstantSQL automatically closes the cursor for the result set.

If a prior SQL FETCH ROW statement has set the query status to sql-StatEndOfQuery, any subsequent SQL FETCH ROW statement will also set the query status to sql-StatEndOfQuery and set sql-EndOfData to true until another SQL START QUERY statement is executed for the same query handle value. If an SQL END QUERY with the sql-Close option is executed, subsequent SQL FETCH ROW statements will return the error code value that sets sqe-QueryNotStarted to true.

If any columns have been bound to COBOL data items with the SQL BIND COLUMN statement, the column data values are transferred to the specified COBOL data items each time a row is fetched. When no row is fetched because the end of the result set is encountered, any COBOL data items bound to columns are not modified.

If any output parameters have been bound to COBOL data items with the SQL BIND PARAMETER statement, the parameter values are transferred to the specified COBOL data items when the SQL FETCH ROW statement sets sql-EndOfData to true. If the configuration option GetOutputParamsEachRow is set to "True", data for output parameters are transferred to the specified COBOL data items after each SQL FETCH ROW statement. It is data source - dependent whether the output values of parameters are valid before all rows of the result set, if any, have been fetched.

After an SQL FETCH ROW statement has been successfully executed, the SQL GET DATA statement may be used to get the values of result columns that have not been bound to COBOL data items.

A successful SQL FETCH ROW statement sets the status of the query identified by the value of the QueryHandle argument to sql-StatFetching, except that, if the Option argument specified sql-Close, then the status of the query is set to sql-StatPrepared. An SQL FETCH ROW statement that encounters the end of the result set sets the status of the query identified by the value of the QueryHandle argument to sql-StatEndOfQuery. (Other than when a query is dropped, the current status of a query can be obtained using the SQL DESCRIBE QUERY statement.)

When the Option argument specifies the value sql-Close, the cursor for the query is closed as if an SQL END QUERY statement with the sql-Close option were executed after fetching the row. In this case, the query handle will be valid for later use, but any pending results are discarded and an SQL START QUERY statement is required to re-open the cursor. The sql-Close option is useful mainly when it is known that the result set contains only one row to fetch, for example, when the SQL statement contains a WHERE clause specifying a unique row in the table.

When the Option argument specifies the value sql-Drop, the query is dropped as if an SQL END QUERY statement with the sql-Drop option were executed after fetching the row. In this case, the query handle will not be valid for later use in an SQL GET DATA statement. However, if the data has been bound to COBOL data items using the SQL BIND COLUMN statement then the data will already be available in those bound COBOL data items.

When the Option argument specifies the value sql-None, no action is taken with respect to the query other than to fetch the row. A subsequent SQL END QUERY statement may be used to either close the query cursor or drop the query.

If the row to be fetched is locked by another application and the fetch is attempting to lock the row (see the SQL PREPARE QUERY statement for information on locking rows), the fetch will wait for the lock to be released before returning. By default, the wait is forever, but a query timeout value may be specified in the SQL START QUERY statement or configured to a specific value with the configuration option QueryTimeout.

SQL FETCH ROW Statement Examples:

SQL FETCH ROW
sql-QueryHandle.

SQL FETCH ROW
MyQueryHandle,

sql-Drop.