Running Examples of Stored Procedures using MuleSoft – Mule 4

Utilizing the Database Anypoint Connector, MuleSoft’s Stored Procedure operation on the database is launched (Database Connector). When one or more ResultSet instances are returned by a stored procedure, streaming is automatically applied to prevent preemptive results from being consumed, which could affect memory use and performance. You must give this online MuleSoft Training course credit in order to advance your abilities and increase your professional knowledge.

The below examples show invoking the stored procedures for configuring the Input, Output, and In-out parameters fields, as well as how to use callable statements and dates on stored procedures using MuleSoft.

Stored Procedures with Callable Statements

Database Connector allows you to utilize callable statements in the following format:

The parameter :paramN in the declaration of stored procedure corresponds to the Nth parameter. Among the stored procedure’s names and the initial parenthesis, be sure there is no whitespace. Despite the fact that every JDBC driver should adhere to the JDBC standard format, few drivers have specifications on their own. Additional details on invoking the stored procedure can be found in your JDBC driver’s documentation. For the JDBC driver for Snowflake, for instance, the curly brackets must not be used in the callable statement “{ and }”:

Using Stored Procedures to Pass Parameters

While passing arguments for stored procedure:

• Include all of the store procedure’s parameters, including default values for optional parameters.
• Keep the sequence of the parameters as specified by the stored procedure.
• Don’t hard code numbers, strings, or other parameters in the arguments.

The example below shows an Oracle database which is created when a table named SYSTEM.employees and a stored method named createEmployee: were created.

Attempting to match parameters by name doesn’t work since Database Connector isn’t supporting callable statements with named parameters, such as:

The arguments must be entered in the proper sequence, for example:

Stored Procedures with Dates

Embedded engine-specific functionalities are not supported by Database Connector. For instance, with an Oracle database, the following approach will not work if you wish to modify the date to a specified format before invoking a procedure:

Before invoking the callable statement, use DataWeave to do any data transformations. Apply the following transformation to the preceding example:

1. Choose the operation Stored procedure in your Studio flow.
2. Set the field for “SQL Query Text” on the operation settings screen to the query to run, for example:
3. Set the DataWeave code in the field for “input parameters” to:

The setting in Studio is shown in the screenshot below:

Fig. 1) Dates on the configuration of a stored procedure.

The <db:input-parameters> with the transformation of DataWeave appears like this in the XML editor:

Configuring the Field for Input Parameters in the Operation of a Stored Procedure

Set values in the field for “Input parameters” to construct a map, where the names of the input parameters are the keys to be configured for the JDBC prepared statement. Use a colon prefix to refer to each argument in the SQL text, such as . Each parameter’s actual assignment is stored in the map’s values. When assigning the parameters’ values, keep the following in mind:

• Use single quotes to specify the name of each parameter, such as .
• Use a variable or DataWeave for default values; when you pass an empty string or null, Mule’s runtime engine will not push the stored procedure’s default value.

Set the field for “Input parameters” as follows:

1. Choose the operation Stored procedure in your Studio flow.
2. Configure the field for “SQL Query Text” on the operation settings screen to the query to perform, for example:
3. Configure the field of Input parameters to

The setting in Studio is shown in the screenshot below:

Fig. 2) Configuration of Input parameter in a stored procedure

The <db:stored-procedure> and <db:input-parameters> configuration in the XML editor appears like this:

Configuring the Field for In-Out Parameters in the Operation of a Stored Procedure

Set values for the field ”In Out parameters” to generate a map with keys that are the names of objects. Use a colon prefix to refer to each argument in the SQL text, such as . Each parameter’s actual assignment is stored in the map’s values.

For configuring the field for “In Out parameters”:

1. Choose the operation “Stored procedure” in your Studio flow.
2. Set the query to run in the field of “SQL Query Text” on the operation settings screen, for example.
3. Set the Edit inline option in the field for “In out parameters”.
4. For adding a new value, press the plus sign (+).
5. Set myInt as the Key and 3 as the Value in the new window.

The setup in Studio is shown in the screenshot below:

Fig. 3) Configuration of In Out parameters in a stored procedure

The <db:stored-procedure> and <db:in-out-parameter> configuration in the XML editor appears like this:

Configuring the Fields for Input and Output Parameters in the Operation of a Stored Procedure

Specify the values list to be configured on the JDBC prepared statement in the Output parameters field. Use a colon prefix to refer to every SQL text parameter, for example, .

To set up the fields of Output and Input parameters, follow these steps:

1. Choose the operation Stored procedure in your Studio flow.
2. Set the field for “SQL Query Text” on the operation settings screen to the query to run, for example:
3. Configure the Input parameters field to:

1. Set the Edit inline option in the Output parameters field.
2. For adding a new value, press the plus sign (+).
3. Set result1 as the Key and INTEGER as the Type in the new window.
4. To add a new value, repeat steps 5 and 6 with result2 as the Key and INTEGER as the Type.

The setup in Studio is shown in the screenshot below:

Fig. 4) Configuration of Output and Input parameters in a stored process

The <db:input-parameters> and <db:output-parameters> configurations in the XML editor appears like this:

Conclusion:

In this blog, we have discussed various examples of stored procedures such as callable statements, passing parameters, using dates, and configuring the fields of operations with input and output parameters. Getting certified with MuleSoft will help you learn more. Preview this MuleSoft tutorial for more details which will provide you with a basic grasp of concepts like ESB, mule application installation and deployment, and so on.