Blog Post

In this blog, we will see how to call a stored procedure in Dell Boomi.

What is the Stored Procedure?

A Stored procedure is a set of SQL Statements which are stored in a relational management system as a group so that it can be reused and shared by multiple programs. The stored procedure accepts the input in the form of parameters and performs the task.

In this Use Case, we will create a process that calls the store procedure from the MYSQL Database and sends the records to the disk in the form of an XML file.

NOTE: We have already created a procedure in the MYSQL Database with all the student details i.e., Student ID, Student Name and Student Loc which accepts input as Student ID and gets the record associated with that particular Student ID.

Here is the Stored Procedure that we created in the MYSQL Database.

Let us now create a process in Boomi and call the stored procedure.

Step 1: Log on to the Boomi platform (https://platform.boomi.com/) with the required credentials i.e., Email Address and Password.

Step 2: Once logged into the Boomi platform, we will be able to view the Home page.

Step 3: Now, click on Services followed by Integration. We will see the Build page. Click on New.

Step 4: Once, click on New, we will be able to see three fields i.e. Type, Component Name and Folder.

                            

• Select Type as process as we are building a process. Component Name and Folder can be given based on your choice (i.e. which name to be given and where do we want to create the process). Click on Create.

Step 5: We see that the process gets created with a start shape which is configured with AS2 Shared Server by default.

Step 6: Select the start shape and choose No Data. Click ok.

                                   

Step 7: Drag and drop the Database connector onto the process canvas.

• We have to configure 3 fields in connector i.e., Action, Connector and Operation.

• We see 2 actions i.e., Get and Send.

Get – To get the data from the Database.

Send – To send the data to the Database.

• Here, we choose action as Get as we are sending data.

• Click + on connection as shown and name it

 

Driver Type: Select the database to connect to from the drop-down list. Here, it would be SQL Server(jTDS).

            Username and Password: Give the Database username and password.

Host: It will be the Name or IP address of the database server.

Port: The port that is used to connect to the database server. The default port for SQL Server is 1433.

Database Name: Give the Database name.

NOTE: Drivers are not included in MYSQL. We need to manually add the jars for MYSQL Connectivity.

                               

• Save and close.
• Click + on the operation and name it.

• Click + on the profile to add it.

• Name the profile. Click on Statement and select Stored Procedure Read as shown in the screenshot.

• Choose import.

• Choose the atom and connection. Click on next.

• Choose the procedure name as Getdetails and click Next.

• Choose all the fields. Click Next.

• We see a message stating we can add the fields and resultset columns manually. Click Finish and add the fields manually.

• Expand Fields and add the fields which we want to display manually.

• Delete @RETURN_VALUE and choose Add Multiple Records. Choose the number of fields we want to add. In this Use case, it is 3.

• Click save and close.

Step 8: Select the database connector and click on parameters to give the input value

• Click + on parameters to add the input.

• Choose Input as ID, Type as Static and static value as 102. Click ok.

Step 9: Drag and drop the Map shape onto the process canvas. Click + and Name the map.

                                

• Add source side and Destination side profiles to the map. Here, the source side would be the Database profile which we have imported and the Destination side would be the XML profile. First, we will add a Source profile. Click choose and select the folder where we saved the profile.

                       

• Here, Profile Type will be Database and select the Profile.

• We see that the source profile has been added to the map.

Step 9: Now, add the destination profile. Click choose on the right side as shown.

• Select Profile Type as XML and click on Create New Profile.

• Name the profile and click on import.

• Choose the file from the directory where we saved it. Click Next.

• We see that the profile has been imported. Click finish, save and close.

• This is how the XML profile looks like

Step 10: We see that source and destination profiles have been imported. Provide one-to-one mapping from source to destination. Click save and close.

Step 11: Drag and drop the set properties shape onto the process canvas to set the file name which comes to the disk. Select + on properties.

                   

Step 12: Select the disk connector from Connectors and choose the file name. Click ok.

         

Step 13: Select + on the parameters side and assign a value to the disk.

           

Step 14: Choose type as static and static value as Payload.xml.

                      

• After configuring the properties in a set properties shape, it looks like

                         

Step 15: Drag and drop the disk connector shape onto the process canvas to send a response to the disk.

Step 16: We have to configure 3 fields in connector i.e. Action, Connector and Operation.

Get – To get the data from a disk location.

Send – To send to the disk location.

• Here, we will choose action as SEND as we are sending a response to the file.

• Click + on connection to create a new one.

• Name the file and give the directory to save the response file. Here, it is the D drive and Boomi Examples folder as shown in the screenshot.

• Click save and close.

Step 17: Click + on operation and name it. Leave everything to default. Click save and close.

                                          

Step 18: Drag and drop the stop shape onto the process canvas to indicate the end of the flow.

Step 19: Arrange all the shapes in order and run the test by clicking on Run Test and configuring the local atom.

                                                       

Step 20: We see that the process has been executed and the file has been sent to the disk directory. First, we will see the data which comes from a database. Select map shape and click on view source. We see that the record of id 102 has been fetched for which we have set the input in the parameters tab of the database connector.

      

Step 21: Now, select disk connector and click on shape source data.

Step 22: Click on view source Once we open the document, we see an XML file with the data existing in the Database.

Step 23: Navigate to the folder that we have configured in the disk location and we see that the file named payload.xml is sent to the disk location.