Dynamic SQL Query Construction
- Aryn Wiebe
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.
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'
