Example 1

This example passes parameters from a Cloverleaf message into a stored procedure using the Database Outbound protocol.

For example, a web service that returns several fields must be invoked. Then, these fields must be inserted into the database.

Finally, you must pass the parsed fields as parameters into the Database Outbound protocol; that is, you must bind the fields to the stored procedure parameters.

To do this, use the Database Outbound protocol and pass parameters as the placeholder value defined in the outbound stored procedure invocation. That is, you pass parameters to a stored procedure to insert data into the database.

For additional information on the usage of stored procedures, see the online help at Configuration > Protocols > Database > Stored Procedure Configurator.

For invoking stored procedures, use any of these syntaxes:

{?= call procedure-name[(arg1,arg2, ...)]}
{call procedure-name[(arg1,arg2, ...)]}

This is a stored procedure SQL escape syntax that invokes stored procedures for all RDBMS.

  • Only two types of parameters, IN parameter and OUT parameter, are supported in the Database protocol stored procedure. An INOUT parameter is not supported.
  • When setting an OUT 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 Cloverleaf engine has a certain name "_CLRC_" for the result parameter. The result parameter is registered as an OUT parameter. Not all databases support the stored procedure return value, so you must first confirm with the database provider before using it.
  • In a stored procedure invocation, OUT parameter names should not be duplicated.

This example inserts data into a database by a stored procedure:

{call sp_dbp_insert(TABLE_SANITY.ID, TABLE_SANITY.STRFLD, 
TABLE_SANITY.INTFLD, TABLE_SANITY.DATEFLD, TABLE_SANITY.FLAG)};

tableName.fieldName is a placeholder of the field value tableName.fieldName of the Cloverleaf outbound message.

In the Database Outbound protocol, the Cloverleaf engine first parses the outbound message that is based on the table schema format. This format is defined in protocol properties and replaces the placeholder with the tableName.fieldName field value.

Command issued:

hcidbprotocoltest -e ASCII -f nl oracle_sp_write C:/cloverleaf/cisversion/integrator/
oracle_dbtest/test/test.txt

Command output:

Test with MESSAGE 1
key01,test1,1,10/01/12,0
Running DB Write action succeeded.

SQL result:

{call sp_dbp_insert(key01, test1, 1, 10/01/12, 0)}