Subject Re: [IBO] TIB_Cursor & AutoFetchAll
Author Helen Borrie
At 03:05 PM 07-08-02 +0000, you wrote:
>I'm still getting to grips with IBO, so forgive me if this is a dumb

If you ask a question and provide good information about a problem, it's
not a dumb question. :-)

>I'm using a TIB_Cursor to get a list of customers names using the
>code below (Is the code the correct usage?).

> curCustomerNames->Prepare();
> curCustomerNames->Execute(); <-- shouldn't be here
> curCustomerNames->Open(); <---- shouldn't be here
> curCustomerNames->First();
> int nID;
> String sName;
> while( ! curCustomerNames->Eof )
> {
> nID = curCustomerNames->FieldByName("Customer_ID")->AsInteger;
> sName =curCustomerNames->FieldByName("Name")->AsString;
> // Do stuff
> curCustomerNames->Next();
> }
> curCustomerNames->Unprepare();
> curCustomerNames->Close();
> tnMain->Commit();

Initially, I set
AutoFetchAll to true, but the 'curCustomerNames->Eof' line failed
first time through. When AutoFetchAll was set to false, I received
the expected rows. I would have thought that I would get the same
results regardless what AutoFetchAll was set to. Why is this not so?

TIB_Cursor doesn't store the dataset: it fetches the rows one by one,
"one-off", so the "current row" (the one it is looking at) is the only one
in the buffer. If you set AutoFetchAll to true, then once the entire
dataset is fetched, the cursor has had every row as "current row" and is
now sitting at the end of this buffer - a state which is encapsulated as
EOF. Hence, your loop never runs.

To do what you are doing here, you want AutoFetchAll false. Because
TIB_Cursor is unbuffered, it's no good for jobs that need a scrollable
dataset. But, with AutoFetchAll true, a call to First() (absent those
inapplicable calls to Execute and Open) will cause the cursor to refetch
all the rows, never stopping at the first row at the time you want to start
your loop.

If you want to have a look at a real-life example of a TIB_Cursor fetching
rows into a TIB_CursorGrid (a specialised TStringGrid) open the IB_SQL tool
on the Cursor tab and submit a select query to it. When the query
finishes, you will see one row (the last in the set), with the First button
active in the Navbar. Click that and the grid will display all of the rows.

Of course, you wouldn't usually use the client to do the kind of procedure
you are doing here. You would write a stored procedure to perform this
cursor operation (your //do stuff) on the server. In your IBO app, you
would use a TIB_DSQL to accept some parameters and shoot off a SP
call; and get your job done PDQ with no network overhead. TIB_Cursor can
also be used for this both cases, the start-gun is fired by a
call to Execute, not First.