Frank, What sort of SQL are you using when you query Oracle and SQL Server?
What role does the INFORMATION_SCHEMA play in you changed ODBC driver? I noticed that, with the current version if I created an INFORMATION_SCHEMA and the appropriate views, ogrinfo started to work. With Oracle, if you use the DBA_* views (eg DBA_OBJECTS) you will see tables in schemas that you may not actually have permission to read/update. ALL_* (eg ALL_OBJECTS) will show you those the connecting user can see (eg SELECT) but may not be able to update. My point is this: you should really only show the tables that the connecting user can actually read/update. No point in being able to see all sorts of other table/columns if you can't actually use them. regards Simon On Thu, 22 Oct 2009 16:06:11 +1100, Frank Warmerdam <[email protected]> wrote:
Folks, This evening I have done some long outstanding work on the OGR ODBC driver to support tables within schemas reasonable. http://trac.osgeo.org/gdal/ticket/1969 Previously the default behavior of the driver was to treat all tables and views in the ODBC datasource as layers. For heavy duty RDBMSes like Oracle, SQL Server this included all the system tables though due to the lack of proper schema handling the system table "layers" were not actually readable as normal layers. That is now fixed. Enumerating all tables and views as layers can be quite expensive at runtime - it just takes several seconds to query all the field information for all these tables and turn it into OGRLayer objects. In the past my advice to people has been to list only the tables they need in the OGR datasource name they connect with. eg. ODBC:MyDB,table1,table This ensures that only the listed tables are queried for details and speeds things up substantially. However, I am now wondering if it would be better to only enumerate tables in the empty/default schema by default if no table list is provided in the datasource string. This would dramatically speed up the connection speed for Oracle, SQL Server, etc, and also keep huge numbers of useless layers out of people faces in GUI apps like QGIS. The main downside is that it would be substantially harder to find out about potentially useful system tables via the OGR API though they could still be accessed by listing them in the table list, or pass through SQL queries. Does anyone have any opinion on this? Feel free to reply here, drop me a note, or add a note to the ticket. Best regards,
-- SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: [email protected] Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18" E) Latitude: -43.01530 (43° 00' 55" S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 _______________________________________________ gdal-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/gdal-dev
