• Introduction
• Best Practice: Database Connection Objects
• db conn query{}: Reading from a database
• db conn execute{}: Writing to a Database
• Basic db.tables{} and db conn merge{} example
• Inserting using db.tables{} and db conn merge{}
• Understanding how merge uses key columns
• Understanding the bulk insert option
• Calling a SQL stored procedure

Introduction

This article contains an overview of using databases with Iguana Translator. We suggest you read the full article for a basic understanding of how Iguana works with databases, or atleast skim the the article – you can always come back later and read relevant sections in more depth.

For more specific database techniques and best practices read Working with Databases.

Best Practice: Database Connection Objects [top]

When working with databases, we recommend using our database connection objects. These objects are more reliable and easier to use than the legacy database functions.

Is there any occasion not to use connection objects?

Advantages:

• Connections are no longer pooled, resulting in safer transactions:
  • Create dedicated connections using db.connect
    Note: If network connectivity to the database is lost a rollback will occur.
• The ability to check a connection to confirm that the database is still available:
  • Use the conn:check method to test a connection

Usage:

1. Create a connection using db.connect
   • Optionally check to see if a connection created previously is still live and create a new connection if needed
2. Do database tasks (insert/update data etc)

How the live flag for db.connect affects the execution of database methods in the Iguana editor:

By default the connection handle returned from db.connect is live (live = true).

• If db.connect live = true (the default) connection methods will run in the editor (based on their default or live setting).
• If db.connect live = false it means that all database operations are always non-live.

For more information, see the help documentation for db.connect.

Examples

1. Download and import this project into a To/From Translator channel: db_connection.zip
2. Check to see if the DB connection is valid and create a new connection if needed.
3. Merge, update and read data.
4. Close the connection.

And this is how to copy a connection… Easy!

And here is the code:

local function SQLiteTableCreation()
   -- Set "live = true" to delete the Patient table
   -- this can be used to delete a Patient table that is not
   -- working (probably because it has a the wrong fields)
   -- ALTERNATIVELY: if you need to *keep* the current Patient 
   -- table you can change the DB name in db.connect{} in main()  
   conn:execute{sql=[[DROP TABLE Patient;]],
      live = false}  -- change to "true" to delete Patient table

   -- create Patient table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Patient (
      Id TEXT(255) NOT NULL, LastName TEXT(255),
      GivenName TEXT(255), Race TEXT(255), PhoneHome TEXT(255),
      PhoneBusiness TEXT(255), Religion TEXT(255),
      MaritalStatus TEXT(255),Ssn TEXT(255),
      LicenseNumber TEXT(255),PRIMARY KEY (Id));
      ]],
      live = true}
end

function main()
   local out = db.tables{vmd='demo.vmd', name='ADT'}
   out.patient[1].Id='1'
   out.patient[1].GivenName='Gyulia'
   out.patient[1].LastName='Smith'

   -- check if connection is valid an recreate if required
   -- (assume "conn" object was created earlier)
   if not conn or not conn:check() then
      if conn and conn:check() then 
         conn:close() end     -- close stale connection
      conn = db.connect{   
         api=db.SQLITE, 
         name='test',
         user='root',         -- not required - ignored by SQLite
         password='password', -- not required - ignored by SQLite
         use_unicode = true,
         live = true
      }
   end

   -- create SQLite tables if needed
   SQLiteTableCreation()

   -- merge using name 'Gyulia'
   conn:merge{data=out, live = true}

   -- update name 'Gyulia' to 'Julia'
   conn:execute{sql=[[
      UPDATE Patient SET GivenName = 'Julia'
      WHERE GivenName = 'Gyulia']],
      live = true}
   conn:query{sql='SELECT * from Patient'}

   -- best practice to manually close connection
   conn:close() 

   -- how to copy a DB connection
   local conn2 = db.connect(conn:info())

end

See also:

• db – interacting with databases for information on new and legacy database functions
• db conn – new database methods
• db – old style database functions

Is there ever a case where it is best NOT to use connection objects?

As long as you are using Iguana 5.5 or above, the answer is no.

For new code, we recommend using connection objects. If you are migrating from an older version of Iguana (before 5.5), there is no need to change but it strongly recommended that you update your code as soon as possible.

db conn query{}: Reading from a database [top]

To read from a database you should use conn:query{}.

To write to a database you should use conn:execute{}.

How It Works

To read from a database, use a conn:query{} method call.

This allows you to execute an ad hoc SQL SELECT statement on a database (a count of the rows returned is shown for information):

