OGR PGeo driver should recognize 16-bit integer fields as OFTInteger rather than OFTString

[jjr8]

PGeo wraps ESRI personal geodatabases, which are MS Access databases. ArcGIS allows creation of both 32-bit signed and 16-bit signed integer fields. In the ArcGIS GUI, these types are called "LONG" and "SHORT". In the MS Access GUI, they are called "Long Integer" and "Integer".

PGeo currently support 32-bit correctly but not 16-bit. It recognizes 16-bit fields as OFTString:

>>> from osgeo import ogr
>>> ds = ogr.Open('C:\\Example_PDBs\\Arc91.mdb')
>>> layer = ds.GetLayerByName('TestPoints')
>>> layerDefn = layer.GetLayerDefn()
>>> layerDefn.GetFieldDefn(layerDefn.GetFieldIndex('PassDuration')).GetType()
4
>>> layerDefn.GetFieldDefn(layerDefn.GetFieldIndex('PassDuration')).GetType() == ogr.OFTString
True

I believe this is because the switch statement in ogrpgeolayer.cpp only tests for SQL_INTEGER:

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

I think the problem can be fixed by also testing for SQL_SMALLINT:

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

Unlike issue #2691, I believe the odbc driver handles this problem by classifying 32-bit and 16-bit signed and unsigned as OFT_Integer. (It may be dangerous to classify 32-bit unsigned as OFTInteger, but currently there is not OFTUnsignedInteger type, so it seems reasonable to provide partial support for 32-bit unsigned by going with OFTInteger.)

Although MS Access supports other types of integer fields besides 32-bit signed and 16-bit signed, ArcGIS does not. Therefore I do not think you need to add support for those additional types. Simply adding support for 16-bit integer will be sufficient to achieve compatibility with ArcGIS.

I will attach a ZIP with the PDB I used in this example, so you can test your implementation.

[jjr8]

Personal geodatabase referenced by this ticket

[Even Rouault]

Thanks

Fixed in trunk in r15784.

Don't hesitate to attach your proposed correction as patches, as you're doing well at finding and fixing bugs ;-)

[jjr8]

I can confirm that this is fixed in GDAL 1.6.0 for 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('PassDuration')).GetType()
0
>>> layerDefn.GetFieldDefn(layerDefn.GetFieldIndex('PassDuration')).GetType() == ogr.OFTInteger
True
>>>