Execute Block with Parameters (Revisited)

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for InterBase & Firebird in Delphi and C++Builder
Post Reply
jrodenhi
Posts: 9
Joined: Thu 18 Feb 2010 23:17

Execute Block with Parameters (Revisited)

Post by jrodenhi » Tue 13 May 2014 23:22

I had previously asked (http://forums.devart.com/viewtopic.php?f=26&t=28857) whether I could write a query like this to test the EXECUTE BLOCK functionality in Firebird:

Code: Select all

execute block (smallest int = :Smallest, largest int = :Largest)
returns (number int, square bigint, cube bigint, fourth bigint)
as
begin
    number = smallest;
    while (number <= largest) do
    begin
       square = number * number;
       cube = number * square;
       fourth = number * cube;
       suspend;
       number = number + 1;
    end
end
It works just fine. But for real work, I need something like this to run:

Code: Select all

execute block (hTarget Integer = :hTarget)
returns (sLastName varchar(35))
as
begin
  FOR SELECT e.sLastName
  FROM PR_Employee e
  WHERE e.hEmployer = hTarget
  INTO sLastName
  DO
    suspend;
end
But with this, I get an error message:
"Dynamic SQL Error
SQL error code = -206
Column unknown
HTARGET
At line 8, column 3."
It seems the error happens when you try to reference the block parameter within the code. If you put a colon on the HTARGET parameter within the block, the error changes to "invalid request BLR at offset 65 bad parameter number."
Is there a trick to doing this? Is it possible to use EXECUTE BLOCK like this?
Thank you in advance for any help.

PavloP
Devart Team
Posts: 149
Joined: Fri 24 Jan 2014 12:33

Re: Execute Block with Parameters (Revisited)

Post by PavloP » Fri 16 May 2014 08:30

This is due to the specificity of dbExpress, and we can't affect it. Your sample works correctly in our products, IBDAC and UniDAC.

Post Reply