|Subject||Re: [IBO] Confusion about StoredProc vs use of Cursor or Query (was Help with OnCallBack)|
> Looks like I need to play around with these. I could not getI was not aware of any bugs (of this nature) in your older
> 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.
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" workedThe Filter property is implemented through SQL - so it is
> well and quickly to limit the dataset (and setting FILTERED
> true). (Not sure what you had in mind for OnFilterRecord.)
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
- 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 theThe detail would work something like:
> SELECT SP, but was not able to do this easily:
SELECT <your field select>
WHERE ML_ID = :MASTERID
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>
FROM MYSP(:K, :W) A
LEFT OUTER JOIN MyTable B ON B.ML_ID=A.MLID
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 newThere is no point. In your case the entire point of the SP
> SELECT using WHERE ml_id IN (id0001..id1499) OR IN (etc...)
> as suggested recently in the FB support group;
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 theI cant answer this one. I am not aware of any reason for what
> 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.
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 keepsTo file or just back to your client so you can see what it
> 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.
> Any other suggestions? I know it works fine on the clientThis would be my preference. You have at least three steps:
> 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.
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 reallyYep. Its a huge amount of work for a bit of obsurity. It may
> frought with a lot of "gotcha's.")
make you start to wonder if it is worth it. :-)