Firebird Documentation Index → Firebird 2.5 Language Ref. Update → PSQL statements → EXECUTE STATEMENT
Firebird Home Firebird Home Prev: EXECUTE PROCEDUREFirebird Documentation IndexUp: PSQL statementsNext: EXIT

EXECUTE STATEMENT

Table of Contents

No data returned
One row of data returned
Any number of data rows returned
Improved performance
WITH {AUTONOMOUS|COMMON} TRANSACTION
WITH CALLER PRIVILEGES
ON EXTERNAL [DATA SOURCE]
AS USER, PASSWORD and ROLE
Parameterized statements
Caveats with EXECUTE STATEMENT

Available in: PSQL

Added in: 1.5

Changed in: 2.5

Description: EXECUTE STATEMENT takes a 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.

Syntax (full): 

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

No data returned

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.

One row of data returned

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

Any number of data rows returned

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

Improved performance

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.

WITH {AUTONOMOUS|COMMON} TRANSACTION

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]

WITH CALLER PRIVILEGES

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]

ON EXTERNAL [DATA SOURCE]

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: 

  • External connections made by statements WITH COMMON TRANSACTION (the default) will remain open until the current transaction ends. They can be reused by subsequent calls to EXECUTE STATEMENT, but only if the connect string is exactly the same, including case.

  • External connections made by statements WITH AUTONOMOUS TRANSACTION are closed as soon as the statement has been executed.

  • Notice that statements WITH AUTONOMOUS TRANSACTION can and will reuse connections that were opened earlier by statements WITH COMMON TRANSACTION. If this happens, the reused connection will be left open after the statement has been executed. (It must be, because it has at least one uncommitted transaction!)

Transaction pooling: 

  • If WITH COMMON TRANSACTION is in effect, transactions will be reused as much as possible. They will be committed or rolled back together with the current transaction.

  • If WITH AUTONOMOUS TRANSACTION is specified, a fresh transaction will always be started for the statement. This transaction will be committed or rolled back immediately after the statement's execution.

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

  • The character set used for the external connection is the same as that for the current connection.

  • Two-phase commits are not supported.

  • For authentication details, please look under AS USER, PASSWORD and ROLE :: Authentication, below.

AS USER, PASSWORD and ROLE

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.

  • If ON EXTERNAL is present, a new connection is always opened, and:

    • If at least one of AS USER, PASSWORD and ROLE is present, native authentication is attempted with the given parameter values (locally or remotely, depending on the connect string). No defaults are used for missing parameters.

    • If all three are absent and the connect string contains no host name, then the new connection is established on the local host with the same user and role as the current connection. The term 'local' means 'on the same machine as the server' here. This is not necessarily the location of the client.

    • If all three are absent and the connect string contains a host name, then trusted authentication is attempted on the remote host (again, remote from the POV of the server). If this succeeds, the remote OS will provide the user name (usually the OS account under which the Firebird process runs).

  • If ON EXTERNAL is absent:

    • If at least one of AS USER, PASSWORD and ROLE is present, a new connection to the current database is opened with the given parameter values. No defaults are used for missing parameters.

    • If all three are absent, the statement is executed within the current connection.

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 all-caps can be equal tot CURRENT_USER or CURRENT_ROLE.

Parameterized statements

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:

  • When a statement has parameters, it must be placed in parentheses when EXECUTE STATEMENT is called, regardless whether it is given directly as a string, as a variable name, or by another expression.

  • Named parameters must be preceded by a colon (“:”) in the statement itself, but not in the parameter assignments.

  • Each named parameter may occur several times in the statement, but only once in the assignments.

  • Each named parameter must be assigned a value when EXECUTE STATEMENT is called; the assignments can be placed in any order.

  • The assignment operator for named parameters is “:=”, not “=” like in SQL.

  • With positional parameters, the number of values supplied must exactly equal the number of parameters (question marks) in the statement.

Caveats with EXECUTE STATEMENT

  1. There is no way to validate the syntax of the enclosed statement.

  2. There are no dependency checks to discover whether tables or columns have been dropped.

  3. Even though the performance in loops has been significantly improved in Firebird 2.5, execution is still considerably slower than that of statements given directly.

  4. Return values are strictly checked for data type in order to avoid unpredictable type-casting exceptions. For example, the string '1234' would convert to an integer, 1234, but 'abc' would give a conversion error.

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.

Prev: EXECUTE PROCEDUREFirebird Documentation IndexUp: PSQL statementsNext: EXIT
Firebird Documentation Index → Firebird 2.5 Language Ref. Update → PSQL statements → EXECUTE STATEMENT