Subject Comparing text blobs within a stored procedure - FB1.5
Author Bob Murdoch
I'm using FB1.5 Classic on Windows 2003 server, with a dialect 1

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)
if (not exists(select 1
from sched_note
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)
(:sched_sale_id, :note_type_id, :note, 2000);

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.


Bob M..