Subject Re: [IBO] Help on TIB_StoredProc
Author Helen Borrie
At 05:38 PM 17/10/2005 +0000, you wrote:
>Please help.
>I have a SP on my database and it looks this way:
>as input parameters (date1(DATE), date2(DATE),person_id(INTEGER))
>as output parameters it must return
>RETURNS (
> ID INTEGER,
> DATAX DATE,
> NR_ACT VARCHAR(16),
> SOLD_FSAI NUMERIC(15,4),
> INCASARE NUMERIC(15,4),
> PLATA NUMERIC(15,4),
> SOLD_FSA NUMERIC(15,2))
>
>ok, the number of rows returned by the procedure could be between 3
>and a few hundreds. I need to load the output into a grid and after
>print it.
>I use a TIB_CURSOR to execute the procedure, like this (from runtime):
>======
>cursor.SQL.Clear;
>cursor.SQL.Add(select * from calc_sold_fsa(param1,param2,param3));
>------
>
>the problem is that in my grid only the first row is loaded. It's the
>first time when I use SP this way and if someone have time for me,
>please gimme some ideeas.

OK, as Christian said, you will need to use IB_Query if you want the output
set to be in the scrollable IB_Grid. You *can* output an ib_cursor to an
IB_CursorGrid, though this is nothing much more than a visible container in
which to buffer the results.

IB_Cursor by design returns rows one at a time. You "open" it by calling
First and you access subsequent rows by calling Next while not EOF.

There may be a problem in your SP. It needs to have been written as a
selectable SP, viz.

begin
...
for select
ID, DATAX. NR_ACT, SOLD_FSAI, INCASARE,
PLATA, SOLD_FSA
from aTable
where DATAX between :date1 and :date2
and ID = :person_id
into :ID, :DATAX. :NR_ACT, :SOLD_FSAI, :INCASARE,
:PLATA, :SOLD_FSA
DO BEGIN
/* massage the data if you need to */
SUSPEND;
END
END

This will put the first output row into a buffer at the server and wait
until the application, i.e. your ib_cursor, calls First (to fetch the first
row). Then it will output the next row and wait for your ib_cursor to call
Next.

Helen


Helen