OCI driver does not seem to support date/time field combinations used by ESRI

[bclay]

I had several problems using OGR in conjunction with ESRI based tables in Oracle and exporting the tables to shapefiles. They appear to be related to the date / time combination used in the tables which is fully supported by Oracle. All of the changes are listed below.

Ogrocidatasource.cpp – add support to set field type for unique ID column. Without this change tables are created that don’t work with ArcMap/ArcCatalog – change in 2 places

Ogrocisession.cpp Add SQLT_DAT for date time support – change one place

Ogrodbcdatasource.cpp – use existing but unused bTestOpen parameter to allow used to control whether to build list of all tables

Ogrshapelayer.cpp – added support to split date/time fields into separate date and time fields rather than just striping off the time as currently happens

Shape2ogr.cpp - added support to split date/time fields into separate date and time fields rather than just striping off the time as currently happens

[warmerdam]

Mateusz,

Please review the OCI related changes for possible inclusion.

I do *not* believe that the shapefile driver should be modified to split datetime values into two internal fields. So please leave aside those changes.

I'm dubious about the described use of bTestOpen. It does not seem to have anything to do with the intended use of it.

[warmerdam]

I'm deep in the oracle driver now, so I'll take this one.

[warmerdam]

After quick a bit of fudzing around, I've come to the conclusion that the actual patch for the date change is:

*** old/ogrocisession.cpp	Mon Feb 11 08:13:18 2008
--- ogrocisession.cpp	Wed Aug 27 17:17:16 2008
***************
*** 356,361 ****
--- 355,361 ----
          break;
  
          case SQLT_DATE:
+ 		case SQLT_DAT:
              poOGRDefn->SetType( OFTString );
              poOGRDefn->SetWidth( 24 );
              break;

Bruce, can you supply an example of how a field of this type would be declared and populated with an INSERT statement? I'd like to add a test for this to our regression test.

Also, I believe the FID change is:

*** old/ogrocidatasource.cpp	Tue Dec 25 12:25:25 2007
--- ogrocidatasource.cpp	Wed Aug 27 17:16:42 2008
***************
*** 455,460 ****
--- 455,468 ----
      if( pszGeometryName == NULL )
          pszGeometryName = "ORA_GEOMETRY";
  
+ 
+ /* -------------------------------------------------------------------- */
+ /*      Determine name of Unique ID column to use.                       */
+ /* -------------------------------------------------------------------- */
+ 
+ 	const char *pszFIDDataType = 
+         CPLGetConfigOption( "FID_DATA_TYPE", "INTEGER" );    
+ 
  /* -------------------------------------------------------------------- */
  /*      Create a basic table with the FID.  Also include the            */
  /*      geometry if this is not a PostGIS enabled table.                */
***************
*** 465,473 ****
      OGROCIStatement oStatement( poSession );
      sprintf( szCommand, 
               "CREATE TABLE \"%s\" ( "
!              "%s INTEGER, "
               "%s %s )",
!              pszSafeLayerName, pszExpectedFIDName, pszGeometryName, SDO_GEOMETRY );
  
      if( oStatement.Execute( szCommand ) != CE_None )
      {
--- 473,482 ----
      OGROCIStatement oStatement( poSession );
      sprintf( szCommand, 
               "CREATE TABLE \"%s\" ( "
!              "%s %s, "
               "%s %s )",
!              pszSafeLayerName, pszExpectedFIDName, pszFIDDataType,
! 			 pszGeometryName, SDO_GEOMETRY );
  
      if( oStatement.Execute( szCommand ) != CE_None )
      {

Does that look right? Is the intention that someone wanting to create a table that can be used from SDE will need to set FID_DATA_TYPE to some particular special value in their environment or via other config methods? If so, what value? Might it make sense to just use that type by default?

The text for the bug reports stuff about several other files but not in enough detail to act on. What is the scoop on them?

[tamas]

I don't know how it is related but as far as I remember I've already added the support for the dates and times incl. SQLT_DAT in the trunk. See r14363

[warmerdam]

Tamas,

Excellent, indeed with your change we can disregard the date related part of this ticket as already addressed.

[Nicolas Simon]

Tamas,

The last part of the ticket is your request to handle other datatype for FID, Isn't ?

I think it's a problem since FID should be an integer related type to implement long OGRFeature::GetFID ()

Considering other data type for FID will break OGR API.

Nicolas

[warmerdam]

Ivan,

Could you review and see if there are outstanding items to address here?

[Jukka Rahkonen]

I tried to understand this ticket with poor success. Does it mean that in the beginning the situation is that there are tables in Oracle which are created with ESRI SDE and which contain columns having some Oracle datatime datatype. These are (captured from ​http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm)

The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE,
and TIMESTAMP WITH LOCAL TIME ZONE. Values of datetime datatypes 
are sometimes called datetimes.

After r14363 all these should be supported.

Problems with exporting advanced date formats belong to the nature of shapefile because the dBase format supports natively only YYYY/MM/DD. There is no perfect workaround for this.

The FID stuff in this ticket is not mentioned in summary nor in description so it would have earned rather its own ticket.

I repeat the question: Could you review and see if there are outstanding items to address here?

[Jukka Rahkonen]

Closing as fixed because a) no feedback b) ESRI SDE is mostly gone.