Tutorial: Merging data into a Database

This post was originally written for Iguana 5 so it contains version 5 screenshots, and may contain out of date references.

In this article we will merge some data from an HL7 message to a database, using the conn:merge{} function. Merging data updates a record if it exists, or creates a new one if it does not. The conn:merge{} function takes care of this process for you. We placed the merge code in a To Translator component, which is the most logical place to write to a database.

You can use exactly the same method to merge any data to any database.

These are the steps:

  1. Preparation: Parse the message into a source HL7 node tree structure
    • Note: You can use any data source HL7 is just a convenient example
  2. Preparation: Create a database table node tree for the target message
  3. Preparation: Map the data from the source node tree to the target node tree
  4. Write the the target node tree data to the database

This tutorial only addresses the last step, the preparatory steps have already been completed for you.

We recommend that you type in the code for each step, but we also include complete Sample Code if you prefer to paste it in and follow along.

Tip: If you want to modify the message data see the “Transforming Messages:” tutorials in this section. Mapping and transformation use very similar techniques, the main difference is the intention: When you are mapping you are just copying the data, when you are transforming a message you are modifying the data (as well as copying it).

Preparation: Create the “test” database [top]

If you do not already have a SQLite database called “test” in the Iguana install directory, then you can create it by following the instructions in the Create a SQLite DB to match your VMD tutorial.

Create the Channel [top]

  1. Create a Channel with the the following settings:
    • Source = LLP Listener
    • Destination = To Translator
    • Channel name = Merge into Database
  2. Click the Add Channel button to create the channel.
    Ignore the red warning messages, see resolving the commit configuration error.
  3. Open the Translator by clicking the Edit Script link at the bottom of the Destination tab.
  4. Download and import the Merge_into_Database_To_Translator.zip project file.
    This file contains a skeleton project and six sample HL7 messages.
  5. Iguana will load the project and data into the Translator, your screen should look like this:

Tutorial Instructions [top]

  1. Inspect the incoming and outgoing messages to see the data that will be written to the database.
    Add a trace() command and click on the Msg and Out (ADT) annotations to view them:
  2. Write the data to the database.
    Add this code to your script:

Tip: You can use any data source HL7 is just a convenient example. You can read the data another database, CSV file, FTP, web service, XML, X12, etc.

Complete Sample Code [top]

Here is the completed mapping code that you can cut and paste into your script:

function main(Data)
   -- Parse the HL7 message
   local Msg, Name = hl7.parse{vmd = 'example/demo.vmd', data = Data}
   
   -- Create the database tables target node tree
   local Out       = db.tables{vmd = 'example/demo.vmd', name = Name}
   
   --Map (part of) the message
   Out.patient[1].Id        = Msg.PID[3][1][1]
   Out.patient[1].LastName  = Msg.PID[5][1][1][1]
   Out.patient[1].GivenName = Msg.PID[5][1][2]
   Out.patient[1].Ssn       = Msg.PID[19]
   
   -- (1) Save data to database
  
   -- connect to the database
   if not Conn or not Conn:check() then
      Conn = db.connect{
         api=db.SQLITE,
         name='test',
         user='',
         password='',
         live=true
      }
   end
   
   -- merge the data into the database
   Conn:merge{data=Out, live = true}
end

More Information [top]

Leave A Comment?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.