...
The only thing you need to be careful of is to make sure that values coming from external sources are properly escaped. We will use the using conn:quote{} function to do this. The conn:quote{} function quotes all returned strings, so you must not add quotes around strings in SQL query text. Other than this, all you have to do is concatenate your strings to create the stored procedure call - take a look at the strategies for writing dynamic SQL statements.
...
Take the following example stored procedure:
| Code Block | ||
|---|---|---|
| ||
CREATE PROCEDURE GetPatientByLastName(IN LastName TEXT)
BEGIN
SELECT * FROM Patients WHERE LastName = LastName;
END; |
In Iguana, we can execute this stored procedure using conn:execute{}:
| Code Block | ||
|---|---|---|
| ||
-- Prepare the SQL call
local sqlCall = "CALL GetPatientByLastName(" .. conn:quote(lastName) .. ")"
-- Execute the stored procedure
local result = conn:execute{sql=sqlCall, live=true} |
| Info |
|---|
The SQL statement syntax will vary depending on the database you are working with. For example, MySQL would use CALL where as Microsoft SQL Server would use EXEC to execute a stored procedure. It’s important to know the proper syntax for the database you are working with! |