r25656 (#4966) breaks -update -append on ogr2ogr

[msmitherdc]

The #4966 ticket addition has broken the -update -append operation of ogr2ogr using the OCI driver.

It works correctly at r25655

[Even Rouault]

Sorry Dmitry : I really intended to put jef in CC instead as he's the author of the pull request of #4966

[msmitherdc]

the -update -append loads we do, loading data to oracle spatial tables started failing after rebuilding with r25656

instead of detecting the existing table, it trys to do a create table and then fails

i went back to pre #4966 and it worked as expected so it did seem to be that patch that affected the table selection/detection

[jef]

Replying to msmitherdc:

the -update -append loads we do, loading data to oracle spatial tables started failing after rebuilding with r25656

How are the tables called that appear to be affected?

[msmitherdc]

Ogr2ogr -update -append -f oci oci;user/pass@tns:schema.table sourcefile.shp

[Even Rouault]

Michael,

perhaps jef wants the *exact* table names that your are using, in case this makes a difference (I guess that the issue might be in the new GetLayerByName() method)

[msmitherdc]

The exact table name was

lpcp_inactive

[jef]

Replying to msmitherdc:

The exact table name was

lpcp_inactive

ie. completely lower-case?

[msmitherdc]

The value we used at the command line was completely lower case. Oracle table names are uppercase unless enclosed by quotes and oracle treats a lowercase name as equivalent unless it is also quoted so this is commonly done.

[jef]

Replying to msmitherdc:

The value we used at the command line was completely lower case. Oracle table names are uppercase

Ok, then it's still not reproducable.

ogr2ogr -f OCI -overwrite OCI:test/test@mapserver/ORCL lpcp_inactive.shp
ogr2ogr -f OCI -append -update OCI:test/test@mapserver/ORCL lpcp_inactive.shp

produces a table "LPCP_INACTIVE" and has twice a much rows as the shape. Please include more information - preferably a reproducable example.

[msmitherdc]

It would be

ogr2ogr -f OCI -append -update OCI:test/test@mapserver/ORCL:test.lpcp_inactive some_other_file.shp -nln lpcp_inactive

[ilucena]

I could not replicated that problem.

[Even Rouault]

Could it depend on the server version... ?

[msmitherdc]

I just built on linux and was able to replicate the problem. Is a case sensitivity issue.

Example

[gridusr@gridvm6 ~]$ ogrinfo oci:user/pass@localhost/vm:us_state_fips_lk -al OCI: Userid=user, Password=pass, Database=localhost/vm 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') ERROR 1: ORA-04043: object "us_state_fips_lk" does not exist

in OCIDescribeAny

ERROR 1: ORA-04043: object "us_state_fips_lk" does not exist

in OCIDescribeAny

OGR: OGROpen(oci:grid/grid@localhost/vm:us_state_fips_lk/0xd45e80) succeeded as OCI. INFO: Open of `oci:grid/grid@localhost/vm:us_state_fips_lk'

using driver `OCI' successful.

OGR: GetLayerCount() = 0

[gridusr@gridvm6 ~]$ ogrinfo oci:user/pass@localhost/vm:US_STATE_FIPS_LK -al OCI: Userid=user, Password=pass, Database=localhost/vm 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 COUNT(*) FROM ALL_SDO_GEOM_METADATA u, TABLE(u.diminfo) t WHERE u.table_name = 'US_STATE_FIPS_LK' AND u.column_name = 'SHAPE') OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER('US_STATE_FIPS_LK') AND COLUMN_NAME = UPPER('SHAPE')) OCI: Prepare(SELECT WKTEXT, AUTH_SRID, AUTH_NAME FROM MDSYS.CS_SRS WHERE SRID = 8265 AND WKTEXT IS NOT NULL) OGR: OGROpen(oci:grid/grid@localhost/vm:US_STATE_FIPS_LK/0x15c8e80) succeeded as OCI. INFO: Open of `oci:grid/grid@localhost/vm:US_STATE_FIPS_LK'

using driver `OCI' successful.

