[Felix Ortiz]

This is the stored procedure that is called.

CREATE PROCEDURE dbo.insert_LabData(

   @TDATE datetime,

   @TTIME datetime,

   @MR char(12),

   @HI char(12),

   @SN char(22),

   @LD datetime,

   @LT datetime,

   @LType char(40),

   @R char(255),

   @U char(15),

   @RR char(40),

   @AF char(20)

   )

AS

   BEGIN

       INSERT INTO dbo.LabData

   ( TranDate, TranTime, MedRecID, HospitalID, SessionNum, LabDate, LabTime, LabType, Result, Units, ReferenceRange, AbnormalFlags)

VALUES

   ( @TDATE, @TTIME, @MR,@HI,@SN,@LD,@LT,@LType, @R,@U,@RR,@AF)

   END

I sent you a copy and some other notes

[Felix Ortiz]

I receive clinic lab results (HL7)  and using a stored procedure write them to a SQL server DB.

I think stored procedures the best and most efficiant way to go.  I am using QDX5.4 and the Quovadx provided Connect 5.0 ODBC driver.  I use the ODBC connection with many standalone Tcl scripts, this is the only one I am running directly within the Interface Engine.

Here is the rough outline copied from a tps proc.

   switch -exact — $mode {

       start {

           # Perform special init functions

   # N.B.: there may or may not be a MSGID key in args

           package require odbc 7

       }

       run {

   # ‘run’ mode always has a MSGID; fetch and process it

           keylget args MSGID mh

           lappend dispList “CONTINUE $mh”

           echo “Entering tps_odbc_neodata”            

           echo [ odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv ]

           echo [ odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0 ]

           echo [ odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc ]

           echo [ odbc SQLConnect $hdbc EOPC_Lab SQL_NTS USERNAME SQL_NTS PASSWORD SQL_NTS  ]

           echo [ odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt ]

       

           set msg [msgget $mh]

           puts stdout “msg: $msg”

           set fieldList [split $msg |]

           echo $fieldList

           set tDate [ lindex $fieldList 0 ]

           set tTime [ lindex $fieldList 1 ]

           set MRN [ lindex $fieldList 2 ]

           set visit [ lindex $fieldList 3 ]

           set sesNum [ lindex $fieldList 4 ]

           set lDate [ lindex $fieldList 5 ]

           set lTime [ lindex $fieldList 6 ]

           set lType [ lindex $fieldList 7 ]

           set result [ lindex $fieldList 8 ]

           set units [ lindex $fieldList 9 ]

           set rRange [ lindex $fieldList 10 ]

           set aFlags [ lindex $fieldList 11 ]

           #set comments [ lindex $fieldList 12 ]

           set SQLTEXT ” insert_LabData  ‘$tDate’, ‘$tTime’, ‘$MRN’, ‘$visit’, ‘$sesNum’,

                                         ‘$lDate’, ‘$lTime’, ‘$lType’, ‘$result’, ‘$units’,

                                         ‘$rRange’, ‘$aFlags’;”

           set flag [ odbc SQLExecDirect $hstmt $SQLTEXT SQL_NTS ]

           echo $flag

           if {$flag != “SQL_SUCCESS” & $flag != “SQL_SUCCESS_WITH_INFO”} {

             odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 511 TextLength

             echo $MessageText

             echo “Failed!!!!!!!!”

           }

           echo $SQLTEXT

           odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt

           odbc SQLDisconnect $hdbc

           odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc

           odbc SQLFreeHandle SQL_HANDLE_ENV $henv        }

       time {

           # Timer-based processing

   # N.B.: there may or may not be a MSGID key in args

       }

       

       shutdown {

   # Doing some clean-up work

}

   }

I specifically open  new connection for each HL7 message parsed.  My volume is small enough that it doesn’t matter.  If the volume warrants you may consider opening the connection, setting up the handles in the startup.  If you do, you will need checking in the run{} to verify the connection is still open and responsive.

This is in production, but it was my first one and I see lots of opportunities for improvement.

[Felix Ortiz]

Sorry for the extra info.  We did upgrade from Connect 4.2 to 5.0.   5.0 was the first version supporting DB2 V8, according to DataDirect documentations I found.

We are not ready to move to 5.5 yet.

Just spoke to Toni.  She is sending me the 5.5 media.  I am going to load Connect 5.2 and test with it.

[Felix Ortiz]

Host Server: clprod/10.107.9.186

Current Platform:

Java version: 1.4.2

Java vendor: IBM Corporation

OS type: AIX

OS version: 5.3

OS arch: ppc

Server Build Information:

Version: 5.4P

Date: Mon Nov 14 2005

Time: 10:28:47

