Hello everyone, I'm having some issues with a stored procedure. This is a legacy development, so the time was stored in a varchar field and the date in a timestamp field. The issue stills happens after the upgrade from 2.1.5 to 2.1.7 on linux open suse 11.4 x64, classic engine. However, this is not happening on my development machine using Windows XP. Even on my development environment I've restored the database to 2.1.5 using classic and super, but the issue is not being reproduced there, It seems to be a linux specific issue. In both cases I'm using FlameRobin as client. Any help will be appreciated. Thanks in advance. Here is the SP: SET TERM ^ ; CREATE PROCEDURE PA_ASIGNA_CASETAS --RETURNS (LOG VARCHAR(255)) AS DECLARE VARIABLE ID INTEGER; DECLARE VARIABLE UNIDAD VARCHAR(255); DECLARE VARIABLE FECHACRUCE DATE; DECLARE VARIABLE HORACRUCE TIME; DECLARE VARIABLE FECHAHORACRUCE TIMESTAMP; DECLARE VARIABLE PLAZACOBRO VARCHAR(255); DECLARE VARIABLE IMPORTETOTAL DECIMAL(18,2); DECLARE VARIABLE IMPORTEFACTURADO DECIMAL(18,2); DECLARE VARIABLE FECHAHORACARGA TIMESTAMP; DECLARE VARIABLE STATUS CHAR(1); DECLARE VARIABLE FOLIO INTEGER; DECLARE VARIABLE SUCURSAL SMALLINT; DECLARE VARIABLE PARTIDA SMALLINT; DECLARE VARIABLE CUENTA SMALLINT; DECLARE VARIABLE IDCASETA SMALLINT; DECLARE VARIABLE PARTIDACASETA SMALLINT; BEGIN -- PRIMERO RECORRE PARA ENCONTRAR LAS CASETAS QUE EXISTEN DENTRO DE UN CONTRATO FOR SELECT A.ID, A.UNIDAD, A.FECHAHORACRUCE, A.PLAZACOBRO, A.IMPORTETOTAL, A.IMPORTEFACTURADO, A.STATUS FROM STGEXCELIAVE A WHERE A.STATUS = 'P' INTO :ID, :UNIDAD, :FECHAHORACRUCE, :PLAZACOBRO, :IMPORTETOTAL, :IMPORTEFACTURADO, :STATUS DO BEGIN --LOG = CAST(ID AS VARCHAR(10)) || ' ' || UNIDAD || ' ' || CAST(FECHAHORACRUCE AS VARCHAR(40)); --SUSPEND; FOR SELECT FIRST 1 C.FOLIO, C.SUCURSAL, V.PARTIDA FROM ( SELECT X.FOLIO, X.SUCURSAL, (CAST(X.FECHAINI AS DATE) + CAST(X.HORAINI AS TIME)) FECHAHORAINI, (CAST(X.FECHAFIN AS DATE) + CAST(X.HORAFIN AS TIME)) FECHAHORAFIN, X.ESTADO FROM ( SELECT * FROM CONTRATOS WHERE NOT HORAFIN LIKE '%a.m.%' AND NOT HORAFIN LIKE '%p.m.%' AND NOT HORAINI LIKE '%a.m.%' AND NOT HORAINI LIKE '%p.m.%' ) AS X ) AS C JOIN VIAJES V ON C.SUCURSAL = V.SUCURSAL AND C.FOLIO = V.FOLIO JOIN UNIDADES U ON V.UNIDAD = U.UNIDAD WHERE C.ESTADO <> 'C' AND C.FECHAHORAINI <= :FECHAHORACRUCE AND C.FECHAHORAFIN >= :FECHAHORACRUCE AND TRIM(U.NOMUNIDAD) = :UNIDAD INTO :FOLIO, :SUCURSAL, :PARTIDA DO BEGIN --LOG = 'FOLIO ' || CAST(FOLIO AS VARCHAR(10)) || ' SUCURSAL ' || CAST(SUCURSAL AS VARCHAR(10)) || ' PARTIDA ' || CAST(PARTIDA AS VARCHAR(10)); --SUSPEND; SELECT COUNT(*) FROM CASETAS WHERE TRIM(DESCRIPCION) = :PLAZACOBRO INTO :CUENTA; IF (CUENTA > 0) THEN -- LA CASETA EXISTE BEGIN SELECT CASETA FROM CASETAS WHERE TRIM(DESCRIPCION) = :PLAZACOBRO INTO :CUENTA; END ELSE -- LA CASETA NO EXISTE BEGIN SELECT COALESCE(MAX(CASETA),0) + 1 FROM CASETAS INTO :CUENTA; INSERT INTO CASETAS (CASETA, DESCRIPCION, PRECIO, PRECIOIAVE, PRECIOCAMIONETA, PRECIOIAVECAMIONETA) VALUES (:CUENTA, :PLAZACOBRO, :IMPORTEFACTURADO, :IMPORTEFACTURADO, :IMPORTEFACTURADO, :IMPORTEFACTURADO); END -- INSERTA EL RECORD EN EL CONTRATO SELECT COALESCE(MAX(PARTIDACASETA),0) + 1 FROM CASETASCONTRATO WHERE SUCURSAL = :SUCURSAL AND FOLIO = :FOLIO AND PARTIDA = :PARTIDA INTO :PARTIDACASETA; INSERT INTO CASETASCONTRATO (FOLIO, SUCURSAL, PARTIDA, PARTIDACASETA, IMPORTE, ESIAVE, FECHAHORA, NUMERO, TIPO, CASETA) VALUES (:FOLIO, :SUCURSAL, :PARTIDA, :PARTIDACASETA, :IMPORTEFACTURADO, 'S', :FECHAHORACRUCE, 0, 'I', :CUENTA); --LOG = 'UPDATING ' || CAST(ID AS VARCHAR(10)) || ' ' || UNIDAD || ' WITH STATUS = A'; --SUSPEND; UPDATE STGEXCELIAVE SET STATUS = 'A', FOLIO = :FOLIO, SUCURSAL = :SUCURSAL WHERE ID = :ID; END END -- SEGUNDA OPORTUNIDAD BUSCA CASETAS QUE EXISTEN 24 HORAS ANTES Y DESPUES FOR SELECT A.ID, A.UNIDAD, A.FECHAHORACRUCE, A.PLAZACOBRO, A.IMPORTETOTAL, A.IMPORTEFACTURADO, A.STATUS FROM STGEXCELIAVE A WHERE A.STATUS = 'P' INTO :ID, :UNIDAD, :FECHAHORACRUCE, :PLAZACOBRO, :IMPORTETOTAL, :IMPORTEFACTURADO, :STATUS DO BEGIN FOR SELECT FIRST 1 C.FOLIO, C.SUCURSAL, V.PARTIDA FROM ( SELECT X.FOLIO, X.SUCURSAL, (CAST(X.FECHAINI AS DATE) + CAST(X.HORAINI AS TIME)) FECHAHORAINI, (CAST(X.FECHAFIN AS DATE) + CAST(X.HORAFIN AS TIME)) FECHAHORAFIN, X.ESTADO FROM ( SELECT * FROM CONTRATOS WHERE NOT HORAFIN LIKE '%a.m.%' AND NOT HORAFIN LIKE '%p.m.%' AND NOT HORAINI LIKE '%a.m.%' AND NOT HORAINI LIKE '%p.m.%' ) AS X ) AS C JOIN VIAJES V ON C.SUCURSAL = V.SUCURSAL AND C.FOLIO = V.FOLIO JOIN UNIDADES U ON V.UNIDAD = U.UNIDAD WHERE C.ESTADO <> 'C' AND dateadd(-12 hour to C.FECHAHORAINI) <= :FECHAHORACRUCE AND dateadd(12 hour to C.FECHAHORAFIN)>= :FECHAHORACRUCE AND TRIM(U.NOMUNIDAD) = :UNIDAD INTO :FOLIO, :SUCURSAL, :PARTIDA DO BEGIN --LOG = 'FOLIO ' || CAST(FOLIO AS VARCHAR(10)) || ' SUCURSAL ' || CAST(SUCURSAL AS VARCHAR(10)) || ' PARTIDA ' || CAST(PARTIDA AS VARCHAR(10)); --SUSPEND; SELECT COUNT(*) FROM CASETAS WHERE TRIM(DESCRIPCION) = :PLAZACOBRO INTO :CUENTA; IF (CUENTA > 0) THEN -- LA CASETA EXISTE BEGIN SELECT CASETA FROM CASETAS WHERE TRIM(DESCRIPCION) = :PLAZACOBRO INTO :CUENTA; END ELSE -- LA CASETA NO EXISTE BEGIN SELECT COALESCE(MAX(CASETA),0) + 1 FROM CASETAS INTO :CUENTA; INSERT INTO CASETAS (CASETA, DESCRIPCION, PRECIO, PRECIOIAVE, PRECIOCAMIONETA, PRECIOIAVECAMIONETA) VALUES (:CUENTA, :PLAZACOBRO, :IMPORTEFACTURADO, :IMPORTEFACTURADO, :IMPORTEFACTURADO, :IMPORTEFACTURADO); END -- INSERTA EL RECORD EN EL CONTRATO SELECT COALESCE(MAX(PARTIDACASETA),0) + 1 FROM CASETASCONTRATO WHERE SUCURSAL = :SUCURSAL AND FOLIO = :FOLIO AND PARTIDA = :PARTIDA INTO :PARTIDACASETA; INSERT INTO CASETASCONTRATO (FOLIO, SUCURSAL, PARTIDA, PARTIDACASETA, IMPORTE, ESIAVE, FECHAHORA, NUMERO, TIPO, CASETA) VALUES (:FOLIO, :SUCURSAL, :PARTIDA, :PARTIDACASETA, :IMPORTEFACTURADO, 'S', :FECHAHORACRUCE, 0, 'I', :CUENTA); --LOG = 'UPDATING ' || CAST(ID AS VARCHAR(10)) || ' ' || UNIDAD || ' WITH STATUS = A'; --SUSPEND; UPDATE STGEXCELIAVE SET STATUS = 'A', FOLIO = :FOLIO, SUCURSAL = :SUCURSAL WHERE ID = :ID; END END END^ SET TERM ; ^ And this is the error message on linux: Executing... Error: *** IBPP::SQLException *** Context: Statement::Execute( EXECUTE PROCEDURE PA_ASIGNA_CASETAS ) Message: isc_dsql_execute2 failed SQL Message : -413 Overflow occurred during data type conversion. Engine Code : 335544334 Engine Message : conversion error from string "10:00:00 a.m. " At procedure 'PA_ASIGNA_CASETAS' line: 32, col: 9 _______________________________ I'm free, I use GNU/Linux Close your WindowsOpen your mind.
[firebird-support] Timestamp conversion issue
Alex Castillo acr_k...@yahoo.com [firebird-support] Tue, 05 Apr 2016 02:51:10 -0700
- [firebird-support] ... Alex Castillo acr_k...@yahoo.com [firebird-support]
- Re: [firebird-... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
- Re: [fireb... Alex Castillo acr_k...@yahoo.com [firebird-support]
- Re: [f... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]