Using string manipulation functions like gsub 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.
| Expand |
|---|
| title | String concatenation can be used for simple SQL statements. |
|---|
|
Simple string concatenation can be used for simple SQL statements. | Code Block |
|---|
local result = conn:query{sql='SELECT * FROM Patients WHERE LastName=' ..conn:quote(lastName)} |
|
| Expand |
|---|
| title | 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. | Code Block |
|---|
| 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:  |
| Expand |
|---|
| title | gsub string.format can be used to replace specific placeholders to dynamically insert values. |
|---|
|
In this example, gsub 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. | Code Block |
|---|
| local queryString = "SELECT * FROM Patients WHERE LastName = $LASTNAME%s AND Gender = $GENDER%s"
local lastName = conn:quote("O'Reilly")
local gender = conn:quote("Male")
-- Use gsubstring.format to replace the placeholders with actual values
local query = queryString:gsubformat("%$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'
 Image Removed Image Added |