Opened 18 years ago

Closed 16 years ago

Last modified 16 years ago

#1172 closed defect (fixed)

GDAL OVF OCI does not work with SrcSQL

Reported by: bartvde@… Owned by: Mateusz Łoskot
Priority: normal Milestone: 1.5.3
Component: OGR_SF Version: unspecified
Severity: normal Keywords: oracle oci
Cc: warmerdam

Description (last modified by warmerdam)

When using SrcSQL for an Oracle (OCI) table all coordinates are reported as 0 (using ogrinfo). When using a SrcLayer to a database view everything works fine.

ovf file used:

<OGRVRTDataSource>
  <OGRVRTLayer name="calamiteitenoud">
<SrcDataSource>OCI:BVDE/BART@//rws-svl012i.int.storage.nwr.local:1521/TGEOS.rws.nl:VW_CALAMITEITEN_OUD</SrcDataSource>
<SrcSQL>SELECT * FROM CALAMITEITEN WHERE ACTUEEL = 0</SrcSQL>
<GeometryType>wkbPoint</GeometryType>
<LayerSRS>EPSG:28992</LayerSRS>
<GeometryField encoding="PointFromColumns" x="CALX" y="CALY"/>
</OGRVRTLayer>
</OGRVRTDataSource>

I will attach a database script to create the tables and data.

Attachments (1)

ogr.tgz (2.7 KB ) - added by bartvde@… 18 years ago.
tgz file with Oracle SQL scripts and shell scripts

Download all attachments as: .zip

Change History (10)

by bartvde@…, 18 years ago

Attachment: ogr.tgz added

tgz file with Oracle SQL scripts and shell scripts

comment:1 by warmerdam, 18 years ago

Mateusz,

Could you try reproducing this?  

comment:2 by warmerdam, 17 years ago

Cc: warmerdam added
Description: modified (diff)
Milestone: 1.4.2
Priority: highnormal

comment:3 by Mateusz Łoskot, 17 years ago

Status: newassigned

I think I reproduced the problem and there is something broken.

If I query OCI layer this way, value of coordinates columns (CALX, CALY) is 0:

mloskot:~/dev/gdal/bugs/1172$ ogrinfo OCI:system/pantera@localhost -sql 'SELECT * FROM CALAMITEITEN WHERE CALID=1'
OCI: Userid=system, Password=pantera, Database=localhost
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
OGR: OGROpen(OCI:system/pantera@localhost/0x804e690) succeeded as OCI.
INFO: Open of `OCI:system/pantera@localhost'
      using driver `OCI' successful.
OCI: ExecuteSQL(SELECT * FROM CALAMITEITEN WHERE CALID=1)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)

Layer name: SELECT * FROM CALAMITEITEN WHERE CALID=1
Geometry: Unknown (any)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
Feature Count: 1
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
Layer SRS WKT:
(unknown)
CALID: Integer (0.0)
CALTITEL: String (255.0)
CALDATETYPE: String (20.0)
CALX: Integer (0.0)
CALY: Integer (0.0)
CALNAAMCOORD: String (255.0)
CALAARD: String (255.0)
CALCODE: String (100.0)
ACTUEEL: Integer (0.0)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
OGRFeature(SELECT * FROM CALAMITEITEN WHERE CALID=1):0
  CALID (Integer) = 1
  CALTITEL (String) =  
  CALDATETYPE (String) = X���X
  CALX (Integer) = 0
  CALY (Integer) = 0
  CALNAAMCOORD (String) = H���H
  CALAARD (String) = X���X
  CALCODE (String) = X���X
  ACTUEEL (Integer) = 0

OCI: 3 features read on layer 'SELECT * FROM CALAMITEITEN WHERE CALID=1'.
OGR: GetLayerCount() = 0

When I query for CALX and CALY explicitly, all values are returned properly:

