Save to Database: JSON to DB

In this article we will use conn:execute{} to update a database with patient data from a JSON message. We will check if a corresponding patient already exists, if it does we will update that patient record, if it does not we will insert a new patient.

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

These are the steps:

  1. Preparation: Use the From Translator component to import and queue the JSON message data
    • Note: This step is simulated by using sample data
  2. Preparation: Parse the message into a source JSON tree structure
    • Note: Unlike HL7, XML etc a JSON tree structure is a Lua table not a node tree
  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: JSON 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 = Save JSON 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 Save_JSON_to_Database_To_Translator.zip project file.
    This file contains a skeleton project and six sample JSON messages.
  5. Iguana will load the project and data into the Translator, your screen should look like this:
    Screen Shot 2014-10-23 at 16.04.11

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 table object to view the first sample message:
  4. Create the INSERT query using concatenated data from the JSON message for the VALUES.
    1. Add this code that includes the concatenated patient Id:

      Note: The double square brackets [[ ]] on lines 18 and 28 are the Lua terminators for a multi-line string.
    2. Inspect the query string.
      Add a trace() and click the string annotation to view the partial query:
    3. Creating the other VALUES is just more of the same.
      Add these three lines of code to code the query:

      Note      : The newlines (\n) and 3 spaces at the start of lines 28 to 31 are only for formatting (the query will work equally well if they are removed).
    4. View the query string to confirm that it is correct.

      Tip: For more detailed explanations of creating a JSON query string see the Database Update: JSON to DB and Database Insert: JSON to DB tutorials in the Interface Tutorials section.

  5. Create the UPDATE query string, this is built up in a similar way to the INSERT query.
    1. Add the following code:
    2. Inspect the query string.
      Add a trace() and click the string annotation to view the query:

      As you can see this query is formatted on a single line because we did include any newline (\n) characters:
  6. Save the message to the database: If the record exists then update it, if the record does not exist then insert a new record.
    Note: This is exactly the same logic that is used by Conn:merge{}.

    1. Select the count of records from patient for the Id number in the current JSON message
      Add the following code:
    2. If the patient record exists (Cnt = 1) then update the it, otherwise insert a new record.
      Add the following code:
  7. Test the completed code.
    1. Delete all the patient records.
      Add this testing code at the end of main():
    2. Click on the SELECT query result to confirm that the patient records were deleted:
    3. Add patient records corresponding to the sample data.
      • Comment out the DELETE statement.
      • Navigate through the sample data to insert corresponding patient records.
    4. Click on the SELECT query result to confirm that the patient records were inserted:
    5. 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”.
      • Navigate through the sample data to update patient records.
    6. Click on the SELECT query result to confirm that the patient records were updated:
  8. Finally remove any the test code and trace statements, and you’re done!

Complete Sample Code [top]

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

function main(Data)
   -- Parse the JSON message
   local Msg = json.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["last-name"].."',"..
   "\n   '"..Msg.patients["first-name"].."',"..
   "\n   '"..Msg.patients["social-security-no"].."'"..
   '\n   )'   
      
   -- (3) create update query string
   local SqlUpdate =
   "UPDATE patient SET "..
   "LastName = '"..Msg.patients["last-name"].."', "..
   "GivenName = '"..Msg.patients["first-name"].."', "..
   "Ssn = '"..Msg.patients["social-security-no"].."' "..
   "WHERE Id = '"..Msg.patients["patient-id"].."'"
      
   -- (4) Save data to database  
   local Sql = [[
   SELECT COUNT(*) FROM patient 
   WHERE Id = ']]..Msg.patients["patient-id"].."'"
   local Cnt = tonumber(Conn:query(Sql)[1]["COUNT(*)"]:nodeValue())
   
   if Cnt == 1 then
      Conn:execute{sql = SqlUpdate, live = true}
   else
      Conn:execute{sql = SqlInsert, live = true}
   end
end

More Information [top]