Stored Procedure
In a previous microlearning we learned how to set up a connection with a database. In this microlearning, we'll focus on calling functions or stored procedures directly linked to a database from an eMagiz flow. Building on our previous knowledge of setting up database connections, we'll delve into configuring stored procedure components, addressing key aspects such as identifying the procedure name, determining whether you're calling a function or stored procedure, and specifying the required parameters. We'll guide you through setting up the component and provide practical examples to ensure you can effectively integrate these database operations into your workflows.
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