Call DB Procedure/Function
Description
Call DB Procedure/Function executes a stored procedure or function in a database and retrieves the results. Use this process entry when your process needs to invoke server-side database logic — for example, running a batch calculation, triggering a data refresh procedure, or calling a function that returns a status value. Input parameters can come from PS variables, and output values are available for downstream process entries.
Configurations
| Field Name | Description |
|---|---|
| Process Entry name | Specify a unique name for the process entry, on the workspace area. A process entry can be placed on the canvas several times; and each instance of the entry must have a unique name. |
| Local Connection | Specify the name of the database connection on which the procedure resides. |
| Type | Select type of call you want to make, that is, either procedure or function. |
| Procedure name | Specify the name of the procedure or function to call. |
| Find it | Click to search available procedures and functions (Oracle and SQL Server only; With MySQL and JDBC, it is not possible to retrieve the result set of a stored procedure.) |
| Enable auto commit | In some instances, you want to perform updates. Commit in the database using the specified procedure. You can either have the changes performed using auto-commit or not. If auto-commit is disabled, a single commit is performed after the last row is received by the entry. |
| Abort connection on stop? | Select the checkbox to immediately drop the database connection when stopping a workflow. Use this setting for heavy SQL queries to bypass the waiting for the active query to complete. If cleared (default), the system sends a standard cancel command and waits for the active query to finish gracefully. |
| Result name | Specify a PS Variable in result name field starting with Local or Global.For example, Local.userId Note: The field is unavailable if you select Procedure in the Type field. |
| Result type | Specify a field to store the Type of the result of the function call. Note: The field is unavailable if you selected Procedure in the Type field. |
| Parameters | Provide a list of parameters that the procedure or function requires. - Parameter Name: Specify either Local or Global PS variables. For example, - Direction: Select any of the following: o IN (input only) o OUT (output only) o INOUT (value is changed in the database) - Type: Used for output parameters so that Process Studio knows what data type returns. |