Stored Procedure

In a previous microlearning we learned how to set up a connection with a database. In this microlearning, we will build upon that knowledge and learn how to configure the stored procedure components within eMagiz so you can call a function or stored procedure available on the database level.

Should you have any questions, please get in touch with academy@emagiz.com.

1. Prerequisites

• Expert knowledge of the eMagiz platform

2. Key concepts

The focus of this microlearning will be on learning how to call a function or stored procedure on the database level.

There are three critical considerations when calling a stored procedure (regardless of the ones discussed earlier that still apply):

• What is the name of the stored procedure you are calling?
• Do you want to call a function or a stored procedure?
• Which parameters do you need to provide to make the call work?

3. Stored procedure

In a previous microlearning we learned how to set up a connection with a database. In this microlearning, we will build upon that knowledge and learn how to configure the stored procedure components within eMagiz so you can call a function or stored procedure available on the database level.

There are three critical considerations when calling a stored procedure (regardless of the ones discussed earlier that still apply):

• What is the name of the stored procedure you are calling?
• Do you want to call a function or a stored procedure?
• Which parameters do you need to provide to make the call work?

3.1 Setting up the connection component

In our previous microlearning we have explained how to set up the connection to a database. Therefore we assume this information is known.

3.2 Configure the stored procedure component

Once you have laid the groundwork for a stable connection between eMagiz and the database, the final step will define the correct setup of the stored procedure component. Depending on your specific activity, you need a different piece. For example, like with web services and REST services, eMagiz offers various components to retrieve or write data. See below for the complete list.

We will use a simple outbound channel adapter to illustrate the functionality in this example. After adding the component to the flow, you need to ensure that the element is linked to the data source component and that you have added the name of the stored procedure. Optionally you can define parameters that are needed to get the correct result. An example of how this could work is displayed below. For example, you can use the message payload and headers from the input message as parameters in the stored procedure.

4. Key takeaways

• Do you have a proper JDBC driver for the database type you want to connect to?
• Is the database URL known, or can it be determined?
• Is the authentication information known?
• What is the name of the stored procedure you are calling?
• Do you want to call a function or a stored procedure?
• Which parameters do you need to provide to make the call work?
• Don't forget to add the driver as a resource to the flow

5. Suggested Additional Readings

If you are interested in this topic and want more information, please read the release notes provided by eMagiz. Furthermore, check out the help text provided within the platform and see if the store can help you make the configuration a little bit easier.