Wiki source code of Stored Procedure
Last modified by Martijn Woudstra on 2022/08/05 13:42
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | {{container}}{{container layoutStyle="columns"}}((( | ||
| 2 | In a previous [[microlearning>>Main.eMagiz Academy.Microlearnings.Advanced Level.Advanced Database Connectivity.advanced-database-connectivity-configuration.WebHome||target="blank"]] 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. | ||
| 3 | |||
| 4 | Should you have any questions, please get in touch with [[academy@emagiz.com>>mailto:academy@emagiz.com]]. | ||
| 5 | |||
| 6 | == 1. Prerequisites == | ||
| 7 | |||
| 8 | * Expert knowledge of the eMagiz platform | ||
| 9 | |||
| 10 | == 2. Key concepts == | ||
| 11 | |||
| 12 | The focus of this microlearning will be on learning how to call a function or stored procedure on the database level. | ||
| 13 | |||
| 14 | There are three critical considerations when calling a stored procedure (regardless of the ones discussed earlier that still apply): | ||
| 15 | |||
| 16 | * What is the name of the stored procedure you are calling? | ||
| 17 | * Do you want to call a function or a stored procedure? | ||
| 18 | * Which parameters do you need to provide to make the call work? | ||
| 19 | |||
| 20 | == 3. Stored procedure == | ||
| 21 | |||
| 22 | In a previous [[microlearning>>Main.eMagiz Academy.Microlearnings.Advanced Level.Advanced Database Connectivity.advanced-database-connectivity-configuration.WebHome||target="blank"]] 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. | ||
| 23 | |||
| 24 | There are three critical considerations when calling a stored procedure (regardless of the ones discussed earlier that still apply): | ||
| 25 | |||
| 26 | * What is the name of the stored procedure you are calling? | ||
| 27 | * Do you want to call a function or a stored procedure? | ||
| 28 | * Which parameters do you need to provide to make the call work? | ||
| 29 | |||
| 30 | === 3.1 Setting up the connection component === | ||
| 31 | |||
| 32 | In our previous [[microlearning>>Main.eMagiz Academy.Microlearnings.Advanced Level.Advanced Database Connectivity.advanced-database-connectivity-configuration.WebHome||target="blank"]] we have explained how to set up the connection to a database. Therefore we assume this information is known. | ||
| 33 | |||
| 34 | === 3.2 Configure the stored procedure component === | ||
| 35 | |||
| 36 | 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. | ||
| 37 | |||
| 38 | [[image:Main.Images.Microlearning.WebHome@expert-database-connectivity-stored-procedure--available-options-for-stored-procedures.png]] | ||
| 39 | |||
| 40 | 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. | ||
| 41 | |||
| 42 | [[image:Main.Images.Microlearning.WebHome@expert-database-connectivity-stored-procedure--configuration-the-stored-procedure-component.png]] | ||
| 43 | |||
| 44 | {{warning}}The microlearning assumes the stored procedure or function is available on the database level. Creating stored procedures and functions is not our core business, so we do not discuss how you could approach this.{{/warning}} | ||
| 45 | |||
| 46 | == 4. Assignment == | ||
| 47 | |||
| 48 | Build a database connection between eMagiz and a database that calls a stored procedure. This assignment can be completed with the help of the (Academy) project you created/used in the previous assignment. | ||
| 49 | |||
| 50 | == 5. Key takeaways == | ||
| 51 | |||
| 52 | * Do you have a proper JDBC driver for the database type you want to connect to? | ||
| 53 | * Is the database URL known, or can it be determined? | ||
| 54 | * Is the authentication information known? | ||
| 55 | * What is the name of the stored procedure you are calling? | ||
| 56 | * Do you want to call a function or a stored procedure? | ||
| 57 | * Which parameters do you need to provide to make the call work? | ||
| 58 | * Don't forget to add the driver as a resource to the flow | ||
| 59 | |||
| 60 | == 6. Suggested Additional Readings == | ||
| 61 | |||
| 62 | 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. | ||
| 63 | |||
| 64 | == 7. Silent demonstration video == | ||
| 65 | |||
| 66 | This video demonstrates a working solution and how you can validate whether you have completed the assignment. | ||
| 67 | |||
| 68 | {{video attachment="expert-database-connectivity-stored-procedure.mp4" reference="Main.Videos.Microlearning.WebHome"/}})))((({{toc/}}))){{/container}}{{/container}} |