Define variable and run a query using EXECUTE BLOCK

Question

I have a large query in Firebird (which I run using FlameRobin), using a parameter all over the place, but getting the query below to run will do:

SELECT * FROM customers WHERE customerid = 1234;

I want to define 1234 as a variable, say customerID, so that I can easily replace it with something else.

I've learned that I need to put this inside a EXECUTE BLOCK.

EXECUTE BLOCK 
AS
DECLARE customerID INT = 1234;

BEGIN
  SELECT * FROM customers WHERE customerid = :customerID
END

If of any importance, the error I am getting is Engine Message : Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 3, column 26

Seidel

Answer 1

The problem is that FlameRobin needs to know when a statement ends and the next statement starts. By default it uses the semicolon (;) for this. However an EXECUTE BLOCK is essentially a stored procedure that isn't stored in the database, so it contains PSQL code that also uses the semicolon as a statement separator.

The consequence of this is that you get syntax errors because FlameRobin is sending incomplete statements to the server (that is: it is sending a statement after each ; it encounters).

You need to instruct FlameRobin to use a different statement terminator using SET TERM. Other Firebird query tools (eg isql) require this as well, but it is not actually part of the syntax of Firebird itself!

So you need to execute your code as:

-- Instruct flamerobin to use # as the terminator
SET TERM #;
EXECUTE BLOCK 
AS
DECLARE customerID INT = 1234;

BEGIN
  SELECT * FROM customers WHERE customerid = :customerID;
END#
-- Restore terminator to ;
SET TERM ;#

However doing this will still result in an error, because this query is invalid for PSQL: A SELECT in a PSQL block requires an INTO clause to map the columns to variables. And to get the values out of the EXECUTE BLOCK returned to FlameRobin you also need to specify a RETURNS clause as described in the documentation of EXECUTE BLOCK:

-- Instruct flamerobin to use # as the terminator
SET TERM #;
EXECUTE BLOCK 
   RETURNS (col1 INTEGER, col2 VARCHAR(100))
AS
DECLARE customerID INT = 1234;

BEGIN
  SELECT col1, col2 FROM customers WHERE customerid = :customerID INTO :col1, :col2;
  SUSPEND;
END#
-- Restore terminator to ;
SET TERM ;#

As far as I know the SUSPEND is technically not required here, but Flamerobin will not fetch the returned row if it isn't included.

However the above will not work if the select produces multiple rows. For that you need to use FOR SELECT ... DO combined with a SUSPEND:

-- Instruct flamerobin to use # as the terminator
SET TERM #;
EXECUTE BLOCK 
   RETURNS (col1 INTEGER, col2 VARCHAR(100))
AS
DECLARE customerID INT = 1234;

BEGIN
  FOR SELECT col1, col2 FROM customers WHERE customerid = :customerID INTO :col1, :col2
  DO
     SUSPEND;
END#
-- Restore terminator to ;
SET TERM ;#

The SUSPEND here returns the row and waits until the caller has fetched that row and then continues with the FOR loop. This way it will iterate over the results.

IMHO this too much effort for parameterization. You might want to consider simply not parametrizing when you use FlameRobin, or use a tool that supports asking for parameter values for the normal parameter placeholders of Firebird (but to be honest I am not sure if there are any).

Appolonia

Your samples work perfectly. Thanks for your response. It's indeed too much effort do implement it, I would have to change lots of queries. But at least I know I am knowing at the wrong door. – Seidel

Answer 2

Well you need a input and return value, ex:

EXECUTE BLOCK (p_customerID integer=?)
returns(col_1 type of column table.id_cv)
as
begin
  for
    select col_1 from table
    where customerID=:p_customerID
    into col_1 do
     suspend;
end

Delphi as client side:

myquery1.ParamByName('p_customerID ').Value:=1234;
myquery1.Open()
if not myquery1.IsEmpty then
begin
  // using returning values
  if myquery1.FieldbyName('col_1').AsString='this' then DoThat(); 
end;

Use this way ever yout need input parameters and/or returning values;

Clift