Using a DB with an autoincrementing ID

Verified
Added by iNTERFACEWARE

How to handle inserts and updates for a table using an autoincrementing ID

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
How to handle inserts and updates for a table using an autoincrementing ID
Attachments
Usage Details

If your code is using conn:merge{} to implement insertion operations, handling these key columns requires a little thought.

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:

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