How to: |
Batch statements enable you to execute multiple SQL and/or parameterized SQL statements within one transaction.
Iterative processes are batch type processes that reference other processes, including stored procedures. In an iterative batch statement, each statement and procedure can be called multiple times. This differs from a regular batch statement, in which a statement or procedure can be called in a single batch; instead, an iterative batch statement allows you to iterate the batch itself.
If you add a statement to a batch without selecting the Iterate option, you can call the statement only once. If you iterate the statement only, which is done by default, you can iterate only a single statement. By choosing the Iterate option when you add a statement to a batch, you will be able to call several statements multiple times from a single batch.
All iterative processes reside within the Batches node. All prepared statements created in the Statements node are automatically imported into the Iterate container. However, you can import processes from Tables or stored procedures as well.
To create a batch statement:
The Create A Batch pane opens.
The batch properties information appears in the right pane as shown in the following image.
The Node Type drop-down selection appears.
To import tables or processes into an iterative process:
By default, the Iterate node contains all the statements created in the Statements node.
The Import Process pane appears.
If you import tables, the GET, INSERT, UPDATE, DELETE, and UPSERT (but not CURSOR) functions are imported for each table you select. The following image shows the addition of the table functions for the STUDENT table added to the Iterate node:
The stored procedures you Import will appear the same way.
The following is an example of the input XML for a batch statement named batchtest. Two parameterized SQL statements were added to this statement, one named ParamSTMT1, with an INSERT accessing TableA, and another named ParamSTMT2, with an INSERT accessing TableB. When the SQL statements were added to the batch statement, the Iterate check box was selected.
In this input XML document, two different statements are being called, with two different sets of values for each statement.
Note: Added statements or procedures to a batch must be included in the input document.
The following is an example of the input XML for a regular ITERATE process.
You can add a new process to a batch statement after you create it. The adapter also allows you to add a process to a batch even if the batch already contains it, allowing you to make limitless calls to the same process.
The Edit Batch pane appears on the right.
The iterate setting determines whether the table function you choose will be iterative. If so, the adapter searches for the process node in the Iterate node. If not, the adapter will search for it within the Schemas node.
You have the option of viewing the details of the statement you are adding by clicking View Details.
The iterate setting determines whether the prepared statement you choose will be iterative. If so, the adapter searches for the process node in the Iterate node. If not, the adapter will search for it within the Statements node.
You have the option of viewing the details of the procedure you are adding by clicking Show Details.
The iterate setting determines whether the Procedure you choose will be iterative. If so, the adapter searches for the process node in the Iterate node. If not, the adapter will search for it within the Procedures node.
You can reorder the processes within a batch statement:
The Edit Batch pane appears.
A table listing the processes appears in the right pane. Each row includes a position column.
To test a batch process:
The Test Run pane appears on the right. You can edit parameter names and data types for the test.
The results appear in the right pane.