Subject Re: [firebird-support] Re: Converting A Blob To A String Within A SP
Author Helen Borrie
At 11:39 PM 8/11/2005 -0200, you wrote:
>martinthrelly wrote:
> >thanks for the quick reply.
> >
> >does this mean that if my blob contains text which is more than 1024
> >characters long then i will lose the rest of it? if so whats the max
> >varchar size i can use?
> >
>If you use from 1 for 1024 just the first 1024 will be returned.
>The Max size of a varchar is 32767 IIRC.

32765. Varchar uses 2 bytes for length attributes.

>Never did it, maybe it works, but you could do something like this:
>s1 = Substring(MyBlob from 1 for 32767);

for 32765 should work.

>s1 = Substring(MyBlob from 32768 for 65535);

Won't work. "for 65535" says you want to return a string of max. 65535
bytes, which will overflow a varchar hugely.

>> and what if the blob size is still bigger than that?

Then don't try to use it as a string at all. You haven't indicated why you
want to do this.....but you might like to make a loop in your SP that
retrieves successive substrings into *separate* varchar variables and
returns a (necessarily limited) number of output fields in the return
values, then have your application code reassemble them somehow. (Which
seems totally pointless, since it's a heck of a lot easier just to stream
the whole blob across to a single buffer and have the application handle
the blob as a whole!)

Also, as a rule of thumb, you must treat a blob as though it were read-only
- Firebird never updates blobs. When a record containing a blob is
updated, and the blob column is named in the update spec, the old version
of the blob is destroyed and a completely new version created. It's not
possible to do an equivalence check between the old.blob and the new.blob
in Fb 1.5 and below without resorting to a UDF.