Subject Re: [IBO] Confusion about StoredProc vs use of Cursor or Query (was Help with OnCallBack)
Author Geoff Worboys
> Looks like I need to play around with these. I could not get
> the OnCallBack to fire ever, except as you said when I put a
> IB_Grid on the test form. Then almost whenever I clicked on
> it it would fire; like scrolling.

I was not aware of any bugs (of this nature) in your older
version of IBO, but if you cannot at least get one or two hits
from the OnCallback in my example (without a grid) then I start
to wonder if it may be time to progress to the newer version
of IBO so at least we can eliminate some possibilities.

> Simply adding to the FILTER property "ml_id <> 0" worked
> well and quickly to limit the dataset (and setting FILTERED
> true). (Not sure what you had in mind for OnFilterRecord.)

The Filter property is implemented through SQL - so it is
likely to send you back to the same result as using the
WHERE clause. That is; this filtering is done at the server.

OnFilterRecord allows you to attach a filtering process to
the fetching of rows at the client. The rows remain fetched
but they are marked as filtered allowing you to avoid
showing them on the interface. (Assuming Filtered = true).

HOWEVER, looking more closely at the IBO code this sort of
filtering will also prevent the callbacks from occuring. So
that will not solve your problem. You appear to be stuck with
one of...

- doing it all at the server and living with the delay

- returning a potentially large result set of which most
are not required (non-matches) but which should generate
callbacks to give user feedback

The best choice depends on lots of factors. Indeed the entire
discussion about callbacks could be redundant if you are not
going to use data-aware controls (like IB_Grid or whatever).
Because if that were the case then you could use an IB_Cursor
to go through the result set and pick out just the ones you
want, generating your own user feedback from your own loop.

> Tried a number of things to make a detail dataset from the
> SELECT SP, but was not able to do this easily:

The detail would work something like:

DetailQuery.SQL =
SELECT <your field select>
FROM MyTable

DetailQuery.MasterSource = <your MYSP query datasource>
DetailQuery.MasterParamLinks = MASTERID=MYSP.MLID

Notice that in this arrangement the detail will only ever show
one row - the row corresponding to the currently selected row
in the master. Or no rows if the master represents a non-match
rows. As you scroll the master you can pick the detailed
information from the detail dataset.

Another possibility is to try and do it all in one dataset
using a join...

SELECT <whatever fields you need>

If you use the previous example of MYSP (that returns a result
for all rows, with MLID as NULL on no matches) then this will
return lots of rows, most of which will have null in the field
instances relating to MyTable because the A.MLID will be NULL
where there are no matches.

I am not certain, but you may need MYSP to return two copies
of the MLID field. One that always contains the correct
MyTable record ID that can be used as the key field for the
dataset, and one that is null on non-match that can be used
in the join shown in the above statement.

Not sure exactly how this joined arrangement will operate from
a performance perspective.

> 4. I could parse out the results of the SELECT SP into a new
> SELECT using WHERE ml_id IN (id0001..id1499) OR IN (etc...)
> as suggested recently in the FB support group;

There is no point. In your case the entire point of the SP
returning lots of rows was to give the client some sort of
feedback that things were happening. Doing convoluted things
in SQL means doing them at the server which means you are back
where you were - just more complicated and redundant.

(And IMHO you should avoid using IN(...) in where clauses if
you can, there be nasty things buried in there.)

> When using f_substr (from FreeUDFLib and when looking at the
> Delphi code, its just POS( ) function ) to see if a phrase or
> string is in a decrypted field (after using MY_Decrypt_UDF) ,
> this function only seems to work for 1 or 2 characters. If a
> full word, it does not show a hit (a return of > -1). It
> works just fine on a fully unencrypted table.

I cant answer this one. I am not aware of any reason for what
you describe, but then I do not use FreeUDFLib (I have my own).
Could there be character set issues here?

> Is there some kind of optimization occuring in FB that keeps
> this function from working as expected? I'm thinking that I
> could output all the decrypted ML_UPPER_ITEM to a file and
> peruse that and see.

To file or just back to your client so you can see what it

> Any other suggestions? I know it works fine on the client
> side to check this with an equivalent function. Would it be
> best to create another UDF which does the decryption and
> seeing if the passed string is in the decrypted field?
> i.e. keep all that two stepped process in the DLL rather
> than call two functions like I've been doing.

This would be my preference. You have at least three steps:

For large blobs you maybe able to optimise by combining all
these steps into a single loop in the udf:

while have more stuff
do a case-insensitive find
if found a match
keep some of this chunk for search with next chunk

Note the last step is necessary because you may have a match
on the overlap between two chunks that you have read.

And for largish text (non-blob) fields it may also be more
efficient to do a case insensitive find inside a variation
of your decrypt UDF. (ie. Only doing decrypt/case-fold for
as many characters as necessary.)

The benefit depends on whether the search is likely to be
found or not. If a match is unlikely then there is little
benefit (but done well there should be minimal cost).

> (Whew! this entire encryption-decryption thing is really
> frought with a lot of "gotcha's.")

Yep. Its a huge amount of work for a bit of obsurity. It may
make you start to wonder if it is worth it. :-)

Geoff Worboys
Telesis Computing