Subject Re: [firebird-support] Performing LIKE queries on a BLOB
Author Helen Borrie
At 02:21 PM 3/10/2003 -0700, you wrote:
>Are there size limitations on the size of a pattern if Firebird? For
>example, if I have a BLOB with say 500 lines in it, I can search for
>blob_field LIKE "line1%line70" and it works. But I can NOT search for
>"line1%line200". It seems like it has to be a bug with the string
>matching algorithm. I tried different segment sizes on the blob but this
>had no impact. I haven't had a chance to check if there is the same
>limitation of searching within large VARCHAR fields.

I remember Claudio explaining this as occurring when the matched string
spans the boundary of a segment. That is, if the pattern is completely
enclosed in a segment, it will be found; but if it partly in one segment
and partly in another, it won't.

The engine itself doesn't know or care what's in a blob: at storage time,
it's just a parking attendant. At retrieval time it works segment-by-segment.

It might be a solution to match your segment size exactly to your line size
and to make sure that the client application was actually sending segments
in such a way that they were stored as that size. Even so, it would only
work if all lines were the same length and your client application wasn't
messing about with segment sizes. I believe most DAOs optimise the sizes
of blob segments for the wire, which makes the database-declared segment
size irrelevant.

And no, it doesn't affect large VARCHARs, as they aren't stored in segments.

If you decide to experiment, I'd be interested to hear what you come up with.