Oracle stored procedure to update trigger database

[Russ Ross]

Looks like I’m going to get my first chance to do an ODBC interface using Cloverleaf.

I hope it turns out well becuase it has been a tough sell for the first one since we don’t have a single working example or experience doing one.

I will be doing this for ADT demographics into the DMS (documentum) system should that be relevant in some way.

Or better yet, has anyone already done this exact integration and would be willing to talk to me about it.

At this point discusions have us headed towards using the ODBC capability of cloverleaf to call an Oracle stored procedure that takes a tab delimited record created by cloverleaf as an argument and updates an Oracle trigger database.

Jim Kosloskey and I discussed this with the DBA assigned to the project and the concept is not something they have done either.

The DBA said it would be very helpful if they could get an example of an Oracle stored procedure from someone who had done something similar.

I would apreciate if I could get some posts of Oracle stored proceedures that others have used to update an Oracle trigger database with the record sent to it by cloverleaf via ODBC.

Russ Ross
RussRoss318@gmail.com

Topic

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

[Russ Ross]

Felix:

Thanks so much for your generosity which will be very helpful in reducing my learning and development pain for the cloverleaf work.

Please consider emailing me ( rross@mdanderson.org ) with your email address and phone number.

Would it be possible for you to post the SQL stored procedure called insert_LabData that cloverleaf is calling?

This is what my DBA wants to take a look at to better understand the concept.

I think it will click for her if she could see that stored procedure.

Granted your stored procedure may not be for an Oracle DB but I think this DBA can make sense out of it enough to get the concept.

Until our DBA gets the concept she is wondering how to write the stored procedure that cloverleaf will be calling.

The way that Jim Kosloskey explained it to her is to write a stored procedure that takes the tab delimited record as an argument/parameter and returns a success or failed code.

I think when she sees an example she will go “Oh now I get it”.

The challenge with our DBA grasping the concept of cloverleaf/ODBC/stored-procedure is that it seems to be a new mind shift for her.

PS:  If anyone has an Oracle stored procedure for accepting a data record from cloverleaf via ODBC please consider sharing it with me.

Russ Ross
RussRoss318@gmail.com

[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

[Todd Horst]

Thanks for the code. The only difference between calling a SP and doing a regular query (at least how i was doing it anyway) is that you put the SP name and then space and then list all your params.

Thanks again

Quote:

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

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

                                        ‘$rRange’, ‘$aFlags’;”

[Garrett Fletcher]

Hi Felix,

I noticed you doing echo [odbc …]. Is this actually running a command? I thought echo only printed text to the screen.

Thanks,

Garrett

[Todd Horst]

anything between the brackets gets executed. The value returned from executing is echo’d

[Garrett Fletcher]

This is probably a dumb question, but is it executed by the tcl intrepreter or by the command prompt / shell?

[Todd Horst]

tcl

[Garrett Fletcher]

Thank you, Todd.

[Steve Robertson]

I don’t read posts very often. But here is my 2 cents …

You have gotten very good advice so far. In my shop, we also write data to Oracle via ODBC calling a stored procedure.

Let me mention that opening a new connection to Oracle for each write, then closing it, is by far the easiest way to go, since error handling is so easy. However, this is also very slow. If you have much volume, you’re going to be sunk. We route about 250,000 per day to our database, spread over 8 Cloverleaf sites, each site having it’s own DB connection.

Much faster to hold a persistent connection. But much harder.

If you are interested, let me know and I can e-mail you a tcl that has the code to hold a connection, retry for failures, etc. There are at least 2 specific Oracle error codes that you have to contend with properly.

Steve Robertson

[Author]

Replies