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.

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:

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?

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...

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________