Polling a database checking for flag fields to send out data

Assume you have an existing HIS system with data stored in its on table schema, and you need to generate messages based off that schema. There is no interface system provided, so you need to query the database directly.

Before You Start

This example assumes that a couple of tables exist:

• A Patient table populated by the existing HIS, which contains the patient demographic information. It has an Admit Date column that is used to determine when to send out messages.
• A patient_polling_table, which contains a single value called PollTime, which will record the last time a poll was done for messages.

How It Works

You will set use a channel with ‘From’ and ‘To Translator’ components:

1. Use the live=true setting to get the ‘From Translator’ component working
   • When the ‘From Translator’ component once it is working you can set live=false (to prevent accidental updates)
2. Run the channel to push the messages to the queue and the logs
   • Sample messages are not needed for the ‘From Translator’ as it reads from a database
   • You can change the time in patient_polling_table to force re-processing of patients
3. Open the ‘To Translator’ component for the channel and import sample data from the logs
   • Note: You could just add some sample IDs manually
4. Use the live=true setting to get the ‘To Translator’ component working
5. When both components are working you just run the channel and the ‘From Translator’ will process the queued messages

Source: From Translator

First, set up Iguana to have a From Translator->To Translator channel. The ‘From Translator’ script will check when the database was last polled, and identify any admissions that occurred after that time. It then pushes the ID for each row to Iguana’s queuing system.

In this example ID “4525285” was admitted after the last time the database was polled:

The code, for reference:

conn = db.connect{
   api=db.MY_SQL, 
   name='test',
   user='root', 
   password='password',
   live=true
}

-- Here we get multiple patient ids from a table, based on a AdmitDate column
-- looking for rows that are ready to be processed
function main()
   local PollTimeRs = conn:query('SELECT PollTime FROM patient_polling_table;')

   if #PollTimeRs == 1 then
      local PollTime = PollTimeRs[1].PollTime;
      --now select for all patient ids from the last time we polled
      local SelectSql = "SELECT Id from Patient WHERE AdmitDate>'"..PollTime.."' AND  AdmitDate<=NOW();"
      local IdRs = conn:query(SelectSql)
      -- we push in the patient id into the queue. The To Translator component
      -- will generate the messages needed
      for i = 1,#IdRs do
         queue.push{data=IdRs[i].Id:nodeValue()}
      end
      -- once added the the queue, update the polltime
      conn:execute{sql='UPDATE patient_polling_table SET PollTime=NOW();',live=true}
   end
end

We set the conn:execute() second param to true to execute SQL interactively in the Iguana editor. The interactive option does not push items onto the queue. To test the ‘To Translator’ component you need to import sample data from the logs (details at the bottom of the page).

Destination: To Translator

By storing patient ids in Iguana’s queue system, we can import the ids as sample messages while developing the ‘To Translator’ component.

The script does three things:

1. SELECTs the corresponding row from the Patient table (using the Id from the queue)
2. Maps the patient data into HL7 message node tree
3. Passes the message on for further processing, e.g., write it to a file, database, etc

The code for generating messages in the ‘To Translator’ component is as follows:

Here is the example_demo.vmd and the code:

conn = db.connect{
   api=db.MY_SQL, name='test',
   user='root', 
   password='password',
   live=true
}

function main(Data)
   --remove any newlines that are present
   Data = string.removeNL(Data)
   --select for data
   local RS = conn:query(
      "SELECT * FROM Patient WHERE Id='"..Data.."'"
   )
   if #RS > 0 then
      --load the vmd file containing the message definition 
      local msg = hl7.message{
         vmd='example/demo.vmd',name='ADT'
      }

      -- now map what we want and generate the message
      mapPatient(msg,RS)

      -- now we have the message, at this point we can 
      -- write it to a file put it into a db, etc
      msg:S()
   end
end

function mapPatient(msg,RS)
      -- TODO map other segments
      msg.PID[2][1]=RS[1].Id
      msg.PID[5][1][1][1] = RS[1].LastName
      msg.PID[5][1][2] = RS[1].GivenName
      msg.PID[19] = RS[1].Ssn
      msg.PID[10][1][1] = RS[1].Race
      msg.PID[13][1][1] = RS[1].PhoneHome
      msg.PV2[8] = RS[1].AdmitDate
end

-- you could put this function in a string module
function string.removeNL(S)
   if S:byte(#S-1) == 13 and S:byte(#S) == 10 then
      return S:sub(1, #S-2)
   end
   return S
end

What’s Next?

As you can see, this is a straightforward method for generating a message from an existing database table schema. You can adapt this code to your needs, by saving the message data to a file, database, or transmitting it to another channel, etc.

You may be interested in the following sections in the wiki:

• Mapping To/From Databases
• Working with HL7
• File and FTP Interfaces
• Web Services, XML, JSON and Cloud

Please contact support at support@interfaceware.com if you need more help.