| 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 |