Executing compound SQL (CLI) statements in CLI applications

Compound SQL allows multiple SQL statements to be grouped into a single executable block. This block of statements, together with any input parameter values, can then be executed in a single continuous stream, reducing the execution time and network traffic.

About this task

Compound SQL (CLI) does not guarantee the order in which the substatements are executed, therefore there must not be any dependencies among the substatements.
Compound SQL (CLI) statements cannot be nested.
The BEGIN COMPOUND and END COMPOUND statements must be executed with the same statement handle.
The value specified in the STOP AFTER FIRST ? STATEMENTS clause of the BEGIN COMPOUND SQL statement must be of type SQL_INTEGER, and you can only bind an application buffer of type SQL_C_INTEGER or SQL_C_SMALLINT for this value.
Each substatement must have its own statement handle.
All statement handles must belong to the same connection and have the same isolation level.
Atomic array input is not supported within a BEGIN COMPOUND and END COMPOUND block of SQL statements. Atomic array input refers to the behavior where all inserts will be undone if any single insert fails.
All statement handles must remain allocated until the END COMPOUND statement is executed.
SQLEndTran() cannot be called for the same connection or any connect requests between BEGIN COMPOUND and END COMPOUND.
Only the following functions may be called using the statement handles allocated for the compound substatements:
- SQLAllocHandle()
- SQLBindParameter()
- SQLBindFileToParam()
- SQLExecute()
- SQLParamData()
- SQLPrepare()
- SQLPutData()

Procedure

To execute compound SQL (CLI) statements in CLI applications:

Allocate a parent statement handle.

For example:

SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtparent);

Allocate statement handles for each of the compound substatements.

For example:

SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtsub1);
SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtsub2);
SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtsub3);

Prepare the substatements.

For example:

SQLPrepare (hstmtsub1, stmt1, SQL_NTS);
SQLPrepare (hstmtsub2, stmt2, SQL_NTS);
SQLPrepare (hstmtsub3, stmt3, SQL_NTS);

Execute the BEGIN COMPOUND statement using the parent statement handle.

For example:

SQLExecDirect (hstmtparent, (SQLCHAR *) "BEGIN COMPOUND NOT ATOMIC STATIC",
               SQL_NTS);

If this is an atomic compound SQL operation, execute the substatements using the SQLExecute() function only.
For example:
```
SQLExecute (hstmtsub1);
SQLExecute (hstmtsub2);
SQLExecute (hstmtsub3);
```
Note: All statements to be executed inside an atomic compound block must first be prepared. Attempts to use the SQLExecDirect() function within an atomic compound block will result in errors.

Execute the END COMPOUND statement using the parent statement handle.

For example:

SQLExecDirect (hstmtparent, (SQLCHAR *) "END COMPOUND NOT ATOMIC STATIC",
               SQL_NTS);

Optional: If you used an input parameter value array, call SQLRowCount() with the parent statement handle to retrieve the aggregate number of rows affected by all elements of the input array.
For example:
```
SQLRowCount (hstmtparent, &numRows);
```

Free the handles of the substatements.

For example:

SQLFreeHandle (SQL_HANDLE_STMT, hstmtsub1);
SQLFreeHandle (SQL_HANDLE_STMT, hstmtsub2);
SQLFreeHandle (SQL_HANDLE_STMT, hstmtsub3);

Free the parent statement handle when you have finished using it.
For example:
```
SQLFreeHandle (SQL_HANDLE_STMT, hstmtparent);
```

Results

If the application is not operating in auto-commit mode and the COMMIT option is not specified, the sub-statements will not be committed. If the application is operating in auto-commit mode, however, then the sub-statements will be committed at END COMPOUND, even if the COMMIT option is not specified.