Multiple-Statement Requests Require Cursors
A multiple-statement request often returns a response having more than one row. Each statement in the request produces its own results (success/failure, activity count and so on), which are returned to the application program.
Because the results table for a multiple-statement request returns more than one response, you must declare a cursor to fetch and manipulate the results.
| Association | Statement to Use |
|---|---|
| Static multiple-statement request with request cursor | DECLARE CURSOR statement for a request cursor. |
| Dynamic multiple-statement request with dynamic cursor | PREPARE statement with the statement string containing a multiple-statement request. The dynamic request is then associated with a dynamic cursor. |
Using the FOR STATEMENT Clause with PREPARE and DESCRIBE
You can extend the syntax of PREPARE and DESCRIBE by using the FOR STATEMENT clause. FOR STATEMENT permits you to specify which of the statements in the multiple-statement request is to be described.
To describe all statements of a multiple-statement request, the DESCRIBE statement must be run multiple times for each data returning statement within the request.
Even though the output SQLDA contains no column descriptions, you can DESCRIBE a non-data-returning statement.
Using SQLDA to Track Statement Status
In processing the output from a multiple-statement request, you must know the success or failure of each statement and when the output from one request ends and output from the next begins.
The mechanism described by the following table, which is similar to that used for single statement requests, provides a framework for achieving this.
| Situation | Result |
|---|---|
| Request cursor is opened. | SQLCODE in SQLCA is set to reflect the success of the first statement of the request or the failure (failure defined as an IMPLICIT ROLLBACK occurrence) of the request as an entity. A failure condition overrides a success report. If successful, the activity count is reported to the application in the third SQLERRD element in the SQLCA. |
| Statement is in error (error defined as a non-implicit ROLLBACK). | Next FETCH returns the appropriate error code: SQLCODE in the SQLCA is < 0 and the error code in the first element of SQLERRD. |
| No rows are returned or the rows returned for a specific statement are exhausted. | SQLCODE is set to +100 on return from the FETCH. |
| Application must position to the next (or any) statement of the request. | You must use the POSITION statement. POSITION moves control to the output for the specified statement of the request and sets the SQLCA information based on the success or failure of the OPEN request. The program can then use FETCH to retrieve the output of the statement. |
| Application must position to the beginning of all output for the request | You must use the REWIND statement. The REWIND statement is exactly equivalent to POSITION TO STATEMENT 1. REWIND moves control to the output for the specified statement of the request and sets the SQLCA information based on the success or failure of the OPEN request. The program can then use FETCH to retrieve the output of the statement. |
| You receive +100 SQLCODE for the current statement. | You must use POSITION or REWIND to access the results of another (or even the same) statement. You need not wait until the +100 is received. You can issue POSITION or REWIND statements at any time. |
Multiple-Statement Request Example
An example of a multiple-statement request is shown in the following passages. The SQL prefixes and terminators are omitted. This example assumes successful completion of the statements in the request.
DECLARE curs CURSOR FOR
’SELECT ent1,ent2,ent3 FROM tabx;
UPDATE ...;SELECT entt FROM tabl’
OPEN curs {SQLCA gets first SELECT result}
WHENEVER NOT FOUND GOTO updstmt
selstmt1:
FETCH curs INTO :vara,:varb,:varc
.
.
GOTO selstmt1
updstmt:
WHENEVER NOT FOUND CONTINUE
POSITION curs TO STATEMENT 2 {SQLCA gets UPDATE
result}
FETCH curs
.
.
WHENEVER NOT FOUND GOTO reread
POSITION curs TO STATEMENT 3 {SQLCA gets second
SELECT result}
selstmt2:
FETCH curs INTO :vars
.
.
GOTO selstmt2
reread:
REWIND curs {SQLCA gets first SELECT result}
WHENEVER NOT FOUND GOTO alldone
selstmt1x:
FETCH curs INTO :varaa,:varbb,:varcc
.
.
GOTO selstmt1x
alldone:
CLOSE curs
Related Information
- Embedded SQL-related topics, see the following sections: SQL Cursors, Result Code Variables, Client-Server Connectivity Statements, or Multisession Programming with Embedded SQL.
- PREPARE and DESCRIBE, see COMMENT (Returning Form) and PREPARE (Dynamic).
- Using cursors with multiple-statement requests, see DECLARE CURSOR.