Execute Stored Procedures Examples - Mule 4

Anypoint Connector for Database (Database Connector) Stored procedure operation invokes a stored procedure on the database. When the stored procedure returns one or more ResultSet instances, streaming is applied automatically to avoid preemptive consumption of the results, which can lead to performance and memory issues.

The following examples illustrates callable statements and dates on stored procedures, and how to invoke stored procedures by configuring the Input parameters, Output parameters, and In out parameters fields.

Callable Statements in Stored Procedures

Database Connector supports the use of callable statements with the format:

{ call procedureName(:param1, :param2, …, :paramN) }

Where :paramN matches the Nth parameter in the stored procedure declaration. Make sure that no whitespace is between the name of the stored procedure and the first parentheses. Even though all JDBC drivers must comply with the JDBC specification format, some drivers have their own specifications. Check the documentation of your JDBC driver for additional information on how to call the stored procedure. For example, for the Snowflake JDBC driver you must set the callable statement without the curly brackets { and }:

call procedureName(:param1, :param2, …, :paramN)

Passing Parameters to Stored Procedures

When passing parameters to a stored procedure:

Place all the parameters the store procedure contains, even optional parameters with default values.
Put the parameters in the same order as they were defined in the stored procedure.
Parameterize the parameters, do not hardcode strings, numbers, etc.

The following example illustrates an Oracle database that was initialized when creating a table named SYSTEM.employees and stored procedure named createEmployee:

    CREATE TABLE SYSTEM.employees(
        employee_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        employee_name VARCHAR2(100),
        employee_age INTEGER,
        employee_birthday TIMESTAMP,
        PRIMARY KEY(employee_id)
    );


    CREATE PROCEDURE createEmployee(e_name VARCHAR2, e_age NUMBER, e_birth_date DATE) AS
    BEGIN
        INSERT INTO SYSTEM.employees(employee_name, employee_age, employee_birthday) VALUES(e_name, e_age, e_birth_date);
    END;

Because Database Connector does not support named parameters in callable statements, attempting to match parameters by name does not work, for example:

{ call createEmployee(e_age ⇒ :age, e_birth_date ⇒ :date, e_name ⇒ :name) }

You must provide the parameters in the appropriate order, for example:

{ call createEmployee(:name, :age, :date) }.

Use Dates on Stored Procedures

Database Connector does not support the use of engine-specific embedded functions. For example, if you want to change a date to a specific format before calling a procedure in an Oracle database, the following approach does not work:

{ call createEmployee(e_name ⇒ :name, employee_age ⇒ :age, e_birth_date ⇒ TO_DATE(:date, 'YYYY-MM-DD HH:mm:ss')) }.

Use DataWeave for all data transformations before the invocation to the callable statement. Using the previous example, perform the following transformation:

In your Studio flow, select the Stored procedure operation.
In the operation configuration screen, set the SQL Query Text field to the query to execute, for example:

{ call createEmployee(:name, :age, :date) }
Set the Input parameters field to the following DataWeave code:

%dw 2.0
  output application/json
  fun format(d: DateTime) = d as String { format: "yyyy-MM-dd HH:mm:ss" }
  ---
  { 'date': format(|2019-10-31T13:00:00.000Z|), 'name': 'rick', 'age': 60 }

[source,xml,linenums]

The following screenshot shows the configuration in Studio:

Configuring dates on the Stored procedure operation

Figure 1. Dates on Stored Procedure configuration

In the XML editor, the <db:input-parameters> with the DataWeave transformation looks like this:

<db:stored-procedure doc:name="Create Employee" config-ref="Database_Config">
    <db:sql>{ call createEmployee(:name, :age, :date) }</db:sql>
		<db:input-parameters>
		 	<![CDATA[#[%dw 2.0
				output application/json
				fun format(d: DateTime) = d as String { format: "yyyy-MM-dd HH:mm:ss" }
				---
				{ 'date': format(|2019-10-31T13:00:00.000Z|), 'name': 'rick', 'age': 60 }
			]]]>
		</db:input-parameters>
	</db:stored-procedure>

Configure the Input Parameters Field in the Stored Procedure Operation

In the Input parameters field, specify values that create a map in which keys are the name of an input parameter to be set on the JDBC prepared statement. Reference each parameter in the SQL text using a colon prefix, for example, where id = :myParamName. The map’s values contain the actual assignation for each parameter. When giving values to the parameters:

Define each parameter name with single quotes for example, 'paramName': value.
For default values either use DataWeave or a variable, so if you pass null, or an empty string the store procedure’s default value won’t be pushed by Mule runtime engine.
Enter input values with parameters only. Do not hardcode the values within the Stored Procedure operation.

To configure the Input parameters field:

In your Studio flow, select the Stored procedure operation.
In the operation configuration screen, set the SQL Query Text field to the query to execute, for example:

