Polling a database, and generating CSV files

This page shows you how to create a simple “From Database to CSV file” channel. These same ideas can be applied to create a “From Database to Anything” channel, or a “From Anything to CSV file” channel.

Before You Start

We are going to follow the structure recommended in the parent page, Efficient Database Access. Basically, this means we will do as little work as possible in the From Translator component, and do most of the work in a Filter Translator. This approach leverages the power and convenience of the Iguana queue.

There are various advantages to doing things this way:

• The From Translator code is very simple, just SELECT the unprocessed record IDs and push them into the Iguana queue
• Once the IDs have been queued it is very easy to import them from the log into the Filter to use as sample data
• Once you have sample data it is easy to generate the full messages in the filter script (using the sample IDs)
• You can also easily test your code against many messages, by quickly moving through the sample IDs

How It Works

First you create a channel in Iguana with a “From Translator” source, and a “To File” destination.

This channel will also use a Translator in its Filter, so when I initialize the channel’s properties, I check the “Use filter” checkbox in the Filter tab (making sure to leave the other Filter settings as their default values).

Source: From Translator

First things first: You start by writing a simple database polling function. All the script needs to do is poll the database for rows that have not been processed (determined by a NULL value in the Status column). It then pushes the ID for each row to Iguana’s queuing system.

In this example we are pushing the ID “MM241” onto the queue:

Here is the entire script:

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

-- The main function is the first function called from Iguana.
function main()
   local RS = conn:query('SELECT Id FROM patient WHERE Status IS NULL')

   for i = 1, #RS do
      PushId(RS[i].Id:nodeValue())
   end
end

function PushId(Id)
   queue.push(Id)
end

We set live = true to execute SQL interactively in the Iguana script editor (we do the same in filter script below).

Filter: Translator

The filter script does three things:

1. SELECTs the corresponding row from the Patient table (using the Id from the queue)
2. Creates the CSV data by concatenating the fields in the row (excluding the Status field)
3. Pushes the CSV data onto the queue

The script is as follows:

require 'csv'

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

function main(Data)
   -- Get all patient fields
   local RS = conn:query("SELECT * FROM patient WHERE Id='"..Data.."'")

   -- Generate CSV content. Here we will include every field
   -- other than the Status column.
   local Patient = RS[1]
   local CSV = ''
   for i=1,#Patient do
      if Patient[i]:nodeName() ~= 'Status' then
         if i > 1 then CSV = CSV..',' end
         CSV = CSV..csv.escape(Patient[i])
      end
   end

   -- Mark the row as "processed"
   conn:execute{
      sql = "UPDATE patient SET Status=NULL WHERE Id='"..Data.."'",
      live = true
      }

   -- Send the CSV content out
   queue.push{data=CSV}
   return CSV
end

You need to escape the CSV data as follows:

• Escape any double quotes inside a string, i.e., ‘xx”x’ becomes ‘xx””x’
• Wrap strings in double quotes, i.e., ‘xxx’ becomes ‘”xxx”‘

Use this Lua simple module to do the escaping:

csv = {}

-- Pad the value with double quotes (if the value is a string),
-- and escape any double quotes inside the value.
--
function csv.escape(N)
   if N:isNull() then
      return ''
   elseif N:nodeType() ~= 'string' then
      return tostring(N)
   end
   local V = tostring(N)
   V = V:gsub('"', '""')
   return '"'..V..'"'
end

Destination: To File

The To File is configured to write each message out into its own .csv file, like this:

And that’s all there is to it!

What’s Next?

We showed you how simple it is to read from a database and output the data into CSV files. The next step is to adapt the code sample to your own needs, you can poll for any value/condition and do whatever processing you need. For example, the next page shows how to poll for a flag field and generate an HL7 message in response.

You may also be interested in some of these pages:

• HL7 to Database Tutorial
• Database to HL7 Tutorial
• Throttling: Controlling database inserts and updates

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

– Kevin Senn

iNTERFACEWARE