Hi Tim,

> I have a very strange error. Our production systems use Interbase 6.0.2.0,
> running an app written in D5 using IBX components.
>
> Running the following stored procedure in IB console produces a "arithmetic
> error, numeric overflow or string truncation error". This does not happen
> when the select only is run in IB Console, but only when the stored
> procedure is run.
>
> ???
>
> ALTER PROCEDURE "ACCOUNTSUMMARY"
> (
> "POSID" INTEGER,
> "SHIFTNUM" INTEGER
> )
> RETURNS
> (
> "RECEIPT" INTEGER,
> "RECEIPT2" INTEGER,
> "ACC_NUM" VARCHAR(11),
> "SUB_CODE" VARCHAR(11),
> "MQUANTITY" FLOAT,
> "SURNAME" VARCHAR(40),
> "SUB_NAME" VARCHAR(25),
> "CARD_CODE" VARCHAR(20),
> "REGNUM" VARCHAR(10),
> "INV_TYPE" SMALLINT,
> "INV_PREFIX" VARCHAR(3)
> )
> AS
> BEGIN
> FOR SELECT
> l.Receipt, l.Receipt2, l.Acc_Num, l.Sub_Code,
> SUM(l.MQuantity) AS MQuantity, L.INV_TYPE, L.INV_PREFIX,
> c.SurName, s.Sub_Name, s.Card_Code, s.RegNum
> FROM
> log L JOIN client C
> ON
> C.code = L.acc_Num
> JOIN clnt_sub S
> ON
> S.code = L.acc_num
> WHERE
> L.POS_ID = :POSID
> AND L.SHIFTNUM = :SHIFTNUM
> AND s.sub_code = l.sub_code
> GROUP BY l.Receipt, l.Receipt2, l.Acc_Num, l.Sub_Code,
> l.inv_type, l.inv_prefix, c.SurName, s.Sub_Name, s.Card_Code,
> s.RegNum
> ORDER BY c.Surname, l.Acc_Num, l.Sub_Code
> INTO
> :Receipt, :Receipt2, :Acc_Num, :Sub_Code, :MQuantity,
> :Surname, :Sub_Name, :Card_Code, :RegNum, :inv_type, :inv_prefix
> DO
> BEGIN
> SUSPEND;
> END
> END

Do the procedure output parameters match the column data types
of the underlying tables?

And, give us an example how you use the stored procedure.


--
Best Regards,
Thomas Steinmaurer

The IB LogManager Product Family
Logging/Auditing Suite for InterBase and Firebird
http://www.iblogmanager.com