Hi, Geoff:

Thank you again for your response.

Let me back up a little, since you may have come into this series of posts after
my initial one in which I wrote out more of my code and settings.

A couple of months ago you and I had an exchange about encrypting a table. After
that discussion I decided that I need obscurity, not so much full encryption.

After seriously thinking about it, I came to the realization that for my project
(a database treeview used for a medical based expert system) a simple UDF would
work best. I settled on XOR type of obscurity because encryption adds length to
the string and I still wanted to do a search without necessarily creating full
text search capabilities.

As long as I'm searching from the first character on, incremental search works
just fine in XOR. About 90% of the expected searches will use this simple
incremental search. And its fast. But for any search where the user is trying to
find a word/phrase within the tree item name string or in the stored
documentation in each record (a text blob), then things get more complicated.

As far as I am aware, I need to decrypt everything to see if the substring
exists in the encrypted table.

But using the UDF to simply call:

SELECT MyUDF(EncryptedField, key) from myTable

takes a fair amount of time and from the user's point of view, looks like the
application (a desktop app using FB embedded) has frozen, which is not
acceptable. I thought the OnCallBack would be the way to go to give the user
some visual indicator via a progress bar that the program is processing his
request.

Well, after some back and forth on this topic, plus searching the archives, it
looked like I need a Selectable SP with SUSPEND.

This is my SP: (taken from IB_Expert)


SET TERM ^ ;

CREATE PROCEDURE GETALL_ML (
K INTEGER)
RETURNS (
MLUPITEM VARCHAR(128),
MLITEM VARCHAR(128),
MLID INTEGER,
REC_COUNT INTEGER,
FETCH_COUNT INTEGER)
AS
begin
/* Procedure Text */

select Count(ml.ml_id) from Masterlibrary ml INTO Rec_Count;

Fetch_Count = 0;

for select m.ml_id, MyUDF(m.ml_item,:K) ,
MyUDF(m.ml_upper_item,:K) as Upper_Item
from MasterLibrary m
Order by 3
INTO
:MLID,
:MLITEM,
:MLUPITEM
do begin
Fetch_COUNT = Fetch_COUNT + 1; //Helen felt I needed this here
suspend;
end
end
^


SET TERM ; ^

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

(for all these tests so far, it has been simply "SELECT * from MYSP")

The result set is used to populate a Infopower grid that I already have been
using for search results from the unencrypted table.

The OnCallBack simply does the following based on a separate email with another
IBO user who has had no problems with using progress bars in this manner:

procedure TForm1.qryCallBack1Callback(ADataset: TDataSet;
Status: TIB_CallbackStatus; CursorRowNum: Integer; var Abort: Boolean);
var
Rows : integer;

begin
if Status in [csRefresh, csFinal] then
begin
Rows := ADataset.FieldByName('fetch_count').AsInteger;
fcProgressBar1.Progress := Trunc(( CursorRowNum /Rows)*100);
end;
end;

Now, the above is the code given to me, but I would think that Rows should be:
ADataset.FieldByName('Rec_Count').AsInteger.

But right now that is the least of my problems, since nothing is happening in
this event as I've already mentioned.

My calling code for a test button is just the following:

procedure TForm1.Button1Click(Sender: TObject);
begin
with qryCallBack1 do
begin
label1.Visible := false;

Prepare;
ParambyName('k').AsInteger := 1234;// a test number acting as the key

//BeginCallBackFetching; I tried using this but it made no difference.
Open;
//EndCallBackFetching;

label1.Visible := true;//just says "SQL Finished"
end;
end;


At this point I tried AutoFetchAll := False/True and AutoFetchFirst :=
True/False;

Nothing changes with firing the OnCallBack event.

Not sure if you saw Jason's reply which stated that perhaps the server during
prepare is actually getting the entire dataset, so that there never is a
Fetching.

I tried using just the FB embedded rather than the server and it made no
difference--the person that suggested the SP with SUSPEND, mentioned he was
using FB embedded and this approach successfully. The dataset is 41,000 records
(before the SQL WHERE clause).

Well, as you can see I'm confused, but really need some way of doing this search
(and not turning it into such a big deal), letting the user know that everything
is all right and in just a few more seconds he'll get his search result.

Geoff (or anyone else) do you have any suggestion?

Thank you for your patience and time,

Chuck Belanger