Using ODBC and creating an HL7 message

[Paul Johnston]

Hello All,

I am in the process of learning how to connect to an MS SQL Db using the Data Direct ODBC Drivers. I am very new to this type of Interface processing.

Own main objective is to use provided STORED Procedures to capture data from the SQL Database and then create an HL7 message from the data and send it to the destintation system

I have viewed many threads in Clovertech but all seem to be related to updating an SQL database.

If anyone has any TCL examples which Connects to a SQL Db then writes out a message I would be very interested.

Thank you

Topic

[Peter Heggie]

We use this code in production. An inbound thread is configured with protocol fileset-local; in the protocol properties, this tcl is specified for the DirectoryParse. The Deletion UPOC runs another custom tcl which skips the delete of the input file. Basically the file is never deleted and continues to trigger the DirectoryParse tcl.

This DirectoryParse tcl is triggered every 10 minutes (by setting the Scan Interval). It reads the file, checks that it is indeed the trigger file, and then ignores the file and its contents and continues with the ODBC logic.

This code expects that the entire HL7 message is stored in MS SQL Server and is obtained using a stored procedure. You could easily run some other stored procedure that extracts discrete data elements, and then uses those to build a message using string concatenation and list processing. You could put this logic in another proc and call it from within the while-loop.

We needed to perform the stored procedure at this UPOC so we could then place that HL7 message into the Inbound message flow and have it continue on normally through filters and translates and route to a destination.

Code:

######################################################################
# Name:       tpsSqlGetMsg
# Purpose:    Executes a stored procedure that will return one or more
#             messages (string version of HL7 message).
#             For each string message, will convert it to proper HL7
#             format (newlines) and Continue the message.
#
# UPoCtype:   tps
# Args:    tps keyedlist of MODE, MSGID, ARGS
#
# Returns: tps disposition list:
#          Returns a list of messages to be forwarded to Routing phase.
#
#
##### ————————————————————####
#  date
#  2013/06/13 – PCH – created
# —————————————————————–###

