[gdal-dev] Query regarding results from ExecuteSQL and the OCI driver - pre OGR 1.8.n

[Frank Warmerdam]

Peter J Halls wrote:
> Dear Team,
> 
>     I'm waiting for 1.8 to be firmed up before upgrading to it, so this 
> relates to the old OCI driver.

Peter,

You are in luck.  This weekend I managed to install Oracle on my
ubuntu development system, so my interest level in matters oracle has
suddenly jumped substantially.

>     I'm puzzled.  In order to work around the possibility that a table 
> may not exist, and therefore require to be created, I attempted to use 
> ExecuteSQL through the OCI driver with the SQL expression
> "Select * from user_catalog where table_name like '<required table>'"
> 
>     The call returns a non-zero value, but when treating this as a 
> layerhandle and looking to check that the layer name is that requested I 
> find the results of  OGR_FD_GetName are the text of my original SQL 
> query.  Looking at the code in ogrocidatasource I can see no apparent 
> reason for this result.

What does it matter what the name of the layer is in this case?  It
is true that the OCI driver assigns the whole query as the layer name
of the query result but this should not normally cause any problem.

I checked a few other drivers and find that:

   shapefile: identifies the primary shapefile being processed and
              uses it's name for the sql query layer.

   postgis: uses the fixed text "sql_statement" as the layer name for
            the result.

>     Am I going about my query incorrectly?  Should I be expecting to 
> obtain a valid layer from this query or is a non-zero return value 
> adequate indication that the result of the query contains one or more 
> table names?

I believe you are getting back a perfectly ok query layer that
happens to have a somewhat esoteric, yet descriptive name.

BTW, I run a query like yours against my instance and get back reasonable
results:

ogrinfo OCI:warmerda/tiger at localhost \
-sql "select * from user_catalog where table_name like 'R%'" --debug off

INFO: Open of `OCI:warmerda/tiger at localhost'
       using driver `OCI' successful.

Layer name: select * from user_catalog where table_name like 'R%'
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
TABLE_NAME: String (30.0)
TABLE_TYPE: String (11.0)
OGRFeature(select * from user_catalog where table_name like 'R%'):0
   TABLE_NAME (String) = ROAD
   TABLE_TYPE (String) = TABLE


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    | Geospatial Programmer for Rent