Calling Sybase stored procedure using Database Outbound Protocol Thread

[William Grow]

I was trying to determine if I could use the database outbound protocol to call a Sybase ASE stored procedure. I was able to register the sybase driver and pull back the Schema and also import a database configuration. I have a couple of challenges at this time one of which is also not truly understanding how to send a message that fits the stored procedure call parameters and how to handle data returned from the stored procedure call (not out parameters). I am also getting an error message trying to call the stored procedure.

The error message is:

Stored procedure ‘XXXX’ may be run only in unchained transaction mode. The ‘SET CHAINED OFF’ command will cause the current session to use unchained transaction mode.

It looks like the official sybase documentation has information on how to turn off this option during the external call but I am uncertain of where to set that option at this time. Where would non-connection string options be input into the database configuration?

 

Additionally, without having a database table with parameters that actually fit the stored procedure call, I was hoping that I could create a VRL with the parameter name definition as the VRL layout and then map from an inbound HL7 V2 message to a VRL record layout to send parameters to the stored procedure. Is this possible?

 

Finally, I do not understand why the schema configurator asks for me to select a DB table when using this architecture as the operation executes against a stored procedure and not a sybase table. Is there a short answer that could clear up this point of confusion?

 

 

Topic

[Peter Heggie]

You can make a VRL manually and use that in a translate, but you cannot use that in the DB outbound (not naturally). When Cloverleaf gets the message in the outbound thread, especially in the protocol portion, it must have a way to refer to each field in that message (internally, each data element is separated by commas, and I don’t know if you can change the delimiter). When you configure the DB outbound, you are forced to pick a view or a table as the base for the data element layout because Cloverleaf needs to use (select)  the correct layout (VRL) that allows you to use the VRL element name (<vrl.element> in the stored procedure call syntax (the parameter list ) so that Cloverleaf will substitute the proper message’s VRL’s element’s value into the stored procedure call parameter list item. I’m sure the manual describes it better.

However – 1) I am pretty sure that I heard that an upcoming release will allow you to use the GUI dialog to select a stored procedure as the basis for the VRL, in addition to views or tables. But don’t quote me! because that was a ‘forward looking statement!’, and 2) what you can do, and what we have done, is to create a ‘fake’ view in the database, where the columns in the view match the stored procedure parameter list. But our work on this approach was limited to input parameters, and I don’t know if this technique can handle output parameters.

The additional configuration parameters for Sybase, I have no clue..

Peter Heggie

[William Grow]

Thank you Peter. That answered my question perfectly. I am unable to alter or modify any part of the DB schema. Your response indicates that for what I am trying to do, I may be better off at this time using the traditional approach (TCL).

[Jim Kosloskey]

If you have the ODBC Drivers, that is the way I would go. I have used those in the past and had a Proc that relied on a VRL, HRL, or FRL built in an Xlate.

Everything argument driven and easily deployed by level 1 trained personnel with some minor additional OTJ training.

I have long advocated for better interaction of the DB Protocl with Stored Procedures. If I ever get my hands on a later (19.1 and beyond) release I will be able to tell if Cloverleaf is up to snuff there.

I have some rather specific demands for Stored Procedure interaction that Cloverleaf needs to support.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Rob Abbott]

Jim, Cloverleaf 6.2 supports stored procedures as part of the Database Protocol.  19.1.0 has more robust support with the following additions:

• support for output parameters
• support for return codes
• ability to execute stored procedure during translation (advanced lookup table)

19.1.1 also addressed a couple of bugs related to stored procedures.

What other requirements do you have?

Rob Abbott
Cloverleaf Emeritus

[Jim Kosloskey]

Rob,

What you relate regarding 19.1 and SP sounds encouraging.

Can we discuss this off-line?

Jim

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Author]

Replies