OGR: GetLayerCount() = 1

Layer name: US_STATE_FIPS_LK Geometry: Unknown (any) OCI: Prepare(SELECT COUNT(*) FROM US_STATE_FIPS_LK ) Feature Count: 57 OCI: Prepare(SELECT MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(t.SHAPE,m.DIMINFO,1)) AS MINX,MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(t.SHAPE,m.DIMINFO,2)) AS MINY,MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(t.SHAPE,m.DIMINFO,1)) AS MAXX,MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(t.SHAPE,m.DIMINFO,2)) AS MAXY FROM ALL_SDO_GEOM_METADATA m, US_STATE_FIPS_LK t WHERE m.TABLE_NAME = UPPER('US_STATE_FIPS_LK') AND m.COLUMN_NAME = UPPER('SHAPE')) Extent: (-179.144806, -14.605210) - (179.764160, 71.332649) Layer SRS WKT:

[msmitherdc]

[gridusr@gridvm6 ~]$ svn up -r 25655 ... [gridusr@gridvm6 ~]$ ogrinfo oci:user/pass@localhost/vm:us_state_fips_lk -al -so INFO: Open of `oci:user/pass@localhost/vm:us_state_fips_lk'

using driver `OCI' successful.

Layer name: us_state_fips_lk Geometry: Unknown (any) Feature Count: 57 Extent: (-179.144806, -14.605210) - (179.764160, 71.332649) ...

[ilucena]

That seems to be the "case"

[ilucena]

Jef,

Do we really need that quoted table name on that line:

http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/oci/ogrocitablelayer.cpp?rev=25656#L158

Why?

Michael,

Did you try to get ogrinfo with owner.table_name on the table list? In both revisions?

I remove the quotes from Jef's code and ogrinfo works with upper or lower case but it doesn't work with owner.table_name.

Regards,

Ivan

[msmitherdc]

Ivan,

On current master, it only works for me with an uppercase tablename and no owner, with or without quotes.

[jef]

Replying to ilucena:

Do we really need that quoted table name on that line: http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/oci/ogrocitablelayer.cpp?rev=25656#L158

Why?

Yes, otherwise DELETE is not recognized (a table used in NAS).

[msmitherdc]

At r25655, any combination of case on tablename or owner.tablename works

[msmitherdc]

Does the Oracle driver need to allow DELETE as a tablename? Thats not a valid name in Oracle, its a actual command

[jef]

Replying to msmitherdc:

Does the Oracle driver need to allow DELETE as a tablename? Thats not a valid name in Oracle, its a actual command

Sure it is - it just needs to be quoted.

[ilucena]

Yes, you can use anything in quotes as a table name but I can't see it working properly on that latest release either.

I tried to load a shapefile named delete.shp and got error like that:

C:\Temp>ogr2ogr -f oci -overwrite oci:scott/tiger@orcl delete.shp
ERROR 1: ORA-04043: object "NAVTEQ_SF.GC_ROAD_SEGMENT_NVT" does not exist
 in OCIDescribeAny
ERROR 1: ORA-04043: object "NAVTEQ_SF.GC_ROAD_SEGMENT_NVT" does not exist
 in OCIDescribeAny
ERROR 1: ORA-01031: insufficient privileges
 in INSERT INTO MDSYS.CS_SRS (SRID, WKTEXT, CS_NAME)  VALUES (1000003006,'GEOGCS
["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_84",6378137,298.257223563]],PRIME
M["Greenwich",0],UNIT["Decimal Degree",0.0174532925199433]]', 'GCS_WGS_1984' )
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "NAME" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "ISO_3_CODE" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "ISO_2_CODE" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "AREA" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "NAME_1" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "POP2005" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "REGION" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "GMI_CNTRY" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in ALTER TABLE DELETE ADD "NAME_12" VARCHAR2(80)
ERROR 1: ORA-00903: invalid table name
 in CREATE INDEX "DELETE_IDX" ON DELETE("ORA_GEOMETRY") INDEXTYPE IS MDSYS.SPATI