As you can see, the format of the data returned is a standard node tree, which means that it’s very easy to manipulate and map data coming back from the query.

conn:query{} actually executes SELECT statements when you alter the Editor script or change the sample data. It assumes that the SELECT statements do not alter the state of the database and can be safely run to power the Translator’s annotation and auto-completion capabilities. For anything which does alter the database, use conn:execute{} or conn:merge{}.

Sample Code

local function SQLiteTableCreation()
   -- Set "live = true" to delete the Patient table
   -- this can be used to delete a Patient table that is not
   -- working (probably because it has a the wrong fields)
   -- ALTERNATIVELY: if you need to *keep* the current Patient 
   -- table you can change the DB name in db.connect{} in main()  
   conn:execute{sql=[[DROP TABLE Patient;]],
      live = false}  -- change to "true" to delete Patient table
   
   -- create Patient table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Patient (
         Id TEXT(255) NOT NULL, LastName TEXT(255),
         GivenName TEXT(255), Race TEXT(255), PhoneHome TEXT(255),
         PhoneBusiness TEXT(255), Religion TEXT(255),
         MaritalStatus TEXT(255),Ssn TEXT(255),
         LicenseNumber TEXT(255),PRIMARY KEY (Id));
      ]],
      live = true}
end

-- create connection once when channel starts
conn = db.connect{   
   api=db.SQLITE, 
   name='test',
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   use_unicode = true,
   live = true
}

local function findPatient(Id)
   local R = conn:query{
      sql="SELECT * FROM Patient WHERE id = '"..Id.."'"
   }
   trace(#R)   -- count of Patient rows returned
   trace(R[1]) -- first row returned 
   return #R, R
end
 
local function getAllPatients()
   local R = conn:query{sql="SELECT * FROM Patient"}
   trace(#R)   -- count of Patient rows returned
   trace(R[1]) -- first row returned
   return #R, R
end

function main()
   -- create Patient and Kin tables if they do not exist 
   SQLiteTableCreation()
   
   -- find a patient with a specified Id
   findPatient(4525285)
   
   -- get all patients
   getAllPatients()
end

db conn execute{}: Writing to a Database [top]

To write to a database you should use conn:execute{}.

Unlike conn:merge{}, conn:execute{} does not handle database transactions automatically. When you use conn:execute{}, it is your responsibility to manage how transactions are handled.

To read from a database you should use conn:query{}.

How It Works

To write to a database you can use conn:execute{}.

This allows you to execute any ad hoc SQL statement on a database, including UPDATE and INSERT statements (unlike conn:query{} which will not run UPDATE and INSERT statements). You can also use conn:execute{} to call a stored procedure.

By default, SQL statements specified in calls to conn:execute{} are not executed while you are editing the script. They are only executed when the channel is being run. This behavior can be overridden by setting the live parameter to true, in which case the statements are executed every time the script is executed in the editor. Be cautious about using live=true since the script is executed many times to get the annotation and auto-completion data (which can cause many INSERTs and UPDATEs) .

In this example setting live = true and advancing through the sample data in the editor will add rows to the Patient table:

Return Values

Both conn:execute{} and conn:query{} both return a pair of values:

1. The last recordset selected by the SQL statement (or nil if the statement resulted in no recordsets)
2. The array of all recordsets resulting from the SQL statement (if no recordsets were produced, the array will be empty):
   
3. The following screenshot illustrates how multiple result sets are rendered in the editor:
   Note: This code will need to be run against a different database like MySql, because SQLite always returns only one recordset as it only accepts a single query at a a time (if you send a query string with multiple SQL queries to SQLite it will execute the first SQL statement and ignore the rest).
   

Error Handling

Both conn:execute{} and conn:query{} throw errors if a database error is encountered. The errors are regular Lua errors and will cause the execution of the script to halt, unless they are caught by exercising Lua error handling mechanisms.

In this case we tried to insert a duplicate Patient record, which caused a primary key violation:

The error thrown is a table with two fields:

1. message: a string with the description of the error
2. code: an integer error code returned by the database. Currently, error codes from ODBC sources and MySQL databases are supported.

We added a protected Lua call (pcall) and, as you see below, the error is captured, and the procedure is no longer halted:

Note: In this case is the error message is not very helpful as it only tells you a constraint was violated, it doesn’t tell you that it was a primary key constraint violation. Messages from other databases will usually say something more useful like “primary key violation”.

Sample Code

This code includes everything we have done above. You can use this sample_data.txt data file.

local function SQLiteTableCreation()
   -- Set "live = true" to delete the Patient table
   -- this can be used to delete a Patient table that is not
   -- working (probably because it has a the wrong fields)
   -- ALTERNATIVELY: if you need to *keep* the current Patient 
   -- table you can change the DB name in db.connect{} in main()  
   conn:execute{sql=[[DROP TABLE Patient;]],
      live = false}  -- change to "true" to delete Patient table
   
   -- create Patient table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Patient (
         Id TEXT(255) NOT NULL, LastName TEXT(255),
         GivenName TEXT(255), Race TEXT(255), PhoneHome TEXT(255),
         PhoneBusiness TEXT(255), Religion TEXT(255),
         MaritalStatus TEXT(255), Ssn TEXT(255),
         LicenseNumber TEXT(255), PRIMARY KEY (Id));
      ]],
      live = true}
   
   -- the process is identical for the Kin table
      conn:execute{sql=[[DROP TABLE Kin;]],
      live = false}  -- change to "true" to delete Kin table
   
   -- create Kin table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Kin (
         Relationship TEXT(255), LastName TEXT(255),
         FirstName TEXT(255));
      ]],
      live = true}
