• Preparation: Create database
• Create the Channel
• Tutorial Instructions
• Complete Sample Code
• More Information

In this article we will use conn:execute{} to insert patient data from a XML message into a database.

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

These are the steps:

1. Preparation: Use the From Translator component to import and queue the XML message data.
   • Note: This step is simulated by using sample data.
2. Preparation: Parse the message into a source XML node tree structure.
3. Insert into the database.

This tutorial only addresses the last step, the preparatory steps have already been completed for you. To run this channel you would need to complete the first step, see the tutorial Reading from files: XML in this section.

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.

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 = From Translator
   • Destination = To Translator
   • Channel name = Insert XML into Database
2. Click the Add Channel button to create the channel.
   Ignore the red warning messages, see resolving the milestone configuration error.
3. Open the Translator by clicking the Edit Script link at the bottom of the Destination tab.
4. Download and import the Insert_XML_into_Database_To_Translator.zip project file.
   This file contains a skeleton project and six sample XML messages.
5. Iguana will load the project and data into the Translator, your screen should look like this:
   

Tutorial Instructions [top]

1. 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
2. Create a database connection object, using the db.connect{} function.
   Add the following code to your script:
   
3. Click on the document object to view the first sample message:
   
4. Use the data from the first sample message to hard-code a SQL INSERT statement.
   Add the following code to your script:
   
   Note: The double square brackets [[ ]] on lines 18 and 33 are the Lua terminators for a multi-line string.
5. Test that the query works by running it using Conn:execute{} and then viewing the inserted data.
   Add the following code, then click on the result of the SELECT query to view the patient data:
   
   As you can see the patient record has been inserted correctly.
6. Comment out the DELETE statement to see what happens when we try to insert a duplicate record:
   
   The expanded message tells that a cryptic reference that a “constraint failed”, which basically means that we are not allowed to insert the same patient record twice.
   Note: Technically the constraint is the primary key on the Id (which prevents duplicate patient records with the same Id), and the failure is because we tried to insert a patient record with a duplicate Id.
   
7. Uncomment the DELETE statement.
8. View the hard-coded SQL statement string in the SqlInsert variable.
   Add a trace() and click the string annotation to view it:
   
9. Now we will reproduce the same INSERT query but replacing the hard-coded VALUES with concatenated data from the XML message.
   1. Make a second copy the SqlInsert immediately below the original.
   2. Modify it as shown so the Id value is copied from the Msg variable (XML data).
   3. Add a trace() and click the string annotation to view it:
      
      Note: At this point Conn:execute{} returns an error because the query is incomplete, this will disappear after we add the other VALUES.
   4. Reproducing the other VALUES is just more of the same.
      Add these three lines of code to code the query:
      
      Note: The newline (\n) and 3 extra spaces at the start of lines 49 to 52 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 SqlInsert string, and the trace() below it:
      
10. Test the code by adding the six sample messages to the database.
    1. Click on the SELECT query result to confirm that there is currently a single patient (Tracy Smith):
       
    2. Comment out the DELETE statement, so we can add all six rows:
       
       You can safely ignore any errors about trying to insert duplicate records.
    3. Navigate through the sample data to insert add the other messages.
       Click next message button on the toolbar five times:
       
    4. Comment out the INSERT statement, to prevent errors from displaying:
       
    5. Click on the SELECT query result to confirm that the messages were added:
       
11. Delete the test code:
    
12. Add the database insert statement:
    

    Note: If you attempt to insert duplicate patient records (with the same patient id) this code will produce errors.

    To handle duplicate messages you need to use conn:merge() or add logic to check for duplicates, see the Save to Database: XML to DB tutorial in the Interface Tutorials 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 XML message
   local Msg = xml.parse(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.patients.patient.id.."',"..
   "\n   '"..Msg.patients.patient["first-name"][1].."',"..
   "\n   '"..Msg.patients.patient["last-name"][1].."',"..
   "\n   '"..Msg.patients.patient["social-security-no"][1].."'"..
   '\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

More Information [top]

• See our other interface tutorials in this section
• See our general tutorials section
• See our code samples section