Error passing values for parameters of type date to oracle functions in dbExpress 8.0.2 driver, sent 12/30/1899
Posted: Mon 07 Mar 2022 11:55
After updating the dbExpress driver from 7.1.2 to 8.0.2 we started to have problems with parameters of type date, where even passing a valid date, the driver is sending a zero date (12/30/1899).
As an example, we have an oracle function with the following definition, with a parameter of type DATE:
Using a TSQLQuery object, the parameters are passed to the above function through the SQL property:
Then in a method the parameters are set for the TSQLQuery like the example below:
using dbMonitor it is possible to observe that the driver is not considering this date, passing a zero date (12/30/1899).
https://prnt.sc/5YPqsdIi_Ei8
In a debug made in the dbxoda.pas unit, in the TOraSQLCommand.setParameter method, it was observed that the DateTime variable is not being set
This situation can occur at other points in the method since the DateTime variable is widely used.
We would like the Devart team to look into this situation and if they could make a fix available if needed.
Environment used:
Windows 10 64 bit
Oracle 19c
Delphi 10.1 Update 2
* The same situation was also simulated in linux and oracle 11g
As an example, we have an oracle function with the following definition, with a parameter of type DATE:
Code: Select all
create or replace FUNCTION saldoitem (
i_nestab INTEGER,
i_nitem INTEGER,
i_ncodigosaldo INTEGER,
i_nlocalini INTEGER,
i_nlocalfim INTEGER,
i_ddata DATE,
i_phora VARCHAR,
i_nseqnota VARCHAR,
i_nseqproducao VARCHAR,
i_slocalfaixa VARCHAR2 DEFAULT NULL
)
RETURN NUMBER
AS ...
Code: Select all
SELECT SALDOITEM(:ESTAB, :ITEM, :CODSALDO, :LOCALINI, :LOCALFIM, :DATA, :HORA, :SEQNOTA, :SEQPRODUCAO) AS NSALDO FROM DUAL
Code: Select all
SQLQuery1.Close;
SQLQuery1.ParamByName('HORA').DataType := ftTime;
SQLQuery1.ParamByName('SEQNOTA').DataType := ftString;
SQLQuery1.ParamByName('SEQPRODUCAO').DataType := ftString;
SQLQuery1.ParamByName('ESTAB').AsInteger := 1;
SQLQuery1.ParamByName('ITEM').AsInteger := 100088;
SQLQuery1.ParamByName('CODSALDO').AsInteger := 1;
SQLQuery1.ParamByName('LOCALINI').AsInteger := 7;
SQLQuery1.ParamByName('LOCALFIM').AsInteger := 7;
SQLQuery1.ParamByName('DATA').AsDate := EncodeDate(2022, 3, 31); // <-- here is passed a valid date
SQLQuery1.ParamByName('HORA').Clear;
SQLQuery1.ParamByName('SEQNOTA').Clear;
SQLQuery1.ParamByName('SEQPRODUCAO').Clear;
SQLQuery1.Open;
https://prnt.sc/5YPqsdIi_Ei8
In a debug made in the dbxoda.pas unit, in the TOraSQLCommand.setParameter method, it was observed that the DateTime variable is not being set
Code: Select all
...
if uLogType = fldDATE then begin
TimeStamp.Time := 0;
TimeStamp.Date := Integer(pBuffer^);
DateTimeToOraDate(TimeStampToDateTime(TimeStamp), @DateTime); //<-- here TimeStamp has the correct date value
ParamDesc.SetItemAsDateTime(0, DateTime); //<-- here DateTime is 12/30/1899
end
else
...
We would like the Devart team to look into this situation and if they could make a fix available if needed.
Environment used:
Windows 10 64 bit
Oracle 19c
Delphi 10.1 Update 2
* The same situation was also simulated in linux and oracle 11g