OGR PGeo driver should recognize datetime fields as OFTDateTime rather than OFTString

[jjr8]

(I am unsure what component this ticket should be categorized as. Please adjust as appropriate.)

Currently, MS Access datetime fields are recognized as OFTString. See OGRPGeoLayer::BuildFeatureDefn() in /trunk/gdal/ogr/ogrsf_frmts/pgeo/ogrpgeolayer.cpp@15662#L101):

        switch( poStmt->GetColType(iCol) )
        {
          case SQL_INTEGER:
            oField.SetType( OFTInteger );
            break;

          case SQL_BINARY:
          case SQL_VARBINARY:
          case SQL_LONGVARBINARY:
            oField.SetType( OFTBinary );
            break;

          case SQL_DECIMAL:
            oField.SetType( OFTReal );
            oField.SetPrecision( poStmt->GetColPrecision(iCol) );
            break;

          case SQL_FLOAT:
          case SQL_REAL:
          case SQL_DOUBLE:
            oField.SetType( OFTReal );
            oField.SetWidth( 0 );
            break;

          default:
            /* leave it as OFTString */;
        }

Datetime fields are essential for many applications. For certain applications, the caller will not know the field type ahead of time and will want to get it from OGR. As a workaround, the caller can try to parse a date and then conclude that the field contains dates, but this cumbersome and does not work in all situations.

Please update the PGeo driver to recognize datetime for GDAL 1.6.0. If there are no developer resources for this, I may be able to contribute a fix. Having datetime support for ESRI personal geodatabases is pretty important for my application and it would be better to fix OGR than to kludge up a workaround in my own code.

[warmerdam]

jjr8,

could you make a personal geodatabase with data fields available for download? Please be specific about which table/field should be date time.

I imagine it should be easy to implement.

Patches are also welcome, though I'd still prefer to be able to test it before committing.

Potentially the same issue exists with the generic ODBC driver.

[jjr8]

Example personal geodatabases for Arc 9.1, 9.2, 9.3

[jjr8]

Frank - the attached personal GDBs have one feature class, TestPoints?, which has fields of all types that can be created by the ArcGIS Add Field geoprocessing tool except type BLOB. The data are actually Argos satellite points. There are two datetime fields, with obvious names.

If you believe this would be easy to implement and have the time to do so for 1.6.0 or some other near-term release, I would prefer that you do it. But if this is low priority enough that you wouldn't get to it for a long time, I can try to contribute a patch. I have never built GDAL/OGR before so it would be a large effort for what should be a small fix. (I don't think I should contribute a patch without at least building it and doing some basic testing.)

Let me know what you prefer.

[jjr8]

Oh, and even though I attached GDBs created with Arc 9.1, 9.2, and 9.3, I don't think the Arc version makes a difference for this fix. I just attached them in case you were interested in having them for other purposes.

[warmerdam]

jjr8,

I'm working on it now, and it should make it into 1.6.0. The changes don't appear too complicated, though I have confirmed they also need to be made in the generic ODBC driver (which is similarly structured).

[warmerdam]

I have added support for date, time and datetime (timestamp) fields in the ODBC and PGeo drivers. I have only tested the datetime/timestamp as I don't have examples of the others (which is somewhat worrysome).

Changes are in trunk (r15766). I do not intend to retrofit into 1.5 as the changes are kind of risky and are really a new feature, not a bug fix.

[jjr8]

Great! Thanks for the quick fix.

I think the lack of testing for date and time fields will be fine with PGeo. I'm pretty sure all modern versions of MS Access only implement datetime (i.e. SQL_TIMESTAMP) internally. In any case, that is the only type that ArcGIS allows you to create, so it'll work fine for PGeo.

For ODBC, I think you can test date by opening a DBF file using the ODBC driver called "Microsoft dBASE Driver". Here is some Python that dumps the supported ADO data types for the MS Access and dBASE drivers:

>>> from win32com.client import Dispatch
>>> conn = Dispatch('ADODB.Connection')
>>> conn.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\Temp_MDB\\Arc91.mdb')
>>> 
>>> rs = conn.OpenSchema(22)            # 22 == adSchemaProviderTypes
>>> while not rs.EOF:
...     print(rs.Fields.Item(u'TYPE_NAME').Value + ' ' + str(rs.Fields.Item(u'DATA_TYPE').Value))
...     rs.MoveNext()
... 
GUID 72
LONGCHAR 130
VARCHAR 130
CHAR 130
BIT 11
BYTE 17
LONGBINARY 128
VARBINARY 128
BINARY 128
CURRENCY 6
INTEGER 3
COUNTER 3
SMALLINT 2
REAL 4
DOUBLE 5
DATETIME 135
>>> del rs, conn
>>> conn = Dispatch('ADODB.Connection')
>>> conn.Open('Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\\')
>>> rs = conn.OpenSchema(22)            # 22 == adSchemaProviderTypes
>>> while not rs.EOF:
...     print(rs.Fields.Item(u'TYPE_NAME').Value + ' ' + str(rs.Fields.Item(u'DATA_TYPE').Value))
...     rs.MoveNext()
... 
Logical 11
Memo 129
Numeric 5
Date 133
Char 129

You can look up the codes in adoint.h in the Win32 Platform SDK. The code 135, returned from the Access driver, is adDBTimeStamp. The code 133, returned by the dBASE driver, is adDBDate.

I do not know how to test a time column with ODBC.

HTH, Jason

[jjr8]

I can confirm that this appears to be fixed for the PGeo OGR driver in GDAL 1.6.0 with Python 2.5 bindings on win32:

Python 2.5.2 (r252:60911, Feb 21 2008, 13:11:45) [MSC v.1310 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from osgeo import ogr
>>> ds = ogr.Open('C:\\Example_PDBs\\Arc91.mdb')
>>> layer = ds.GetLayerByName('TestPoints')
>>> layerDefn = layer.GetLayerDefn()
>>> layerDefn.GetFieldDefn(layerDefn.GetFieldIndex('FirstMessageDateTime')).GetType()
11
>>> layerDefn.GetFieldDefn(layerDefn.GetFieldIndex('FirstMessageDateTime')).GetType() == ogr.OFTDateTime
True
>>>

I have not tested the ODBC OGR driver.