firebird procedural query throwing "token unknown" error at "SET TERM #;"

Question

I am quering a firebird 2.5 database using php (ibase). Simple queries are working fine, but when I try to use the following procedural query I get a "token unknown" error at line 1, column 5 - ie when "TERM" is encountered. I feel I am missing something very basic here!

$query = <<<X
SET TERM #;
EXECUTE BLOCK 
   RETURNS (product INT, minPrice FLOAT, maxPrice FLOAT)
AS
DECLARE transID INT = 8733;

BEGIN
  FOR
    SELECT "Product", MIN("CurrencyRate" * "UnitPrice"), MAX("CurrencyRate" * "UnitPrice")
    FROM "CustomerStockInDetail"
    HAVING "Product" = :transID
    INTO :product, :minPrice, :maxPrice
  DO
     SUSPEND;
END#
SET TERM ;#
X;

Answer 1

The statement SET TERM is not part of the syntax of Firebird itself. It is part of the ISQL syntax, and other Firebird query tools (eg FlameRobin) follow its example.

SET TERM instructs the query tool when a statement ends (by default they use the semi-colon (;)). When the query tool reads a statement terminator the query tool knows the statement is complete and it can be sent to the server. However stored procedures (and execute block) also use semi-colons to end statements. In that case the query tool needs a different terminator, hence SET TERM.

However when communicating with Firebird through the API, you can only send individual complete statements. So there is no need for statement terminators and therefor Firebird itself doesn't have the concept of statement terminators, except in PSQL (procedural language).

Long story short, remove SET TERM and change your code to:

$query = <<<X
EXECUTE BLOCK 
   RETURNS (product INT, minPrice FLOAT, maxPrice FLOAT)
AS
DECLARE transID INT = 8733;

BEGIN
  FOR
    SELECT "Product", MIN("CurrencyRate" * "UnitPrice"), MAX("CurrencyRate" * "UnitPrice")
    FROM "CustomerStockInDetail"
    HAVING "Product" = :transID
    INTO :product, :minPrice, :maxPrice
  DO
     SUSPEND;
END
X;

Also note the absence of a terminator after the last END.