db conn execute{}: Writing to a Database

To write to a database you should use conn:execute{}.

Unlike conn:merge{}, conn:execute{} does not handle database transactions automatically. When you use conn:execute{}, it is your responsibility to manage how transactions are handled.

To read from a database you should use conn:query{}.

How It Works

To write to a database you can use conn:execute{}.

This allows you to execute any ad hoc SQL statement on a database, including UPDATE and INSERT statements (unlike conn:query{} which will not run UPDATE and INSERT statements). You can also use conn:execute{} to call a stored procedure.

By default, SQL statements specified in calls to conn:execute{} are not executed while you are editing the script. They are only executed when the channel is being run. This behavior can be overridden by setting the live parameter to true, in which case the statements are executed every time the script is executed in the editor. Be cautious about using live=true since the script is executed many times to get the annotation and auto-completion data (which can cause many INSERTs and UPDATEs) .

In this example setting live = true and advancing through the sample data in the editor will add rows to the Patient table:

Return Values

Both conn:execute{} and conn:query{} both return a pair of values:

1. The last recordset selected by the SQL statement (or nil if the statement resulted in no recordsets)
2. The array of all recordsets resulting from the SQL statement (if no recordsets were produced, the array will be empty)

The following screenshot illustrates how multiple result sets are rendered in the editor:

Error Handling

Both conn:execute{} and conn:query{} throw errors if a database error is encountered. The errors are regular Lua errors and will cause the execution of the script to halt, unless they are caught by exercising Lua error handling mechanisms.

In this case we tried to insert a duplicate Patient record, which caused a primary key violation:

The error thrown is a table with two fields:

1. message: a string with the description of the error
2. code: an integer error code returned by the database. Currently, error codes from ODBC sources and MySQL databases are supported.

We added a protected Lua call (pcall) and, as you see below, the error is captured, and the procedure is no longer halted:

Sample Code

This code includes everything we have done above. You can use this sample_data.txt data file.

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

function main(Data)
   --conn:execute{sql='DELETE FROM Patient', live=true}
   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 databse

   if Name=='ADT' then
      local Success, Error = pcall(addPatient, Out.PID)
      if (not Success) then
         trace(Error)
         trace(Error.code)
         trace(Error.message)
      end
   end

   conn:query{sql='SELECT * FROM Patient'}
   local R1, R2 = conn:execute{
      sql='SELECT * FROM Kin; SELECT * FROM Patient', 
      live=true
   }
   trace(R1, R2)
end

function addPatient(PID)
   conn:execute{
      sql=[[INSERT INTO Patient(
      Id, 
      LastName, 
      GivenName,
      Race,
      PhoneHome,
      PhoneBusiness,
      Religion,
      MaritalStatus,
      Ssn,
      LicenseNumber
   ) 
      VALUES(']]
      ..PID[3][1][1].."', '"
      ..PID[5][1][1][1].."', '"
      ..PID[5][1][2].."', '"
      ..PID[10][1][1].."', '"
      ..PID[13][1][1].."', '"
      ..PID[14][1][1].."', '"
      ..PID[17][1].."', '"
      ..PID[16][1].."', '"
      ..PID[19].."', '"
      ..PID[20][1].."'"
      ..")",
   live=true
   }
end