Page 1 of 1

Passing AsDate := StrToDate('01/03/2021') param results in -303 error with FB

Posted: 26.04.2021, 08:48
by louis
Hello,
I try to update my application from Zeos 7.2.0b to new 8.0.0 but I have first trouble.

I have this query:

Code: Select all

UPDATE
  DOC_FE DFE
SET
  DFE.STATO = :Stato
WHERE
   DFE.STATO < :Stato
  AND DFE.DOC_TESTA_ID = (SELECT
                                           DT.ID
                                         FROM
                                           DOC_TESTA DT,
                                           TIPO_DOCUMENTI TD
                                        WHERE
                                          DT.DOCUMENTO_ID = TD.ID
                                          AND TD.DESCRIZIONE = :Documento
                                          AND DT.NUMERO = :Numero
                                          AND DT.SERIE = :Serie
                                          AND DT.DATA = :Data
                                         )
                                    
passing this param:
query1.Params.ParamByName('Data').AsDate := DocumentoData;
the same passing:
query1.Params.ParamByName('Data').AsDate := StrToDate('01/03/2021');
run time firebird 2.5.9 results in a -303 error tha says:
Incompatible column/host variable data type; arithmetic exception, numeric overflow, or string truncation; Arithmetic overflow or division by zero has occurred.
Instead, in this query:

Code: Select all

SELECT FIRST 1
  A.*
FROM
  AZIENDA A
WHERE
  A.DECORRENZA <= :DATA
ORDER BY
  A.DECORRENZA Desc
passing this param: "query1.Params.ParamByName('DATA').AsDate := Date;"
result Ok, no error.

With 7.2.0b both query results Ok.

What I'm wrong?

Thanks

Re: Passing AsDate := StrToDate('01/03/2021') param results in -303 error with FB

Posted: 26.04.2021, 09:46
by louis
If I change the query like this:

Code: Select all

UPDATE
  DOC_FE DFE
SET
  DFE.STATO = :Stato
WHERE
   DFE.STATO < :Stato
  AND DFE.DOC_TESTA_ID = (SELECT
                                           DT.ID
                                         FROM
                                           DOC_TESTA DT,
                                           TIPO_DOCUMENTI TD
                                        WHERE
                                          DT.DOCUMENTO_ID = TD.ID
                                          AND TD.DESCRIZIONE = :Documento
                                          AND DT.NUMERO = :Numero
                                          AND DT.SERIE = :Serie
                                          AND DT.DATA = '01.03.2021'
                                         )
works, no error, but I need to pass the date as a parameter.

Suggestions?

Re: Passing AsDate := StrToDate('01/03/2021') param results in -303 error with FB

Posted: 26.04.2021, 11:26
by marsupilami
Hello Louis,

what is the coumn type definition of DOC_TESTA.DATA? I can only assume that AZIENDA.DECORRENZA and DOC_TESTA.DATA use different data types. Also: Is your database a dialect 1 database or a dialect 3 database?

Best regards,

Jan

Re: Passing AsDate := StrToDate('01/03/2021') param results in -303 error with FB

Posted: 26.04.2021, 11:50
by louis
Hello marsupilani,
both column are defined as "DATE":

in table AZIENDA, the column is defined as:
DECORRENZA Date NOT NULL
in table DOC_TESTA, the column is defined as:
DATA Date
The only different is that AZIENDA.DECORRENZA is PRIMARY KEY

Database is on a Dialect 3.

Thanks.

Re: Passing AsDate := StrToDate('01/03/2021') param results in -303 error with FB

Posted: 26.04.2021, 14:22
by louis
Forgive me, I found the mistake, my mistake!

The problem was not the parameter of type "TDate" but another parameter (DT.NUMERO = :Numero) of type "String" but passed for "Integer" even if the error came out only deleting the line of code:
query1.Params.ParamByName('Data').AsDate := DocumentDate;
In fact, if I remove the line above, the query does not give an error but, and I have only just discovered this, it does not returns any rows affected.

But why does this happen?

Thanks

Re: Passing AsDate := StrToDate('01/03/2021') param results in -303 error with FB

Posted: 27.04.2021, 08:09
by marsupilami
Hello Louis,

if you don't assign a value to the parameter, Zeos most probably will send a null there. Any comparisons with null will evaluate to false in SQL, so the server will not return any rows.

Best regards,

Jan