Database Insert: DB to DB

In this article we will use conn:query{} and conn:execute{} to retrieve patient data from a source database and insert it into a target database.

These are the steps:

  1. Read the unprocessed patient messages from the source database.
  2. Update the flag table to indicate the messages have been processed.
  3. Insert the data into the target database.

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 INSERT statement for every message that is processed. It does not check if a record already exists, therefore if you attempt to insert duplicate records (with the same patient Id) the 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: DB to DB tutorial in the Interface Tutorials 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]

  1. Create a Channel with the the following settings:
    • Source = From Translator
    • Destination = To Channel
    • Channel name = Insert DB data 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 Source tab.
  4. Download and import the Insert_DB_data_into_Database_From_Translator.zip project file.
  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 tables and data in the source database.
    1. Paste the following code into your script:

         ------------ START ADD TABLES AND DATA ------------
   local ConnSource = db.connect{
      api=db.SQLITE,
      name='test_source',
      user='',
      password='',
      live=true
   }
   local Sql = [[DROP TABLE IF EXISTS Patient]]
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[CREATE TABLE IF NOT EXISTS Patient ( 
   PatientId TEXT(255) NOT NULL PRIMARY KEY, Surname TEXT(255) NULL, 
   FirstName TEXT(255) NULL, Ssn TEXT(255) NULL);]]
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[DROP TABLE IF EXISTS ProcessPatient]]
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[CREATE TABLE IF NOT EXISTS ProcessPatient (
   PatientId TEXT(255) NOT NULL PRIMARY KEY, Processed TEXT(255) NULL );]]
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO Patient VALUES('4525285','Smith','Tracy','284-517-569')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO Patient VALUES('5488754','Smith','Fred','558-171-617')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO Patient VALUES('9639271','White','Tracy','571-349-734')]] 
   ConnSource:execute{sql=Sql,live=true}
   Sql = [[INSERT INTO Patient VALUES('2668844','Fitzgerald','Gary','957-321-126')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO Patient VALUES('9229102','Fitzgerald','Tracy','223-15-522')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO Patient VALUES('4101077','Adams','Jim','128-474-184')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('4525285','N')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('5488754','N')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('9639271','N')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('2668844','N')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('9229102','N')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('4101077','N')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   -- check data
   ConnSource:query('SELECT * FROM Patient')
   ConnSource:query('SELECT * FROM ProcessPatient')
   ------------ END ADD TABLES AND DATA ------------
    2. Click on the two SELECT query results to confirm that six rows were added to each table:
    3. Delete the code that you inserted above.
  3. Create a database connection object for the source database, using the db.connect{} function
    Add the following code to your script:
  4. Create a database connection object for the target database.
    Add the following code to your script:
  5. Read the messages to be processed from the source database.
    Add the following code:
  6. Click on the result set to view the retrieved messages:
  7. 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 36 and 51 are the Lua terminators for a multi-line string.
  8. 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.
  9. 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.
  10. Uncomment the DELETE statement.
  11. View the hard-coded SQL statement string in the SqlInsert variable.
    Add a trace() and click the string annotation to view it:
  12. Now we will reproduce the same INSERT query but replacing the hard-coded VALUES with concatenated data from the retrieved source DB messages.
    1. Make a second copy the SqlInsert immediately below the original.
    2. Modify it as shown so the Id value is copied from the first record in the r variable (retrieved source DB messages).
    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 68 to 71 are 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. Add a for loop using an index “i” to process all the retrieved records.
      Modify the code to look like this:

      Note: We changed from brackets [[]] to quotes ” as string delimiters simply to keep the query string format the same as before.
    7. View the query string for the first iteration of the for loop to confirm that it matches the original query.
      Click the string annotation to view it:
    8. Navigate through the loop iterations to see the different queries for each record:

      Note: The seventh repetition does no processing it just exits the loop.
    9. Delete the trace() statement from the for loop:
    10. Delete the initial hard-coded SqlInsert string, and the trace() below it:
  13. Add this test code before the for loop:
  14. Add this test code at the end of main():
  15. Delete the old test code:
  16. Add the database insert statement, it needs to be moved inside the for loop so it processes all the messages.
    Modify the code to look like this:
  17. Update the queue (ProcessPatient) in the source database after processing each record, to prevent trying to insert duplicate patients.
    Add this code to the end of the for loop:
  18. Test that the code is adding the six messages to the target database.
    Click the result set for the patient SELECT query:

    As you can see the six records have been correctly inserted.
  19. Check what happens when you try to insert duplicate patient records (with the same patient Id).
    1. Comment out the DELETE statement before the for loop:

      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.
    2. Uncomment the DELETE statement.
  20. Check that the queue (ProcessPatient) in the source database is correctly updated.
    Click the result set for the ProcessPatient SELECT query:

    Note: The UPDATE query on the next line resets the Processed field to reprocess the messages for testing.
  21. Remove the test code.
    1. Comment out the UPDATE for the Processed field:
    2. Comment out the DELETE for the patient table before the for loop:

      Note: If you reverse the order the code will try to insert duplicate records, and give this error:

      If this occurs simply uncomment both lines and comment out the UPDATE first then the DELETE.
    3. Delete all the test code:

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: DB to DB tutorial in the Interface Tutorials section.

Complete Sample Code [top]

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

function main(Data)
   -- (1) connect to the source database
   if not ConnSource or not ConnSource:check() then
      ConnSource = db.connect{
         api=db.SQLITE,
         name='test_source',
         user='',
         password='',
         live=true
      }
   end
      
   -- (2) connect to the target database
   if not ConnTarget or not ConnTarget:check() then
      ConnTarget = db.connect{
         api=db.SQLITE,
         name='test',
         user='',
         password='',
         live=true
      }
   end
   
   -- (3) create insert query string

   -- Read messages to process
   local Sql = [[
   SELECT P.* FROM Patient P
   JOIN ProcessPatient PP 
   WHERE P.PatientId = PP.PatientId 
   AND PP.Processed = 'N'
   ]]
   local r = ConnSource:query(Sql)

   for i=1,#r do  
      local SqlInsert =
      "   INSERT INTO patient"..
      "\n   ("..
      "\n   Id,"..
      "\n   LastName,"..
      "\n   GivenName,"..
      "\n   Ssn"..
      "\n   )"..
      "\n   VALUES"..
      "\n   ("..
      "\n   '"..r[i].PatientId.."',"..
      "\n   '"..r[i].Surname.."',"..
      "\n   '"..r[i].FirstName.."',"..
      "\n   '"..r[i].Ssn.."'"..
      '\n   )'

      -- (4) Insert data into database
      ConnTarget:execute{sql = SqlInsert, live = true}
      
      -- Update source DB queue to prevent re-processing
      Sql = [[
      UPDATE ProcessPatient 
      SET Processed = 'Y' 
      WHERE PatientId = ']]
      ..r[i].PatientId.."'"
      ConnSource:execute{sql = Sql, live = true}      
   end
end

More Information [top]

Leave A Comment?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.