TOraQuery AsDateTime property

[jjeffman]

Hello,
I am using ODAC 9.7.26 for C++Builder 6.0 Professional Edition.

I have built an application which can connect either to Oracle or MSSQL, where there a table containing date and time values with milliseconds.

On the way to reduce the amount of necessary code I am casting TOraQuery and TADOQuery to TDataSet, which TFields have the TField::AsDateTime property.

Code: Select all

  
TSQLTimeStamp TimeStamp;   
TDataSet *Consulta;
TOraQuery *OraQry;
TADOQuery *SqlQry_STO;

if( FServerSTO == "ORACLE" )
   { Consulta = OraQry ;}
if( FServerSTO == "SQLSERVER" )   
   { Consulta = SqlQry_STO ;}
<cut>

TimeStamp             = DateTimeToSQLTimeStamp(Consulta->FieldByName ( "E3TIMESTAMP")->AsDateTime) ;
DtHrE3TIMESTAMP = TDateTime( TimeStamp.Year, TimeStamp.Month, TimeStamp.Day) +
                       TDateTime( TimeStamp.Hour, TimeStamp.Minute, TimeStamp.Second, TimeStamp.Fractions);
   

TADOQuery seems not support the AsSQLTimeStamp, so I am using a workaround converting the TField::AsDateTime valua to TSQLTimeStamp using the TDateTime::DateTimeToSQLTimeStamp method.

Previously the application was written only for Oracle and I was using Field::AsSQLTimeStamp from TOraQuery and it was working fine, when I added the connection to MSSQL I had to change the code and I realise that TField::AsDateTime works differently for TOraQuery and TADOQuery, although both components answer the same for the TField::AsString property.

TOraQuery omits the milliseconds part on the AsDateTime property while TADOQuery do not.

I am stuck because I would not like to write different codes just to accommodate the behaviour of TOraQuery, which I in my humble opinion should include milliseconds when answering to TField::AsDateTime property.

Is there anything else i can do instead of writing different codes ?

Best regards.

Jayme Jeffman

[MaximG]

In Delphi, operations when using TDateTime with a fractional part are incorrect. The following code sample confirms this:

Code: Select all

...
var
  dt1, dt2: TDateTime;
  str: string;
begin
  dt1 := Now;
  str := DateTimeToStr(dt1);
  dt2 := StrToDateTime(str);
  if dt1 = dt2 then
    ShowMessage('equal')
  else
    ShowMessage('not equal');
end;
...

That's why our products have two properties: AsDateTime - to return a time value without fractional part and AsSQLTimeStamp - correspondingly, to get a value with fractional part

[jjeffman]

Hi Maxim,

Thank you very much for answering me.

So, your answer do not solve my problem.

From your answer I might deduce that you have already tested other 'As' TField properties.

Previously the application was written only for Oracle and I was using Field::AsSQLTimeStamp from TOraQuery and it was working fine, when I added the connection to MSSQL I had to change the code and I realise that TField::AsDateTime works differently for TOraQuery and TADOQuery, although both components answer the same for the TField::AsString property.

Both databases and components have the same result using TField::AsString. So why the conversion from TField::AsDateTime to TSQLTimeStamp truncate the milisseconds part when the componnent is the TOraQuery ?

Does Delphi 'AsString' show the correct date and time value with milisseconds ?

Best regards.

Jayme Jeffman

[jjeffman]

Hi Maxim,

I have made a function to workaround the problem:

Code: Select all

TSQLTimeStamp TDBPROVIDER::GetSQLTimeStamp( TDataSet *DataSet, String FieldName )
{
  TSQLTimeStamp sqlTimeStamp = DateTimeToSQLTimeStamp(TDateTime(0));

  if( DataSet->Active && !DataSet->FieldByName(FieldName)->IsNull )
  {
    if( dynamic_cast<TOraQuery*>(DataSet) != NULL )
    { //Oracle - ODAC
      sqlTimeStamp = DataSet->FieldByName(FieldName)->AsSQLTimeStamp;
    }
    else if( dynamic_cast<TADOQuery*>(DataSet) != NULL )
    {
      sqlTimeStamp = DateTimeToSQLTimeStamp(DataSet->FieldByName(FieldName)->AsDateTime) ;
    }
  }

  return sqlTimeStamp;
}

I am calling the function to obtain a SQL TimeStamp value and convert it to a TDateTime value with the milliseconds part:

TimeStamp = DBPROVIDER->GetSQLTimeStamp( Consulta, "E3TIMESTAMP" ) ;
DtHrE3TIMESTAMP = TDateTime( TimeStamp.Year, TimeStamp.Month, TimeStamp.Day) +
TDateTime( TimeStamp.Hour, TimeStamp.Minute, TimeStamp.Second, TimeStamp.Fractions);

This function is working fine and have solved my problem, although I think (IMHO) that the problem is the way Devart-ODAC implements the TField::AsDateTime property, because the AsString retrieve the milliseconds and AsDateTime do not.

Best regards.

Jayme Jeffman

[bork]

We will change the AsDateTime method behaviour in the next ODAC release and it will return milliseconds for TIMESTAMP fields.

[jjeffman]

Hi Bork,

Thank you very much for answering me.

How about a patch for the version 9.7.26 which I am using. I think the enterprise I work for wont buy a new version of ODAC.

Best regards.

Jayme Jeffman Filho

[bork]

We can't provide any official patches for previous ODAC versions. But if you have ODAC with source code and an active subscription, please contact us via the contact form devart.com/company/contactform.html , specify your license number and we will help you to add the fix to your source code.

[jjeffman]

Hello Bork,

Just to add some information.

I have a PL/SQL function which returns data of Oracle Date type and the TOraQuery::AsDateTime is returning a date without any fractional part of the day !!

Best regards.

Jayme Jeffman

[MaximG]

On July 9, a new version of ODAC 10.2.6 was released, including the changes you are interested in. A full list of changes in ODAC 10.2.6 is available at: https://www.devart.com/odac/revision_history.html