HL7 to Database

Merge the Database Tables

Contents

To merge the data into our database we will use the conn:merge() function, which is provided with the Iguana Translator.

Before You Start

If your database does not contain the necessary tables, you can generate them from within the Iguana Translator. To do this:

  1. In the Project Files panel hover your cursor over the arrow next to the VMD file. A popup menu appears:
  2. Select Create DB Tables.

    The Export Tables screen appears:
  3. Fill in the fields to match the picture below:

    Note: If you want to use a different database, then these fields must match the parameters you provided in the call to db.connect()

    • From the Database API list box, select the database to use
    • In the Database field, type the name of the database
    • In the Username field, type the username to use when accessing the database
    • In the Password field, type the password to use when accessing the database
  4. Click Preview Create Statements. The SQL statements that will be executed are displayed in the Preview Results/SQL Statements panel:

    Note: Username and Password are not used by SQLite (you can supply them but they will just be ignored).

  5. Examine these statements. If they are correct, click Execute Statements. The results of the executed statements are displayed in the Execute Results panel:
  6. Click Close to close this window.

How it Works

We use the conn:merge() function to merge the data T (in the table node tree) into our database.

Here is an example of a call to conn:merge():

We are using SQLite because it is plug and play, so you can paste in the sample code and it will just work.

Note: You can also connect to a different database if you wish.

The merge process is composed of two parts:

  1. You create a database connection object using db.connect().
  2. You merge the data using the conn:merge() method of the connection object you created.

The following list show the parameters for db.connect():

Key Required Description
api Yes The database type. Valid values are db.MY_SQL, db.ORACLE_OCI, db.ORACLE_ODBC, db.SQLITE, db.SQL_SERVER, db.POSTGRES, db.DB2, db.INFORMIX, db.INTERBASE, db.FILEMAKER, db.SYBASE_ASA and db.SYBASE_ASE, db.ACCESS.
name Yes The name or address of the database (for example, test@localhost).
user Yes The username to use when accessing the database (neither required or used for SQLite)
password Yes The password to use when accessing the database (neither required or used for SQLite)
live No if true, the connection is opened in the editor (defaults to true)
use_unicode No If true, Unicode will be used when communicating with the database
timeout No Maximum time in seconds allowed for the query (0 for infinite) Note: Timeout is supported only for ODBC based connections and defaults to 5 minutes

The following list show the parameters for conn:merge():

Key Required Description
data Yes et to a node table tree created using db.tables()
bulk_insert No set to true to use bulk insert logic
transaction No set to false to disable inserting/updating all rows as a transaction
live No if true, the connection is opened in the editor (defaults to true)

In the script, you can add the call to conn:merge() anywhere after the Out table node tree have been populated.

For example, you can add it as the last statement in the main() function:

Note: we also added db.connect() above main().

Sample Code

Simply replace the first 22 lines of code (up to and including the end of the main() function), with the code below.

require 'dateparse'
require 'node'
require 'hl7util'
require 'codemap'
require 'stringutil'

local function trace(a,b,c,d) return end

local conn = db.connect{
   api = db.SQLITE,
   name = 'Test',
   live = true
}

-- This is a skeleton example of the Translator configured to map HL7 to a database.

-- The main function is the first function called from Iguana.
-- The Data argument will contain the message to be processed.
function main(Data)
   local T = MapData(Data)
   if T then
      conn:merge{
         data=T, 
         live = true
      }
   end 
end

Next Step?

Once you have added the call to conn:merge to the script, and have created the tables in the database (if necessary), the next step is to save the script as a milestone.

Continue: Save a Channel Milestone