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