Compound SQL (embedded) statement
Combines one or more other SQL statements (sub-statements) into an executable block.
Invocation
This statement can only be embedded in an application program. The entire compound SQL (embedded) statement construct is an executable statement that cannot be dynamically prepared. The statement is not supported in REXX.
Authorization
No privileges are required to invoke an compound SQL (embedded). However, the privileges held by the authorization ID of the statement must include all necessary privileges to invoke the SQL statements that are embedded in the compound statement.
Syntax
Description
- ATOMIC
- Specifies that, if any of the sub-statements within the compound SQL (embedded) statement fail, then all changes made to the database by any of the sub-statements, including changes made by successful sub-statements, are undone.
- NOT ATOMIC
- Specifies that, regardless of the failure of any sub-statements, the compound SQL (embedded) statement will not undo any changes made to the database by the other sub-statements.
- STATIC
- Specifies that input variables for all sub-statements retain their
original value. For example, if
is followed by:SELECT ... INTO :abc ...
the UPDATE statement will use the value that :abc had at the start of the execution of the compound SQL (embedded) statement, not the value that follows the SELECT INTO.UPDATE T1 SET C1 = 5 WHERE C2 = :abc
If the same variable is set by more than one sub-statement, the value of that variable following the compound SQL (embedded) statement is the value set by the last sub-statement.
Note: Non-static behavior is not supported. This means that the sub-statements should be viewed as executing non-sequentially and sub-statements should not have interdependencies. - STOP AFTER FIRST
- Specifies that only a certain number of sub-statements will be executed.
- host-variable
- A small integer that specifies the number of sub-statements to be executed.
- STATEMENTS
- Completes the STOP AFTER FIRST host-variable clause.
- sql-statement
- All executable statements except the following can be contained
within an embedded static compound SQL (embedded) statement:
CALL FETCH CLOSE OPEN CONNECT PREPARE Compound SQL RELEASE (Connection) DESCRIBE ROLLBACK DISCONNECT SET CONNECTION EXECUTE IMMEDIATE SET variable
Note: INSERT, UPDATE, and DELETE are not supported in compound SQL for use with nicknames.If a COMMIT statement is included, it must be the last sub-statement. If COMMIT is in this position, it will be issued even if the STOP AFTER FIRST host-variable STATEMENTS clause indicates that not all of the sub-statements are to executed. For example, suppose COMMIT is the last sub-statement in a compound SQL block of 100 sub-statements. If the STOP AFTER FIRST STATEMENTS clause indicates that only 50 sub-statements are to be executed, then COMMIT will be the 51st sub-statement.
An error will be returned if COMMIT is included when using CONNECT TYPE 2 or running in an XA distributed transaction processing environment (SQLSTATE 25000).
Rules
- No host language code is allowed within a compound SQL (embedded) statement; that is, no host language code is allowed between the sub-statements that make up the compound SQL (embedded) statement.
- Compound SQL (embedded) statements cannot be nested.
- A prepared COMMIT statement is not allowed in an ATOMIC compound SQL (embedded) statement
Notes
- One SQLCA is returned for the entire compound SQL (embedded) statement.
Most of the information in that SQLCA reflects the values set by the
application server when it processed the last sub-statement. For instance:
- The SQLCODE and SQLSTATE are normally those for the last sub-statement (the exception is described in the next point).
- If a
no data found
warning (SQLSTATE 02000) is returned, that warning is given precedence over any other warning so that a WHENEVER NOT FOUND exception can be acted upon. (This means that the SQLCODE, SQLERRML, SQLERRMC, and SQLERRP fields in the SQLCA that is eventually returned to the application are those from the sub-statement that triggered theno data found
warning. If there is more than oneno data found
warning within the compound SQL (embedded) statement, the fields for the last sub-statement will be the fields that are returned.) - The SQLWARN indicators are an accumulation of the indicators set for all sub-statements.
- If one or more errors occurred during NOT ATOMIC compound SQL
execution and none of these are of a serious nature, the SQLERRMC
will contain information about these errors, up to a maximum of seven
errors. The first token of the SQLERRMC will indicate the total number
of errors that occurred. The remaining tokens will each contain the
ordinal position and the SQLSTATE of the failing sub-statement within
the compound SQL (embedded) statement. The format is a character string
of the form:
with the substring starting with X repeating up to six more times and the string elements defined as follows.nnnXsssccccc
- nnn
- The total number of statements that produced errors. (If the number would exceed 999, counting restarts at zero.) This field is left-aligned and padded with blanks.
- X
- The token separator X'FF'.
- sss
- The ordinal position of the statement that caused the error. (If the number would exceed 999,
counting restarts at zero.) For example, if the first statement failed, this field would contain the
number one left-aligned (
1
). - ccccc
- The SQLSTATE of the error.
- The second SQLERRD field contains the number of statements that failed (returned negative SQLCODEs).
- The third SQLERRD field in the SQLCA is an accumulation of the number of rows affected by all sub-statements.
- The fourth SQLERRD field in the SQLCA is a count of the number of successful sub-statements. If, for example, the third sub-statement in a compound SQL (embedded) statement failed, the fourth SQLERRD field would be set to 2, indicating that 2 sub-statements were successfully processed before the error was encountered.
- The fifth SQLERRD field in the SQLCA is an accumulation of the number of rows updated or deleted due to the enforcement of referential integrity constraints for all sub-statements that triggered such constraint activity.
Examples
- Example 1: In a C program, issue a compound SQL (embedded)
statement that updates both the ACCOUNTS and TELLERS tables. If there
is an error in any of the statements, undo the effect of all statements
(ATOMIC). If there are no errors, commit the current unit of work.
EXEC SQL BEGIN COMPOUND ATOMIC STATIC UPDATE ACCOUNTS SET ABALANCE = ABALANCE + :delta WHERE AID = :aid; UPDATE TELLERS SET TBALANCE = TBALANCE + :delta WHERE TID = :tid; INSERT INTO TELLERS (TID, BID, TBALANCE) VALUES (:i, :branch_id, 0); COMMIT; END COMPOUND;
- Example 2: In a C program, insert 10 rows of data into
the database. Assume the host variable :nbr contains the value 10
and S1 is a prepared INSERT statement. Further, assume that all the
inserts should be attempted regardless of errors (NOT ATOMIC).
EXEC SQL BEGIN COMPOUND NOT ATOMIC STATIC STOP AFTER FIRST :nbr STATEMENTS EXECUTE S1 USING DESCRIPTOR :*sqlda0; EXECUTE S1 USING DESCRIPTOR :*sqlda1; EXECUTE S1 USING DESCRIPTOR :*sqlda2; EXECUTE S1 USING DESCRIPTOR :*sqlda3; EXECUTE S1 USING DESCRIPTOR :*sqlda4; EXECUTE S1 USING DESCRIPTOR :*sqlda5; EXECUTE S1 USING DESCRIPTOR :*sqlda6; EXECUTE S1 USING DESCRIPTOR :*sqlda7; EXECUTE S1 USING DESCRIPTOR :*sqlda8; EXECUTE S1 USING DESCRIPTOR :*sqlda9; END COMPOUND;