Insert data into a database

Verified
Added by iNTERFACEWARE

How to insert data from an HL7 message into a database (similar principles apply to other message types)

Source Code
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 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   )'  
   
   -- (3) Insert data into database
   
   -- Note: This insert statement will fail and raise
   -- an error for any duplicate patient record
   Conn:execute{sql=SqlInsert, live=true}
end
Description
How to insert data from an HL7 message into a database (similar principles apply to other message types)
Usage Details

This example shows how to insert data from an HL7 message into a database. The message is parsed into a read-only source HL7 node tree structure, and then used to generate a SQL INSERT statement.

The process for using other message types follows the same general pattern.

How to use the code:

  • Use a Filter or To Translator script
    • Add the code
    • Load the sample messages from sample_data.txt
  • Alternatively you can also load the attached project which already contains the sample messages
  • Inspect the code and annotations to see how it works

Note: See the Interface Tutorials (Iguana 5 documentation) section for more information database inserts and updates using other message types.