Dynamic HL7 with SQL Query

Hello, Jon

When performing message enrichment, there are several design decisions to be considered depending on your needs.

1. Do you want to perform the database query and HL7 message update within an Xlate (using the built-in Cloverleaf database protocols) or within a pre-Xlate tps? I have always favored using a TPS because it gives you more control and you can react / respond more appropriately to exceptions that may arise during the execution.

2. If TPS, what method do you use to access the database (ODBC, JDBC, tclODBC…)?

3. Depending on the volume of the integration, do you connect to / disconnect from the database for every HL7 message, or do you maintain a permanent connection or implement connection pooling?

4. If you are unable to connect to the database for a given HL7 message, how many retries do you want to perform? If still unsuccessful after all retries, do you continue the HL7 message unmodified or do you send it to the Cloverleaf error database for future handling? Or do you want to stop the Cloverleaf outbound thread until the connection issue is resolved?

5. If connection is successful, how do you search the database: simple SQL query, prepared statement, or stored procedure?

6. If the query returns no data, do you continue the HL7 message unmodified or do you send it to the Cloverleaf error database for reprocessing?

The last time we had to do similar integration, we went a different route. For speed, performance, and ease, we found it better to run the HL7 through Cloverleaf then we used the Perl DBI to access the database and pull the desired data to add to the HL7. It is a very fast and robust module.

I hope this helps.

I recommend not using database query processing inside an xlate. Except for the new 6.1.1 functionality of performing a simple table lookup inside an Xlate action, I don’t think that odbc error handling is consistently reliable. It has frozen our threads more than once. We have pulled all odbc calls out of translates.

Using a database protocol covers many of the issues Levy mentioned. However if the destination thread is the ancillary, then you will have to perform your database query somewhere in between. I recommend it be in a tcl proc in the outbound thread, unless you really need the data to be updated before the translate. Then that leaves either the inbound proc or a pre-translate proc, and there I would recommend the pre-translate proc.

I also recommend that you put in an enhancement request to allow the OVER disposition in the Send-OK or Send-Fail proc, which would go a long way towards getting a workflow that gives you a protocol thread dedicated to your database work, followed by a protocol thread to deliver the message to the ancillary. It is true that there is still work to be done to allow a database protocol to return a result that could then be worked (to update the message) before the message is sent to the next thread.

Many people including me recommend using a stored procedure instead of a straight query, whereever possible, for any database access.