Execute Script Reference
This operation executes a script of random length as a single statement. Execute script differs from other operations in the following ways:
The script can contain multiple statements.
Statements can be of different types.
No input/output parameters are accepted.
The execute-script operation runs any script that does not involve a SQL projection. You can use
<db:execute-script> in the following ways:
Embed execute-script in an operation.
Reference execute-script from a file.
You cannot use both ways of executing a script at the same time.
Executing a script returns an array of integer numbers, one element per each executed statement. Each number represents the number of objects affected by the statement.
Embed in an Operation
1 2 3 4 5 6 <db:execute-script config-ref="dbConfig"> <db:sql> update PLANET set NAME='Mercury' where POSITION=0; update PLANET set NAME='Mercury' where POSITION=4 </db:sql> </db:execute-script>
Reference from a File
1 2 3 <flow name="executeScriptFromFile"> <db:execute-script config-ref="dbConfig" file="integration/executescript/bulk-script.sql" /> </flow>
The execute script operation frequently creates schemas and tables, inserts data, and performs data rotation. Data rotation is performed by nightly jobs that move and archive data into historic tables and purge on-line tables.
Execute script and bulk operations are intended for different uses. For example, although you can build a script that inserts many rows into the database, you could not conveniently provide dynamic parameters to the script. Also, you need to ensure that the script protects against a SQL injection attack.
Executing a select statement in a script, although possible, returns no data and poses a data input problem; therefore, doing so makes no sense.