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

local function sp_AddPatient(PID)
   -- the EscapeFuntion adds quotes around strings
   -- so you must not add quotes around strings
   -- in SQL query text 
   -- (adding quotes will produce a query error)
   trace(Sql)
   local Sql = "CALL AddPatient ("..
   conn:quote(PID[3][1][1]:nodeValue())..", "..
   conn:quote(PID[5][1][1][1]:nodeValue())..", "..
   conn:quote(PID[5][1][2]:nodeValue())..
   ")"
   trace(Sql)
   conn:execute{sql=Sql, live=true}
end

local function createProcedure()
   -- First you need a MySQL database with:
   --    name='test'
   --    user='root'
   --    password='password'
   
   -- Second you need a patient table with fields:
   --    Id
   --    GivenName
   --    LastName
   
   -- NOTE: Code works for MySQL but may not work for other databases
   
   conn:execute{sql='DROP PROCEDURE IF EXISTS test.AddPatient',live=true}
   conn:execute{sql=[[CREATE PROCEDURE addPatient(
      IN Id VARCHAR(255),
      IN LastName VARCHAR(255),
      IN GivenName VARCHAR(255)
   )
      BEGIN
      INSERT INTO patient(Id, GivenName, LastName) 
      VALUES(Id, GivenName, LastName);
   END]],
      live=true
   }
end

function main(Data)
   -- uncomment to delete patient rows so you can
   -- insert again without duplicate key errors
   --conn:execute{sql='DELETE FROM Patient', live=true}
   
   -- create addPatient() stored procedure
   createProcedure()
   
   local Msg, Name = hl7.parse{vmd='demo.vmd', data=Data}
   local Out = hl7.message{vmd='demo.vmd', name=Name}
   Out:mapTree(Msg)
   -- do some processing on Out before saving to database
   
   -- only call stored proc for ADT messages
   if Name=='ADT' then
      sp_AddPatient(Out.PID)
   end
   
   conn:query{sql='SELECT * FROM Patient'}
end