Stored procedure configuration
When configuring the Database protocol for the first time, you must select a Database Connection and Table Schema.
Stored procedures are supported in the Lookup Table Configurator. You can edit the stored procedures to assign input and output variables. Return values and result sets are mapped for use by the engine.
Selecting
and clicking opens the Stored Procedure Configurator. The columns from the selected table are shown.Syntax
Invoking statement syntax options:
-
{?= call procedure-name[(arg1,arg2, ...)]}
-
{call procedure-name[(arg1,arg2, ...)]}
This is the stored procedure SQL escape syntax for stored procedures to invoke in a standard way for all RDBMS systems.
- Only two types of
parameters (
IN
andOUT
) are supported in the database protocol stored procedure. TheINOUT
parameter is not supported. - When setting a parameter,
use the
OUT
flag at the end of the parameter name. Parameters without theOUT
flag are considered asIN
parameters. - The form
{?= call procedure-name[(arg1,arg2, ...)]}
indicates that a result parameter is set to retrieve the return value of the stored procedure.The engine uses the _CLRC_ name for the result parameter. The result parameter is registered as an
OUT
parameter. Not all databases support the stored procedure return value. You must confirm with the database provider before using it. - In one stored procedure
invocation,
OUT
parameter names should not be duplicated.
Examples:
{?= call sptest_rc(inValue1, inValue2, outPara1 OUT, outPara2 OUT)}
This invokes the sptest_rc
stored
procedure. This has two IN
parameters and two
OUT
parameters, and requests the return value
of the sptest_rc
stored procedure.
{call sptest(inValue1, inValue2, outPara1 OUT, outPara2 OUT)}
This invokes the sptest
stored
procedure, which has two IN
parameters and two
OUT
parameters.
NetConfig migration
Migration is required when migrating from CIS 6.0 to CIS 6.2 and later versions.
Run hcirootcopy to do the NetConfig migration for the Database protocol’s stored procedure invocation.
If a stored procedure is used in the inbound read action for the Oracle
database, then you must manually add the OUT CURSOR
parameter. This retrieves the resultset
.
Closing the Stored Procedure Configurator
Clicking Stored Procedure Configurator dialog box and populates the configured statement into the Content area on the main dialog box.
closes theIf a stored procedure statement has already been defined, then it displays when you open the Stored Procedure Configurator dialog box.