MDAC 2.5 SDK - ODBC Programmer's Reference
Chapter 21: ODBC API Reference

SQLMoreResults

Conformance

Version Introduced: ODBC 1.0
Standards Compliance: ODBC

Summary

SQLMoreResults determines whether more results are available on a statement containing SELECT, UPDATE, INSERT, or DELETE statements and, if so, initializes processing for those results.

Syntax

SQLRETURN SQLMoreResults(
     SQLHSTMT     StatementHandle);

Arguments

StatementHandle

[Input]
Statement handle.

Returns

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

Diagnostics

When SQLMoreResults returns 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 SQLMoreResults 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 "Batches of SQL Statements" and "Arrays of Parameter Values" in Chapter 9, "Executing Statements."

After executing the batch, the application is positioned on the first result set. The application can call SQLBindCol, SQLBulkOperations, SQLFetch, SQLGetData, SQLFetchScroll, SQLSetPos, 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 SQLMoreResults to move to the next result set. If another result set or count is available, SQLMoreResults 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 SQLMoreResults. After calling SQLMoreResults for UPDATE, INSERT, or DELETE statements, an application can call SQLRowCount.

If there was a current result set with unfetched rows, SQLMoreResults discards that result set and makes the next result set or count available. If all results have been processed, SQLMoreResults 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 SQLMoreResults 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 SQLFetch or SQLFetchScroll may result in an error or truncation. To prevent this, the application has to call SQLBindCol to explicitly rebind as appropriate (or do so by setting descriptor fields). Alternatively, the application can call SQLFreeStmt 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 SQLMoreResults. If this happens, SQLMoreResults will return SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value has changed).

Calling SQLCloseCursor, or SQLFreeStmt 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 SQLCancel 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 SQLMoreResults.

For more information, see "Multiple Results" in Chapter 11, "Retrieving Results (Advanced)."

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

For additional information about the valid sequencing of result-processing functions, see Appendix B, "ODBC State Transition Tables."

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 SQLGetInfo. 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 SQLMoreResults 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 SQLGetInfo does not return the SQL_BRC_ROLLED_UP bit for the SQL_BATCH_ROW_COUNT information type.) The first call to SQLMoreResults 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 SQLMoreResults 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 SQLGetInfo returns the SQL_BRC_ROLLED_UP bit for the SQL_BATCH_ROW_COUNT information type.) The first call to SQLMoreResults will position you on the rolled-up row count, and the second call to SQLMoreResults 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 Functions