This post was originally written for Iguana 5 so it contains version 5 screenshots, and may contain out of date references.

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

These are the steps:

Read the unprocessed patient messages from the source database.
Update the flag table to indicate the messages have been processed.
Update 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 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 does not, see the Save to Database: DB to DB tutorial in the Interface Tutorials (Iguana 5 documentation) 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]

Create a Channel with the the following settings:
- Source = From Translator
- Destination = To Channel
- Channel name = Update DB data to Database
Click the Add Channel button to create the channel.
Ignore the red warning messages, see resolving the commit configuration error.
Open the Translator by clicking the Edit Script link at the bottom of the Source tab.
Download and import the Update_DB_data_to_Database_From_Translator.zip project file.
Iguana will load the project and data into the Translator, your screen should look like this:

Tutorial Instructions [top]

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

Create tables and data in the source database.

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','Y')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('5488754','Y')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('9639271','Y')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('2668844','Y')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('9229102','Y')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   Sql = [[INSERT INTO ProcessPatient VALUES('4101077','Y')]] 
   ConnSource:execute{sql=Sql,live=true}
   
   -- check data
   ConnSource:query('SELECT * FROM Patient')
   ConnSource:query('SELECT * FROM ProcessPatient')
   ------------ END ADD TABLES AND DATA ------------

Click on the two SELECT query results to confirm that six rows were added to each table:
Delete the code that you inserted above.

Create a database connection object for the source database, using the db.connect{} function
Add the following code to your script:
Create a database connection object for the target database.
Add the following code to your script:
Read the messages to be processed from the source database.
Add the following code:
Click on the result set to view the retrieved messages:
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 36 and 42 are the Lua terminators for a multi-line string.
Add the code to update the database:
Test the hard-coded UPDATE query.
1. Modify SqlUpdate, we changed the surname to “iNTERFACEWARE”:
2. Add a SELECT query to view the patient data:
3. Click on the SELECT query result to confirm that the surname was changed:
4. Change the surname back to “Smith”.
View the hard-coded SQL statement string in the SqlUdate variable.
Add a trace() and click the string annotation to view it:
Now we will reproduce the same UPDATE query but replacing the hard-coded data with concatenated data from the retrieved source DB messages.
1. Make a second copy the SqlUpdate 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:
  
  We changed from brackets [[]] to quotes ” as string delimiters simply to keep the query string format the same as before.
  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.
  Modify the code so it looks like this:
  
  Note: The newline (\n) and an extra space at the start of lines 48 to 51 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. Add a for loop using an index “i” to process all the retrieved records.
  Modify the code to look like this:
  
  Note: the “#” (count) operator is routinely used to loop through a repeating data structure.
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 SqlUpdate string, and the trace() below it:
Add the database update statement, it needs to be moved inside the for loop so it processes all the messages.
Modify the code to look like this:
Update the queue (ProcessPatient) in the source database after processing each, to prevent multiple updates of the same record.
Add this code to the end of the for loop:

Add the following test code.
Add this code to modify the source patient name to “iNTERFACEWARE”, and to force the target data to be updated:

   ConnSource:execute{
      sql = 'UPDATE Patient SET Surname = "iNTERFACEWARE"',
      live = true}
   ConnSource:execute{
      sql = 'UPDATE ProcessPatient SET Processed = "N"',
      live = true}

Test that the code is updating 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 updated.
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.

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)

   -- use a for loop to process all the messages
   for i=1,#r do
      local SqlUpdate =
      " UPDATE patient SET"..
      "\n LastName  = '"..r[i].Surname.."',"..
      "\n GivenName = '"..r[i].FirstName.."',"..
      "\n Ssn       = '"..r[i].Ssn.."'"..
      "\n WHERE Id  = '"..r[i].PatientId.."'"
   
      -- (4) update database
      ConnTarget:execute{sql = SqlUpdate, 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]

See our other interface tutorials (Iguana 5 documentation) in this section
See our general tutorials (Iguana 5 documentation) section
See our code samples (Iguana 5 documentation) section

Post

Tutorial: Database Update – DB to DB