Basic db.tables{} and db conn merge{} example

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.

The merge will only run in the editor if live=true for db.tables{} and conn:merge{}.

Sample Code

The source code is shown below:

local conn = db.connect{
   api=db.SQL_SERVER, 
   name='Test', 
   user='root', 
   password='password', 
   live = true
}

function main()   
   local Out = db.tables{vmd='tables.vmd', name='Message'}

   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[1].Id = '3'
   Out.Relationship[1].RelationId = '1'
   Out.Relationship[1].Relationship = 'Son'

   conn:merge{data=Out, live=true}
end