end

-- create connection once when channel starts
conn = db.connect{   
   api=db.SQLITE, 
   name='test',
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   use_unicode = true,
   live = true
}

function addPatient(PID)
   conn:execute{
      sql=[[INSERT INTO Patient(
      Id, 
      LastName, 
      GivenName,
      Race,
      PhoneHome,
      PhoneBusiness,
      Religion,
      MaritalStatus,
      Ssn,
      LicenseNumber
   ) 
      VALUES(']]
      ..PID[3][1][1].."', '"
      ..PID[5][1][1][1].."', '"
      ..PID[5][1][2].."', '"
      ..PID[10][1][1].."', '"
      ..PID[13][1][1].."', '"
      ..PID[14][1][1].."', '"
      ..PID[17][1].."', '"
      ..PID[16][1].."', '"
      ..PID[19].."', '"
      ..PID[20][1].."'"
      ..")",
   live=true
   }
end

function main(Data)   
   -- create Patient table if it does not exist 
   SQLiteTableCreation()

    -- change to "live=true" to delete all rows in Patient table
   conn:execute{sql='DELETE FROM Patient', live=false}
   
   -- parse the HL7 message to read-only node tree
   local Msg, Name = hl7.parse{vmd='demo.vmd', data=Data}

   -- copy parsed message to a writable "Out" node tree
   local Out = hl7.message{vmd='demo.vmd', name=Name}
   Out:mapTree(Msg)
   -- now we can do some processing on the writable Out 
   -- node tree before saving it to the databse
 
   if Name=='ADT' then
      local Success, Error = pcall(addPatient, Out.PID)
      if (not Success) then
         trace(Error)
         trace(Error.code)
         trace(Error.message)
      end
   end
 
   -- run a single query
   conn:query{sql='SELECT * FROM Patient'}

   -- run multiple queries - returns multiple result sets
   -- doesn't work with MySQL because it only runs the 1st 
   -- query and ignores any subsequent queries
   local R1, R2 = conn:execute{
      sql=[[SELECT * FROM Patient;
      SELECT * FROM Kin;]], 
      live=true
   }
   trace(R1, R2)
end

Basic db.tables{} and db conn merge{} example [top]

Here is a simple example that uses db.tables{} and conn:merge{}. These methods are always used together: db.tables{} creates the empty set of tables that will be populated by the script. conn:merge{} performs the actual work of putting the data into the database.

The merge process all happens within a single database transaction.

How It Works

This sample code shown here provides a trivial example that that demonstrates how these methods work. Normally, the data would be coming from another source, such as an XML, JSON or HL7 message. In this case, the information is hard-coded within the script.

The code uses the attached tables.vmd file. This file has just one message defined, Message, and two tables, Person and Relationship:
Note: The merge will only run in the editor if live=true for conn:merge{}.

Sample Code

The source code is shown below:

local function SQLiteTableCreation()
   -- Set "live = true" to delete the Person table
   -- this can be used to delete a Person table that is not
   -- working (probably because it has a the wrong fields)
   -- ALTERNATIVELY: if you need to *keep* the current Person 
   -- table you can change the DB name in db.connect{} in main()  
   conn:execute{sql=[[DROP TABLE Person;]],
      live = false}  -- change to "true" to delete Person table
   
   -- create Patient table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Person (
      Id INT4 NOT NULL, LastName TEXT(255),
      FirstName TEXT(255), PRIMARY KEY (Id));
      ]],
      live = true}
   
   -- the process is identical for the Relationship table
      conn:execute{sql=[[DROP TABLE Relationship;]],
      live = false}  -- change to "true" to delete Relationship table
   
   -- create Relationship table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Relationship (
      Id INT4 NOT NULL, RelationId INT4 NOT NULL,
      Relationship TEXT(255), PRIMARY KEY (Id,RelationId));
      ]],
      live = true}
   
