Board index » delphi » string truncation or arithmetic ecxeption

2006-03-07 11:52:29 PM
delphi267
Hello!
The stored.proc attached, stops suddenly with a message "string truncation
or arithmetic ecxeption"
I'm not making any division and I checked that all the string variables I've
created are longer than the table fields they contain.
I don't know what more to check. Any advise will be very welcome!!
thanks!
-------------
CREATE PROCEDURE ARTICULOSPORCLIENTE (
IN_TODOSCLIENTES INTEGER,
IN_TODOSARTICULOS INTEGER,
IN_DESDE DATE,
IN_HASTA DATE)
RETURNS (
OUT_MONTH INTEGER,
OUT_YEAR INTEGER,
OUT_YEAR_MONTH CHAR (9),
OUT_DESCRIPCION VARCHAR (85),
OUT_CANTIDAD NUMERIC (13, 3),
OUT_IMPORTE NUMERIC (13, 3),
OUT_COSTO NUMERIC (13, 3),
OUT_GANANCIA NUMERIC (13, 3),
OUT_PORC_GAN NUMERIC (13, 3),
OUT_NOMBRE VARCHAR (85),
OUT_ID_ARTICULO INTEGER,
OUT_CODIGO_S_PROV VARCHAR (30),
OUT_CODIGO_BARON VARCHAR (10),
OUT_IMAGEN_ART BLOB sub_type 0 segment size 80,
OUT_IMAGEN_RUB BLOB sub_type 0 segment size 80,
OUT_IMAGEN_FAM BLOB sub_type 0 segment size 80,
OUT_IMAGEN_GRU BLOB sub_type 0 segment size 80,
OUT_IMAGEN_SGR BLOB sub_type 0 segment size 80)
AS
declare variable loc_ID_CLIENTE integer;
declare variable loc_UNITARIO numeric(13,3);
declare variable loc_COSTO numeric(13,3);
declare variable loc_ID_TIPO_MOV integer;
declare variable loc_CONSIGNACION char(3);
declare variable loc_DESC_CORTA char(7);
declare variable loc_SIGNO integer;
declare variable loc_CANTIDAD numeric(13,3);
declare variable loc_ID_MOVIMIENTO integer;
/* variables para controlar el suspend */
declare variable loc_MONTH INTEGER;
declare variable loc_YEAR INTEGER;
declare variable loc_NOMBRE VARCHAR (85);
declare variable loc_ID_ARTICULO integer;
BEGIN
/* controles */
loc_MONTH = -1;
loc_YEAR = -1;
loc_ID_ARTICULO = -1;
loc_NOMBRE = '-1';
/* acumuladores */
OUT_CANTIDAD = 0;
OUT_IMPORTE = 0;
OUT_COSTO = 0;
OUT_GANANCIA = 0;
OUT_PORC_GAN = 0;
FOR
SELECT
"d".ID_ARTICULO,
"d".UNITARIO,
"d".COSTO,
"d".DESCRIPCION_ADIC,
"a".CODIGO_S_PROV,
"a".CODIGO_BARON,
"a".IMAGEN,
extract (month from "m".FECHA),
extract (year from "m".FECHA),
"e".NOMBRE,
"m".ID_TIPO_MOV,
"t".SIGNO,
"d".CANTIDAD,
"f".IMAGEN AS IMAGEN1,
"g".IMAGEN AS IMAGEN2,
"r".IMAGEN AS IMAGEN3,
"s".IMAGEN AS IMAGEN4
FROM
MOV_DETALLE "d"
INNER JOIN MOVIMIENTOS "m" ON ("d".ID_MOVIMIENTO = "m".ID_MOVIMIENTO)
INNER JOIN ENTIDADES "e" ON ("m".ID_ENTIDAD = "e".ID)
INNER JOIN TIPOS_MOVIMIENTOS "t" ON ("m".TIPO = "t".CODIGO)
INNER JOIN ARTICULO "a" ON ("d".ID_ARTICULO = "a".ID)
INNER JOIN FAMILIAS "f" ON ("a".ID_FAMILIA = "f".ID_FAMILIA)
INNER JOIN GRUPOS "g" ON ("a".ID_GRUPO = "g".ID_GRUPO)
INNER JOIN RUBROS "r" ON ("a".ID_RUBRO = "r".ID_RUBRO)
INNER JOIN SUBGRUPOS "s" ON ("a".ID_SUBGR = "s".ID_SUBGRUPO)
WHERE
("m".TIPO starting with 'vF') AND
("m".FECHA BETWEEN :in_DESDE AND :in_HASTA) and
(("e".SELECCIONADO = '>') or (:in_TodosClientes = -1)) and
(("a".SELECCIONADO = '>') or (:in_TodosArticulos = -1))
ORDER BY
"e".NOMBRE, 9, 8, "d".ID_ARTICULO
into
out_ID_ARTICULO,
loc_UNITARIO,
loc_COSTO,
out_DESCRIPCION,
out_CODIGO_S_PROV,
out_CODIGO_BARON,
out_IMAGEN_ART,
out_MONTH,
out_YEAR,
out_NOMBRE,
loc_ID_TIPO_MOV,
loc_SIGNO,
loc_CANTIDAD,
out_IMAGEN_RUB,
out_IMAGEN_FAM,
out_IMAGEN_GRU,
out_IMAGEN_SGR
DO
BEGIN
/* SOLO LA PRIMERA VEZ */
if (loc_year = -1) then
begin
loc_MONTH = out_MONTH;
loc_YEAR = out_YEAR;
loc_ID_ARTICULO = out_ID_ARTICULO;
loc_NOMBRE = out_NOMBRE;
OUT_YEAR_MONTH = CAST (out_year as char(4)) || '-' ||
CAST (out_month as char(2));
end
/* SI CAMBIO ALGO: SUSPEND */
if ((loc_MONTH <>out_MONTH) or (loc_YEAR <>out_YEAR) or
(loc_ID_ARTICULO <>out_ID_ARTICULO) or (loc_NOMBRE <>out_NOMBRE))
then
begin
suspend;
/* control */
loc_MONTH = out_MONTH;
loc_YEAR = out_YEAR;
loc_ID_ARTICULO = out_ID_ARTICULO;
loc_NOMBRE = out_NOMBRE;
OUT_YEAR_MONTH = CAST (out_year as char(4)) || '-' ||
CAST (out_month as char(2));
/* acumuladores */
OUT_CANTIDAD = 0;
OUT_IMPORTE = 0;
OUT_COSTO = 0;
OUT_GANANCIA = 0;
OUT_PORC_GAN = 0;
end
/* POR LAS DUDAS */
if (loc_SIGNO is null) then
loc_SIGNO = 1;
/* ES UNA N.C.?? */
loc_CANTIDAD = loc_CANTIDAD * loc_SIGNO;
OUT_CANTIDAD = OUT_CANTIDAD + loc_CANTIDAD;
OUT_IMPORTE = OUT_IMPORTE + (loc_CANTIDAD * loc_UNITARIO);
OUT_COSTO = OUT_COSTO + (loc_CANTIDAD * loc_COSTO);
OUT_GANANCIA = OUT_GANANCIA + (loc_CANTIDAD * (loc_UNITARIO -
loc_COSTO));
OUT_PORC_GAN = 0;
END
SUSPEND;
END
 

Sergio Gonzalez writes:
error.
I find that the best approach for me is to remove parts of the
procedure and run it until the error goes away. Keep trying different
parts until you find the exact line which is causing the error.
Once you've determined which line is causing the error it will be
easier to find the cause.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
qc.borland.com -- Vote for important issues
 

In addition to Craigs answer,
You could try a de{*word*81} like the one in Database Workbench
( www.upscene.com ) and see if you can catch the error with
that.
Be sure to pass the same data as the time that is returns the
exception.
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
My thoughts:
blog.upscene.com/martijn/
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Thanks Craig and Martijn!
Finally found a variable 2 bytes shorter than the field it was meant to
store...
mmmhhhh, next time, I promise to check more carefully before posting...
-sergio