[firebird-support] Timestamp conversion issue

[Alex Castillo acr_k...@yahoo.com [firebird-support]]

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.