GeoTools
  1. GeoTools
  2. GEOT-4405

gt-imagemoasic-jdbc: Case sensitivity problem with Oracle Spatial

    Details

    • Type: Bug Bug
    • Status: Open Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 9.0-beta1
    • Fix Version/s: None
    • Labels:
      None
    • Environment:
      Oracle Spatial database

      Description

      I tried to insert tiles into an Oracle Spatial database using this tutorial:

      http://docs.geoserver.org/stable/en/user/tutorials/imagemosaic-jdbc/imagemosaic-jdbc_tutorial.html#importing-the-image-data

      My mapping file uses oracle spatial extension:

      <spatialExtension name="oracle"/>

      I get the following stack trace:

      java.io.IOException: select srid from USER_SDO_GEOM_METADATA where table_name=? and column_name = ? has no result for osmdata_0,geom
      at org.geotools.gce.imagemosaic.jdbc.Import.calculateSRS(Import.java:600)
      at org.geotools.gce.imagemosaic.jdbc.Import.<init>(Import.java:203)
      at org.geotools.gce.imagemosaic.jdbc.Import.start(Import.java:400)
      at org.geotools.gce.imagemosaic.jdbc.Toolbox.main(Toolbox.java:46)

      Main Cause is the old behaviour of Oracle to turn all entries in USER_SDO_GEOM_METADATA into upper case (see for example http://jira.codehaus.org/browse/GEOT-1965)

      Resolution:

      In JDBCAccessOracle, add "UPPER(..)" to the SELECT statements to:

      	static String SRSSelect = "select srid from ALL_SDO_GEOM_METADATA where owner = UPPER(?) and table_name=UPPER(?) and column_name = UPPER(?)";
      
      	static String SRSSelectCurrentSchema = "select srid from USER_SDO_GEOM_METADATA where table_name=UPPER(?) and column_name = UPPER(?)";
      
      

        Activity

        Hide
        Christian Mueller added a comment -
        Since I am not an Oracle expert, the interesting questions is:

        Do these new statements work for all versions Oracle Spatial ?.

        I do not want to break any existing behaviour.
        Show
        Christian Mueller added a comment - Since I am not an Oracle expert, the interesting questions is: Do these new statements work for all versions Oracle Spatial ?. I do not want to break any existing behaviour.
        Hide
        Björn Höfling added a comment -
        Speaking for Oracle 10 and 11.

        For the tables created with the tool, this works. It could be the case that manually added tables are in lower/mixed case. Then one should also add an UPPER(..) on the left hand side of the equations. And hope that not both cases are present in the table.

        Though I wonder if anybody tested this on Oracle? The current code fails in all standard cases, AFAIK.

        Show
        Björn Höfling added a comment - Speaking for Oracle 10 and 11. For the tables created with the tool, this works. It could be the case that manually added tables are in lower/mixed case. Then one should also add an UPPER(..) on the left hand side of the equations. And hope that not both cases are present in the table. Though I wonder if anybody tested this on Oracle? The current code fails in all standard cases, AFAIK.
        Hide
        Christian Mueller added a comment -
        AFAIK Oracle behaves like DB2.

        create table test (....) creates a table called TEST.

        If you want to use lower case characters, you have to use double quotes

        create table "Test" (...) creates a table called Test.

        IMHO the following would code change would be correct.

        Build the statement dynamically at runtime, if the table name/column name starts and ends with a double column, remove the double columns and use ? in the statement. If the double quotes are missing, use UPPER(?) in the statements.





        Show
        Christian Mueller added a comment - AFAIK Oracle behaves like DB2. create table test (....) creates a table called TEST. If you want to use lower case characters, you have to use double quotes create table "Test" (...) creates a table called Test. IMHO the following would code change would be correct. Build the statement dynamically at runtime, if the table name/column name starts and ends with a double column, remove the double columns and use ? in the statement. If the double quotes are missing, use UPPER(?) in the statements.

          People

          • Assignee:
            Christian Mueller
            Reporter:
            Björn Höfling
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: