7.6.10. EXECUTE STATEMENT

Used for

Executing dynamically created SQL statements

Available in

PSQL

Syntax

<execute_statement> ::= EXECUTE STATEMENT <argument>
  [<option> …]
  [INTO <variables>]
<argument> ::= <paramless_stmt>
            | (<paramless_stmt>)
            | (<stmt_with_params>) (<param_values>)
<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>] {filepath | db_alias}
<hostspec> ::= <tcpip_hostspec> | <NamedPipes_hostspec>
<tcpip_hostspec> ::= hostname[/port]:
<NamePipes_hostspec> ::= \\hostname\
<variables> ::= [:]varname [, [:]varname ...]

The statement EXECUTE STATEMENT takes a string parameter and executes it as if it were a DSQL statement. If the statement returns data, it can be passed to local variables by way of an INTO clause.

Parameterized Statements

You can use parameters — either named or positional — in the DSQL statement string. Each parameter must be assigned a value.

Special Rules for Parameterized Statements

1. Named and positional parameters cannot be mixed in one query

2. If the statement has parameters, they must be enclosed in parentheses when EXECUTE STATEMENT is called, regardless of whether they come directly as strings, as variable names or as expressions

3. Each named parameter must be prefixed by a colon (‘:’) in the statement string itself, but not when the parameter is assigned a value

4. Positional parameters must be assigned their values in the same order as they appear in the query text

5. The assignment operator for parameters is the special operator “:=”, similar to the assignment operator in Pascal

6. Each named parameter can be used in the statement more than once, but its value must be assigned only once

7. With positional parameters, the number of assigned values must match the number of parameter placeholders (question marks) in the statement exactly

Examples of EXECUTE STATEMENT with parameters

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

WITH {AUTONOMOUS | COMMON} TRANSACTION

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.

WITH CALLER PRIVILEGES

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

ON EXTERNAL [DATA SOURCE]

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 [DATA SOURCE] clause is considered absent and the statement is executed against the current database.

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 re-use 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 un-committed 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 exceptions cannot be caught in the usual way. 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.

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

AS USER, PASSWORD and ROLE

The optional AS USER, PASSWORD and ROLE clauses allow specificaton of which user will execute the SQL statement and with which role. The method of user log-in and whether a separate connection is open depend on the presence and values of the ON EXTERNAL [DATA SOURCE], AS USER, PASSWORD and ROLE clauses:

• 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 perspective of the server). If this succeeds, the remote operating system will provide the user name (usually the operating system 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 suppled parameter values. No defaults are used for missing parameters

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

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 when the same statements are launched 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 caveats into account. If you can achieve the same result with PSQL and/or DSQL, it will almost always be preferable.

See also

FOR EXECUTE STATEMENT