Subject Re: [firebird-support] Stored Proc with blob parameter: internal error
Author Helen Borrie
At 09:45 AM 24/08/2004 +0200, you wrote:
> I've experienced an "internal error" when
>I execute a stored procedure that takes
>a blob parameter and inserts it's data
>into a table.
>Here's a test case:
># isql
>SQL> create database "test.fdb";
>SQL> create table test( text blob sub_type 1 );
>SQL> set term ^;
>SQL> create procedure insert_test( text blob sub_type 1 ) as
>CON> begin
>CON> insert into test( text ) values ( :text );
>CON> end^
>SQL> execute procedure insert_test( null )^
>SQL> execute procedure insert_test( 'foo' )^
>Statement failed, SQLCODE = -902
>internal error
>The strange thing is, when I execute it from my application,
>using parameters instead of directly putting the values
>in the statement, it works flawlessly.
>But, unfortunately, I have to call this stored procedure
>from another stored procedure. And here, again, I get the
>internal error.
>Does somebody know a solution to this problem?

Yes: a BLOB is not a string, so you'll get an error if you pass a string
to an SP that is expecting a BLOB. If you can't pass a BLOB every time the
SP is going to be called, then redefine it to take a string as
input. Inside the SP, as in DSQL, you can pass a string as input to a BLOB
column in your DML statements.

When passing things around your nested SPs inside the mother-procedure,
take care to pass BLOBs to BLOBs there, as well. You can invoke a UDF
inside a SP, too, if you want to "cast" a string as a BLOB...