I have detected a strange behaviour: a specific query that calls to
a stored procedure fails if the parameters are assigned in run-time,
but it works if the parameters are assigned manually or if some
parameters are reordered.


When I execute my program, in the IB_MonitorDialog1 I see:

select sum((select days from crossdays(? /* mindate
*/ ,s.indate))) from orders s where s.indate<=? /*
maxdate */
...
[MAXDATE] = '31 may 2004'
[MINDATE] = '01 may 2003' ]
...
FETCH
STMT_HANDLE = 12218732
FIELDS = [ Version 1 SQLd 1 SQLn 1
SUM = <NULL> ]

The query is ok, the parameters are passed ok (MAXDATE and MINDATE),
but the result is wrong (SUM = <NULL>), the correct result should be
2.

Now, if for example I use IBOConsole and I enter the above query and
the above parameters, the result is given as ok. I would expect the
same result in the 2 ways. Maybe I am skipping something, so I would
be grateful if somebody could give me a clue.

Thanks in advance


The simplified query is:

select sum((select days from crossdays(:mindate,s.indate))) from
orders s where s.indate<=:maxdate
------

The simplified stored procedure:

CREATE PROCEDURE "CROSSDAYS"
(
"LIMIT0" DATE,
"INDATE" DATE
)
RETURNS
(
"DAYS" INTEGER
)
AS
begin
days=1;
suspend;
end
^
------
The simplified table:

CREATE TABLE "ORDERS"
(
"INDATE" DATE NOT NULL,
"OUTDATE" DATE,
CONSTRAINT "PK_ORDERS_1" PRIMARY KEY ("INDATE")
);

------

Source C++:

qryTest->Close();
qryTest->SQL->Text="select sum((select days from \
crossdays(:mindate,s.indate))) \
from orders s \
where s.indate<=:maxdate";

qryTest->Prepare();
qryTest->FindParam("mindate")->AsDateTime=TDateTime(2003,5,1);
qryTest->FindParam("maxdate")->AsDateTime=TDateTime(2004,5,31);
qryTest->Open();

ShowMessage(qryTest->Fields->Fields[0]->AsString);
-----

Environment:

Firebird 1.5
Builder C++6
IBObjects