Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

In the Translator, you can write data to a database by executing SQL statements using conn:execute{} or by calling stored procedures. When writing to a database, you can perform INSERT, UPDATE, or DELETE operations with conn:execute{}. As an alternative to conn:query{} for reading data from a database, SELECT queries can also be executed through this method.

local values = {'John', 'Smith', '1980-01-01', 'Male'} 
local T = {} 
for i, v in ipairs(values) do 
  T[i] = conn:quote(v) -- safely quote each value
end 

local sqlInsert = 'INSERT INTO Patients (FirstName, LastName, DOB, Gender) VALUES ('..table.concat(T, ', ')..')' 
-- Execute SQL Insert   
local result = conn:execute{sql=sqlInsert, live=true}

In the example above conn:quote() is used to safely quote string and date values and table.concat() is used to concatenate the quoted values for the SQL statement. See Writing SQL Statements for additional tips on structuring SQL statements.

By default, SQL statements executed with conn:execute{} are only run when the component is executed outside the Translator’s editing mode. To override this behavior and execute the statement during script editing, use the Live Flag to set live=true. However, use this cautiously – each script change or use of sample data will trigger the SQL execution, potentially leading to multiple unintended INSERTs or UPDATEs.

 Greater Control over Handling Transactions

Unlike conn:merge{}, which automatically handles transactions, conn:execute{} does not manage transactions automatically. This gives you greater flexibility for more complex operations, allowing you to manually start, commit, or rollback transactions, which is useful when grouping multiple SQL operations that should succeed or fail together. 

conn:execute{sql='BEGIN TRANSACTION'}
conn:execute{sql=sqlInsert}
conn:execute{sql='COMMIT'}
 Handling Database Errors

conn:execute{} can also throw a Lua error if a database error occurs. The error thrown is a table with two values:

  • message: a string with the description of the error.

  • code: an integer error code returned by the database.

pcall() can be used to perform a protected call, capturing any returned database errors allowing you to appropriately handle errors according to your needs. In this example, we are using pcall to catch and log the error message.

  • No labels