demo-autoinc.vmd

Verified
Added by iNTERFACEWARE

Use with conn:merge{} to handle autoincrementing Primary Keys in a database

Description
Use with conn:merge{} to handle autoincrementing Primary Keys in a database
Attachments
Usage Details

This VMD is designed to be used with conn:merge{} to handle autoincrementing Primary Keys in a database. It is simply our demo.vmd with the Mrn (external patient identifier) field added. The Mrn field is used to identify the patient exists in the the database.
Note: Technically in “database speak” the Mrn is an Alternate Key for the patient table (but you don’t need to know this to use the code).

First setup a MySQL database to use with the demo-autoinc.vmd:
Note: You can use a different database if you prefer.

  • 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 to use the demo-autoinc.vmd:

  • Check for an external patient identifier in the inbound feed
  • Do a SELECT from the patient table, to retrieve the patient Id
  • 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
  • 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

See Handling tables that use an auto-incremented ID column for details.