{ call doubleMyInt(:myInt) }
Set the Input parameters field to {'description' : payload}.

The following screenshot shows the configuration in Studio:

The Stored procedure operation with the Input parameter field configuration

Figure 2. Stored procedure with Input parameter configuration

In the XML editor, the <db:stored-procedure> and <db:input-parameters> configuration looks like this:

<db:stored-procedure config-ref="dbConfig">
    <db:sql>{ call doubleMyInt(:myInt) }</db:sql>
    <db:input-parameters>
        #[{'description' : payload}]
    </db:input-parameters>
</db:stored-procedure>

Configure the In Out Parameters Field in the Stored Procedure Operation

In the In out parameters field, specify values that create a map in which keys are the name of a parameter to be set on the JDBC prepared statement which is both input and output. Reference each parameter in the SQL text using a colon prefix, for example, where id = :myParamName. The map’s values contain the actual assignation for each parameter.

To configure the In out parameters field:

In your Studio flow, select the Stored procedure operation.
In the operation configuration screen, set the SQL Query Text field to the query to execute, for example:

{ call doubleMyInt(:myInt) }
Set the In out parameters field to Edit inline.
Click the plus sign (+) to add a new value.
In the new window, set the Key field to myInt and the Value field to 3.

The following screenshot shows the configuration in Studio:

The Stored procedure operation with the In out parameters field configuration

Figure 3. Stored procedure with In Out parameters configuration

In the XML editor, the <db:stored-procedure> and <db:in-out-parameter> configuration looks like this:

<db:stored-procedure config-ref="dbConfig">
  <db:sql>{ call doubleMyInt(:myInt) }</db:sql>
  <db:in-out-parameters>
      <db:in-out-parameter key="myInt" value="3"/>
  </db:in-out-parameters>
</db:stored-procedure>

Configure the Input and Output Parameters Fields in the Stored Procedure Operation

In the Output parameters field, specify a list of values to be set on the JDBC prepared statement. Reference each parameter in the SQL text using a colon prefix, for example, call multiply(:value, :result).

To configure the Input parameters and Output parameters fields:

In your Studio flow, select the Stored procedure operation.
In the operation configuration screen, set the SQL Query Text field to the query to execute, for example:

{ call multiplyInts(:int1, :int2, :result1, :int3, :result2) }

Set the Input parameters field to:

{
    'int1' : 3,
    'int2' : 4,
    'int3' : 5
}

Set the Output parameters field to Edit inline.
Click the plus sign (+) to add a new value.
In the new window, set the Key field to result1 and the Type field to INTEGER.
Repeat the steps 5 and 6 to add a new value, set the Key field to result2 and the Type field to INTEGER.

The following screenshot shows the configuration in Studio:

The Stored procedure operation with the Input and Output parameters fields configuration

Figure 4. Stored procedure with Input and Output parameters configuration

In the XML editor, the <db:input-parameters> and <db:output-parameters> configuration looks like this:

<db:stored-procedure config-ref="dbConfig">
    <db:sql>{ call multiplyInts(:int1, :int2, :result1, :int3, :result2) }</db:sql>
    <db:input-parameters>
        #[{
            'int1' : 3,
            'int2' : 4,
            'int3' : 5
        }]
    </db:input-parameters>
    <db:output-parameters>
        <db:output-parameter key="result1" type="INTEGER"/>
        <db:output-parameter key="result2" type="INTEGER"/>
    </db:output-parameters>
</db:stored-procedure>

Returning a Result Set

When you call a stored procedure that returns a result set, the result set is assigned to the payload by default. To change the returning result set, configure a Target Variable or a Target Value in the Advanced tab of the Stored procedure operation configuration.

The following example illustrates an MSSQL database that is initialized when creating a table named languages and a stored procedure named myProc that returns a result set:

CREATE TABLE LANGUAGES (NAME VARCHAR(128), SAMPLE_TEXT VARCHAR(max));

CREATE PROCEDURE myProc AS
   SELECT * FROM LANGUAGES l;
RETURN;

In the Stored procedure operation configuration screen, set SQL Query Text to { call myProc() }.
In the Advanced tab, set Target Variable to aResult.

The Stored procedure operation with the Target Variable field configuration

In the XML editor, the <db:stored-procedure> configuration looks like this:

<db:stored-procedure config-ref="MsSQL" target="aResult">
  <db:sql ><![CDATA[{ call myProc() }]]></db:sql>
</db:stored-procedure>

Execute Stored Procedures Examples - Mule 4

Callable Statements in Stored Procedures

Passing Parameters to Stored Procedures

Use Dates on Stored Procedures

Configure the Input Parameters Field in the Stored Procedure Operation

Configure the In Out Parameters Field in the Stored Procedure Operation

Configure the Input and Output Parameters Fields in the Stored Procedure Operation

Returning a Result Set

See Also