Database Protocol Help

[Brad Cook]

Hello – what an amazing community this is, preface with a big thanks to all that have contributed to help and assist 🙂

I have been tinkering with a design issue and I could use a sounding board to see if I am on the right track.

integration model: Get a value from database in transit of hl7 route

hl7 inbound -> call stored procedure to get a value -> send hl7 oubound.

Summary:

I have an HL7 MFN event that I need to call a stored procedure in order to get a value before I send the HL7 outbound. I have an inbound HL7 tcp/ip thread that uses an Xlate to map the HL7 inbound to outbound.

Right now I have a java JAR that is being used in a pre-proc (which is working) but the boss wants to use the native database threads within Cloverleaf. I am not sure if I can even do this – is it possible?

I have setup a database-outbound thread with the stored procedure ({call IMR.PROVIDER.prPhyndAddExternalProviders(inValue1, inValue2, inValue3, inValue4, outPara1 OUT)} ) my issue is I cannot get the value back. Do I need a TCL on the TPS Inbound Reply piece? How do I value the parameters in the stored procedure? Is this only done via an Xlate?

I found some good examples of inbound and outbound database threads, but all of them are very straight forward, use inbound to VRL and Xlate out or use VRL to Xlate into a database. Nothing with a stored procedure.

I am using the ms sql server database connection

Topic

[Jim Kosloskey]

I don’t have an answer but knowing what release of Cloverleaf you are on could help.

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

[Brad Cook]

Cloverleaf Version: 6.1.2

OS: AIX 7.1

JDBC: MSSQL Driver

[Jim Kosloskey]

I do not use the DB threads but is it possible that the Stored Procedure must return the data as a RecordSet when invoked via the CL DB threads?

I think you are expecting the SP to return the response via a parameter – is that correct?

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

[Brad Cook]

Yup – that is what I was hoping for with the “OUT” parameter “outPara1 OUT)}”. The problem I have is that I am trying to get the value and route to another thread. Would this have to be done via an “OVER” or something?

Basically I am looking to make this SP call mid-flight between an inbound HL7 thread and outbound thread – getting the value to send on the outbound.

I am not sure if it is even possible using the database thread or if I need to use a tcl call within my Xlate. I have included a doc with a rough idea for the route. I was thinking I could use a tcl prc for the inbound tps reply to get the value and send along.

[Jim Kosloskey]

I am not sure the database thread returns the outbound parameter to you. Perhaps it expects only query results in a RecordSet and that then becomes a message.

The issue as I see it is you might have lost the original HL/7 message to which you want to append the result (if you can get it). I suppose you could send the original message as a parameter to the SP then ask it to put that in a column of the RecordSet returned (along with the query result in another column or columns) but then you will need Tcl to stuff the data from the message column in the message and the query result columns in USERDATA Metadata field of the message. Then some Tcl code inside the Xlate to get the Metadata info and place it in the message. Or there may be other mechanisms – pretty messy to me it seems.

In any case, I don’t have an environment to play around with so this is probably as far as I can take this theory.

I would expect the best place to query the DB in flight is either pre Xlate as you have already done or inside the Xlate – in both cases utilizing Tcl not a thread.

I would hope a future release of Cloverleaf will have DB Actions inside the Xlate.

I do have a concern regarding DB threads and invoking SP and it has to do with communicating back to Cloverleaf that the SP has an issue while trying to do its work – and in a fashion lending itself to externalizing the problem to those who would need to support  as well as what to do when such an issue occurs while a message is in flight.

If you would like to talk about some of those philosophies or more about the what ifs (understanding I am limited by not having a Cloverleaf environment), email me and I will be happy to chat.

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

[Brent Fenderson]

Brad, I think you can use Advanced Data Base lookup to call a stored procedure.

[Brad Cook]

Thanks – let me pull back a little so I can get a basic understanding of some things.

If I have an inbound hl7 thread and an database-outbound thread using a stored procedure – how do I pass the parameters defined in the database-outbound protocol properties? Do I need to put the actual hl7 coordinates?

Here is what I have {call IMR.PROVIDER.prPhyndAddExternalProviders(inValue1, inValue2, inValue3, inValue4, outPara1 OUT)}

Do I need to

{call IMR.PROVIDER.prPhyndAddExternalProviders( 4(0).2(0).MFN(0).#20(0), 4(0).2(0).MFN(0).#20(0), 4(0).2(0).MFN(0).#20(0), outPara1 OUT)}

[Brad Cook]

Thanks Brent – I just found the help on advanced DB lookup – reviewing

[Brad Cook]

It appears that stored procedures are not supported with advanced database lookup – has someone used a SP with this before?

Code:

MESSAGE 1
[0:TEST] Invalid sql statement: ‘{call PROVIDER.prPhyndAddExternalProviders[(?,?,?,?, OUT)]}’.
[0:TEST] Only SELECT statement is allowed in DBLookup

[Brent Fenderson]

Yea, I tried it too Sorry to add to the confusion. If you could create a view based on the logic in your stored procedure then it would work. Your other option would be to treat your sp result as an inbound reply(Supported in 6.1.2)

[Jim Kosloskey]

I think you still have the challenge of getting access to the original HL/7 message you wanted to enrich when you retrieve the DB data using the DB thread protocol.

It is doable but I think it could get quite messy.

Your original solution via a Tps proc (or inside an Xlate) is the way to go in my opinion. Still need some code but I think potentially much cleaner.

At this point Cloverleaf does not provide what your manager wants as a configurable tool.

Hopefully a future release will.

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

[Charlie Bursell]

Which DBMS?  Have you thought about using ODBC?

[Brad Cook]

Thanks all – I really appreciate the insight and guidance. I actually have TCL training next week, so an Infor person who can hopefully give me some input will be onsite. Let me respond back to what I find out.

[Peter Heggie]

As Charlie indicated, ODBC can do what you want. If you are on Windows, it is free; on UNIX, it is a purchased add-on utility to Cloverleaf.

With ODBC, you can call a tcl proc at any point, which would use the ODBC API to invoke the stored procedure and get the output parameter value. I recommend calling this kind of function from an inbound, outbound, or pre- or post-translate tcl point. I would not call it in a translate action because I am unsure that if an error occurred in the SP call, that the translator would correctly catch the error and gracefully complete processing. I only say that because we had a significant issue with an ODBC call in a translator tcl.

Cloverleaf is pretty much geared towards invoking protocols at the beginning and/or end of an interface, not in an intermediate node. I understand that you wanted to use a database protocol to get your value and then do an ‘OVER” to continue the message to an outbound thread, but as Jim has pointed out, that is messy.

We have implemented the ODBC approach to invoke stored procedures that return values in an output parameter, and it works well; we use it in several interfaces where we need to perform complex logic involving SQL stored procedures.

Infor continues to enhance the database access in the base product, in both the table lookup and the database protocols, so I am hopeful that in a near-future release we will see something for your use case.

I am intrigued by the suggestion about using a view. I’m not that familiar with all the functionality of a view, but if a view can actually invoke logic like an SP, then maybe you could use the table DB Lookup, with a SELECT action, passing it your input parameters and having the view do its thing to give you back – as a RETURN value (not an output parameter) – the data you are looking for. I hope someone can comment on this possibility.

Peter

Peter Heggie

[Author]

Replies