--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

>
> 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?
> >
> > >
> >Yes...
> >
> >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.
>
> ./heLen
>
>
> ./heLen
>

hello helen. let me describe what i want to do with a simplified
example. sorry if this is a bit long but hopefully you will get the
gist of what i am trying to do.

lets say i have a table called Clients with 3 columns as such:

ID integer
Name varchar(32)
Notes Blob

so i insert a row into the table like this

ID: 1
Name: 'John Smith'
Notes: 'Keen chess player...etc'

so now i have inserted this row i want to take each word and
populate a SEARCH_WORDS table in order to facilitate a full text
search.

so from my insert trigger i call an SP which makes a long blob
consisting of all the words in my searchable fields. in this
case 'Name' and 'Notes. like this:

declare variable KEYWORDS BLOB SUB_TYPE 1 SEGMENT SIZE 80;
keywords = :Name || ' ' || :Notes;

so now i have a new blob containing all my keywords. now in order to
populate my SEARCH_WORDS table i must seperate this keywords blob
into individual WORDS of type varchar(32). so i send this new blob
off to a second SP called SP_BLOB_SPLITTER.

so i do somthing like this:

for select WORDS from SP_BLOB_SPLITTER (:keywords) into word
do begin
insert into SEARCH_WORDS(:word); ...etc...

in order to achieve this my SP_BLOB_SPLITTER must therefore accept a
blob and somehow return a list of words within this blob deliminated
by a ' '. i have achieved this with an input parameter of varchar
(32000) by iterating over each character. but how can i do this with
a blob? how can i iterate over each character within a blob without
first making a varchar. and how can i make a varchar when the blob
might be too big? this is my problem.