Page Comparison

To write In the Translator, you can write data to a database you can use 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{}. This allows you to execute any ad hoc SQL statement on a database, including UPDATE and INSERT statements. Can also call stored proceduresAs an alternative to conn:query{} for reading data from a database, SELECT queries can also be executed through this method.

By default, SQL statements specified in calls to executed with conn:execute{} are not executed while you are editing the script. They are only executed only run 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) .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.

Code Block

language	lua

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.

Expand

title	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.

Code Block

language	lua

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

Expand

title	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.

Image Added

Versions Compared

Old Version 1

New Version Current

Key