CL 6.0.0: DB Protocol – Stored Procedure parm passing

Hi Jim,

We use the database outbound protocol to execute stored procedures. We do not use them to store entire messages, but we could, as long as we had previously created/generated a VRL having data elements required by the stored procedure.

There is a limitation where most of the configuration elements are table-based instead of stored procedure-based. When building the VRL, in the database schema configurator, you are working with a table. When you build the stored procedure statement in the database protocol configurator, you are working from a list of  columns’ selected from a table schema.

You can certainly manually configure the stored procedure statement to include your own columns and you can set the direction to be OUTPUT, but the actual data values placed in those arguments must come from the VRL you defined earlier in the database schema configurator.

So given a scenario where you have only two arguments – an output argument that returns execution status information, and in input argument that contains your message, you would have to have a table defined in your database that has a column which could hold a message, and you would pick that table for use in the schema configurator and select that column. You may have to define such a table just for this purpose, just to have something to pick from in Cloverleaf.

As far as the output argument – no luck there – here is the text from the manual:

User-defined output parameters. This is optional. Only integers, varchars, and other types (e.g., data/time) that can be expressed by a string are supported as output parameters. The database driver does nothing with the output values, except to print them to the engine log.

I poked around a little in the database schema configurator, and there seems to be no way to ‘add new columns’ (i.e. add columns that are not in a table). You could directly edit the VRL.

Over the years, and many times here, I’ve heard that stored procedures are prefered to most raw SQL, especially in terms of shifting the ‘application logic’ to the database/application endpoint. Hopefully future releases of Cloverleaf will have more capabilities around stored procedures. I think databases are a valid type of application endpoint, like FTP, local fileset, TCP/IP, etc., and should provide all the same mechanisms for handling data. On the other hand, from this post and the other recent post on database protocol, it seems like we may need a little ‘extra’ capability so that we can ‘integrate’ with a database, instead of ‘just pushing and pulling data’ to a database.

Jim,

If it is of any help, I have come across the issue with the output paramater not returning to the message level already and worked with Infor on this.  Their engineers agreed that it should and I believe there is an AR out there to include that ability in some future update/release althought they could not tell me exactly when.  Hopefully if more people push for this then it will gain a higher priority item to develop.

Thanks,

Jerry

So, perhaps similar to some of the other protocols, if there was a pre- and post-action procedure, like the Directory Parse and Delete proc, there would be an opportunity to modify the SQL/Storproc before execution, and an opportunity to examine and react to the returned values from output arguments (or from a non-zero return code) after execution. Having these two features probably would mean most or all of the other enhancement requests would not be needed. Except for having clearer and more detailed error messages. And maybe more control over long-term connections.