Dynamic SQL Query Construction

Using string manipulation functions like string.format or table.concat combined with escaping characters using conn:quote() can be used to build efficient, dynamic SQL statements.

These techniques can be used when reading or writing to a database using SQL statements in the Translator.

Simple string concatenation can be used for simple SQL statements.

local result = conn:query{sql='SELECT * FROM Patients WHERE LastName=' ..conn:quote(lastName)}

In this example, each value is escaped and then concatenated into the SQL statement.

local values = {'John', "O'Reilly", '1980-01-01', 'Male'} 
-- Loop through table of values
local T = {} 
for i, v in ipairs(values) do 
   T[i] = conn:quote(v) 
end 
   
-- Execute SQL Insert
local sqlInsert = 'INSERT INTO Patients (FirstName, LastName, DOB, Gender) VALUES ('..table.concat(T, ', ')..')' 
local results = conn:execute{sql=sqlInsert, live=true}

In the Annotations you can see:

The for loop using conn:quote to handle special characters.
The result of the SQL execution.

In this example, string.format() is used to replace placeholders (like %sfor string or %d for ) in a SQL query template with user-provided values - useful when you want to insert dynamic content into a pre-defined template.

local queryString = "SELECT * FROM Patients WHERE LastName = %s AND Gender = %s" 
local lastName = conn:quote("O'Reilly")
local gender = conn:quote("Male")

-- Use string.format to replace the placeholders with actual values 
local query = queryString:format(lastName, gender) 
local result = conn:query{sql=query}

The query will look like:

SELECT * FROM Patients WHERE LastName = 'O''Reilly' AND Gender = 'Male'