Outbound Database Protocol — Stored Procedure

Here is an example stored procedure configuration. You first need to select a database instance and table name, in order for Cloverleaf to parse the message sent to the outbound thread. Cloverleaf will make the message  comma delimited, and each comma-delimited field will represent a column in the database table. This should have been created first in the database schema configurator.

The items in the stored procedure parameter list will correspond to the values in the outbound message. Behind the scenes it creates a VRL for the table. The VRL is used in your translate on the inbound thread. And you use the VRL field names in the S.P. call statement below. You don’t need to include all the columns/fields in your S.P. call, just the fields you want, as long as you use the correct field names.

In this example, the stored procedure name is ‘prPRL_insertMsg’.

{call prPRL_insertMsg(,,, , , )}

You will need the brackets and parenthesis. Don’t forget to set the security access for the stored procedure.

Peter,

How do you get confirmation the Stored Procedure functioned properly?

For example if the Stored Procedure is to do a simple insert and the insert fails, how does the Stored Procedure return the Error code and text? Is there a provision for parameter exchange (the Stored Procedure could return the error code and the text associated with it) or some other mechanism.

If all that gets returned is SQL_SUCCESS or something like that doesn’t that just mean the Stored Procedure was found, you have permissions to execute it, and it was loaded? The Stored Procedure itself could have failed I suspect.

Thanks.

Jim, you are absolutely right. As far as I know, version 6.1 does not provide adequate error handling in this area. Stored procedure failures will show up in the process log and the message will go to the Error Database, but you do not have the opportunity to intervene or take action.

However, version 6.2 allows the use of OUTPUT variables in the stored procedure parameter list, and the values that are returned can be examined in a TCL proc. I have not worked on this yet.

Also I believe that there are new configuration options where specified (TCL) actions can be taken when a message is written to the Error DB – I believe that there is a ‘hook’ in the middle of the process, giving you the opportunity to execute some kind of action. Not sure what you would or could do though.

Peter,

Thanks. That is encouraging regarding 6.2.

What I would do is to notify whoever is monitoring the integration (via email, paging or other mechanism) and optionally turn off the connection. Unless the return indicated continuing was advisable, one would have to presume every other interaction will also fail until the issue is resolved.