AL_INDEX
ERROR 1: ORA-01418: specified index does not exist
 in DROP INDEX "DELETE_IDX"

Try to extract from that layer and got that errors:

C:\Temp>ogr2ogr -f "ESRI Shapefile" xpto.shp oci:scott/tiger@orcl delete
ERROR 1: ORA-04043: object "NAVTEQ_SF.GC_ROAD_SEGMENT_NVT" does not exist
 in OCIDescribeAny
ERROR 1: ORA-04043: object "NAVTEQ_SF.GC_ROAD_SEGMENT_NVT" does not exist
 in OCIDescribeAny
ERROR 1: ORA-00903: invalid table name
 in SELECT "ORA_GEOMETRY","OGR_FID" FROM DELETE

It seems like more work need to be done to support the name DELETE or any weird quoted name.

[ilucena]

For Michael problem I thing we need to add more quotes on that code, like: "owner"."table_name", not "owner.table_name"

[jef]

Replying to ilucena:

Yes, you can use anything in quotes as a table name but I can't see it working properly on that latest release either.

For NAS to work properly the schema need to be created beforehand. So OGR never needs to create the DELETE table, but still needs to recognize the existing table.

[ilucena]

OK. I understand.

Every time I look at that code I found new reasons to re-write it :)

Anyway, what I see by running on the two different versions of the code in Windows is that even if your shapefile name is "UPdown.SHP" the table will be created as "UPDOWN". I will check that on Linux too.

So even with the fix that I just did you still have to enter uppercase on the table list because that is the real table name.

Users are used to type lowercase on OGR commands and that was working so far and we need to restore that functionality somehow.

[ilucena]

Fix available on r25759

Just to confirm. A geometry tables names must be uppercase.

The use of the table name DELETE is strongly discouraged. It might be a good idea to create an special for it on "OGROCISession::CleanName"

Please, help me out with some tests and let me know if it is working for you all.

Regards,

Ivan

[msmitherdc]

Only case that didn't work for me is cross-schema access

[gridusr@gridvm6 ~]$ ogrinfo oci:userb/pass@localhost/vm:grid.us_state_fips_lk -al -so INFO: Open of `oci:userb/pass@localhost/vm:grid.us_state_fips_lk'

using driver `OCI' successful.

Layer name: us_state_fips_lk Geometry: Unknown (any) ERROR 1: ORA-00942: table or view does not exist

in SELECT COUNT(*) FROM us_state_fips_lk

ERROR 1: ORA-24374: define not done before fetch or execute and fetch

in OCIStmtFetch

ERROR 1: ORA-00942: table or view does not exist

in SELECT "SHAPE","OBJECTID","NAME","STATE_ABBR","UP_NAME","ST","OGR_FID" FROM us_state_fips_lk

Feature Count: 0 Extent: (-179.144806, -14.605210) - (179.764160, 71.332649) Layer SRS WKT: GEOGCS["Longitude / Latitude (NAD 83)",

DATUM["NAD 83",

SPHEROID["GRS 80",6378137,298.257222101]],

PRIMEM["Greenwich",0.000000], UNIT["Decimal Degree",0.01745329251994330], AUTHORITY["Oracle","8265"]]

FID Column = OGR_FID Geometry Column = SHAPE OBJECTID: Integer (11.0) NAME: String (60.0) STATE_ABBR: String (2.0) UP_NAME: String (100.0) ST: String (2.0)

[ilucena]

Updated on r25770

Please try this one.

Note that in case you entered uppercase table name you should do the same with layer name.

ogrinfo oci:userA/passwd@db:userB.TABLEQ userB.TABLEQ

The previous version was accepting:

ogrinfo oci:userA/passwd@db:userB.TABLEQ userB.tableQ

But that is not working now. Is that a problem?