Subject Re: Converting A Blob To A String Within A SP
Author martinthrelly
--- In, Helen Borrie <helebor@t...>
> 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
> > >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
> >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.
> 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
> retrieves successive substrings into *separate* varchar variables
> returns a (necessarily limited) number of output fields in the
> values, then have your application code reassemble them somehow.
> seems totally pointless, since it's a heck of a lot easier just to
> the whole blob across to a single buffer and have the application
> the blob as a whole!)
> Also, as a rule of thumb, you must treat a blob as though it were
> - Firebird never updates blobs. When a record containing a blob
> updated, and the blob column is named in the update spec, the old
> 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
> 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

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:

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.