Handling tables that use an auto-incremented ID column

Vendors often create tables in their databases that use auto-incremented key columns. If your code is using conn:merge{} to implement insertion operations, handling these key columns requires a little more thought.

Before You Start

For the code in this example to work you need to create a Patient table with an auto-incrementing Id column.

Follow these steps for MySQL:

• Create a database called Test
• Use the demo-autoinc.vmd to create the tables from within Iguana
• Change the Id column to an int data type, using “MySQL Workbench”
• Set the Id column as the Primary Key, using “MySQL Workbench”

Note: we assume you are reasonably familiar with MySQL Workbench or a similar tool for MySQL.

How It Works

Suppose that your database contains a patient table with an auto-incremented Id column. You will need to implement this type of algorithm:

1. Check whether an external patient identifier has appeared in the inbound feed
2. Do a SELECT query on the patient table, with a WHERE clause that checks for the presence of the patient in the table
3. If the patient exists, leave the main Id column in the table. This ensures that the conn:merge{} call will do an UPDATE for this Id
4. Otherwise, if the patient does not exist, use T:remove('Id') to remove the ID column, then conn:merge{} will then do an insert, and a new auto-incremented Id will be created

Here is an example:

In this case the Mrn (External Id) is not found, so a new row will be inserted in the patient table.

The example uses the demo-autoinc.vmd, and you can get the Using a DB with an autoincrementing ID code from our repository.

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