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.
.