Platform: Windows_NT

Java Vendor: Sun Microsystems Inc.

JDK Version: 1.4

Swing Version: 1.4

RMI Version: 1.4

DataDirect Technologies

DataDirect Connect for ODBC on UNIX

Edition 5.0

May 2004

[Felix Ortiz]

When i try to download the patch I am presented with a login page.  i have left a message with Toni Star (sp?)

[Felix Ortiz]

[To the Cloverkind] Do I need to contact my Rep to discuss a ODBC patch?

[Felix Ortiz]

These are the only setting he found:

Idle Query:  15 minutes

[Felix Ortiz]

I found this on the DataDirect Website

…

P1 defect DEF0001864 was filed to development and a patch was made to the 5.0 DB2 ODBC driver to correct the issue.  The fix is evident in driver versions 5.00.0142 (B0065, U0037) and above.

…

http://knowledgebase.datadirect.com/kbase.nsf/ce24d9443437511f85256e57006bfe2a/42828981a74db7528525701c00699d0a?OpenDocument&Highlight=0,%5Bdatadirect%5D%5Bodbc,db2,wire,protocol,driver%5Dsocket,closed

Using their binary my version of the DB2 driver came back

$ bin/ivtestlib lib/CVdb220.so

Load of lib/CVdb220.so successful, qehandle is 0x3

File version: 05.00.0102 (B0048, U0031)

There is a link on the page to a patch, but it times out.  Will DataDirect support help me or does everything have to go through Quovadx?

[Felix Ortiz]

SQL_ERROR

[DataDirect][ODBC DB2 Wire Protocol driver]Socket closed.

This is the error I am getting after 662 rows have been fetched.  I am beginning to wonder if it is the something in the DSN rather than the Column bindings.

[Felix Ortiz]

I used this statement at first

for { set x 1 } { $x < 18 } { incr x } {        odbc SQLBindCol $hstmt $x SQL_C_CHAR fld($x) 100 pcbValue$x } but after the problems starte occurring I started using individual calls for each column. odbc SQLBindCol $hstmt 1 SQL_C_CHAR fld(1) 1000 pcbValue1 odbc SQLBindCol $hstmt 2 SQL_DECIMAL fld(2) 1000 pcbValue2 odbc SQLBindCol $hstmt 3 SQL_C_CHAR fld(3) 1000 pcbValue3 …..

[Felix Ortiz]

I have since read more of the posts.  

I like the idea of reading the file into memory at startup.  That way I don’t have the overhead of opening an closing the database for each message.  Is it safe?

I also like the idea of using the modification date of the file.  Do you think it could be used to avoid starting and stopping the threads, when updating the database?  

Scenario: I overwrite/update the database file, the mod date changes, all threads would check the mod date of the file notice it is different from the date it has stored in the global when the thread started and reload the table.

I wonder if five threads tried to reload the table at the same time if there would be an issue.

I will have a lot of testing ahead, I appreciate any and all of the suggestions you have made so far.  Thanks again for your help.

[Felix Ortiz]

We are designing interfaces to an EMR.  The EMR will be populated from various sources, but the common filter will be whether or not the doc has signed up.

I am planning a small tcl that will pull the doctor numbers from the various HL7 messages and look into a SQLite table to decide whether to kill or continue the message to the EMR.

1) This Tcl will be called from multiple threads, processes and sites.  Will I be affected by locking if I use a single file? The calls will all be reads.

2) The table would be rebuilt daily from a system generated flat file, should I create multiple copies, one per site or thread?  

3) Will I have to shutdown all threads using the script when I update the table?

4) There will literally be tens of thousands of messages checked against this filter daily.  What about the speed and reliability of SQLite in your experience?

I am brainstorming the idea, so suggestions or comments are welcome.  Thanks.

[Felix Ortiz]

After you change the INI files, bounce the hostserver.  😈

[Felix Ortiz]

I thinkit may have to do with the library lokking to the Connect4.2 directory.

for /integrator/qdx5.4/integrator/tcl/lib/odbc/../libODBC7.so because:

0509-136   Symbol __dl__FPv (number 0) is not exported from

 dependent module /integrator/qdx5.4/integrator/lib/Connect4.2/lib/libodbc.a(odbc.so).

I have changed the references everywhere I have found one from Connect 4.2 to Connect 5.0.  this includes:

odbc.ini and odbcInfo in the $HCIROOT/lib directory

[Felix Ortiz]

I agree, but until devices become MPI aware any data they send must be tied to something.

Of course, one day the device might be attached, and MPI # of some type input into the system.  Clinical people love data entry! and they are really accruate too. 😀  😀  😀

We are looking into 3rd party solutions.

[Author]

Replies