| 1 |
SQL Language Extension: EXECUTE STATEMENT |
| 2 |
|
| 3 |
Implements capability to take a string which is a valid dynamic SQL |
| 4 |
statement and execute it as if it had been submitted to DSQL. |
| 5 |
Available in triggers and stored procedures. |
| 6 |
|
| 7 |
Author: |
| 8 |
Alex Peshkoff <[email protected]> |
| 9 |
|
| 10 |
Syntax may have three forms. |
| 11 |
|
| 12 |
Syntax 1 |
| 13 |
======== |
| 14 |
|
| 15 |
EXECUTE STATEMENT <string>; |
| 16 |
|
| 17 |
Description |
| 18 |
|
| 19 |
Executes <string> as SQL operation. It should not return any data rows. |
| 20 |
Following types of SQL operators may be executed: |
| 21 |
|
| 22 |
* Insert, Delete and Update. |
| 23 |
* Execute Procedure. |
| 24 |
* Any DDL (except Create/Drop Database). |
| 25 |
|
| 26 |
Sample: |
| 27 |
|
| 28 |
CREATE PROCEDURE DynamicSampleOne (Pname VARCHAR(100)) |
| 29 |
AS |
| 30 |
DECLARE VARIABLE Sql VARCHAR(1024); |
| 31 |
DECLARE VARIABLE Par INT; |
| 32 |
|
| 33 |
BEGIN |
| 34 |
SELECT MIN(SomeField) FROM SomeTable INTO :Par; |
| 35 |
Sql = 'EXECUTE PROCEDURE ' || Pname || '('; |
| 36 |
Sql = Sql || CAST(Par AS VARCHAR(20)) || ')'; |
| 37 |
EXECUTE STATEMENT Sql; |
| 38 |
END |
| 39 |
|
| 40 |
|
| 41 |
Syntax 2 |
| 42 |
========= |
| 43 |
|
| 44 |
EXECUTE STATEMENT <string> INTO :var1, ., :varn; |
| 45 |
|
| 46 |
Description |
| 47 |
|
| 48 |
Executes <string> as SQL operation, returning single data row. Only |
| 49 |
singleton SELECT operators may be executed with this form of EXECUTE |
| 50 |
STATEMENT. |
| 51 |
|
| 52 |
Sample: |
| 53 |
|
| 54 |
CREATE PROCEDURE DynamicSampleTwo (TableName VARCHAR(100)) |
| 55 |
AS |
| 56 |
DECLARE VARIABLE Par INT; |
| 57 |
|
| 58 |
BEGIN |
| 59 |
EXECUTE STATEMENT 'SELECT MAX(CheckField) FROM ' || TableName INTO :Par; |
| 60 |
IF (Par > 100) THEN |
| 61 |
EXCEPTION Ex_Overflow 'Overflow in ' || TableName; |
| 62 |
END |
| 63 |
|
| 64 |
|
| 65 |
Syntax 3 |
| 66 |
======== |
| 67 |
|
| 68 |
FOR EXECUTE STATEMENT <string> INTO :var1, ., :varn DO |
| 69 |
<compound-statement>; |
| 70 |
|
| 71 |
Description |
| 72 |
|
| 73 |
Executes <string> as SQL operation, returning multiple data rows. Any SELECT |
| 74 |
operator may be executed with this form of EXECUTE STATEMENT. |
| 75 |
|
| 76 |
Sample: |
| 77 |
|
| 78 |
CREATE PROCEDURE DynamicSampleThree (TextField VARCHAR(100), TableName VARCHAR(100)) |
| 79 |
RETURNING_VALUES (Line VARCHAR(32000)) |
| 80 |
AS |
| 81 |
DECLARE VARIABLE OneLine VARCHAR(100); |
| 82 |
|
| 83 |
BEGIN |
| 84 |
Line = ''; |
| 85 |
FOR EXECUTE STATEMENT 'SELECT ' || TextField || ' FROM ' || TableName |
| 86 |
INTO :OneLine |
| 87 |
DO |
| 88 |
IF (OneLine IS NOT NULL) THEN |
| 89 |
Line = Line || OneLine || ' '; |
| 90 |
SUSPEND; |
| 91 |
END |
| 92 |
|
| 93 |
|
| 94 |
N O T E S |
| 95 |
========= |
| 96 |
|
| 97 |
I. For all forms of EXECUTE STATEMENT SQL, the DSQL string can not contain |
| 98 |
any parameters. All variable substitution into the static part of the SQL |
| 99 |
statement should be performed before EXECUTE STATEMENT. |
| 100 |
|
| 101 |
EXECUTE STATEMENT is potentially dangerous, because: |
| 102 |
|
| 103 |
1. At compile time there is no checking for the correctness of the SQL |
| 104 |
statement. No checking of returned values (in syntax forms 2 & 3 ) |
| 105 |
can be done. |
| 106 |
2. There can be no dependency checks to ensure that objects referred to in |
| 107 |
the SQL statement string are not dropped from the database or modified |
| 108 |
in a manner that would break your statement. For example, a DROP TABLE |
| 109 |
request for the table used in the compiled EXECUTE PROCEDURE statement |
| 110 |
will be granted. |
| 111 |
3. In general, EXECUTE STATEMENT operations are rather slow, because the |
| 112 |
statement to be executed has to be prepared each time it is executed |
| 113 |
by this method. |
| 114 |
|
| 115 |
These don't mean that you should never use this feature. But, please, |
| 116 |
take into account the given facts and apply a rule of thumb to use |
| 117 |
EXECUTE STATEMENT only when other methods are impossible, or perform even |
| 118 |
worse than EXECUTE STATEMENT. |
| 119 |
|
| 120 |
To help (a little) with bugfixing, returned values are strictly checked for |
| 121 |
correct datatype. This helps to avoid some errors where unpredictable |
| 122 |
type-casting would otherwise cause exceptions in some conditions but not |
| 123 |
in others. For example, the string '1234' would convert to an int 1234, |
| 124 |
but 'abc' would give a conversion error. |
| 125 |
|
| 126 |
II. If the stored procedure has special privileges on some objects, the |
| 127 |
dynamic statement submitted in the EXECUTE STATEMENT string does not |
| 128 |
inherit them. Privileges are restricted to those granted to the user who |
| 129 |
is executing the procedure. |
| 130 |
|
| 131 |
III. Even though EXECUTE STATEMENT is available in triggers and stored procedures |
| 132 |
only, it's possible to call it directly from DSQL statements, using EXECUTE BLOCK |
| 133 |
facility. Example: |
| 134 |
|
| 135 |
set term ^; |
| 136 |
execute block returns(i bigint) as |
| 137 |
begin |
| 138 |
execute statement 'select avg(rdb$relation_id) |
| 139 |
from rdb$database' into :i; |
| 140 |
suspend; |
| 141 |
end^ |
| 142 |
set term ;^ |
| 143 |
|
| 144 |
However, for such contrived code, it's better to call EXECUTE BLOCK in a stored |
| 145 |
procedure or to construct the query dynamically in the client side. |
| 146 |
For information on EXECUTE BLOCK, see the README.execute_block document. |
| 147 |
|