Using dynamic SQL inside SQL PL
Comments (2) Visits (16765)
It is very common nowadays for SQL statements to be generated by the application itself for by some productivity middle ware which abstracts the database.
On the server however, within routines, triggers or anonymous blocks most SQL is fixed and will execute just the way the definer of the object has specified it.
There are, however exceptions to this rule:
So there is a need to compose dynamic SQL inside of SQL PL and in this post I'll illuminate the usage and some of the pitfalls.
Thanks to Cuong for bringing up this great question
The most basic way to execute dynamic SQL is the EXECUTE IMMEDIATE statement.
Basic EXECUTE IMMEDIATE
If you provide it with a string it will pass it to DB2's compiler and execute the statement on the spot.
In this case we have presented a constant string. But the string can be an expression as well including bind variables.
So far our examples were doing DDL, but you can also use EXECUTE IMMEDIATE for any other dynamic statement with the exception of queries.
One common scenario for dynamic statements is to force compilation at execution time of the SQL PL because some dependent object does not yet exist when the SQL PL is compiled.
In the following example we insert into a table that does not exist until we execute the block:
In the examples above the entire SQL statement is defined by the composed string.
Any variables we used to compose the string do not appear as variables in the SQL statement.
Instead their values are incorporated into the string.
Let's insert multiple rows into the temperature table above and see what happens
This worked, but was it efficient?
SELECT SUBSTR(STMT_TEXT, 1, 60)
It appears each statement was compiled individually. This is wasteful.
Instead we should recycle the statement and use parameter markers just like we would do from JDBC and ODBC.
EXECUTE IMMEDIATE however does not support variables
To exploit parameter markers we must separate the compilation (PREPARE) from the execution (EXECUTE).
The PREPARE statement will provide us with a handle which can then be used by EXECUTE repeatedly.
DELETE FROM temperature; BEGIN DECLARE i INTEGER DEFAULT 0; DECLARE text VARCHAR(1000); DECLARE stmt STATEMENT; SET text = 'INSERT INTO temperature VALUES(?, ?)'; PREPARE stmt FROM text; WHILE i < 10 DO EXECUTE stmt USING CURRENT DATE - i DAYS, 20 - i; SET i = i + 1; END WHILE; END; / SELECT SUBSTR(STMT_TEXT, 1, 40) AS STMT_TEXT FROM TABL
This time we only got one entry of the statement in the cache.
Note that the USING clause is optional.
If there are no parameters you can still use PREPARE/EXECUTE. You simply don't use the USING clause.
In addition to input parameters there are also statements that return results.
The first three to discuss here are:
VARIABLE sum INTEGER;
Procedure invocation is very similar using a CALL statement.
What it interesting here is that any INOUT parameters need to be specified twice: Once each in the INTO and the USING clause.
CREATE OR REPLACE PROCEDURE add(INOUT arg1 INTEGER, IN arg2 INTEGER, OUT sign INTEGER) BEGIN SET arg1 = arg1 + arg2; SET sign = SIGN(arg2); END; / VARIABLE arg1 INTEGER; VARIABLE sign INTEGER; BEGIN DECLARE arg1 INTEGER DEFAULT 10; DECLARE arg2 INTEGER DEFAULT -3; DECLARE sign INTEGER; DECLARE text VARCHAR(128) DEFAULT 'CALL add(?, ?, ?)'; DECLARE stmt STATEMENT; PREPARE stmt FROM text; EXECUTE stmt INTO arg1, sign USING arg1, arg2; SET (:arg1, :sign) = (arg1, sign); END; / PRINT arg1; 7 PRINT sign; -1
If you prepare a SET statement with a function that contains OUT and INOUT parameters the same rules apply.
An anonymous block works just the same.
CREATE OR REPLACE FUNCTION add(INOUT arg1 INTEGER, IN arg2 INTEGER)
So how does one operate a dynamic cursor?
BEGIN DECLARE text VARCHAR(128); DECLARE cnt, sum INTEGER; DECLARE stmt STATEMENT; DECLARE cur CURSOR FOR stmt; SET text = 'SELECT COUNT(1), SUM(tempC) FROM temperature WHERE tempC < ?'; PREPARE stmt FROM text; OPEN cur USING 15; FETCH cur INTO cnt, sum; CLOSE cur; SET (:cnt, :sum) = (cnt, sum); END; / PRINT cnt; 4 PRINT sum; 50
As you can see, instead of declaring the cursor with a query we declare it with a statement handle.
The PREPARE remains unchanged.
Then, of course, instead of doing and EXECUTE we perform an OPEN, FETCH, CLOSE sequence as is usual for cursors.
Parameters are passed at the OPEN with the USING clause.
Dynamic SQL and string literals
The perhaps most common complication when building dynamic SQL inside of SQL PL is the usage of string literals.
To illustrate let's go all the way back to the first example of the employee table:
CREATE TABLE emp(name VARCHAR(20), salary INTEGER); BEGIN EXECUTE IMMEDIATE 'INSERT INTO emp VALUES ('John', 23000)'; END; / SQL0104N An unexpected token "John" was found following "ERT INTO T VALUES ('". Expected tokens may include: "CONCAT".
What went wrong here?
The single quote before "John" makes the parser believe the string starting with "INSERT" is done.
Now it's looking for a semicolon to finish the statement or perhaps a concat operator to add more pieced to the string.
But really what we want is to produce a string with a single quote inside.
What we need here is an escape sequence.
Single quotes get escaped by doubling them up.
So instead of one single quote we need two single quotes (not one double quote!).
BEGIN EXECUTE IMMEDIATE 'INSERT INTO emp VALUES (''John'', 23000)'; END; / SELECT * FROM emp; NAME SALARY ----
So far so good.
Now let's spin this example a bit further and provide "Jack" via a separate variable:
What's wrong this time?BEGIN DECLARE text VARCHAR(20) DEFAULT 'Jack'; EXECUTE IMMEDIATE 'INSERT INTO emp VALUES (' || text || ', 23000)'; END; / SQL0206N "JACK" is not valid in the context where it is used.
VARIABLE text VARCHAR(40); BEGIN DECLARE text VARCHAR(20) DEFAULT 'Jack'; SET :text = 'INSERT INTO emp VALUES (' || text || ', 23000)'; END; / PRINT text; 'INSERT INTO emp VALUES (Jack, 23000)'
"Jack" is not a string at all!
There are no single quotes here.
Therefore "JACK" must be a variable, parameter, column, ...that's why we get the -206 error.
Again we need to ensure we produce the single quotes.
Typically this is done in the surrounding statement since the name may have been passed in or looked up.
BEGIN DECLARE text VARCHAR(20) DEFAULT 'Jack'; EXECUTE IMMEDIATE 'INSERT INTO emp VALUES (''' || text || ''', 23000)'; END; / SELECT * FROM emp; NAME SALARY ----
You may need to squint to see this on a a tablet.
These are three single-quotes each!
After the round bracket the first two single quotes produce the quote needed for "Jack" to be a string literal.
The third single quotes concludes the "INSERT..." string.
The first single quote after the concat (||) operator starts the next string which immediately uses two more single quotes to conclude "Jack" as a string.
BEGIN DECLARE text VARCHAR(20) DEFAULT 'Jack'; SET :text = 'INSERT INTO emp VALUES (''' || text || ''', 23000)'; END; / PRINT text; 'INSERT INTO emp VALUES ('Jack', 23000)'