Firebird Documentation IndexFirebird 2.5 Release NotesProcedural SQL (PSQL) → New Extensions to EXECUTE STATEMENT
Firebird Home Firebird Home Prev: Borrow Database Column Type for a PSQL VariableFirebird Documentation IndexUp: Procedural SQL (PSQL)Next: Other PSQL Improvements

New Extensions to EXECUTE STATEMENT

Context Issues
External Queries from PSQL
EXECUTE STATEMENT with Dynamic Parameters
Exception Handling
Examples Using EXECUTE STATEMENT

Unusually for our release notes, we begin this chapter with the full, newly extended syntax for the EXECUTE STATEMENT statement in PSQL and move on afterwards to explain the various new features and their usage.

[FOR] EXECUTE STATEMENT <query_text> [(<input_parameters>)]
    [ON EXTERNAL [DATA SOURCE] <connection_string>]
    [WITH {AUTONOMOUS | COMMON} TRANSACTION]
    [AS USER <user_name>]
    [PASSWORD <password>]
    [ROLE <role_name>]
    [WITH CALLER PRIVILEGES]
    [INTO <variables>]
    

Note

The order of the optional clauses is not fixed so, for example, a statement based on the following model would be just as valid:

    [ON EXTERNAL [DATA SOURCE] <connection_string>]
    [WITH {AUTONOMOUS | COMMON} TRANSACTION]
    [AS USER <user_name>]
    [PASSWORD <password>]
    [ROLE <role_name>]
    [WITH CALLER PRIVILEGES]
      

Clauses cannot be duplicated.

Context Issues

If there is no ON EXTERNAL DATA SOURCE clause present, EXECUTE STATEMENT is normally executed within the CURRENT_CONNECTION context. This will be the case if the AS USER clause is omitted, or it is present with its <user_name> argument equal to CURRENT_USER.

However, if <user_name> is not equal to CURRENT_USER, then the statement is executed in a separate connection, established without Y-Valve and remote layers, inside the same engine instance.

Note

In the absence of an AS USER <user_name> clause, CURRENT_USER is the default.

Authentication

Where server authentication is needed for a connection that is different to CURRENT_CONNECTION, e.g., for executing an EXECUTE STATEMENT command on an external datasource, the AS USER and PASSWORD clauses are required. However, under some conditions, the PASSWORD may be omitted and the effects will be as follows:

  1. On Windows, for the CURRENT_CONNECTION (i.e., no external data source), trusted authentication will be performed if it is active and the AS USER parameter is missing, null or equal to CURRENT_USER.

  2. If the external data source parameter is present and its <connection_string> refers to the same database as the CURRENT_CONNECTION, the effective user account will be that of the CURRENT_USER.

  3. If the external data source parameter is present and its <connection_string> refers to a different database than the one CURRENT_CONNECTION is attached to, the effective user account will be the operating system account under which the Firebird process is currently running.

In any other case where the PASSWORD clause is missing, only isc_dpb_user_name will be presented in the DPB (attachment parameters) and native authentication will be attempted.

Transaction Behaviour

The new syntax has an optional clause for setting the appropriate transaction behaviour: WITH AUTONOMOUS TRANSACTION and WITH COMMON TRANSACTION. WITH COMMON TRANSACTION is the default and does not need to be specified. Transaction lifetimes are bound to the lifetime of CURRENT_TRANSACTION and are committed or rolled back in accordance with the CURRENT_TRANSACTION.

The behaviour for WITH COMMON TRANSACTION is as follows:

  1. Causes any transaction in an external data source to be started with the same parameters as CURRENT_TRANSACTION; otherwise

  2. Executes the statement inside the CURRENT_TRANSACTION; or

  3. May use another transaction that is started internally in CURRENT_CONNECTION.

The WITH AUTONOMOUS TRANSACTION setting starts a new transaction with the same parameters as CURRENT_TRANSACTION. That transaction will be committed if the statement is executed without exceptions or rolled back if the statement encounters an error.

Inherited Access Privileges

Vladyslav Khorsun

Tracker reference CORE-1928.

By design, the original implementation of EXECUTE STATEMENT isolated the executable code from the access privileges of the calling stored procedure or trigger, falling back to the privileges available to the CURRENT_USER. In general, the strategy is wise, since it reduces the vulnerability inherent in providing for the execution of arbitrary statements. However, in hardened environments, or where privacy is not an issue, it could present a limitation.

