Merge the Database Tables

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.

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():

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

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:

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.