skip to main content
Progress OpenEdge Business Process Modeler: User's Guide
Introducing Managed Adapters : Database Managed Adapter : Working with the Database Adapter : Using stored procedures
 
Using stored procedures
You can execute a stored procedure that you have defined previously and stored, and use it as a new SQL Statement.
To use a stored procedure:
1. From the Configuration tab of the DBAdapter Properties dialog box, click Configure to open the DBAdapter Configurator dialog box.
It displays the SQL Statements along with their types. You can add a new statement, or modify or delete selected statements. You can click Move Up and Move Down to change the order of the statements.
2. Click Add to open the SQL Statement dialog box.
3. Connect to a database and click the SQL Statement tab, which displays the available SQL statements.
Figure 87. DBAdapterConfigurator - Stored Procedures SQL Statements
4. Click Stored Procedure as the type, then click Choose Procedure to open the Stored Procedures dialog box, which displays the list of available stored procedures
Figure 88. DBAdapterConfigurator - Stored Procedures list
5. From the list of procedures in the Stored Procedures dialog box, select a procedure and click Procedure Info to display the details about the selected procedure.
Figure 89. DBAdapterConfigurator - Stored Procedures info
6. In the DBAdapter Configurator dialog box, click Variables to open the Variable Definitions dialog box. Once a stored procedure is selected; the input, output and I/O variables for that procedure get created and added to the existing list of variables. The input and output parameters of the stored procedure also get mapped to the corresponding variables.
Figure 90. DBAdapterConfigurator - Stored Procedures variables
You can add, modify or remove variables. If you change any variable, you must change the corresponding SQL statement appropriately.
7. From the SQL Statements dialog box, use the Input Parameters tab to view the defined input parameters.
8. Click the Output Parameters tab to view the defined output parameters.
Figure 91. Database Adapter Configurator - Stored Procedures Output Parameters
Note: Result of a stored procedure execution is always a single row. Hence, by default, Expect Single Row option is selected, and all other options are disabled.
9. Click OK to return to the DBAdapter Configurator dialog box. Click OK to return to the Properties view.
10. Click Change Mapping.
Figure 92. Database Adapter Configurator - Stored Procedures mapping
For input parameters, only Source mapping can be defined. For output and return parameters only Target mapping can be defined. For I/O parameters, both Source as well as Target mapping can be defined. Note that this dialog box shows variables for all the stored procedures configured for a specific workstep.