end

-- create connection once when channel starts
conn = db.connect{   
   api=db.SQLITE, 
   name='test',
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   use_unicode = true,
   live = true
}

function main()   
   -- create tables if they do not exist 
   SQLiteTableCreation()

   -- create the table node tree for merging into the DB
   local Out = db.tables{vmd='tables.vmd', name='Message'}
 
   -- Add data to the table node tree
   -- Three Person rows and 2 Relationship rows
   Out.Person[1].Id = '1'
   Out.Person[1].LastName = 'Smith'
   Out.Person[1].FirstName = 'George'
 
   Out.Person[2].Id = '2'
   Out.Person[2].LastName = 'Smith'
   Out.Person[2].FirstName = 'Mary'
 
   Out.Person[3].Id = '3'
   Out.Person[3].LastName = 'Smith'
   Out.Person[3].FirstName = 'Joe'
 
   Out.Relationship[1].Id = '2'
   Out.Relationship[1].RelationId = '1'
   Out.Relationship[1].Relationship = 'Wife'
 
   Out.Relationship[2].Id = '3'
   Out.Relationship[2].RelationId = '1'
   Out.Relationship[2].Relationship = 'Son'
 
   -- merge the data 
   conn:merge{data=Out, live=true}
   
   -- check that merge worked
   conn:query{sql='SELECT * FROM Person'}
   conn:query{sql='SELECT * FROM Relationship'}
end

Inserting using db.tables{} and db conn merge{} [top]

To insert data into databases, you should use db.tables{} and conn:merge{}.

The merge functionality powerful, flexible, and simple to use! Merge will check to see if a row already exists (i.e., a Patient). If it does, it will update the existing data; however, if data does not exist (i.e., a new Patient), it will insert a new row into the table. What’s more, a single conn:merge{} statement can insert/update a single row in one table (or multiple rows in many tables).

Before You Start

Because VMD files are needed to define the table definitions used in db.tables{} and conn:merge{}, we recommend that you familiarize yourself with how to work with VMD files:

• Using Chameleon to examine the VMD structure
• Using Chameleon to Import table definitions directly into the VMD file
• Using Iguana to create database tables from definitions in a VMD file

In the future, we plan to give the Translator the ability to define and edit database schemas directly. When this is implemented, you will no longer need to use VMD files or Chameleon.

How It Works

To merge data into a database, just follow these steps:

1. Use db.tables{} to create a table node tree.
2. Map your data into the table node tree.
3. Use conn:merge{} to insert the data into the database.

To see this procedure in action, check out this basic example using db.tables{} and conn:merge{}.

The following pages describe more advanced merge features:

• Understanding how merge uses key columns
• Handling tables that use an auto-incremented ID column
• Understanding the bulk insert option

Additional Information

We have shown you how to use the Iguana merge function to insert data into a database. We also explained how it works with keys, auto-incrementing IDs, and bulk insert. This should should cover most database insert/update needs.

Sometimes, you may find that the default implementation of merge won’t meet your exact requirements. For instance, conn:customMerge() to prevent it inserting NULLs, or your might need tighter control over how ‘double’ values are formatted. Fortunately, because the Translator is such an open solution, it’s possible for us to provide an equivalent routine written in Lua which gives you tighter control over the behaviour. Because this alternative merge module is written in Lua, you can customize it to suit your needs.

For more information about this topic, you may want to check out these pages:

• Handling transactions explicitly with execute()
• Using Database Connection Objects
• Throttling: Controlling database inserts and updates
• API reference for conn:merge()

Please contact support at support@interfaceware.com if you need more help.

Understanding how merge uses key columns [top]

A call to conn:merge{} will either insert new rows in a database table or update existing rows.

How It Works

This depends on whether the VMD file that specifies database tables has any key columns defined:

• If no key columns are defined, new rows are always inserted. Some applications prefer this behavior, since data is never overwritten.
• If key columns are defined, Iguana performs a SELECT statement to attempt to find rows that match the values provided in the key columns. If a row exists, an UPDATE operation is performed. Otherwise, an INSERT is performed.

Check for Key Columns using Chameleon

To determine whether a table in a VMD file contains a key column.

