Newsletter: February 2006

Newsletter - February 2006

Project Corner - Database Access in Python Scripting

You might think that putting database logic into a Python script in your Chameleon VMD is complicated but it can be quite easy.

Suppose you have HL7 order messages and patient information ADT messages to send to a lab and there is a rule from the lab that they don't want ADT messages unless an order has been sent to them first. You have to have a simple database table that tracks order IDs (OID) and patient IDs (PID) for this. Each time an order is sent to the lab a row is added to the table with the OID and the PID. When you have ADT messages, you have to check for a PID match in this table to know whether you can send the ADT to the lab.

Here is what you might do.

First, create a database table for your Iguana channel's database.

CREATE TABLE OIDPID(OID? VARCHAR(255),PID VARCHAR(255),
                    MessageID VARCHAR(255),PRIMARY KEY (OID?))

The code to execute when you see an order might look like:

   if iterator.segment_id() == 'ORC':
      # add record to OIDPID table
      # OID from Placer Order Number 2
      OID = iterator.field(2).value
      # OID, PID, MessageID, OrderDate
      # check if record already exists - overwrite if there
      selectSQL = "select OID from OIDPID where OID = '" 
                  + str(OID) + "'"
      dbResultSet = db.execute_select(selectSQL)
      if ( (dbResultSet.count_of_row() > 0) ):  # overwrite
           insertSQL = "update OIDPID set PID = '" + str(PID) 
	               + "', MessageID = '" + str(MessageID) 
                       + "' where OID = '" + str(OID) + "'"
           db.execute(insertSQL)
      # not there then add
      else:
           insertSQL = "insert into OIDPID values ('"  
	               + str(OID) + "', '" + str(PID) 
                       + "', '" + str(MessageID) + "')"
           db.execute(insertSQL)

The above is for passthru mapping. In graphical mapping you would use the "In Equation" or "Out Equation" for the ORC segment and the ORC.2 Placer Order Number field. The above assumes a unique ID MessageID is available too.

When you have an ADT to send you can use the script below in passthru mode. It's more complicated when you do this outside of passthru but can be done in Chameleon too. The code below decides whether it's valid to send the ADT or not and either does it or ignores the message.

iterator = environment.input_segment_iterator()
db = environment.get_default_database()
ADTout = 0

while iterator.move_one() and (not ADTout):
   if iterator.segment_id() == 'PID':
      PID = iterator.field(2).value   # PID 2
      selectSQL = "select OID from OIDPID where PID = '" 
                  + PID + "'"
      dbResultSet = db.execute_select(selectSQL)
      if ( (dbResultSet.count_of_row() > 0) ):  # got orders so 
                                                # ok to write
                                                # ADT out
           ADTout = 1
if ADTout == 1:
   iterator.reset()  # start process over and build output only 
                     # if order seen
   init_MSH()

   # output the MSH
   output = iterator.output()

   while iterator.move_one():
      output = output + iterator.output()

   value = output
else:
   value = "" # order not seen so no ADT info to send