Dynamic SQL Query Construction

Using string manipulation functions like gsub 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.

String concatenation can be used for simple SQL statements.

Simple string concatenation can be used for simple SQL statements.

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

table.concat can be used to concatenate multiple values from a table into a SQL statement.

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.

gsub can be used to replace specific placeholders to dynamically insert values.

In this example, gsub is used to replace placeholders (like %s) 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 = $LASTNAME AND Gender = $GENDER" 
local lastName = conn:quote("O'Reilly")
local gender = conn:quote("Male")

-- Use gsub to replace the placeholders with actual values  
local query = queryString:gsub("%$LASTNAME", lastName):gsub("%$GENDER", gender)   
local result = conn:query{sql=query}

The query will look like:

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