Searching for text in BLOBs

2004-08-11 09:34:19 PM
Hi everyone,
My question is how to efficiently search the BLOB (text) fields of
selected records. I am using D5 with Paradox tables.
I have a form where a user enters data to be stored in a record. The
form includes half-a-dozen RichEdit boxes (actually, Raize Components
tRzRichEdit boxes, in case that might make a difference):
var RE : array[1..6] of tRzRichEdit;
I store the contents of the RE's as BLOBS:
for I := 1 to NumBoxes do begin
B := tBlobStream.create(tBlobField(T.fieldByName(Fld[I])),bmWrite);
And read them back with this code when I redisplay a record:
for I := 1 to NumBoxes do begin
B := tBlobStream.create(tBlobField(T.fieldByName(Fld,bmRead);
RE[I].clear; { in case there is no RTF data }
if B.size>0 then RE[I].lines.loadFromStream(B);
So far, so good. Now my client want the ability to search among the
records for a text string. In all cases the search would be in a subset of
the records--each record falls under a user-specified type, and the user
would only search among records of a single type.
Since I cannot search for text in a BLOB from within an SQL statement, I
assume I will need to (a) issue a Query to get a list of all records that
match the user's desired record type, then (b) open each such record, save
each BLOB into a (non-visible) RichEdit, perform a FindText, and add that
record's ID to a list if the search text is found in any of the BLOBs.
Is the above procedure reasonable, or is there some other technique that
would be more efficient? Most users would have no more than 10 or 20 record
types, with anywhere from a zero to several hundred records per type.
Many thanks,
-- Stan