Writing to MSSQL Database – Best Practice Question

[Weston Olmstead]

I have a use-case where I am needing to pick a few fields out of an HL7 message and insert those fields into a MS-SQL Database. There is a  a stored procedure in the database that performs all necessary inserts / updates, all I need to do is pass the appropriate data to these stored procedures.

I’m curious to hear from others what the best way to do this would be? I’ve not used cloverleaf in a couple of years, and I’m now using 6.2 for this project.

Based on work I’ve done in the past, my initial reaction would be to write a TPS script to extract the data elements I’m looking for, and insert the directly via TCL but I’m not sure if that is the best way to do this or not. My primary concern with the TCL approach is exposing the Database credentials in the TCL Script.

I’ve also played around with the Database connector, but I’m unclear how to get values from the Message into the database connector (I assume I would just make an XLATE and store them in variables?).

Any information, especially reference material would be appreciated.

 

 

 

 

 

 

 

 

Topic

[Arie Klop]

We have a couple of threads writing to database, some via a stored procedure and some via db insert.

It works fine, but is kind of a memory hog. Performance is good though, with a couple of messages per second.

We got the recommendation to keep each database thread in it’s own process. I have multiple tcp threads writing to the same db thread, so we created a tcp thread with a db thread in one process and let the other threads provide the messages via tcp. (HL7)

The important thing to remember is that there is one java virtual machine per site (actually per translation thread).

Also don’t forget to load the right (recent) sqlsrv drivers.

We have xlates translating from hl7 to database schema, so create a connection via site preferences and import the schema. Then write the xlate.

I have a small tcl to replace , with ; before the xlate because , is the field separator and if there is a , in your content the db write will fail.

I am thinking of using a separate Java program or REST service to write to database with a connection pool because of the memory footprint.

[Weston Olmstead]

Thank you for the info. An additional question. If I attempt to use the built-in cloverleaf functions instead of writing a JDBC / TCL function how to extract a specific field out into a variable and pass it to the outbound thread. Does the Database connector understand variables?

[Jim Kosloskey]

When you say you have the Database connector do you mean the ODBC Drivers?

I have done what you describe using the ODBC Drivers. In that case, an Xlate builds message in the structure the Stored Procedure wants (VRL, HRL, HL/7, etc.) then a Tcl proc is used at the Thread level to use the ODBC Drivers to connect to the DB, invoke the Stored Procedure and interrogate the return value from the Stored Procedure for errors detected by the Stored Procedure.

I had one Tcl proc used by many DB connections driven by parameters to specify the DB characteristics and the Stored Procedure to be invoked.

That methodology was pretty fast (more than 2 messages per second as I recall) and did not seem to have an excessive memory footprint.

I have not yet used the DB protocol. My understanding is the current release (19.x or later) is improved for proper interaction with Stored Procedures.

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

[Weston Olmstead]

I’m using the built in connectors for Cloverleaf (database protocol-outbound). I figured out what I needed to do for my use case anyway (as I was trying to avoid using TCL if possible).

1. Create a new database driver & connection for MSSQL (Options –> Site Preferences –> Database Configurations)
2. Configure a database schema for a destination table (Configuration –> Database Schema Configurer)
3. Create an XLate file from HL7 to Database Schema and select the Schema from #2.
4. Map the HL7 fields to my schema fields & save the Xlate.
5. Create a new inbound TCP/IP thread and a Database outbound thread
6. Set up a route between the two threads with the Xlate from step #4.
7. Edit my outbound thread to use the approriate fields.

 

I was getting hung up on the last step, as I thought I needed to reference the specific fields from my xLate as values in the outbound, but the schema configurer takes care of that already. If I don’t use “whole message” as the input option, it will already know what I populated in the xLate and use that value.

 

Thanks so much for all the help!

[Author]

Replies