Opened 14 years ago

Closed 7 years ago

#1969 closed enhancement (fixed)

schema support for OGR ODBC

Reported by: jml Owned by: warmerdam
Priority: high Milestone: 1.8.1
Component: OGR_SF Version: 1.4.3
Severity: normal Keywords: schema odbc oracle
Cc: assefa

Description (last modified by warmerdam)

The OGR ODBC driver appears to ignore schema names. In a database with particular schemas it is necessary to segregate the results from SQLColumns() based on the schema, and to include the schema when doing sql queries (eg. 'select * from schema.tablename').

Changes will be required to the cpl_odbc code in gdal/port, and to the OGR ODBC driver to handle such situations properly. The problem can occur with Oracle via ODBC, but presumably it can also occur with other databases supporting schemas.

Change History (9)

comment:1 Changed 14 years ago by warmerdam

Cc: Mateusz Łoskot ilucena added
Component: defaultOGR_SF

jml,

I'm afraid I don't see the issue. SQL passed via -sql is turned over directly to the database to evaluate, and OGR performs no additional processing on it. If Oracle requires the schema name in the SQL then so will OGR's SQL passthrough option.

comment:2 Changed 14 years ago by jml

What we tried to do is, to access a flat (non spatial) table via mapserver -> ogr (.ovf file) -> ODBC and display it. We could not succeed in doing so, because ogrinfo does not supply the schema name in the sql request (even if the table is found, and the fields are displayed). The -sql option was just for testing purposes, to make sure the missing schema name is the problem. With our workaround in the database, the access is now possible without schema name. What I wanted to suggest, is to implement a feature in ogr that automatically adds the schema name in front of the table name in all sql requests, if given in the connect string. If I'm not clear enough or if there is already an option like that (and I did manage to find it), please let me know.

comment:3 Changed 14 years ago by warmerdam

Cc: warmerdam added; Mateusz Łoskot ilucena removed
Description: modified (diff)
Owner: changed from warmerdam to Mateusz Łoskot
Priority: normallow
Summary: schema support for Oracle ODBCschema support for OGR ODBC

I'm rewriting the main description to capture what I believe the problem is. The original report was:

""" We have a connection to an Oracle 10g Database via unixODBC and Oracle InstantClient??. The database user has not been assigned to the database schema. 'ogrinfo ODBC:user/pass@dsn,table table' did show us the table structure (fields), but the sql query 'select * from table' did return no results, because the schema name is missing. If we insert the option '-sql "select * from schema.table", we get the results.

For the moment we have soluted that case with an oracle internal workaround, but it would be nice to have a schema support in ogr. """

Turning over to Mateusz to handle as time permits. This is likely to be a substantial amount of work, so I'm not going to make it "1.5.0" milestone. Great care should be made in a schema upgrade to not alter behavior in cases that are working now. That is, we don't want folks who upgrade to suddenly find that all layer names are now prefixed with a schema if it isn't necessary.

Dropping Ivan off the cc: list since this isn't really very oracle specific.

comment:4 Changed 12 years ago by warmerdam

Cc: assefa added; warmerdam removed
Milestone: 1.7.0
Owner: changed from Mateusz Łoskot to warmerdam
Priority: lowhigh

I have also encountered this problem with SQL Server and a table in a schema which is impossible to access as a regular (ogrodbctablelayer) layer.

The low level CPL ODBC services already seem to support schemas well, so I'm going to operate on ogrodbctablelayer.cpp to add support for parsing schemas out of layer names passed in the table list in the datasource.

comment:5 Changed 12 years ago by warmerdam

Status: newassigned

I have made a patch in trunk (r17870) which does a number of things:

  • ogrodbctablelayer.cpp now supports the layer name coming in with the form schema.table and it will properly query for the primary key and table info using these components.
  • ogrodbcdatasource.cpp now will prepare non-empty schemas to the table name when creating layer names when all tables are enumerated.
  • docs are updated to very briefly note these changes.

Currently the ODBC driver will still enumerate all tables and views in all schemas by default. For most big databases this is a pretty expensive process due to the dozens or hundreds of system tables. I wonder if it might be better to only enumerate tables in the default schema by default. I'm a bit leery about such a change though. It can be nice to get a list of all the system tables.

I will seek feedback on the gdal-dev list.

The changes are likely suitable to backport into 1.6 branch, but I'm not doing so quite yet.

comment:6 Changed 12 years ago by warmerdam

Changes ported to 1.6 (r17872) and 1.5 (r17873) branches. OSGeo4W 1.5, 1.6 and dev packages reissued.

comment:7 Changed 12 years ago by assefa

tested successfully using osgeo4w 1.5

comment:8 Changed 7 years ago by Jukka Rahkonen

I think that the fix was done 5 years ago to GDAL 1.7.0 but this ticket was just not closed. Can someone confirm?

comment:9 Changed 7 years ago by Jukka Rahkonen

Resolution: fixed
Status: assignedclosed

I checked a few changes from r17873 and they seem to be living in GDAL code.

Note: See TracTickets for help on using tickets.