Subject Re: [firebird-support] Execute Statement fails on application but Ok in IBExpert
Author Helen Borrie
At 04:14 PM 19/10/2005 +0000, you wrote:
>Hi All,
>I have a selectable stored procedure that uses EXECUTE STATEMENT. In
>my application, the stored procedure fails with an error,
>"ISC ERROR CODE:335544829
>Variable type (position 1) in EXECUTE STATEMENT 'select ord.OrderID,
>ord.OrderTypeID, ord.Customer,' INTO does not match return".
>However, I can pass exactly the same string to IBExpert and run the
>stored procedure in the debugger and it runs clean and returns the
>correct result set.
>The application uses a TIBOQuery with the SQL property set to:
>select OrderID,
> OrderTypeID,
> Customer,
> OrderDate,
> EstShipDate,
> Total,
> TypeDate,
> CubeWT
>from spGetOutstandingOrders(:pSql)
>The stored procedure is pretty simple.
>create procedure spGetOutstandingOrders(sSql VarChar(1024))
> returns (OrderID BigInt,
> OrderTypeID BigInt,
> Customer VarChar(30),
> OrderDate Date,
> EstShipDate Date,
> Total Numeric(12,2),
> TypeDate Date,
> CubeWT Numeric(12,2))
> declare variable dMultiplier Numeric(12,3);
> for execute statement :sSql
> into :OrderID, :OrderTypeID, :Customer, :OrderDate,
> :EstShipDate, :TypeDate, :dMultiplier
> do
> Begin
> Total = 0;
> CubeWT = 0;
> if (Exists(Select OrderID
> from xOrderDetail
> where (OrderID = :OrderID))) then
> Begin
> Select Sum(ExtendedPrice), Sum(CubeWtExt)
> from xOrderDetail
> where (OrderID = :OrderID)
> into :Total, CubeWt;
> if (:dMultiplier <> 0) then
> Total = (:Total * :dMultiplier);
> Total = Cast(:Total AS Numeric(12,2));
> CubeWT = Cast(:CubeWT AS Numeric(12,2));
> End
> suspend;
> End
>What am I doing wrong?

For this question, the exception message is complaining about a mismatch in
the first argument in the string sent to EXECUTE STATEMENT.
--- If you are using Fb 1.0.x, replace your BigInt declarations with
Numeric(18,0). Fb 1.0.x doesn't know about BigInt.
--- Same if you are using an old (unconverted) database with Fb 1.5.x, or
an older (mismatched) client library, or (possibly) a very old version of IBO.
--- Double-triple check the string that you construct in your app to pass
to the input parameter of the SQL statement. By this, I mean drop a
monitor into you app and look at it word for word. You could get this
mismatch, for example, if that SQL string came through with quotes around
the digits...

--- Consider rethinking your architectural approach to this. To you it
seems like a simple solution. To me it seems like a crazy solution, from
the points of view of both integrity and security. EXECUTE STATEMENT was
not intended to be a backdoor-opener, although it can be used that way --
as here!!

Since your SP knows everything about the statement you are sending to it
except for a handful of parameters, pass the unknowns to the SP as
arguments and construct the statement inside the SP.

>Why is IBExpert running the exact same :pSql
>string clean while my application is failing?

Ann answered this question. The acid test will be whether you can compile
and run your SP in isql or IB_SQL, neither of which uses an emulator.