SQLNextResult function (CLI) - Associate next result set with another statement handle

SQLNextResult() allows non-sequential access to multiple result sets returned from a stored procedure.

Specification:

  • CLI 7.x

Syntax

SQLRETURN   SQLNextResult (SQLHSTMT StatementHandle1
                                SQLHSTMT StatementHandle2);

Function arguments

Table 1. SQLNextResult arguments
Data type Argument Use Description
SQLHSTMT StatementHandle1 input Statement handle.
SQLHSTMT StatementHandle2 input Statement handle.

Usage

A stored procedure returns multiple result sets by leaving one or more cursors open after exiting. The first result set is always accessed by using the statement handle that called the stored procedure. If multiple result sets are returned, either SQLMoreResults() or SQLNextResult() can be used to describe and fetch the result set.

SQLMoreResults() is used to close the cursor for the first result set and allow the next result set to be processed on the same statement handle, whereas SQLNextResult() moves the next result set to StatementHandle2, without closing the cursor on StatementHandle1. Both functions return SQL_NO_DATA_FOUND if there are no result sets to be fetched.

Using SQLNextResult() allows result sets to be processed in any order once they have been transferred to other statement handles. Mixed calls to SQLMoreResults() and SQLNextResult() are allowed until there are no more cursors (open result sets) on StatementHandle1.

When SQLNextResult() returns SQL_SUCCESS, the next result set is no longer associated with StatementHandle1. Instead, the next result set is associated with StatementHandle2, as if a call to SQLExecDirect() had just successfully executed a query on StatementHandle2. The cursor, therefore, can be described using SQLNumResultCols(), SQLDescribeCol(), or SQLColAttribute().

After SQLNextResult() has been called, the result set now associated with StatementHandle2 is removed from the chain of remaining result sets and cannot be used again in either SQLNextResult() or SQLMoreResults(). This means that for 'n' result sets, SQLNextResult() can be called successfully at most 'n-1' times.

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.

SQLNextResult() returns SQL_ERROR if StatementHandle2 has an open cursor or StatementHandle1 and StatementHandle2 are not on the same connection. If any errors or warnings are returned, SQLGetDiagRec() must always be called on StatementHandle1.

Note: SQLMoreResults() also works with a parameterized query with an array of input parameter values specified with the SQL_ATTR_ROW_ARRAY_SIZE statement attribute and SQLBindParameter(). SQLNextResult(), however, does not support this.

Return codes

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

Diagnostics

Table 2. SQLNextResult 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 the memory required to support execution or completion of the function.
HY010 Function sequence error.

The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.

StatementHandle2 has an open cursor associated with it.

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 the memory required to support execution or completion of the function.
HYT00 Time-out expired. The time-out period expired before the data source returned the result set. The time-out period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().

Restrictions

Only SQLMoreResults() can be used for parameterized queries and batched SQL.

Example

  /* use SQLNextResult to push Result Set 2 onto the second statement handle */
  cliRC = SQLNextResult(hstmt, hstmt2); /* open second cursor */