Executing SQL Statements

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.

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 you can 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.
The component.live() API to control code execution in test and live.

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.

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'}

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.