Helen

Thanks for the reply - hope all is well with you

> At 12:30 PM 11-07-01 +0100, you wrote:
> >I have carried out some simple tests on this problem and am even more
> >perplexed.
> >
> >I have an IB_Cursor with a transaction set to autocommit.
> >
> >I have a table 'do_header' which has a primary key 'do_no' and a blob
> >sub_type 1 'delivery_address'.
> >
> >if I
> >
> >procedure TForm1.Button1Click(Sender: TObject);
> >begin
> >with ibc do
> > begin
> > SQL.Text:='Insert into do_header(do_no,delivery_address)values(1,:da);
> > ParamByName('da').Assign(memo1.Lines);
> > ExecSQL;
> > end;
> >end;
> >
> >This works fine.
> >
> >if I create a stored procedure
> >
> >create procedure testda
> >(dono integer,
> >da blob sub_type 1
> >)returns(
> >mess varchar(30))
> >as
> >begin
> >insert into do_header(do_no,delivery_address)values(:dono,:da);
> >mess='OK';
> >suspend;
> >end
> >
> >and then
> >
> >procedure TForm1.Button1Click(Sender: TObject);
> >begin
> >with ibc do
> > begin
> > sql.text:='select * from testda(4,:da)';
> > ParamByName('da').Assign(memo1.Lines);
> > Open;
> > label1.caption:=fields[0].asstring; // this returns 'OK'
> > end;
> >end;
> >
> >it creates the new record with do_no=4 but the delivery address is empty

:-(

> >
> >Have I missed something simple here, am I going loopy or is there a

problem?

>
> Er..um..yes, at the time you are looking at this output set, of course the

transaction is not committed. All your SUSPEND call is doing here is
returning the output parameter which only tells you that a row has been
processed.

>

It does return my Mess value which I use as a simple way to trap errors in
SPs.

> Also, you are trying to SELECT your procedure, when you should be

EXECUTING it. Move it into a TIB_DSQL or a TIB_StoredProc. With a TIB_DSQL
use this SQL:

It's a select because the sp has a suspend and it returns a value. Isn't
there a problem with selects and dsql and prepares?

>
> EXECUTE PROCEDURE testda(4;:da)
>
> However ::::::::::::::: DIRE WARNING ::::::::::::::::::::: 'tis not for

naught that the manual (erroneously?) tells you SPs won't take blobs as
input arguments. As everyone knows, they can, right?

>

Well - it's worked for years via the BDE :-)

> Ann Harrison recently confirmed a serious bug with blobs passed into

Stored Procs and her strong advice was DON'T DO IT. Somehow the engine
mixes up blob ids in an unpredictable way and can corrupt your data.

>
> I believe Claudio is digging into this one at the moment for Firebird so

he will no doubt clarify the situation next time he does his weekly round of
the lists...

>

SO - now I know what the problem is I have worked round it as follows

I have changed the sp parameter to varchar(255) and set
ParamByname('da').asString =memo1.Text;
Then install the external function f_strblob from freeibudflib by the
wonderful Greg Deatz.
In the sp - set up a blob varaible and use f_strblob to convert the string
back to a blob - hey presto it works.

Of course I can only do this because I'm confident that a delivery address <
255 characters.

Hope you're not cringing ;-)

Regards
Paul