Execute a Script Examples - Mule 4

Anypoint Connector for Database (Database Connector) Execute script operation executes a SQL script of random length as a single database statement.

The 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 online tables. Some points to consider:

The script can contain multiple statements.
Statements can be of different types.
No input or output parameters are accepted.
The operation runs any script that does not involve a SQL projection.

You can use the Execute script operation either by embedding the script in the operation or by referencing the script from a file, but you cannot use both ways of executing a script simultaneously.

Executing a script returns an array of integers, one element per executed statement. Each integer represents the number of objects affected by the statement.

The 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, ensure that the script protects against a SQL injection attack.

The following examples illustrate how to configure the Execute script operation embedding the script in the operation, and how to reference the script from a file.

Configure the Execute Script Operation Embedding the Script

To configure the operation in Studio, follow these steps:

In the Mule Palette view, search for database and select the Execute script operation.
Drag the Execute script operation onto the Studio canvas.
In the General tab of the operation configuration screen, click the plus sign (+) next to the Connector configuration field to access the global element configuration fields.
Specify the database connection information and click OK.

Set the SQL Query Text field to the SQL query script to execute:

update PLANET set NAME='Mercury' where POSITION=0;
update PLANET set NAME='Mercury' where POSITION=4`.

The following screenshot shows the configuration in Studio:

The Execute script operation configuration in Studio

Figure 1. Execute script operation configuration

In the XML editor, the <db:execute-script> configuration looks like this:

<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>

Configure the Execute Script Operation Referencing the Script

To configure the operation in Studio, follow these steps:

In the Mule Palette view, search for database and select the Execute script operation.
Drag the Execute script operation onto the Studio canvas.
In the General tab of the operation configuration screen, click the plus sign (+) next to the Connector configuration field to access the global element configuration fields.
Specify the database connection information and click OK.
Set the Script Path field to the location of the script file to load, for example, integration/executescript/bulk-script.sql.
The file can point to a resource on the class path or on a disk.

The following screenshot shows the configuration in Studio:

Figure 2. Execute Script operation configuration

In the XML editor, the <db:execute-script> and file configuration looks like this:

<flow name="executeScriptFromFile">
   <db:execute-script config-ref="dbConfig" file="integration/executescript/bulk-script.sql" />
</flow>

Execute a Script Examples - Mule 4

Configure the Execute Script Operation Embedding the Script

Configure the Execute Script Operation Referencing the Script

See Also