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

[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

[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?

[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

[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.

[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

[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