Nav

Database Connector Examples

This document offers examples of applications which use the Database Connector. For full documentation of the connector, access the Database Connector documentation and Database Connector Reference.

Prerequisites

This document presents two usage examples of the database connector. This document assumes you are familiar with the database connector as well as with DataSense; to understand the second example, the reader should also be familiar with Mule batch processing. You should also review DataWeave, which replaces DataMapper.

Databases Used in the Examples

Most examples in this page were written for MySQL, using one database with two tables. If you wish to create this database and populate it with the sample data used in these examples, you can download this MySQL script. The script creates the database, database user and tables, and populates them with 102 rows of data.

The table below lists the parameters for the database used by these examples. If you modify these parameters to suit your installation, bear in mind that you need to modify the SQL script to match your customized parameters.

  • Databasecompany

  • Database user: generatedata@localhost

  • Database user password: generatedata

  • MySQL listening port: 3306 (MySQL default)

  • Database tables: employees, roles and salary

Tables

  • Table employees. Primary key: no (employee number).

Column Type

no

Integer

dob

Date

first_name

Varchar

last_name

Varchar

gender

Enum(F, M)

hire_date

Date

  • Table roles. Primary key: id (role ID).

Column Type

id

Integer

emp_no

Integer

role

Varchar

Creating the Database, User, and Table

If you wish to create a database with the data used in this example, download this MySQL script, then run it as root on your database server. The script performs the following actions:

  • Creates a database called company

  • Creates user generatedata@localhost with password generatedata

  • Grants all privileges on database company to user generatedata@localhost

  • Creates table employees on database company

  • Creates table roles on the same database

  • Populates both tables with sample data

View the Script for Copy-Paste


          
       
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE DATABASE company;
 
CREATE USER 'generatedata'@'localhost' IDENTIFIED BY 'generatedata';
 
GRANT ALL PRIVILEGES ON company.* TO generatedata@localhost IDENTIFIED BY 'generatedata';
 
FLUSH PRIVILEGES;
 
USE company;
 
CREATE TABLE employees (
no INT NOT NULL,
dob DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (no)
);
 
CREATE TABLE roles (
id mediumint(8) unsigned NOT NULL auto_increment,
emp_no mediumint,
role varchar(255) default NULL,
PRIMARY KEY (id)
) AUTO_INCREMENT=1;
 
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1011,'1985-09-02','Chava','Puckett','F','2008-10-12');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1012,'1971-12-03','Christopher','Tillman','M','2006-11-01');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1013,'1975-07-31','Judith','David','F','10-11-20');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1014,'1957-08-03','Neil','Ford','F','08-09-04');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1015,'1977-01-09','Daryl','Wolfe','M','07-09-14');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1016,'1986-03-08','Maryam','Burt','M','09-09-16');

 
INSERT INTO roles (emp_no,role) VALUES (1011,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1012,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1013,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1014,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1015,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1016,'Office Assistant');

Running the script on MySQL Server

  1. Save the MySQL script to a convenient location on your hard drive.

  2. Open a terminal and run the following command:

    mysql -u root -D mysql -p
  3. You are prompted for the MySQL root user’s password. After you type the password, you should see a mysql prompt:

    mysql>
  4. Run the MySQL script with the following command, where <script> is the full path and filename to the script, such as /home/joe/create.sample.db.sql.

    source <script>;
  5. MySQL creates the user, database and tables specified on the script. To verify the tables, run:

    
                  
               
    1
    2
    
    use company;
    show tables;
  6. The show tables command produces output similar to the following:

    
                  
               
    1
    2
    3
    4
    5
    6
    7
    
    +-------------------+
    | Tables_in_company |
    +-------------------+
    | employees         |
    | roles             |
    +-------------------+
    2 rows in set (0.00 sec)
  7. For information about a table, run describe <table>. To see the full contents of a table, run the standard SQL statement select * from <table>.

  8. To exit mysql, type quit;.

Example: Perform SELECT Operation