proc  tpsSqlGetMsg { args } {

   keylget args MODE mode               ;# Fetch mode
   set dispList {}

   set module “tpsSqlGetMsg:”
   set debug 1
   if {$debug} {echo “$module debug enabled – mode $mode” }

   switch -exact — $mode {
     start  {
       return {}
     }

     run {  
       keylget args MSGID mh
       set msg [msgget $mh]

       # if this is not a trigger/dummy message, continue it and exit
       set trigger_pos [string first “trigger” $msg]
       if {$trigger_pos eq -1 || $trigger_pos > 100} {
           if {$debug > 1} {echo “$module continued non-trigger msg” }
           if {$debug} {echo “$module end”}
           lappend dispList “CONTINUE $mh”
           return $dispList
       }

       # get messages from SQL Server

       # get ODBC connection parameters from common table
       set tablename “SQLwrite_args”

       set user [tbllookup $tablename “db_userid”]
       set pass [tbllookup $tablename “db_passwd”]
       set address [tbllookup $tablename “db_address”]
       set database [tbllookup $tablename “db_name”]
       set timeoutC [tbllookup $tablename “db_connect_timeout_seconds”]
       set timeoutQ [tbllookup $tablename “db_query_timeout_seconds”]

       set table “vbed”

       # all odbc code requires access to the odbc.ini file; however, this code
       # will override the address, database, userid and password values in the file.
       set connstr “DSN=SQL Server Wire Protocol;ADDRESS=$address;UID=$user;PWD=$pass;DB=$database”
       set lconnstr [string length $connstr]

       package require odbc

       # initialize ODBC environment
       set result [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]
       if {$debug > 1} {echo “$module SQLAllocHandle ODBC result: $result”}
       if {$result eq “SQL_ERROR”} {
           msgmetaset $mh USERDATA “$module SQLAllocHandle (ENV) ODBC result: $result – check ODBC installation”
           set dispList {} ; lappend dispList “ERROR $mh” ; return $dispList
       }

       # Set the most current ODBC version (3.0)
       set result [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]
       if {$debug > 1} {echo “$module SQLSetEnvAttr result: $result”}
       if {$result eq “SQL_ERROR”} {
           catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}
           msgmetaset $mh USERDATA “$module SQLSetEnvAttr ODBC result: $result – check ODBC installation”
           set dispList {} ; lappend dispList “ERROR $mh” ; return $dispList
       }

       # Allocate connection handle
       set result [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]
       if {$debug > 1} {echo “$module SQLAllocHandle CONN result: $result”}
       if {$result eq “SQL_ERROR”} {
           catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}
           msgmetaset $mh USERDATA “$module SQLAllocHandle (DBC) ODBC result: $result – check ODBC installation”
           set dispList {} ; lappend dispList “ERROR $mh” ; return $dispList
       }

       # Set connection timeout
       set result [odbc SQLSetConnectAttr $hdbc SQL_ATTR_LOGIN_TIMEOUT $timeoutC 5]
       if {$debug > 1} {echo “$module SQLSetConnectAttr Login Timeout result: $result”}
       if {$result eq “SQL_ERROR”} {
           catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}
           catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}
           msgmetaset $mh USERDATA “$module SQLSetConnectAttr (TIMEOUT LOGIN) ODBC result: $result – check ODBC installation”
           set dispList {} ; lappend dispList “ERROR $mh” ; return $dispList
       }

       # Make a connection
       set result [odbc SQLDriverConnect $hdbc NULL $connstr $lconnstr NULL NULL NULL SQL_DRIVER_NOPROMPT]
       if {$debug > 1} {echo “$module SQLConnect result: $result”}
       if {$result eq “SQL_ERROR”} {
           set retcode [odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SQLState NativeError MessageText 1024 TextLength]
           if {$debug 1} {echo “$module SQLAllocHandle result: $result”}
       if {$result eq “SQL_ERROR”} {
           catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}
           catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}
           msgmetaset $mh USERDATA “$module SQLAllocHandle (STMT) ODBC result: $result – check SQL Server”
           set dispList {} ; lappend dispList “ERROR $mh” ; return $dispList
       }

       # set timeout
       set result [odbc SQLSetStmtAttr $hstmt SQL_ATTR_QUERY_TIMEOUT $timeoutQ 1]
       if {$debug > 1} {echo “$module SQLSetStmtAttr QueryTimeout result: $result”}

       # Setup SP Call variable
       set SQLCmd “exec $database.dbo.prVirtualBedGetMsg”
       if {$debug > 2} {echo “$module SQLCmd: $SQLCmd”}

       set RCode “0”
       set RDesc “0”

       # Execute prepared statement
       set result [odbc SQLExecDirect $hstmt $SQLCmd SQL_NTS]
       if {$debug > 1} {echo “$module SQLExecDirect result: $result”}
       if {$result ne “SQL_SUCCESS”} {
           set retcode [odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SQLState NativeError MessageText 511 TextLength]
           echo “$module SP executed – result: $result”
           echo “$module sqlstate: $SQLState error: $NativeError text: $MessageText”
           msgmetaset $mh USERDATA “$module SQLExecute ODBC result: $result – check SQL Server available or SP parms changed or table def changed”
           set dispList {} ; lappend dispList “ERROR $mh”
       }

       # assign a variable to a result set column
       set result [odbc SQLBindCol $hstmt 1 SQL_C_CHAR Msg 512 Msg_len]
       if {$debug > 1} {echo “$module SQLBindCol result: $result”}
       if {$result eq “SQL_ERROR”} {
           catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}
           catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}
           msgmetaset $mh USERDATA “$module SQLAllocHandle (STMT) ODBC result: $result – check SQL Server”
           set dispList {} ; lappend dispList “ERROR $mh” ; return $dispList
       }
       
       # loop through result set
       set msgList {}
       set msgCt 0
       set Msg “”

       set result [odbc SQLFetch $hstmt]
       if {$debug > 1} {puts “$module SQLFetch result: $result”}
       if {$result eq “SQL_ERROR”} {
           catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}
           catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}
           msgmetaset $mh USERDATA “$module SQLAllocHandle (STMT) ODBC result: $result – check SQL Server”
           set dispList {} ; lappend dispList “ERROR $mh” ; return $dispList
       }

       while {$result == “SQL_SUCCESS” || $result == “SQL_SUCCESS_WITH_INFO”} {
           set mh [msgcreate]
           msgset $mh $Msg
           lappend dispList “CONTINUE $mh”
           set result [odbc SQLFetch $hstmt]
           if {$debug > 1} {puts “$module SQLFetch msg: $Msg”}
           incr msgCt  
       }

       if {$debug} {puts “$module database results – $msgCt rows returned”}

       # clean up SQL and exit tcl
       catch {odbc SQLFreeStmt $hstmt SQL_DROP}
       catch {odbc SQLDisconnect $hdbc}
       catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}
       catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}

       if {$debug} {puts “$module end”}
       return $dispList        
    }

    time {
           # Timer-based processing
   # N.B.: there may or may not be a MSGID key in args
    }

    shutdown {
   # Doing some clean-up work
    }

    default {
   error “Unknown mode ‘$mode’ in tpsDirParseSkipDel”
    }
 }
}

hth

Peter Heggie

[Max Drown (Infor)]

Paul, what are your plans to migrate to CIS 6.0.1? The latest version of Cloverleaf provides direct connections to and from databases using built-in JDBC drivers. It’s pretty slick and relatively easy to use especially with store procedures.

-- Max Drown (Infor)

[David Harrison]

This is the first I’ve heard about Cloverleaf 6.0.1. I’m on v6.0.

[Paul Johnston]

Thanks Max ,

As much as we would like to upgrade soon we are now committed to using the ODBC Connect drivers. Unfortunately, looks like it may be next year before we upgrade.  🙁

[Author]

Replies