Subject Re: [firebird-support] 'Strange' Where / Blob problem
Author Helen Borrie
At 09:35 PM 25/03/2004 +0000, you wrote:
>I have a table that called search, with a field called ptype (blob,
>subtype 2), and I am trying to select all records which have a blank
>blob -- not null, but blank.
>I have tried the following sql
>SELECT * FROM "search"
> WHERE "ptype" = '';
>But this selects a lot of other records, which the ptype field is
>most definatly not blank.
>SELECT * FROM "search"
> WHERE "ptype" IS NULL;
>This one works normally, selecting null records, but does not select
>those that are empty (not null).
>Tried with FB1 and 1.5, on both Win32 and Linux.

It's not so strange. Blobs aren't strings, they're strings of bits. To the
searcher's eye, they are either null (no blob_id stored) or not null
(blob_id stored).

If the blob is not null and it contains text data, i.e. a string of bytes,
you can make the engine look at it as an array of bytes and ask it to match
a search string (also an array of bytes).

So CONTAINING, STARTING WITH and LIKE can all be used with text blobs --
provided you can construct a search string that is capable of returning a
result. Equality searches can't.

You could try an external function. FreeUDFLib has f_blobSize and
f_blobAsPChar which might help here.

I'm curious about why you're using sub_type 2....what are you storing in it?