SQLMoreResults function (CLI) - Determine if there are more result sets

Determines whether there is more information available on the statement handle which has been associated with: an array input of parameter values for a query; a stored procedure that is returning result sets; or, batched SQL.

Specification:

  • CLI 2.1
  • ODBC 1.0

Syntax

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

Function arguments

Table 1. SQLMoreResults arguments
Data type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.

Usage

This function is used to return multiple results set in a sequential manner upon the execution of:
  • a parameterized query with an array of input parameter values specified with the SQL_ATTR_PARAMSET_SIZE statement attribute and SQLBindParameter(), or
  • a stored procedure containing SQL queries, the cursors of which have been left open so that the result sets remain accessible when the stored procedure has finished execution. For this scenario, the stored procedure is typically trying to return multiple result sets.
  • or batched SQL. When multiple SQL statements are batched together during a single SQLExecute() or SQLExecDirect().

After completely processing the first result set, the application can call SQLMoreResults() to determine if another result set is available. If the current result set has unfetched rows, SQLMoreResults() discards them by closing the cursor and, if another result set is available, returns SQL_SUCCESS.

If all the result sets have been processed, SQLMoreResults() returns SQL_NO_DATA_FOUND.

Applications that want to be able to manipulate more than one result set at the same time can use the CLI function SQLNextResult() to move a result set to another statement handle. SQLNextResult() does not support batched statements.

When using batched SQL, SQLExecute() or SQLExecDirect() will only execute the first SQL statement in the batch. SQLMoreResults() can then be called to execute the next SQL statement and will return SQL_SUCCESS if the next statement is successfully executed. If there are no more statements to be executed, then SQL_NO_DATA_FOUND is returned. If the batched SQL statement is an UPDATE, INSERT, or DELETE statement, then SQLRowCount() can be called to determine the number of rows affected.

If SQLCloseCursor() or if SQLFreeStmt() is called with the SQL_CLOSE option, or SQLFreeHandle() is called with HandleType set to SQL_HANDLE_STMT, all pending result sets on this statement handle are discarded.

Return codes

  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_STILL_EXECUTING
  • SQL_ERROR
  • SQL_INVALID_HANDLE
  • SQL_NO_DATA_FOUND

Diagnostics

Table 2. SQLMoreResults SQLSTATEs
SQLSTATE Description Explanation
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. Db2® CLI is unable to allocate memory required to support execution or completion of the function. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY010 Function sequence error. The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.

The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
HY013 Unexpected memory handling error. Db2 CLI was unable to access memory required to support execution or completion of the function.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().
Note: This SQLSTATE applies only to .Net applications.

In addition SQLMoreResults() can return the SQLSTATEs associated with SQLExecute().

Example

    cliRC = SQLMoreResults(hstmt);