Error passing values for parameters of type date to oracle functions in dbExpress 8.0.2 driver, sent 12/30/1899

[wandersonviasoft]

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:

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

Using a TSQLQuery object, the parameters are passed to the above function through the SQL property:

Code: Select all

SELECT SALDOITEM(:ESTAB, :ITEM, :CODSALDO, :LOCALINI, :LOCALFIM, :DATA, :HORA, :SEQNOTA, :SEQPRODUCAO) AS NSALDO FROM DUAL

Then in a method the parameters are set for the TSQLQuery like the example below:

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;

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

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

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

[ViktorV]

Hi All!

Thanks for your reply and provided information.

Please be informed that we currently investigating this issue.
I want to assure you that once we have the result, we will immediately info you by reply to this email.

[wandersonviasoft]

ViktorV, any news on this situation? Did you manage to analyze it and is there any prediction of when a fix will be released?
If you have any patches or corrections to the sources, I could be making the change right here and recompile the dll until an official version is released, this is why we have customers waiting.

[cesar]

Hello

Any news about this problem?

We're still having issues and we don't have a workaround for the above case.

Yours sincerely.