The introduction of the optional clause WITH CALLER PRIVILEGES now makes it possible to have the executable statement inherit the access privileges of the calling stored procedure or trigger. The statement is prepared using any additional privileges that apply to the calling stored procedure or trigger. The effect is the same as if the statement were executed by the stored procedure or trigger directly.

Important

The WITH CALLER PRIVILEGES option is not compatible with the ON EXTERNAL DATA SOURCE option.

External Queries from PSQL

Vladyslav Khorsun

Tracker reference CORE-1853.

EXECUTE STATEMENT now supports queries against external databases by inclusion of the ON EXTERNAL DATA SOURCE clause with its <connection_string> argument.

The <connection_string> Argument

The format of <connection_string> is the usual one that is passed through the API function isc_attach_database(), viz.

   [<host_name><protocol_delimiter>]database_path
        

Character Set

The connection to the external data source uses the same character set as is being used by the CURRENT_CONNECTION context.

Access Privileges

If the external data source is on another server then the clauses AS USER <user_name> and PASSWORD <password> will be needed.

The clause WITH CALLER PRIVILEGES is a no-op if the external data source is on another server.

MORE INFORMATION REQUIRED. ROLES?

Note

Use of a two-phase transaction for the external connection is not available in V.2.5.

EXECUTE STATEMENT with Dynamic Parameters

Vladyslav Khorsun

Alex Peshkov

Tracker reference CORE-1221.

The new extensions provide the ability to prepare a statement with dynamic input parameters (placeholders) in a manner similar to a parameterised DSQL statement. The actual text of the query itself can also be passed as a parameter.

Syntax Conventions

The mechanism employs some conventions to facilitate the run-time parsing and to allow the option of “naming” parameters in a style comparable with the way some popular client wrapper layers, such as Delphi, handle DSQL parameters. The API's own convention, of passing unnamed parameters in a predefined order, is also supported. However, named and unnamed parameters cannot be mixed.

The New Binding Operator

At this point in the implementation of the dynamic parameter feature, to avoid clashes with equivalence tests, it was necessary to introduce a new assignment operator for binding run-time values to named parameters. The new operator mimics the Pascal assignment operator:“:=”.

Syntax for Defining Parameters
    <input_parameters> ::=
      <named_parameter> | <input_parameters>, <named_parameter>

    <named_parameter> ::=
      <parameter name> := <expression>
          

Example for named input parameters

For example, the following block of PSQL defines both <query_text> and named <input_parameters> (<named_parameter>):

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
  BEGIN
  /* Normal PSQL string assignment of <query_text> */
    S = 'INSERT INTO TTT VALUES (:a, :b, :a)';

    WHILE (N > 0) DO
    BEGIN
    /* Each loop execution applies both the string value
       and the values to be bound to the input parameters */

      EXECUTE STATEMENT (:S) (a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
      WITH COMMON TRANSACTION;
      N = N - 1;
    END
  END
          

Example for unnamed input parameters

A similar block using a set of unnamed input parameters instead and passing constant arguments directly:

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
  BEGIN
    S = 'INSERT INTO TTT VALUES (?, ?, ?)';

    WHILE (N > 0) DO
    BEGIN
      EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
      N = N - 1;
    END
  END
          

Note

Observe that, if you use both <query_text> and <input_parameters> then the <query_text> must be enclosed in parentheses, viz.

    EXECUTE STATEMENT (:sql) (p1 := 'abc', p2 := :second_param) ...
            

Exception Handling

The handling of exceptions depends on whether the ON EXTERNAL DATA SOURCE is present.

ON EXTERNAL DATA SOURCE clause is present

If ON EXTERNAL DATA SOURCE clause is present, Firebird cannot interpret error codes supplied by the unknown data source so it interprets the error information itself and wraps it as a string into its own error wrapper (isc_eds_connection or isc_eds_statement).

The text of the interpreted remote error contains both error codes and corresponding messages.

  1. Format of isc_eds_connection error

    Template string
      Execute statement error at @1 :\[email protected] source : @3
    Status-vector tags
      isc_eds_connection,
      isc_arg_string, <failed API function name>,
      isc_arg_string, <text of interpreted external error>,
      isc_arg_string, <data source name>
                
  2. Format of isc_eds_statement error

    Template string
      Execute statement error at @1 :\[email protected] : @3\nData source : @4
    Status-vector tags
      isc_eds_statement,
      isc_arg_string, <failed API function name>,
      isc_arg_string, <text of interpreted external error>,
      isc_arg_string, <query>,
      isc_arg_string, <data source name>
                

At PSQL level the symbols for these errors can be handled by treating them like any other gdscode. For example

  WHEN GDSCODE isc_eds_statement
        

Note

Currently, the originating error codes are not accessible in a WHEN statement. The situation could be improved in future.

ON EXTERNAL DATA SOURCE clause is not present

If ON EXTERNAL DATA SOURCE clause is not present, the original status-vector with the error is passed as-is to the caller PSQL code.

For example, if a dynamic statement were to raise the isc_lock_conflict exception, the exception would be passed to the caller and could be handled using the usual handler:

  WHEN GDSCODE isc_lock_conflict
        

Examples Using EXECUTE STATEMENT

The following examples offer a sampler of ways that the EXECUTE STATEMENT extensions might be applied in your applications.

Test Connections and Transactions

A couple of tests you can try to compare variations in settings:

Test a) :Execute this block few times in the same transaction - it will create three new connections to the current database and reuse it in every call. Transactions are also reused.