1. Open Chameleon
2. Double-click the name of the table that you want to examine:
   
3. Check for any columns with the Key check box selected:

   In this case the Id column is the key for the Person table.

   

Understanding the bulk insert option [top]

Bulk insert is an optional flag for conn:merge{} that can be used with Microsoft SQL Server and MySQL.

It ignores the key columns defined in the VMD files and always does an INSERT using the bulk insert syntax. If your table has a large number of rows, this can improve performance.

Before You Start

How It Works

Because Bulk Insert is only works for MySQL and Microsoft SQLServer, we will use MySQL this example.

Using bulk insert in Iguana is very simple you just need to set bulk_insert = true.

Here is an example of a call to conn:merge{} that uses the bulk insert option:

Because bulk insert only does inserts you will get an error if you try to merge a duplicate record:

Sample Code

To use this code in your script, copy and paste the following code sample:
Note: You will need to create a MySQL database called “test” and create the Patient table – the easiest way is to use the Dashboard to create the table from the VMD file definition.

-- create connection once when channel starts
conn = db.connect{   
   api=db.MY_SQL, 
   name='test',
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   use_unicode = true,
   live = true
}

-- We map Mrn to the Patient ID Number
local function mapPatient(PID, Out)
   Out.patient[1].Id            = PID[3][1][1]
   Out.patient[1].LastName      = PID[5][1][1][1]
   Out.patient[1].GivenName     = PID[5][1][1][1]
   Out.patient[1].Race          = PID[10][1][1]
   Out.patient[1].PhoneHome     = PID[13][1][1]
   Out.patient[1].PhoneBusiness = PID[14][1][1]
   Out.patient[1].Religion      = PID[17][2]
   Out.patient[1].MaritalStatus = PID[16][2]
   Out.patient[1].Ssn           = PID[19]
   Out.patient[1].LicenseNumber = PID[20][1]
   return Out
end

function main(Data)   
   local Msg, Name = hl7.parse{vmd='demo.vmd', data=Data}

   -- create the table node tree for merging into the DB
   local Out = db.tables{vmd='demo.vmd', name=Name}
 
   -- Add data to the table node tree
   Out = mapPatient(Msg.PID, Out)
   
   -- merge the data 
   conn:merge{data=Out, bulk_insert = true, live=true}
   
   -- check that merge worked
   conn:query{sql='SELECT * FROM Patient'}
end

What’s Next?

We showed you the the bulk insert option for the conn:merge{}. If you want more control it is possible use native SQL bulk inserts statements with conn:execute{}.

You might want to use a trigger during bulk insert operations to prevent invalid inserts (i.e., check on insert if record exists and do an update instead). You can also use triggers for other data validation etc.

If you want triggers to fire during bulk insert you will need to do the following:

• For MySQL triggers fire by default so no action is needed
• For Microsoft SQLServer you need to use conn:execute{} to run a native bulk insert statement, using the FIRE_TRIGGERS option

If you do not want triggers to fire during bulk insert you will need to do the following:

• For Microsoft SQLServer triggers do not fire by default so no action is needed
• For MySQL you can use a variable in the trigger to prevent it from running
  Note: I cleaned up the code from this discussion:
  • Trigger code:

    IF @enable_triggers = 1 THEN
        // Do something useful when the trigger is enabled
    END IF;

  • Iguana script:

       conn:execute{sql='SET @enable_trigger = 0', live=true} -- disable triggers
       conn:merge{data=T, bulk_insert=true, live=true}
       conn:execute{sql='SET @enable_trigger = 1', live=true} -- re-enable triggers

     

    1. Set the variable to disable triggers using conn:execute{sql = 'SET @enable_trigger = 0'}
    2. Run conn:merge{}
    3. Set the variable to enable triggers using conn:execute{sql = 'SET @enable_trigger = 1'}

Calling a SQL stored procedure [top]

Because SQLite does not support stored procedures we will use MySQL this example.

It’s straightforward to use conn:execute{} to call a stored procedure.

How It Works

The only thing you need to be careful of is to make sure that values coming from external sources are properly escaped. We will use the conn:quote{} function to do this. The conn:quote{} function quotes all returned strings, so you must not add quotes around strings in SQL query text.

Other than this, all you have to do is join strings together to create the stored procedure call:

As you can see that that the embedded apostrophe (‘) character in LastName is correctly escaped:

Sample Code

You can load this sample project file: call_sp_filter.zip.

Or you can use this sample_data.txt data file, and copy the Call a DB stored procedure code sample from our repository.