Call a DB stored procedure

Verified
Added by iNTERFACEWARE

Demonstrates the use of conn:execute{} to call a stored procedure.

Source Code
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
Description
Demonstrates the use of conn:execute{} to call a stored procedure.
Usage Details

Create a channel and load the project zip file into the Filter component (or paste the code and load the the sample data), then view the code to see how it works.

For a detailed explanation see the “Calling a SQL stored procedure” link in More Information below.