[gdal-dev] more OGR problems

[Frank Warmerdam]

Clay, Bruce wrote:
> I am trying several different things to get my application to read data 
> from an Oracle Spatial table and still can not come up with a solid 
> solution.

Bruce,

This is an oracle spatial table intended to be accessed through SDE,
is that right?

> I have tried ODBC but can not get it to connect to the table.  My 
> application usually hangs on the ogr.open() statement.

This may be because OGR is attempting to create layer objects from all
tables in the database.  This can be very very slow for complex
databases.

 >   I have tried
> “ODBC:username/password at instanceName”, but I get an error that the 
> driver is not specified.

I'm not sure where that error is coming from.  Is 'instanceName' a
legitimate ODBC DSN?

 >   I have also tried “ODBC:{Microsoft ODBC for
> Oracle};Server=instance;UID=userName;PWD=password;". This one hangs as 
> does any others when I specify a driver.

I don't think I've ever tried something like this.  If it is hanging,
it may once again be the "too many tables to enumerate" problem.

> I have tried OCI and can get connected but it only shows a few tables in 
> the whole database.  It only shows those with SDO metadata set.

This is as intended, though I think you should be able to specify a
specific table in the OCI connection string and just access that even if
it doesn't have sdo metadata.  I'm not really sure about this though.

> I can retrieve data from the table with the exception of date values.  I 
> can use the DateTime field in the query itself and it seems to work ok 
> but I can not return the actual date.

I'm no sql guru, but if there is some way of casting the field
contents to string in an SQL statement that might work.

> I can get the index to the “SHAPE” and IsFieldSet returns true but 
> geometry = feature.GetGeometryDef() always returns NULL.

Assuming that shape is an SDE blob geometry instead of an
oracle spatial type geometry object, this is what I would
expect.

> I can connect using the SDE driver but I get an odd error when I issue 
> the same SQL that works with the OCI driver.  The error is “Could not 
> execute SQL statement\nSQL: Syntax error, 14 extra tokens”.  When I use 
> the SDE driver I see all of the tables and all of the fields in the tables.

I'm surprised.  "14 extra tokens" does not sound like something the
OGR SQL parser would report, and the OGR SDE driver does not have a
custom SQL parser - just the generic OGR parser.  On the other hand,
SQL statements against OCI are passed directly through to Oracle.

So I'm confused on this one.

> I keep hoping if that someone out there has run across some of these 
> problems and can give me a clue or something to try.  I would like to 
> ditch C# but that is not an option at this point.

If you are wanting to access SDE managed spatial tables in oracle,
then I believe you should be doing so through the SDE driver for
maximum correctness.  If there are specific bugs in the SDE driver
we can try a few things to try and fix them.

I feel for your frustration, but unfortunately, SDE on Oracle is not something
that I have conveniently available for testing.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGeo, http://osgeo.org