mloskot:~/dev/gdal/bugs/1172$ ogrinfo OCI:system/pantera@localhost -sql 'SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1'D
OCI: Userid=system, Password=pantera, Database=localhost
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
OGR: OGROpen(OCI:system/pantera@localhost/0x8065a20) succeeded as OCI.
INFO: Open of `OCI:system/pantera@localhost'
      using driver `OCI' successful.
OCI: ExecuteSQL(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)

Layer name: SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D
Geometry: Unknown (any)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)
Feature Count: 1
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)
Layer SRS WKT:
(unknown)
CALX: Integer (0.0)
CALY: Integer (0.0)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)
OGRFeature(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D):0
  CALX (Integer) = 33219
  CALY (Integer) = 669433

OCI: 3 features read on layer 'SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D'.
OGR: GetLayerCount() = 0

My first thought is, perhaps the zero precision in definition of Integer (0.0) is significant here, and the value is just queried as a kind of "empty" value, but not null.

comment:4 by warmerdam, 17 years ago

Keywords: oracle oci added
Milestone: 1.4.21.4.3

Lets put this off for 1.4.3, and try to do a fuller review of oracle spatial support in one big pass.

comment:5 by warmerdam, 17 years ago

Milestone: 1.4.31.5.0

Moving to 1.5 to lighten 1.4.3 load.

comment:6 by warmerdam, 16 years ago

Milestone: 1.5.01.5.1

OCI issues deferred to 1.5.1.

comment:7 by warmerdam, 16 years ago

Milestone: 1.5.11.5.2

comment:8 by Mateusz Łoskot, 16 years ago

Resolution: fixed
Status: assignedclosed

After a long break, I got back to this issue and I tried to reproduce it. And, I've revealed that I can not reproduce it. In other words, using the same dataset from the attached ogr.tgz file and the same two ogrinfo commands, I've observed that OCI driver works, means in both cases CALX and CALY coordinates are correctly retrieved.

Using GDAL SVN + Oracle 10g Express Edition on Ubuntu 7.04, here is the proof:

  • SELECT * FROM...
    $ ogrinfo OCI:system/pantera@localhost -sql 'SELECT * FROM CALAMITEITEN WHERE CALID=1'
    OCI: Userid=system, Password=pantera, Database=localhost
    OCI: Prepare(ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'         NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS TZHTZM'         NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS'         NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS TZHTZM')
    OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
    OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEST_POINTS' AND COLUMN_NAME = 'ORA_GEOMETRY')
    OGR: OGROpen(OCI:system/pantera@localhost/0x804d468) succeeded as OCI.
    INFO: Open of `OCI:system/pantera@localhost'
          using driver `OCI' successful.
    OCI: ExecuteSQL(SELECT * FROM CALAMITEITEN WHERE CALID=1)
    OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
    
    Layer name: SELECT * FROM CALAMITEITEN WHERE CALID=1
    Geometry: Unknown (any)
    OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
    Feature Count: 1
    OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
    Layer SRS WKT:
    (unknown)
    CALID: Integer (0.0)
    CALTITEL: String (255.0)
    CALDATE: Date (0.0)
    CALDATETYPE: String (20.0)
    CALX: Integer (0.0)
    CALY: Integer (0.0)
    CALNAAMCOORD: String (255.0)
    CALAARD: String (255.0)
    CALCODE: String (100.0)
    ACTUEEL: Integer (0.0)
    OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
    OGRFeature(SELECT * FROM CALAMITEITEN WHERE CALID=1):0
      CALID (Integer) = 1
      CALTITEL (String) =  
      CALDATE (Date) = (null)
      CALDATETYPE (String) =  
      CALX (Integer) = 33219
      CALY (Integer) = 669433
      CALNAAMCOORD (String) =  
      CALAARD (String) = 0
      CALCODE (String) =  
      ACTUEEL (Integer) = 0
    
    OCI: 3 features read on layer 'SELECT * FROM CALAMITEITEN WHERE CALID=1'.
    OGR: GetLayerCount() = 1
    
  • SELECT ALX, CALY FROM...
