Inserting using db.tables{} and db conn merge{}

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.