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

In this article we will use conn:execute{} to update existing patient records using data from an XML 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:

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. Update 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 = Update XML to 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 Update_XML_to_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 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.
5. Insert patient records corresponding to the XML sample messages.
   1. Paste the following code into your script:

         -- 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   )'  
         
         -- Insert data into database
         Conn:execute{sql=SqlInsert, live=true}
         
         -- Check that the records were inserted
         Conn:query('SELECT * FROM patient')

   2. 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):
      
   3. Comment out the INSERT command:
      
   4. Click on the SELECT query result to confirm that the messages were added:
      
   5. Delete the code that you inserted above.
6. Add the code to update the database:
   
7. Add a SELECT query to view the patient table:
   
8. Test the hard-coded UPDATE query.
   1. Modify SqlUpdate, we changed the surname to “iNTERFACEWARE”:
      
   2. Click on the SELECT query result to confirm that the surname was changed:
      
   3. Change the surname back to “Smith”.
9. View the hard-coded SQL statement string in the SqlUpdate variable.
   Add a trace() and click the string annotation to view it:
   
10. Now we will reproduce the same UPDATE query but replacing the hard-coded VALUES with concatenated data from the XML 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 (XML data).
    3. Add a trace() and click the string annotation to view it:
       
       Note: At this point the database update 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:
       
11. 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:
       
12. 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: 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 update query string      
   local SqlUpdate =
   "   UPDATE patient SET"..
   "\n   LastName = '"..Msg.patients.patient["last-name"][1].."',"..
   "\n   GivenName = '"..Msg.patients.patient["first-name"][1].."',"..
   "\n   Ssn = '"..Msg.patients.patient["social-security-no"][1].."'"..
   "\n   WHERE Id = '"..Msg.patients.patient.id.."'"
         
   -- (3) Update database
   Conn:execute{sql=SqlUpdate, live=true}
end

More Information [top]

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