Debian Codegraph @

doc/sql.extensions/README.execute_statement

raw
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