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 use conn:execute{} to update existing patient records using data from an HL7 message.

You can use the same method to copy data from of the different types of node trees and update a database.

These are the steps:

Preparation: Use the From LLP component to import and queue the HL7 message data
- Note: This step is simulated by using sample data
Preparation: Parse the message into a source HL7 node tree structure
Update 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.

Note: The code in this tutorial executes an UPDATE statement for each message that is processed. This means that any new records are effectively ignored, because they don’t exist in the database, so there is no record to update.

To insert new messages you need to use conn:merge() or add logic to check if the record exists and insert it if does not, see the Save to Database: HL7 to DB tutorial in the Interface Tutorials (Iguana 5 documentation) section.

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]

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

Tutorial Instructions [top]

Pass the message data to the script.
- Iguana automatically passes the message data to the main() function
- The message can be accessed using the Data parameter
- No action is needed
Create a database connection object, using the db.connect{} function.
Add the following code to your script:
Click on the ADT node tree annotation to view the first sample message:
Use the data from the first sample message to hard-code a SQL UPDATE statement.
Add the following code to your script:

Note: The double square brackets [[ ]] on lines 18 and 24 are the Lua terminators for a multi-line string.

Insert patient records corresponding to the HL7 sample messages.

Paste the following code into your script:

   -- create insert query string
   local SqlInsert =
   [[
   INSERT INTO patient
   (
   Id,
   LastName,
   GivenName,
   Ssn
   )
   VALUES
   (
   ]]..
   "'"..Msg.PID[3][1][1].."',"..
   "\n   '"..Msg.PID[5][1][1][1].."',"..
   "\n   '"..Msg.PID[5][1][2].."',"..
   "\n   '"..Msg.PID[19].."'"..
   '\n   )'  
   
   -- Insert data into database
   Conn:execute{sql=SqlInsert, live=true}
   
   -- Check that the records were inserted
   Conn:query('SELECT * FROM patient')

Navigate through the sample data to insert the patient records.
Click next message button on the toolbar five times:

Ignore any errors that occur (they just indicate that the record already exists):
Comment out the INSERT command:
Click on the SELECT query result to confirm that the messages were added:
Delete the code that you inserted above.

Add the code to update the database:
Test the hard-coded UPDATE query.
1. Modify SqlUpdate, we changed the surname to “iNTERFACEWARE”:
2. Add a SELECT query to view the patient data:
3. Click on the SELECT query result to confirm that the surname was changed:
4. Change the surname back to “Smith”.
View the hard-coded SQL statement string in the SqlUpdate variable.
Add a trace() and click the string annotation to view it:
Now we will reproduce the same UPDATE query but replacing the hard-coded data with concatenated data from the HL7 message.
1. Make a second copy the SqlUpdate immediately below the original.
2. Modify it as shown so the LastName value is copied from the Msg variable (HL7 data).
3. Add a trace() and click the string annotation to view it:
  
  Note: At this point the database update will cause an error because the query is incomplete, this will disappear after we finish modifying the query.
4. Reproducing the other fields is just more of the same.
  Modify the code so it looks like this:
  
  Note: The newline (\n) and 3 extra spaces at the start of lines 30 to 33 is only for formatting, so it is easy to compare the strings visually (the query will work equally well if they are removed).
5. View the query string to confirm that it matches the original query.
  Add a trace() and click the string annotation to view it:
6. Delete the initial hard-coded SQLUpdate string, and the trace() below it:
Test the code by updating the six sample records in the database.
1. Modify the sample data.
  - Click the Sample Data link on the toolbar to edit the sample messages:
  - Change data in one or more messages, we changed all the surnames to “iNTERFACEWARE”.
2. Navigate through the sample data to update the patient records.
  Click next message button on the toolbar five times:
3. Click on the SELECT query result to confirm that the surname was changed:
Delete the test code:

Note: The code in this tutorial executes an UPDATE statement for each message that is processed. This means that any new records are effectively ignored, because they don’t exist in the database, so there is no record to update.

To insert new messages you need to use conn:merge() or add logic to check if the record exists and insert it if it does not, see the Save to Database: HL7 to DB tutorial in the Interface Tutorials (Iguana 5 documentation) section.

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}
   
   -- (1) 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
      
   -- (2) Create update query string      
   local SqlUpdate =
   "   UPDATE patient SET"..
   "\n   LastName = '"..Msg.PID[5][1][1][1].."',"..
   "\n   GivenName = '"..Msg.PID[5][1][2].."',"..
   "\n   Ssn = '"..Msg.PID[19].."'"..
   "\n   WHERE Id = '"..Msg.PID[3][1][1].."'"
      
   -- (3) Update database
   Conn:execute{sql=SqlUpdate, live=true}
end

More Information [top]

See our other interface tutorials (Iguana 5 documentation) in this section
See our general tutorials (Iguana 5 documentation) section
See our code samples (Iguana 5 documentation) section

Post

Tutorial: Database Update – HL7 to DB