another bug in TDataRow: failure with datetime fields [solve

[MOISES]

Hello,

DateTime fields are only shown and edited as Date fields in ADO.

Thank you.

[MOISES]

Up!

[MOISES]

Up please!

[MOISES]

Up! Thank you.

[MOISES]

Any help, please?

[MOISES]

Has been fixed in new Fwh?

[nageswaragunupudi]

Please wait a little.
Are you using MySql or Microsoft SQL?

[MOISES]

Access and Mysql. Thank you.

[MOISES]

Any clue please?

Thank you.

[MOISES]

Up

[nageswaragunupudi]

We are looking into the issue raised.

Meanwhile we request you to do this small test at your end also and provide your comments and feedback.

Code: Select all

#include "fivewin.ch"
#include "adodef.ch"

#define USE_ADO

static oCn
static cTable  := "testdatetime"

//----------------------------------------------------------------------------//

function Main()

   local cSql, oRs

   SET DATE ITALIAN
   SET CENTURY ON
   SET TIME FORMAT TO "HH:MM:SS"

   oCn   := FW_DemoDB( "ADO" ) // or connec to your MySql server

   TRY
      oCn:Execute( "DROP TABLE testdatetime" )
   CATCH
   END

   TEXT INTO cSql
   CREATE TABLE `testdatetime` (
   `ID`       INT AUTO_INCREMENT PRIMARY KEY,
   `NAME`     VARCHAR ( 20 ),
   `DTIME`    DATETIME
   )
   ENDTEXT

   oCn:Execute( cSql )

   cSql  := "INSERT INTO testdatetime ( NAME, DTIME ) VALUES ( 'One', '2018-10-20' )"
   oCn:Execute( cSql )

   oRs   := FW_OpenRecordSet( oCn, "select * from testdatetime" )

   ? oRs:Fields( "dtime" ):Type,  ;
     oRs:Fields( "dtime" ):Value, ;
     ValType( oRs:Fields( "dtime" ):Value )

   MakeDialog( oRs )

   oRs:Close()

   oCn:Execute( "DROP TABLE testdatetime" )

   oCn:Close()

return nil

//----------------------------------------------------------------------------//

static function MakeDialog( oRs )

   local oDlg

   DEFINE DIALOG oDlg SIZE 250,150 PIXEL TRUEPIXEL

   @ 20,60 GET oRs:Fields( "name"  ):Value SIZE 150,24 PIXEL OF oDlg
   @ 50,60 GET oRs:Fields( "dtime" ):Value SIZE 150,24 PIXEL OF oDlg

   @ 90,60 BUTTON "OK" SIZE 100,40 PIXEL OF oDlg ACTION oDlg:End()

   ACTIVATE DIALOG oDlg CENTERED

return nil

//----------------------------------------------------------------------------//
 

Please copy this program to \fwh\samples folder and build and run with buildh.bat (Please use Harbour, not xHarbour).

The field "dtime" is created with field type "DATETIME".

You will find that the execution of the code

Code: Select all

   ? oRs:Fields( "dtime" ):Type,  ;
     oRs:Fields( "dtime" ):Value, ;
     ValType( oRs:Fields( "dtime" ):Value )
 

shows the result as
135 (adDBTimeStamp), 20-10-2018, 'D'

Though the field type reported by ADO is adDBTimeStamp, Harbour returns the value as Date not DateTime. If the value stored in the field is a date without timepart, Harbour treats as Date but not DateTime.

We can further confirm this behaviour with the dialog using

Code: Select all

@ r,c GET oRs:Fields( "dtime" ):Value 
 

The above program uses pure ADO with Harbour and does not use FWH classes XBrowse or DataRow.

At present, the behaviour of DataRow is similar to the native behaviour of Harbour as seen above.

Note: xHarbour behaves differently.

Can you please test and let us know if DataRow class behaves differently from the above behaviour of Harbour?

[MOISES]

Thank you very much.

I can´t compile because in FW 14.14 there is not FW_DemoDB( "ADO" ) function, but I already have ordered the FTDN renew.

I asume I will have the same behaviour that you: Harbour returns the value as Date not DateTime. This is the behaviour I get in my software, either with Access or MYSQL.

Is this a Harbour bug?

Thank you again. All the best,

[MOISES]

Yes, with latest FWH I get the same wrong behaviour.

Thank you.

[nageswaragunupudi]

Extract from whatsnew.txt FWH 19.10

* Treatment of ADO Date and DateTime fields/values
XBrowse, TDataRow, TRecSet and function FWAdoStruct( oRs )

In many databases like MSAccess, MSSQL, Oracle only DateTime field type is
avaiable for storing both simple dates and date-time values. Whether a
particular field is to be treated as Date or DateTime field mostly depends
on the usage. Accordingly FWH libraries treat fields containing pure Date
values (without any time-part) as Date fields and fields containing DateTime
values are treated as DateTime fields. While this approach works satisfactorily
in most cases, there could be exceptions and the programmer needs to have
the facility to override the default behavior.

Databases like MYSQL,POSTGRE provide separate field types for Date and DateTime
and the above approach is not relevant in these cases.

Now from this version:
In cases of databases MYSQL,POSTGRE field types as reported by ADO are used
irrespective of the values stored.
In case of other databases, the existing default behaviour will continue and
the programmer can override by:

oCol:cDataType := .T. // xbrowse
oRec:FieldType( "fieldname", "T" ) // DataRow and TRecSet

[MOISES]

Thank you. It works perfect.

Just a final adjustement. In xbrowse, showing this datetime field adds .000 to the final time:



What is the picture to supress the final .000, so the vaule would be only:

19-11-2019 20:30.37

Thank you.