Subject Re: [IBO] Confusion about StoredProc vs use of Cursor or Query (was Help with OnCallBack)
Author Geoff Worboys
<snip lots of stuff that does make sense and goes a long way
to explaining how we got to this spot - given that I missed
lots of the earlier posts, thanks>

> I'm using a IBOQuery for the Select SP as:
> SELECT * from MYSP(:K) where f_substr(UPPER(<word/phrase to check in string>),
Upper_Item) >> -1

Anything that is specified in the select statement happens at
the server, so you never get to see anything about the
(potentially) thousands of things that the server is doing.

In your above example all the client gets to see are the
results coming out of MYSP that passed the WHERE clause.

I guessing that what Helen probably had in mind with the
fetch_count idea is that you would call MYSP from the client
without any where clause - so you actually get to see the
details of every row. You would then do some sort of filter
or tag process to identify the matching rows - at the client.

It is also possible that Helen may have thought you could do
something like this...

K INTEGER, -- the decryption key
W VARCHAR(128) -- the word you are searching for
MLID INTEGER, -- the matched record id (or 0)
REC_COUNT INTEGER, -- the total possible records
FETCH_COUNT INTEGER -- the number have processed
select Count(ml.ml_id) from Masterlibrary ml INTO Rec_Count;
Fetch_Count = 0;

for select m.ml_id, MyUDF(m.ml_upper_item, :K)
from MasterLibrary m
Order by 3
do begin
Fetch_COUNT = Fetch_COUNT + 1;
if (f_substr(UPPER(W), MLUPITEM) > -1) THEN
MLID = NULL; -- mark it as not matched

Now when you call this procedure you get a return row for every
row that is searched, whether it matched or not. You can tell
if the row was a match by whether MLID was null or not.

Because you get rows returned for each search you will actually
get fetching events from which to drive the callback.

I intentionally returned just the key, not the data fields.
You dont want to waste time and bandwidth returning strings or
blobs for rows you dont want.

You can use the return MLID as an input parameter to extract
the full row details from another dataset. BUT... this is not
going to be elegant if you are using data-aware components like
IB_Grid etc. It is strictly a solution for driving by code and
feeding to some other control, so I am not sure if it is what
you need.

You MAY be able to use TIB_Query's OnFilterRecord event to
keep all rows returned by MYSP and only display matches - and
then extract the interesting stuff through a detail dataset,
or through a LEFT OUTER join.

The main point is that, in order to give a user feedback, you
must get a response from the server. Without using threads
the only way to do it in this sort of situation is to ensure
that a query will in fact return something to indicate what it
is doing.


Geoff Worboys
Telesis Computing