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 Stored Procedure and clicking Configure opens the Stored Procedure Configurator. The columns from the selected table are shown.

Syntax

Note: Stored procedure invocations cannot be configured on a SQLite database.

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 and OUT) are supported in the database protocol stored procedure. The INOUT parameter is not supported.
  • When setting a parameter, use the OUT flag at the end of the parameter name. Parameters without the OUT flag are considered as IN 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 OK closes the Stored Procedure Configurator dialog box and populates the configured statement into the Content area on the main dialog box.

If a stored procedure statement has already been defined, then it displays when you open the Stored Procedure Configurator dialog box.