Debian Codegraph @

doc/sql.extensions/README.execute_statement2

raw
1 SQL Language Extension: EXECUTE STATEMENT
2
3 Extends already implemented EXECUTE STATEMENT with ability to query
4 external Firebird's databases. Introduced support for input parameters.
5
6 Authors:
7 Vlad Khorsun <[email protected]>
8 Alex Peshkoff <[email protected]>
9
10
11 Syntax and notes :
12
13 [FOR] EXECUTE STATEMENT <query_text> [(<input_parameters>)]
14 [ON EXTERNAL [DATA SOURCE] <connection_string>]
15 [WITH AUTONOMOUS | COMMON TRANSACTION]
16 [AS USER <user_name>]
17 [PASSWORD <password>]
18 [ROLE <role_name>]
19 [WITH CALLER PRIVILEGES]
20 [INTO <variables>]
21
22 - Order of clauses below is not fixed :
23 [ON EXTERNAL [DATA SOURCE] <connection_string>]
24 [WITH AUTONOMOUS TRANSACTION | COMMON TRANSACTION]
25 [AS USER <user_name>]
26 [PASSWORD <password>]
27 [ROLE <role_name>]
28 [WITH CALLER PRIVILEGES]
29
30 - Duplicate clauses are not allowed
31
32 - if you use both <query_text> and <input_parameters> then you must
33 enclose <query_text> into round brackets, for example :
34 EXECUTE STATEMENT (:sql) (p1 := 'abc', p2 := :second_param) ...
35
36 - both named and not named input parameters are supported. Mix of named and not
37 named input parameters in the same statement is not allowed.
38
39 - syntax of named input parameters
40 <input_parameters> ::=
41 <named_parameter>
42 | <input_parameters>, <named_parameter>
43
44 <named_parameter> ::=
45 <parameter name> := <expression>
46
47 Syntax above introduced new parameter value binding operator ":=" to avoid
48 clashes with future boolean expressions.
49 This syntax may be changed in release version.
50
51 - if ON EXTERNAL DATA SOURCE clause is omitted then
52 a) statement will be executed against current (local) database
53 b) if AS USER clause is omitted or <user_name> equal to CURRENT_USER
54 and if ROLE clause is omitted or <role_name> equal to CURRENT_ROLE
55 then the statement is executed in current connection context
56 c) if <user_name> is not equal to CURRENT_USER or <role_name> not equal to CURRENT_ROLE
57 then the statement is executed in separate connection established inside the same
58 engine instance (i.e. created new internal connection without Y-Valve and remote layers).
59
60 - <connection_string> is usual connection string accepted by isc_attach_database,
61 i.e. [<host_name><protocol_delimiter>]database_path.
62
63 - connection to the external data source is made using the same character set as
64 current (local) connection is used.
65
66 - AUTONOMOUS TRANSACTION started new transaction with the same parameters as current
67 transaction. This transaction will be committed if the statement is executed ok or rolled
68 back if the statement is executed with errors.
69
70 - COMMON TRANSACTION
71 a) started new transaction with the same parameters as current transaction, or
72 b) used already started transaction in this connection, or
73 c) used current transaction if current connection is used.
74 This transaction lifetime is bound to the lifetime of current (local) transaction
75 and commits\rolled back the same way as current transaction.
76
77 - by default COMMON TRANSACTION is used
78
79 - if PASSWORD clause is omitted then
80 a) if <user_name> is omitted, NULL or equal to CURRENT_USER value and
81 if <role_name> is omitted, NULL or equal to CURRENT_ROLE value
82 then trusted autentication is performed, and
83 a1) for current connection (ON EXTERNAL DATA SOURCE is omitted) -
84 CURRENT_USER/CURRENT_ROLE is effective user account and role
85 a2) for local database (<connection_string> refers to the current database) -
86 CURRENT_USER/CURRENT_ROLE is effective user account and role
87 a3) for remote database - operating system account under which engine
88 process is currently run is effective user account.
89 b) else isc_dpb_user_name (if <user_name> is present and not empty) and isc_dpb_sql_role_name
90 (if <role_name> is present and not empty) will be filled in DPB and native autentication
91 is performed.
92
93 - if WITH CALLER PRIVILEGES is specified and ON EXTERNAL DATA SOURCE is omitted, then
94 the statement is prepared using additional privileges of caller stored procedure or trigger
95 (if EXECUTE STATEMENT is inside SP\trigger). This causes the same effect as if statement
96 is executed by SP\trigger directly.
97
98 - Exceptions handling
99 a) if ON EXTERNAL DATA SOURCE clause is present then error information is interpreted
100 by the Firebird itself and wrapped into Firebird own error (isc_eds_connection or
101 isc_eds_statement). This is necessary as in general user application can't interprete
102 or understand error codes provided by (unknown) external data source. Text of
103 interpreted remote error contains both error codes and corresponding messages.
104
105 a1) format of isc_eds_connection error :
106 Template string
107 Execute statement error at @1 :\n@2Data source : @3
108 Status-vector tags
109 isc_eds_connection,
110 isc_arg_string, <failed API function name>,
111 isc_arg_string, <text of interpreted external error>,
112 isc_arg_string, <data source name>
113
114 a2) format of isc_eds_statement error :
115 Template string
116 Execute statement error at @1 :\n@2Statement : @3\nData source : @4
117 Status-vector tags
118 isc_eds_statement,
119 isc_arg_string, <failed API function name>,
120 isc_arg_string, <text of interpreted external error>,
121 isc_arg_string, <query>,
122 isc_arg_string, <data source name>
123
124 At PSQL level these errors could be handled using appropriate GDS code, for example
125
126 WHEN GDSCODE eds_statement
127
128 Note, that original error codes are not accessible in WHEN statement. This could be
129 improved in the future.
130
131 b) if ON EXTERNAL DATA SOURCE clause is omitted then original status-vector with
132 error is passed to the caller PSQL code as is. For example, if dynamic statement
133 raised isc_lock_conflict error, this error will be passed to the caller and
134 could be handled using following handler
135
136 WHEN GDSCODE lock_conflict
137
138
139
140 Examples :
141
142 1. insert speed test
143
144 RECREATE TABLE TTT (TRAN INT, CONN INT, ID INT);
145
146 a) direct inserts
147
148 EXECUTE BLOCK AS
149 DECLARE N INT = 100000;
150 BEGIN
151 WHILE (N > 0) DO
152 BEGIN
153 INSERT INTO TTT VALUES (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
154 N = N - 1;
155 END
156 END
157
158
159 b) inserts via prepared dynamic statement using named input parameters
160
161 EXECUTE BLOCK AS
162 DECLARE S VARCHAR(255);
163 DECLARE N INT = 100000;
164 BEGIN
165 S = 'INSERT INTO TTT VALUES (:a, :b, :a)';
166
167 WHILE (N > 0) DO
168 BEGIN
169 EXECUTE STATEMENT (:S) (a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
170 WITH COMMON TRANSACTION;
171 N = N - 1;
172 END
173 END
174
175
176 c) inserts via prepared dynamic statement using not named input parameters
177 EXECUTE BLOCK AS
178 DECLARE S VARCHAR(255);
179 DECLARE N INT = 100000;
180 BEGIN
181 S = 'INSERT INTO TTT VALUES (?, ?, ?)';
182
183 WHILE (N > 0) DO
184 BEGIN
185 EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
186 N = N - 1;
187 END
188 END
189
190
191
192 2. connections and transactions test
193
194 a) Execute this block few times in the same transaction - it will
195 create three new connections to the current database and reuse
196 it in every call. Transactions are also reused.
197
198 EXECUTE BLOCK RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
199 AS
200 DECLARE I INT = 0;
201 DECLARE N INT = 3;
202 DECLARE S VARCHAR(255);
203 BEGIN
204 SELECT A.MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS A
205 WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
206 INTO :S;
207
208 WHILE (i < N) DO
209 BEGIN
210 DB = TRIM(CASE i - 3 * (I / 3) WHEN 0 THEN '\\.\' WHEN 1 THEN 'localhost:' ELSE '' END) || :S;
211
212 FOR EXECUTE STATEMENT 'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION FROM RDB$DATABASE'
213 ON EXTERNAL :DB
214 AS USER CURRENT_USER PASSWORD 'masterkey' -- just for example
215 WITH COMMON TRANSACTION
216 INTO :CONN, :TRAN
217 DO SUSPEND;
218
219 i = i + 1;
220 END
221 END
222
223 b) Execute this block few times in the same transaction - it will
224 create three new connections to the current database on every call.
225
226 EXECUTE BLOCK RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
227 AS
228 DECLARE I INT = 0;
229 DECLARE N INT = 3;
230 DECLARE S VARCHAR(255);
231 BEGIN
232 SELECT A.MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS A
233 WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
234 INTO :S;
235
236 WHILE (i < N) DO
237 BEGIN
238 DB = TRIM(CASE i - 3 * (I / 3) WHEN 0 THEN '\\.\' WHEN 1 THEN 'localhost:' ELSE '' END) || :S;
239
240 FOR EXECUTE STATEMENT 'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION FROM RDB$DATABASE'
241 ON EXTERNAL :DB
242 WITH AUTONOMOUS TRANSACTION -- note autonomous transaction
243 INTO :CONN, :TRAN
244 DO SUSPEND;
245
246 i = i + 1;
247 END
248 END
249
250
251 3. input expressions evaluated only once
252
253 EXECUTE BLOCK RETURNS (A INT, B INT, C INT)
254 AS
255 BEGIN
256 EXECUTE STATEMENT ('SELECT CAST(:X AS INT), CAST(:X AS INT), CAST(:X AS INT) FROM RDB$DATABASE')
257 (x := GEN_ID(G, 1))
258 INTO :A, :B, :C;
259
260 SUSPEND;
261 END