This example simply illustrates how to retrieve data from a database with a database connector using a SELECT operation.

For simplicity, this example accesses a database directly from an HTTP connector, but this is not a recommended practice. This example is meant to illustrate the concept of a simple SELECT operation, but we do not recommend exposing database functionality directly as an API. To review how HTTP parameters are accessed from Mule messages, see Mapping Between HTTP Requests and Mule Messages.

The MySQL database company contains a table called employees, with employee information such as first and last names, birth dates, etc.

DBtestFlow

In the Mule application, an inbound HTTP connector listens for HTTP GET requests with the form: http://<host>:8081/?lastname=<parameter>;. The HTTP connector passes the value of <parameter> as an HTTP query parameter to the database connector. The database connector is configured to use MEL to use this value for the SQL query listed below.

select first_name from employees where last_name = #[message.inboundProperties.'http.query.params'.lastname]

As you can see, the MEL expression in the SQL query (\#[message.inboundProperties.'http.query.params'.lastname])references the value of the parameter passed to the HTTP connector. So if the HTTP connector receives http://localhost:8081/?lastname=Smith, the SQL query is interpreted by the database as select first_name from employees where last_name = Smith.

The database connector instructs the database server to run the SQL query, retrieves the result of the query, and passes it to the object-to-JSON message processor which converts the result to JSON. Since the HTTP connector is configured as request-response, the result is returned to the originating HTTP client.

Configuring the Database Connector for This Example

In this example, the database connector retrieves data from a MySQL database that resides on host xubuntu listening on port 3306, the default for MySQL. To run this example use your host name. The table below lists the full configuration for the database connector.

Properties Editor

db-ex-1

Parameter Value

Display Name

Database

Config Reference

MySQL_Configuration

Operation

Select

Query Type

Parameterized

Parameterized SQL Statement

select first_name from employees where last_name = #[message.inboundProperties['lastname']]


    
             
          
1
2
3
&lt;db:select config-ref="MySQL_Configuration" doc:name="Database"&gt;
   &lt;db:parameterized-query&gt;&lt;![CDATA[select first_name from employees where last_name = '#[message.inboundProperties['lastname']]&gt;&lt;/db:parameterized-query&gt;
&lt;/db:select&gt;

In this example, the database connector references the MySQL_Configuration global element to obtain connection parameters. MySQL_Configuration is configured with the parameters listed below.

Global Element - General Tab

global_db_connector_example

Parameter Value

Name

MySQL_Configuration

Host

Not set (defined in URL)

Port

Not set (defined in URL)

User

Not set (defined in URL)

Password

Not set (defined in URL)

Database

Not set (defined in URL)

Configure via Spring bean

No (unchecked)

DataSource Reference

None

URL

jdbc:mysql://xubuntu:3306/ company?user=generatedata&amp;password=
generatedata

Enable DataSense

True (default)


    
             
          
1
&lt;db:mysql-config name="MySQL_Configuration" url="jdbc:mysql://xubuntu:3306/company?user=generatedata&amp;amp;password=generatedata&amp;amp;generateSimpleParameterMetadata=true" doc:name="MySQL Configuration"/&gt;

The target database company contains the table employees, a snippet of which is shown below.


          
       
1
2
3
4
5
6
7
+--------+------------+-------------+-----------+--------+------------+
| emp_no | birth_date | first_name  | last_name | gender | hire_date  |
+--------+------------+-------------+-----------+--------+------------+
|   1010 | 1978-10-07 | Ross        | Rodgers   | M      | 2011-10-07 |
|   1011 | 1985-09-02 | Chava       | Puckett   | F      | 2008-10-12 |
|   1012 | 1971-12-03 | Christopher | Tillman   | M      | 2006-11-01 |
...

When the HTTP connector receives a request, the flow is activated and the database connector performs the following actions:

  • logs in to the target database

  • instructs the MySQL server to run the user-defined query

  • retrieves the result, then passes it to the next message processor as the message payload

The object-to-JSON transformer converts the message payload into JSON, as shown below.

[{"first_name":"<result>"}]

Finally, the HTTP connector returns the string to the originating client. So for example, an HTTP query originated in a Web browser would return the result in the browser window, as shown below.

ex.1.v2.browser_results

The above image shows that the query has returned the correct value for the first_name column of the row where last_name matches Tillman.

To activate the flow in this example, you can use a browser, as shown above, or the curl command-line HTTP client, as shown below.

curl http://<host>:8081/?lastname=<parameter>

Using curl will print the resulting JSON to the terminal’s standard output.

SELECT Operation Example Code


          
       
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
        xmlns:spring="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd">
    <db:mysql-config name="MySQL_Configuration" host="localhost" port="3306" user="" password="" database="company" doc:name="MySQL Configuration"/>
    <http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8080" doc:name="HTTP Listener Configuration"/>
    <flow name="db-connector-select-getresultsetFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/" allowedMethods="GET" doc:name="HTTP"/>
        <db:select config-ref="MySQL_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[select first_name from employees where last_name = #[message.inboundProperties.'http.query.params'.lastname]]]></db:parameterized-query>
        </db:select>
        <json:object-to-json-transformer doc:name="Object to JSON"/>
    </flow>
</mule>

Example: Retrieve from Database and Batch Write to File

In this example, a database connector retrieves data from two tables. DataWeave maps this data to the CSV format. A file connector writes the resulting CSV file to disk, and a logger component logs processing details to the console.

This example uses batch processing, which means that within this Mule app, messages are divided into records and processed asynchronously. Batch processing, in conjunction with streaming enabled on the database connector, can be useful to avoid system overload when dealing with large volumes of data.

ex.2-batchjob.flow

The database connector is inside a poll scope which, every ten minutes, requests the database connector to perform its configured operation on the database. The database connector performs a database query which returns 100 rows. Mule feeds this data into the DataWeave transformer inside the "Batch Commit" batch scope. DataWeave transforms the input into a CSV file; then, a file connector writes the resulting CSV file to disk. A logger component logs processed records to the console.

This example uses the same MySQL database as the previous example. If you want to recreate the database on your MySQL server, you can download or copy-paste the MySQL script provided in this page. For details, see the see Database Configuration section above.

Configuring the Database Connector For This Example

In this example, the database connector retrieves data from a MySQL database that resides on host xubuntu listening on port 3306, the default port for MySQL. The table below lists the full configuration for the database connector.

Properties Editor

example_2_db_gentab

Parameter Value

Display Name

Database

Config Reference

MySQL_Configuration

Operation

Select

Query Type

Parameterized

Parameterized SQL Statement

SELECT no, first_name, last_name, role FROM employees INNER JOIN roles ON employees.no = roles.emp_no;


    
             
          
1
&lt;db:mysql-config name="MySQL_Config"     doc:name="MySQL Config" url="jdbc:mysql://xubuntu:3306/company?user=generatedata&amp;amp;password=generatedata&amp;amp;generateSimpleParameterMetadata=true"/&gt;

In this example, the database connector references MySQL_Configuration to obtain connection parameters. MySQL_Configuration was created with the parameters listed below.

Global Element - General Tab

global_db_connector_example

Parameter Value

Name

MySQL_Configuration

Host

Not set (defined in URL)

Port

Not set (defined in URL)

User

Not set (defined in URL)

Password

Not set (defined in URL)

Database

Not set (defined in URL)

Configure via Spring bean

No (unchecked)

DataSource Reference

None

URL

jdbc:mysql://xubuntu:3306/company?user=generatedata&amp;password=generatedata&amp;generateSimpleParameterMetadata=true

Enable DataSense

True (default)


    
             
          
1
&lt;db:mysql-config name="MySQL_Configuration" url="jdbc:mysql://xubuntu:3306/company?user=generatedata&amp;amp;password=generatedata&amp;amp;generateSimpleParameterMetadata=true" doc:name="MySQL Configuration"/&gt;

Database Query and Results

The target database, called "company," contains two tables, "employees" and "roles." A snippet of each is shown below.

Table "employees"


           
        
1
2
3
4
5
6
7
+--------+------------+-------------+-----------+--------+------------+
| emp_no | birth_date | first_name  | last_name | gender | hire_date  |
+--------+------------+-------------+-----------+--------+------------+
|   1010 | 1978-10-07 | Ross        | Rodgers   | M      | 2011-10-07 |
|   1011 | 1985-09-02 | Chava       | Puckett   | F      | 2008-10-12 |
|   1012 | 1971-12-03 | Christopher | Tillman   | M      | 2006-11-01 |
...

Table "roles"


           
        
1
2
3
4
5
6
7
+-----+--------+--------------------+
| id  | emp_no | role               |
+-----+--------+--------------------+
|   1 |   1011 | Sr. Developer      |
|   2 |   1012 | Office Manager     |
|   3 |   1013 | Secretary          |
...

The database connector has been configured to perform the SQL query shown below.

SELECT no, first_name, last_name, role FROM employees INNER JOIN roles ON employees.no = roles.emp_no;

The query produces 100 rows of results with data from both tables. The first three rows are shown below.


           
        
1
2
3
4
5
6
7
+------+-------------+-----------+--------------------+
| no   | first_name  | last_name | role               |
+------+-------------+-----------+--------------------+
| 1011 | Chava       | Puckett   | Sr. Developer      |
| 1012 | Christopher | Tillman   | Office Manager     |
| 1013 | Judith      | David     | Secretary          |
...

This resulting data will be transformed to CSV by the DataWeave transformer. The next section explains how to configure the DataWeave transformer while avoiding having to manually map the input/output fields.

Configuring DataWeave I/O

To create the batch job in this example, perform these steps:

  1. Drag a batch scope from the Palette onto the canvas.

  2. Drag a database connector to the Input section of the batch scope on the left.

  3. Configure the database connector as necessary to retrieve the desired data from the database. This entails all connection parameters as well as the operation, etc. Test the connection to see that it works. When you leave the database connector configuration (i.e. when you click anywhere outside the database connector on the Studio window) the database connector will automatically retrieve metadata using DataSense.

  4. Drag a batch commit scope to the Process Records section of the batch scope.

  5. Drag a DataWeave (Transform Message) transformer into the batch commit scope.

  6. Drag a file connector into the batch commit scope.

  7. Finally, drag a logger component in the On Complete section of the batch scope.

At this point, you have built your batch job and are ready to configure DataWeave. Double-click the DataWeave transformer to open its configuration.

Datasense should build the input side of your transform. If this is not the case, define the metadata as Map, comprised of List<Element>, adding the attributes and their types per the database table attributes. To instruct DataWeave how to process the MySQL data, right click the words "Payload" on the left side of the pane and click Set Metadata.

set metadata

ex2.dataweave.conn.select

The image below shows the finished DataWeave transformer configuration.

ex2.finished.dataweave

The resulting DataWeave code is auto-generated, and appears like this:


          
       
1
2
3
4
5
6
7
8
9
%dw 1.0
%output application/csv
---
payload map ((payload01 , indexOfPayload01) -> {
        column_0: payload01.first_name,
        column_1: payload01.last_name,
        column_2: payload01.no,
        column_3: payload01.role
})

If you wish to test this Mule application, you need to perform two additional tasks:

  • Configure the file connector to save the CSV file to the desired location on your local disk.

  • Configure the Logger component to output the message displayed below, at log level WARN.

    
                 
              
    1
    
    Total Records exported: #[message.payload.getLoadedRecords()], Failed Records: #[message.payload.getFailedRecords()], Processing time: #[message.payload.getElapsedTimeInMillis()]

When you run the application, DataWeave will output the results to a file connector, which in turn will write them to a file on your local disk. (In this example, the output file is roles.csv.)

When you run the application, the database connector will automatically connect to the database and retrieve the data. If you do not stop the application, it will repeat this operation every ten minutes. The output CSV data will be written to the file that you specified in the file connector. Below are the first lines from the resulting CSV file.


          
       
1
2
3
"Chava","Puckett","1011","Sr. Developer"
"Christopher","Tillman","1012","Office Manager"
"Judith","David","1013","Secretary"

The console displays the messages output by the logger component.


          
       
1
2
3
4
5
6
7
8
9
10
11
12
13
14
INFO  2014-02-28 14:11:20,805 [pool-15-thread-1] com.mulesoft.module.batch.engine.DefaultBatchEngine: Created instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 for batch job db-appBatch1
INFO  2014-02-28 14:11:20,808 [pool-15-thread-1] com.mulesoft.module.batch.engine.DefaultBatchEngine: Starting input phase
INFO  2014-02-28 14:11:20,808 [pool-15-thread-1] com.mulesoft.module.batch.engine.DefaultBatchEngine: Input phase completed
INFO  2014-02-28 14:11:20,822 [pool-15-thread-1] com.mulesoft.module.batch.engine.queue.BatchQueueLoader: Starting loading phase for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
INFO  2014-02-28 14:11:20,847 [pool-15-thread-1] com.mulesoft.module.batch.engine.queue.BatchQueueLoader: Finished loading phase for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1. 100 records were loaded
INFO  2014-02-28 14:11:20,851 [pool-15-thread-1] com.mulesoft.module.batch.engine.DefaultBatchEngine: Started execution of instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
INFO  2014-02-28 14:11:22,007 [batch-job-db-appBatch1-work-manager.01] com.mulesoft.module.batch.DefaultBatchStep: Step Batch_Step finished processing all records for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
INFO  2014-02-28 14:11:28,584 [batch-job-db-appBatch1-work-manager.01] org.mule.lifecycle.AbstractLifecycleManager: Initialising: 'connector.file.mule.default.dispatcher.763473616'. Object is: FileMessageDispatcher
INFO  2014-02-28 14:11:28,586 [batch-job-db-appBatch1-work-manager.01] org.mule.lifecycle.AbstractLifecycleManager: Starting: 'connector.file.mule.default.dispatcher.763473616'. Object is: FileMessageDispatcher
INFO  2014-02-28 14:11:28,592 [batch-job-db-appBatch1-work-manager.01] org.mule.transport.file.FileConnector: Writing file to: /Users/pedro/mule.installations/Dolomites-17feb14/workspace/test2/roles.csv
INFO  2014-02-28 14:11:28,691 [[test2].Batch Dispatcher thread] com.mulesoft.module.batch.engine.DefaultBatchEngine: Finished execution for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
INFO  2014-02-28 14:11:28,692 [[test2].Batch Dispatcher thread] com.mulesoft.module.batch.engine.DefaultBatchEngine: Starting execution of onComplete phase for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
WARN  2014-02-28 14:11:28,702 [[test2].Batch Dispatcher thread] org.mule.api.processor.LoggerMessageProcessor: Total Records exported: 100, Failed Records: 0, Processing time: 7844
INFO  2014-02-28 14:11:28,703 [[test2].Batch Dispatcher thread] com.mulesoft.module.batch.engine.DefaultBatchEngine: Finished execution of onComplete phase for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1

Example: Pass Input Parameter to Stored Procedure Call

Run this example to update a "bonus" field in a table where salary and bonus amounts are stored. This example may prove useful for generating ideas on how to configure the database connector to call a stored procedure from your Mule application.

flow pic stored proc

Set up "salary" Table

  1. First set up another table in your MySQL database, salary, with the primary key as emp_no, with a salary_amount and bonus entry for each person.

    CREATE TABLE salary (
    emp_no INT NOT NULL,
    salary_amount INT NOT NULL,
    bonus INT NOT NULL,
    PRIMARY KEY (emp_no)
    );
  2. Perform the INSERT statements to populate the table.

    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1011,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1012,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1013,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1014,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1015,150000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1016,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1017,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1018,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1019,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1020,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1021,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1022,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1023,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1024,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1025,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1026,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1027,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1028,150000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1029,150000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1030,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1031,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1032,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1033,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1034,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1035,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1036,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1037,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1038,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1039,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1040,150000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1041,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1042,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1043,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1044,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1045,150000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1046,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1047,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1048,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1049,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1050,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1051,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1052,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1053,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1054,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1055,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1056,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1057,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1058,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1059,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1060,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1061,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1062,150000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1063,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1064,150000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1065,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1066,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1067,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1068,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1069,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1070,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1071,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1072,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1073,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1074,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1075,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1076,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1077,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1078,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1079,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1080,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1081,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1082,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1083,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1084,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1085,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1086,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1087,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1088,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1089,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1090,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1091,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1092,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1093,150000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1094,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1095,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1096,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1097,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1098,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1099,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1100,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1101,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1102,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1103,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1104,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1105,5000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1106,70000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1107,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1108,75000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1109,50000,1000);
    INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1110,70000,1000);
  3. Create a simple stored procedure like this one, which adds a given amount to the bonus stored in the table "salary".

    CREATE DEFINER=`root`@`localhost` PROCEDURE `add_bonus_by_emp_no`(IN EMP_NO integer,
    IN ADDL_BONUS integer)
    BEGIN
    UPDATE salary
    SET bonus = ADDL_BONUS + bonus
    WHERE salary.emp_no=EMP_NO;
    END
  4. One would call this example procedure to increase the employee’s bonus using the employee number. The first argument is passed as an input parameter, <db:in-param name="empno" value="#[payload]"/>. The second argument is the amount to add to the bonus.

    CALL add_bonus_by_emp_no(:empno,500);
  5. This call is represented in the example by: <db:parameterized-query><![CDATA[CALL add_bonus_by_emp_no(empno,500);]]></db:parameterized-query>. Here is how you set up the stored procedure in Studio.

    database connector examples fe6e9

    The employee ID is passed as an input parameter. It is first ingested by the application from the HTTP query parameter, when the HTTP endpoint is hit: http://localhost:8081/getemps?emp=1065
  6. Here is the full code for a simple app that calls a stored procedure using the payload as an input/"in" parameter. Make sure to modify the credentials and endpoint in the database connector global element for your MySQL instance.

    
                 
              
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    
    <?xml version="1.0" encoding="UTF-8"?>
    
    <mule xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata" xmlns:batch="http://www.mulesoft.org/schema/mule/batch" xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
            xmlns:spring="http://www.springframework.org/schema/beans"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
    http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
    http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
    http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
    http://www.mulesoft.org/schema/mule/batch http://www.mulesoft.org/schema/mule/batch/current/mule-batch.xsd
    http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd">
        <http:listener-config name="HTTP_Listener_Configuration" host="localhost" port="8081" doc:name="HTTP Listener Configuration" />
        <db:mysql-config name="MySQL_Configuration" host="localhost" port="3306" user="username" password="xyz" database="company" doc:name="MySQL Configuration"/>
        <http:request-config name="HTTP_Request_Configuration" host="localhost" port="8081" doc:name="HTTP Request Configuration"/>
        <flow name="stored-proc-exFlow">
            <http:listener config-ref="HTTP_Listener_Configuration" path="/getemps" doc:name="HTTP">
                <http:response-builder>
                    <http:header headerName="" value=""/>
                </http:response-builder>
            </http:listener>
            <set-payload value="#[message.inboundProperties.'http.query.params'.emp]" doc:name="Set Payload"/>
            <db:stored-procedure config-ref="MySQL_Configuration" doc:name="Database">
                <db:parameterized-query><![CDATA[call add_bonus_by_emp_no(:empno,500);]]></db:parameterized-query>
                                                    <db:in-param name="empno" value="#[payload]"/>
            </db:stored-procedure>
            <logger message="#[payload]" level="INFO" doc:name="Logger"/>
        </flow>
    </mule>

See Also