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:

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.

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:

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.

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.

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
        

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

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?

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.

    <input_parameters> ::=
      <named_parameter> | <input_parameters>, <named_parameter>

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

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
          

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
          

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

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.

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

  WHEN GDSCODE eds_statement
        

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 lock_conflict
        

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

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
          

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
          

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
        

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