<< EXECUTE PROCEDURE | FB 2.5 Language Reference | EXIT >>

EXECUTE STATEMENT

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.

back to top of page

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.

back to top of page

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

back to top of page

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

back to top of page

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.

back to top of page

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]

back to top of page

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]

back to top of page

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

• 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 , below.

back to top of page

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 hostname, 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 hostname, 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 allcaps can be equal to CURRENT_USER or CURRENT_ROLE.

back to top of page

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.

back to top of page

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. The argument string cannot contain any parameters. All variable substitution into the static part of the DSQL statement should be performed before EXECUTE STATEMENT is called.
5. 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.

See also:
IBEBlock: EXECUTE STATEMENT
Firebird 2.5 Release Notes: New extensions to EXECUTE STATEMENT

back to top of page
<< EXECUTE PROCEDURE | FB 2.5 Language Reference | EXIT >>