r$ ogrinfo OCI:system/pantera@localhost -sql 'SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1' 
OCI: Userid=system, Password=pantera, Database=localhost
OCI: Prepare(ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'         NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS TZHTZM'         NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS'         NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS TZHTZM')
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEST_POINTS' AND COLUMN_NAME = 'ORA_GEOMETRY')
OGR: OGROpen(OCI:system/pantera@localhost/0x804d468) succeeded as OCI.
INFO: Open of `OCI:system/pantera@localhost'
      using driver `OCI' successful.
OCI: ExecuteSQL(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)

Layer name: SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1
Geometry: Unknown (any)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)
Feature Count: 1
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)
Layer SRS WKT:
(unknown)
CALX: Integer (0.0)
CALY: Integer (0.0)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)
OGRFeature(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1):0
  CALX (Integer) = 33219
  CALY (Integer) = 669433

OCI: 3 features read on layer 'SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1'.
OGR: GetLayerCount() = 1

I may be wrong, but probably it's got fixed with recent submissions to the OCI driver made by Tamas.

Bart, I'm closing this ticket as fixed. But, if it's still not working for you, please reopen with additional comments.

comment:9 by Mateusz Łoskot, 16 years ago

I've also tested OCI driver (GDAL SVN) and confirmed it works well remotely connecting from Windows XP (a client with OracleXEClient.exe software installed) to Oracle 10g installed on Ubuntu 7.04:

  • SELECT * FROM
    D:\dev\gdal\_svn\trunk\gdal>apps\ogrinfo OCI:system/pantera@10.211.55.7 -sql "SELECT * FROM CALAMITEITEN WHERE CALID=1"
    INFO: Open of `OCI:system/pantera@10.211.55.7'
          using driver `OCI' successful.
    
    Layer name: SELECT * FROM CALAMITEITEN WHERE CALID=1
    Geometry: Unknown (any)
    Feature Count: 1
    Layer SRS WKT:
    (unknown)
    CALID: Integer (0.0)
    CALTITEL: String (255.0)
    CALDATE: Date (0.0)
    CALDATETYPE: String (20.0)
    CALX: Integer (0.0)
    CALY: Integer (0.0)
    CALNAAMCOORD: String (255.0)
    CALAARD: String (255.0)
    CALCODE: String (100.0)
    ACTUEEL: Integer (0.0)
    OGRFeature(SELECT * FROM CALAMITEITEN WHERE CALID=1):0
      CALID (Integer) = 1
      CALTITEL (String) =
      CALDATE (Date) = (null)
      CALDATETYPE (String) =
      CALX (Integer) = 33219
      CALY (Integer) = 669433
      CALNAAMCOORD (String) =
      CALAARD (String) = 0
      CALCODE (String) =
      ACTUEEL (Integer) = 0
    
  • SELECT CALX, CALY FROM
    D:\dev\gdal\_svn\trunk\gdal>apps\ogrinfo OCI:system/pantera@10.211.55.7 -sql "SELECT CALX,CALY FROM CALAMITEITEN WHERE CALID=1"
    INFO: Open of `OCI:system/pantera@10.211.55.7'
          using driver `OCI' successful.
    
    Layer name: SELECT CALX,CALY FROM CALAMITEITEN WHERE CALID=1
    Geometry: Unknown (any)
    Feature Count: 1
    Layer SRS WKT:
    (unknown)
    CALX: Integer (0.0)
    CALY: Integer (0.0)
    OGRFeature(SELECT CALX,CALY FROM CALAMITEITEN WHERE CALID=1):0
      CALX (Integer) = 33219
      CALY (Integer) = 669433
    

Unfortunately, I'm unable to test OCI with Oracle 9 and 8.

Note: See TracTickets for help on using tickets.