DB table Insert with auto-incrementing Id

Verified
Added by iNTERFACEWARE

Demonstrates how to use conn:merge{} with a table that has an auto-incrementing Id column.

Source Code
local conn = db.connect{
   api=db.MY_SQL,
   name='test',
   user='root',
   password='',
   live = true
}

-- We assume that:
-- 1. The auto ID column is Id
-- 2. Mrn is the medical record number - i.e. the external patient ID
function main(Data)
   local Msg, Name = hl7.parse{vmd='demo-autoinc.vmd', data=Data}
   local T = db.tables{vmd='demo-autoinc.vmd',name=Name}
   
   T = mapPatient(Msg.PID, T)
   
   local Q = conn:query{
      sql="SELECT * FROM Patient WHERE Mrn = '"..T.patient[1].Mrn.."'",
      live=true
   }
   if #Q > 0 then
      trace("Patient exists: "..Q[1].LastName..', '..Q[1].GivenName)
      T.patient[1].Id = Q[1].Id
   else
      trace("Patient is new.")
      T.patient[1]:remove('Id')
   end
   conn:merge{data=T, live=true}
end

-- We map Mrn to the Patient ID Number
function mapPatient(PID, t)
   -- map patient ID to Mrn
   t.patient[1].Mrn = tonumber(PID[3][1][1]:nodeValue())
   
   -- map other patient fields
   t.patient[1].LastName = PID[5][1][1][1]
   t.patient[1].GivenName = PID[5][1][1][1]
   t.patient[1].Race = PID[10][1][1]
   t.patient[1].PhoneHome = PID[13][1][1]
   t.patient[1].PhoneBusiness = PID[14][1][1]
   t.patient[1].Religion = PID[17][2]
   t.patient[1].MaritalStatus = PID[16][2]
   t.patient[1].Ssn = PID[19]
   t.patient[1].LicenseNumber = PID[20][1]
   return t
end
Description
Demonstrates how to use conn:merge{} with a table that has an auto-incrementing Id column.
Usage Details

The code in this example uses conn:merge{} to update an existing patient or add a new patient. It does this by using setting the Id for an existing patient (to cause an update),  or removing the Id for a new patient (to cause an insert).

How to use the sample code:

  • Create a MySQL 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”
  • Load the project zip or paste the code into a Filter component
  • If you paste the code you can load the sample data file
  • Try out the code to see how it works

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