The EXECUTE STATEMENT preparser does not know about the CALL statement

[sim1984]

Firebird 6.0 added the ability to call stored procedures using a CALL statement.

However, the EXECUTE STATEMENT preparser is not aware of the CALL statement and therefore cannot work with named parameters.

SET TERM ^;

CREATE PROCEDURE SP_CALL_TEST(
  IN_A INT, 
  IN_B INT
) RETURNS (
  OUT_C INT,
  OUT_D INT
)
AS
BEGIN
  OUT_C = IN_A + IN_B;
  OUT_D = IN_A + 1;
END
^

EXECUTE BLOCK
RETURNS (
  OUT_C INT,
  OUT_D INT
)
AS
  DECLARE IN_A INT = 1;
  DECLARE IN_B INT = 2;
BEGIN
  -- OK
  EXECUTE STATEMENT ('EXECUTE PROCEDURE SP_CALL_TEST(?, ?)')
  (:IN_A, :IN_B)
  INTO OUT_C, OUT_D;

  SUSPEND;

  -- OK
  CALL SP_CALL_TEST(:IN_A, :IN_B, :OUT_C, :OUT_D);
  SUSPEND;

  -- OK
  EXECUTE STATEMENT ('CALL SP_CALL_TEST(?, ?, ?, ?)')
  (:IN_A, :IN_B)
  INTO OUT_C, OUT_D;

  SUSPEND;

  -- OK
  EXECUTE STATEMENT ('CALL SP_CALL_TEST(IN_A => ?, IN_B => ?, OUT_C => ?, OUT_D => ?)')
  (:IN_A, :IN_B)
  INTO OUT_C, OUT_D;

  SUSPEND;

  -- OK
  EXECUTE STATEMENT ('EXECUTE PROCEDURE SP_CALL_TEST(:IN_A, :IN_B)')
  (IN_A := :IN_A, IN_B := :IN_B)
  INTO OUT_C, OUT_D;

  SUSPEND;

  -- OK
  EXECUTE PROCEDURE SP_CALL_TEST(IN_A => :IN_A, IN_B => :IN_B)
  RETURNING_VALUES OUT_C, OUT_D;
  SUSPEND;

  -- OK
  EXECUTE STATEMENT ('EXECUTE PROCEDURE SP_CALL_TEST(IN_A => :IN_A, IN_B => :IN_B)')
  (IN_A := :IN_A, IN_B := :IN_B)
  INTO OUT_C, OUT_D;

  SUSPEND;

  -- OK
  CALL SP_CALL_TEST(IN_A => :IN_A, IN_B => :IN_B, OUT_C => :OUT_C, OUT_D => :OUT_D);
  SUSPEND;

  -- Error. The execute statement preparser does not know about the CALL statement,
  -- and therefore cannot work with named parameters.
/*
  EXECUTE STATEMENT ('CALL SP_CALL_TEST(:IN_A, :IN_B, ?, ?)')
  (IN_A := 1, IN_B := 2)
  INTO OUT_C, OUT_D;

  SUSPEND;

  EXECUTE STATEMENT ('CALL SP_CALL_TEST(IN_A => :IN_A, IN_B => :IN_B, OUT_C => ?, OUT_D => ?)')
  (IN_A := 1, IN_B := 2)
  INTO OUT_C, OUT_D;

  SUSPEND;
*/
END
^

SET TERM ;^

[sim1984]

Simple fix in file firebird/src/jrd/extds/ExtDS.cpp in function void Statement::preprocess(const string& sql, string& ret)

passAsIs = !(ident == "INSERT" || ident == "UPDATE" ||  ident == "DELETE" ||
            ident == "MERGE" || ident == "SELECT" || ident == "WITH" || ident == "CALL");

This change could be ported back to earlier versions of Firebird so that they could call EXECUTE STATEMENT ON EXTERNAL as of Firebird 6.0.

[asfernandes]

Simple fix in file firebird/src/jrd/extds/ExtDS.cpp in function void Statement::preprocess(const string& sql, string& ret)

Things are not simple as you see.

First, the preparser rejects ? alone, but no problem with this.

The real problem is that user may do things as call x(output => :name, input => ?) and the parameters will be mapped incorrectly.

[sim1984]

As far as I know in EXECUTE STATEMENT we do not mix named and unnamed parameter options at the same time.

[asfernandes]

As far as I know in EXECUTE STATEMENT we do not mix named and unnamed parameter options at the same time.

But there is no sense in have :name for an output parameter (dsql ?) of call.