EXECUTE BLOCK
  RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
  DECLARE I INT = 0;
  DECLARE N INT = 3;
  DECLARE S VARCHAR(255);
BEGIN
  SELECT A.MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS A
   WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
    INTO :S;

  WHILE (i < N) DO
  BEGIN
    DB = TRIM(CASE i - 3 * (I / 3)
      WHEN 0 THEN '\\.\' WHEN 1 THEN 'localhost:' ELSE '' END) || :S;

    FOR EXECUTE STATEMENT
      'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION
      FROM RDB$DATABASE'
      ON EXTERNAL :DB
      AS USER CURRENT_USER PASSWORD 'masterkey' -- just for example
      WITH COMMON TRANSACTION
      INTO :CONN, :TRAN
    DO SUSPEND;

    i = i + 1;
  END
END
          

Test b) : Execute this block few times in the same transaction - it will create three new connections to the current database on every call.

EXECUTE BLOCK
  RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
  DECLARE I INT = 0;
  DECLARE N INT = 3;
  DECLARE S VARCHAR(255);
BEGIN
  SELECT A.MON$ATTACHMENT_NAME
    FROM MON$ATTACHMENTS A
  WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
   INTO :S;

  WHILE (i < N) DO
  BEGIN
    DB = TRIM(CASE i - 3 * (I / 3)
      WHEN 0 THEN '\\.\'
      WHEN 1 THEN 'localhost:'
      ELSE '' END) || :S;

    FOR EXECUTE STATEMENT
    'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION FROM RDB$DATABASE'
      ON EXTERNAL :DB
      WITH AUTONOMOUS TRANSACTION -- note autonomous transaction
      INTO :CONN, :TRAN
    DO SUSPEND;

    i = i + 1;
  END
END
          

Input Evaluation Demo

Demonstrating that input expressions evaluated only once:

EXECUTE BLOCK
  RETURNS (A INT, B INT, C INT)
AS
BEGIN
  EXECUTE STATEMENT (
    'SELECT CAST(:X AS INT),
            CAST(:X AS INT),
            CAST(:X AS INT)
       FROM RDB$DATABASE')
      (x := GEN_ID(G, 1))
    INTO :A, :B, :C;

  SUSPEND;
END
        

Insert Speed Test

Recycling our earlier examples for input parameter usage for comparison with the non-parameterised form of EXECUTE STATEMENT:

RECREATE TABLE TTT (
  TRAN INT,
  CONN INT,
  ID INT);

-- Direct inserts:

EXECUTE BLOCK AS
  DECLARE N INT = 100000;
BEGIN
  WHILE (N > 0) DO
  BEGIN
    INSERT INTO TTT VALUES (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
    N = N - 1;
  END
END

-- Inserts via prepared dynamic statement
-- using named input parameters:

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
BEGIN
  S = 'INSERT INTO TTT VALUES (:a, :b, :a)';

  WHILE (N > 0) DO
  BEGIN
    EXECUTE STATEMENT (:S)
      (a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
    WITH COMMON TRANSACTION;
    N = N - 1;
  END
END

-- Inserts via prepared dynamic statement
-- using unnamed input parameters:

EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
  S = 'INSERT INTO TTT VALUES (?, ?, ?)';

  WHILE (N > 0) DO
  BEGIN
    EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
    N = N - 1;
  END
END
        
Prev: Borrow Database Column Type for a PSQL VariableFirebird Documentation IndexUp: Procedural SQL (PSQL)Next: Other PSQL Improvements
Firebird Documentation IndexFirebird 2.5 Release NotesProcedural SQL (PSQL) → New Extensions to EXECUTE STATEMENT