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
>
>ISC ERROR MESSAGE:
>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))
>AS
> declare variable dMultiplier Numeric(12,3);
>Begin
> 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
>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...


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

./heLen