Available in: PSQL
Added in: 1.5
Changed in: 2.5
Description
EXECUTE STATEMENT takes a single string argument and executes it as if it had been submitted as a DSQL statement. If the statement returns data, the INTO clause assigns these to local variables. If the statement may return more than one row of data, the “FOR … DO” form must be used to create a loop.
<execute-statement> ::= EXECUTE STATEMENT <argument> [<option> ...] [INTO <variables>] <looped-version> ::= FOR <execute-statement> DO <psql-statement> <argument> ::= paramless-stmt | (paramless-stmt) | (<stmt-with-params>) (<param-values>) <stmt-with-params> ::= A statement containing one or more parameters, in one of these forms: - named: ':' + paramname, e.g. :a, :b, :size - positional: each param is designated by '?' Named and positional parameters may not be mixed. <param-values> ::= <named-values> | <positional-values> <named-values> ::= paramname := value-expr [, paramname := value-expr ...] <positional-values> ::= value-expr [, value-expr ...] <option> ::= WITH {AUTONOMOUS|COMMON} TRANSACTION | WITH CALLER PRIVILEGES | AS USER user | PASSWORD password | ROLE role | ON EXTERNAL [DATA SOURCE] <connect-string> <connect-string> ::= [<hostspec>]path-or-alias <hostspec> ::= <tcpip-hostspec> | <netbeui-hostspec> <tcpip-hostspec> ::= hostname: <netbeui-hostspec> ::= \\hostname <variables> ::= [:]varname [, [:]varname ...] <psql-statement> ::= A simple or compound PSQL statement.
NOTICE:
paramless-stmt, <stmt-with-params>, user, password, role and <connect-string> are string expressions. When given directly, i.e. as literal strings, they must be enclosed in single-quote characters.
The following paragraphs first explain the basic usage of EXECUTE STATEMENT as it has been since Firebird 1.5. After that, the new features in 2.5 are introduced.
This form is used with INSERT, UPDATE, DELETE and EXECUTE PROCEDURE statements that return no data.
Syntax (partial)
EXECUTE STATEMENT <statement> <statement> ::= An SQL statement returning no data.
Example
create procedure DynamicSampleOne (ProcName varchar(100)) as declare variable stmt varchar(1024); declare variable param int; begin select min(SomeField) from SomeTable into param; stmt = 'execute procedure ' || ProcName || '(' || cast(param as varchar(20)) || ')'; execute statement stmt; end
Warning: Although this form of EXECUTE STATEMENT can also be used with all kinds of DDL strings (except CREATE/DROP DATABASE), it is generally very, very unwise to use this trick in order to circumvent the no-DDL rule in PSQL.
This form is used with singleton SELECT statements.
Syntax (partial)
EXECUTE STATEMENT <select-statement> INTO <var> [, <var> ...] <select-statement> ::= An SQL statement returning at most one row of data. <var> ::= A PSQL variable, optionally preceded by “:”
Example
create procedure DynamicSampleTwo (TableName varchar(100)) as declare variable param int; begin execute statement 'select max(CheckField) from ' || TableName into :param; if (param > 100) then exception Ex_Overflow 'Overflow in ' || TableName; end
This form – analogous to FOR SELECT … DO – is used with SELECT statements that may return a multi-row dataset.
Syntax (partial)
FOR EXECUTE STATEMENT <select-statement> INTO <var> [, <var> ...] DO <psql-statement> <select-statement> ::= Any SELECT statement. <var> ::= A PSQL variable, optionally preceded by “:” <psql-statement> ::= A simple or compound PSQL statement.
Example
create procedure DynamicSampleThree ( TextField varchar(100), TableName varchar(100) ) returns ( LongLine varchar(32000) ) as declare variable Chunk varchar(100); begin Chunk = ''; for execute statement 'select ' || TextField || ' from ' || TableName into :Chunk do if (Chunk is not null) then LongLine = LongLine || Chunk || ' '; suspend; end
Changed in: 2.5
Description
In previous versions, if EXECUTE STATEMENT occurred in a loop, the SQL statement would be prepared, executed and released upon every iteration. In Firebird 2.5 and above, such a statement is only prepared once, giving a huge performance benefit.
Added in: 2.5
Description
Traditionally, the executed SQL statement always ran within the current transaction, and this is still the default. WITH AUTONOMOUS TRANSACTION causes a separate transaction to be started, with the same parameters as the current transaction. It will be committed if the statement runs to completion without errors and rolled back otherwise. WITH COMMON TRANSACTION uses the current transaction if possible. If the statement must run in a separate connection, an already started transaction within that connection is used, if available. Otherwise, a new transaction is started with the same parameters as the current transaction. Any new transactions started under the “COMMON” regime are committed or rolled back with the current transaction.
Syntax (partial)
[FOR] EXECUTE STATEMENT sql-statement WITH {AUTONOMOUS|COMMON} TRANSACTION [...other options...] [INTO <variables>] [DO psql-statement]
Added in: 2.5
Description
By default, the SQL statement is executed with the privileges of the current user. Specifying WITH CALLER PRIVILEGES adds to this the privileges of the calling SP or trigger, just as if the statement were executed directly by the routine. WITH CALLER PRIVILEGES has no effect if the ON EXTERNAL clause is also present.
Syntax (partial)
[FOR] EXECUTE STATEMENT sql-statement WITH CALLER PRIVILEGES [...other options...] [INTO <variables>] [DO psql-statement]
Added in: 2.5
Description
With ON EXTERNAL DATA SOURCE, the SQL statement is executed in a separate connection to the same or another database, possibly even on another server. If the connect string is NULL or ' ' (empty string), the entire ON EXTERNAL clause is considered absent and the statement is executed against the current database.
Syntax (partial)
[FOR] EXECUTE STATEMENT sql-statement ON EXTERNAL [DATA SOURCE] <connect-string> [AS USER user] [PASSWORD password] [ROLE role] [...other options...] [INTO <variables>] [DO psql-statement] <connect-string> ::= [<hostspec>]path-or-alias <hostspec> ::= <tcpip-hostspec> | <netbeui-hostspec> <tcpip-hostspec> ::= hostname: <netbeui-hostspec> ::= \\hostname
NOTICE:
sql-statement, user, password, role and <connect-string> are string expressions. When given directly, i.e. as literal strings, they must be enclosed in single-quote characters.
Connection pooling:
Transaction pooling:
Exception handling: When ON EXTERNAL is used, the extra connection is always made via a so-called external provider, even if the connection is to the current database. One of the consequences is that you can't catch exceptions the way you are used to. Every exception caused by the statement is wrapped in either an eds_connection or an eds_statement error. In order to catch them in your PSQL code, you have to use vWHEN GDSCODE eds_connection, WHEN GDSCODE eds_statement or WHEN ANY. (Without ON EXTERNAL@@, exceptions are caught in the usual way, even if an extra connection is made to the current database.)
Miscellaneous notes:
Added in: 2.5
Description
Optionally, a user name, password and/or role can be specified under which the statement must be executed.
Syntax (partial)
[FOR] EXECUTE STATEMENT sql-statement AS USER user PASSWORD password ROLE role [...other options...] [INTO <variables>] [DO psql-statement]
NOTICE:
sql-statement, user, password and role are string expressions. When given directly, i.e. as literal strings, they must be enclosed in single-quote characters.
Authentication: How a user is authenticated and whether a separate connection is opened depends on the presence and values of the parameters ON EXTERNAL [DATA SOURCE], AS USER, PASSWORD and ROLE.
Notice: If a parameter value is NULL or ' ' (empty string), the entire parameter is considered absent. Additionally, AS USER is considered absent if its value is equal to CURRENT_USER, and ROLE if it's equal to CURRENT_ROLE. The comparison is made case-sensitively; in most cases this means that only user and role names given in allcaps can be equal to CURRENT_USER or CURRENT_ROLE.
Added in: 2.5
Description
Since Firebird 2.5, the SQL statement to be executed may contain parameters. When [FOR] EXECUTE STATEMENT is called, a value must be provided for each parameter.
Syntax (partial)
[FOR] EXECUTE STATEMENT (<parameterized-statement>) (<param-assignments>) [...options...] [INTO <variables>] [DO psql-statement] <parameterized-statement> ::= An SQL statement containing <named-param>s or <positional-param>s <named-param> ::= :paramname <positional-param> ::= ? <param-assignments> ::= <named-assignments> | <positional-assignments> <named-assignments> ::= paramname := value [, paramname := value ...] <positional-assignments> ::= value [, value ...]
NOTICE:
<parameterized-statement> is a string expression. When given directly, i.e. as a literal string, it must be enclosed in single-quote characters.
Examples
With named parameters:
... declare license_num varchar(15); declare connect_string varchar(100); declare stmt varchar(100) = 'select license from cars where driver = :driver and location = :loc'; begin ... select connstr from databases where cust_id = :id into connect_string; ... for select id from drivers into current_driver do begin for select location from driver_locations where driver_id = :current_driver into current_location do begin ... execute statement (stmt) (driver := current_driver, loc := current_location) on external connect_string into license_num; ...
The same code with positional parameters:
... declare license_num varchar(15); declare connect_string varchar(100); declare stmt varchar(100) = 'select license from cars where driver = ? and location = ?'; begin ... select connstr from databases where cust_id = :id into connect_string; ... for select id from drivers into current_driver do begin for select location from driver_locations where driver_id = :current_driver into current_location do begin ... execute statement (stmt) (current_driver, current_location) on external connect_string into license_num; ...
Notes: Some things to be aware of:
All in all, this feature is meant to be used very cautiously and you should always take the above factors into account. If you can achieve the same result with PSQL and/or DSQL, then this is nearly always preferable.