f90SQLMoreResults (SQLMoreResults)

Conformance

Version Introduced: ODBC 1.0

Standards Compliance: ODBC

Summary

f90SQLMoreResults determines whether there are more results available on a statement containing SELECT, UPDATE, INSERT, or DELETE

statements and, if so, initializes processing for those results.

Syntax

Arguments

StatementHandle [Input]

Statement handle.

iRet [Output]

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

Diagnostics

When f90SQLMoreResults returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling f90SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by f90SQLMoreResults 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.

Comments

SELECT statements return result sets. UPDATE, INSERT, and DELETE statements return a count of affected rows. If any of these statements are batched, submitted with arrays of parameters (numbered in increasing parameter order, in the order that they appear in the batch), or in procedures, they can return multiple result sets or row counts. For information about batches of statements and arrays of parameters, see Chapter 6 and 7.

After executing the batch, the application is positioned on the first result set. The application can call f90SQLBindCol, f90SQLBulkOperations, f90SQLFetch, f90SQLGetData, f90SQLFetchScroll, f90SQLSetPos, and all the metadata functions, on the first or any subsequent result sets, just as it would if there were just a single result set. Once it is done with the first result set, the application calls f90SQLMoreResults to move to the next result set. If another result set or count is available, f90SQLMoreResults returns SQL_SUCCESS and initializes the result set or count for additional processing. If any row count - generating statements appear in between result set - generating statements, they can be stepped over by calling f90SQLMoreResults. After calling f90SQLMoreResults for UPDATE, INSERT, or DELETE statements, an application can call f90SQLRowCount.

If there was a current result set with unfetched rows, f90SQLMoreResults discards that result set and makes the next result set or count available. If all results have been processed, f90SQLMoreResults returns SQL_NO_DATA. For some drivers, output parameters and return values are not available until all result sets and row counts have been processed. For such drivers, output parameters and return values become available when f90SQLMoreResults returns SQL_NO_DATA.

Any bindings that were established for the previous result set still remain valid. If the column structures are different for this result set, then calling f90SQLFetch or f90SQLFetchScroll may result in an error or truncation. To prevent this, the application has to call f90SQLBindCol to explicitly rebind as appropriate (or do so by setting descriptor fields). Alternatively, the application can call f90SQLFreeStmt with an Option of SQL_UNBIND to unbind all the column buffers.

The values of statement attributes such as cursor type, cursor concurrency, keyset size, or maximum length, may change as the application navigates through the batch by calls to f90SQLMoreResults. If this happens, f90SQLMoreResults will return SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value has changed).

Calling f90SQLCloseCursor, or f90SQLFreeStmt with an Option of SQL_CLOSE, discards all the result sets and row counts that were available as a result of the execution of the batch. The statement handle returns to either the allocated or prepared state. Calling f90SQLCancel to cancel an asynchronously executing function when a batch has been executed and the statement handle is in the executed, cursor-positioned, or asynchronous state results in all the results sets and row counts generated by the batch being discarded if the cancel call was successful. The statement then returns to the prepared or allocated state.

If a batch of statements or a procedure mixes other SQL statements with SELECT, UPDATE, INSERT, and DELETE statements, these other statements do not affect f90SQLMoreResults.

If a searched update or delete statement in a batch of statements does not affect any rows at the data source, f90SQLMoreResults returns SQL_SUCCESS. This is different from the case of a searched update or delete statement that is executed through f90SQLExecDirect, f90SQLExecute, or f90SQLParamData, which returns SQL_NO_DATA if it does not affect any rows at the data source. If an application calls f90SQLRowCount to retrieve the row count after a call to f90SQLMoreResults has not affected any rows, f90SQLRowCount will return SQL_NO_DATA.

Availability of Row Counts

When a batch contains multiple consecutive row count - generating statements, it is possible that these row counts are rolled up into just one row count. For example, if a batch has five insert statements, then certain data sources are capable of returning five individual row counts. Certain other data sources return only one row count that represents the sum of the five individual row counts.

When a batch contains a combination of result set - generating and row count - generating statements, row counts may or may not be available at all. The behavior of the driver with respect to the availability of row counts is enumerated in the SQL_BATCH_ROW_COUNT information type available through a call to f90SQLGetInfo. For example, suppose that the batch contains a select, followed by two inserts and another select. Then the following cases are possible:

• The row counts corresponding to the two insert statements are not available at all. The first call to f90SQLMoreResults will position you on the result set of the second select statement.
  
  
• The row counts corresponding to the two insert statements are available individually. (A call to f90SQLGetInfo does not return the SQL_BRC_ROLLED_UP bit for the SQL_BATCH_ROW_COUNT information type.) The first call to f90SQLMoreResults will position you on the row count of the first insert, and the second call will position you on the row count of the second insert. The third call to f90SQLMoreResults will position you on the result set of the second SELECT statement.
  
  
• The row counts corresponding to the two inserts are rolled up into one single row count that is available. (A call to f90SQLGetInfo returns the SQL_BRC_ROLLED_UP bit for the SQL_BATCH_ROW_COUNT information type.) The first call to f90SQLMoreResults will position you on the rolled-up row count, and the second call to f90SQLMoreResults will position you on the result set of the second select.

Certain drivers make row counts available only for explicit batches and not for stored procedures.

Related Subroutines