At 11:17 PM 4/09/2007, you wrote:

>I'm using FB1.5 Classic on Windows 2003 server, with a dialect 1
>database.
>
>I have a table that stores notes in a blob subtype 1 column. When
>adding a note from an external source, I'd like to determine whether
>the note already exists so that I don't duplicate it.
>
>The following code results in an error message that simply says
>"Internal Error" when executing the stored procedure:
>
>create procedure edi_add_sales_note (SCHED_SALE_ID integer,
>NOTE_TYPE_ID integer, NOTE blob)
>as
>begin
> if (not exists(select 1
> from sched_note
> where
> sched_sale_id = :sched_sale_id and
> substring(note from 1 for 200) = substring(:note
>from 1 for 200))) then
> insert into sched_note
> (sched_sale_id, note_type_id, note, ins_user_id)
> values
> (:sched_sale_id, :note_type_id, :note, 2000);
>end
>
>It's my understanding that substring can be used on a blob in this
>way, but it does not seem to work within a stored procedure.

It should work, *if* the note being substring-ed actually
exists. You're getting the error where your existence query
encounters a null blob. A null blob is just null, so it won't work
to try to substring it.

To deal with the NULL cases (both input and search) change this SP so
that you've always got two varchars to compare...use dummies to cover
the null cases and make sure your existence test covers *both* the
existence of the record and the IS NOT NULL situation.
-----------------------------
Another way this SP would fail at runtime would be if you were trying
to pass a string in the NOTE parameter. A text blob and a string are
not interchangeable types. You have to pass a blob here; or,
alternatively, change that input parameter to a varchar of 32,765 or
less (for ascii, proportionately less for MBCS). You *can* pass a
string as input to a text blob in your update statement.

./heLen