[gdal-dev] ogr, vrt - connecting to arcsde errors only with srcsql

[Duarte Carreira]

Hi Even.

Thanks, using single-quotes around the fully qualified table name solved it. But I cannot use a where clause... it always results in an error. I have tried everything I could remember: single quotes, double-quotes, fully qualified field name, quotes and double-quotes on the value... nothing worked.

A simple query with a numeric field:
SELECT * FROM 'GDBMAN.SREGA_PERIMETROS_REGA' WHERE OBJECTID_1=21522

Gives this error:
Layer name: regadio
ERROR 1: SE_stream_fetch: -51/Underlying DBMS error

If I use the fully qualified field name:
SELECT * FROM 'GDBMAN.SREGA_PERIMETROS_REGA' WHERE 'GDBMAN.SREGA_PERIMETROS_REGA.OBJECTID_1'=21522

Results in this error:
ERROR 1: Type mismatch or improper type of arguments to = operator.
ERROR 1: SQL statement failed, or returned no layer result:
SELECT * FROM 'GDBMAN.SREGA_PERIMETROS_REGA' WHERE 'GDBMAN.SREGA_PERIMETROS_REGA
.OBJECTID_1'=21522
FAILURE:
Unable to open datasource `sde_Regadio_SQL.vrt' with the following drivers.

If I take out the single-quotes around the field name I get another error:
ERROR 1: SQL Expression Parsing Error: syntax error
ERROR 1: SQL statement failed, or returned no layer result:
SELECT * FROM 'GDBMAN.SREGA_PERIMETROS_REGA' WHERE GDBMAN.SREGA_PERIMETROS_REGA.
OBJECTID_1=21522
FAILURE:
Unable to open datasource `sde_Regadio_SQL.vrt' with the following drivers.

Any clues on how to apply a SQL query here?

Thanks again,
Duarte

-----Mensagem original-----
De: Even Rouault [mailto:even.rouault at mines-paris.org]
Enviada: segunda-feira, 3 de Outubro de 2011 18:51
Para: gdal-dev at lists.osgeo.org
Cc: Duarte Carreira
Assunto: Re: [gdal-dev] ogr, vrt - connecting to arcsde errors only with srcsql

Le lundi 03 octobre 2011 19:17:25, Duarte Carreira a écrit :
> Hi there.
>
> I'm having trouble using a SrcSQL tag in a vrt that connects to an ArcSDE
> feature class.
>
> If I use SrcLayer everything works.
>
> But if I remove SrcLayer, and add a SrcSQL with the simplest of queries
> (SELECT * FROM USER.TABLE1) I get errors in ogrinfo:
>
> ERROR 1: SQL Expression Parsing Error: syntax error
> ERROR 1: SQL statement failed, or returned no layer result:
> SELECT * FROM GDBMAN.SREGA_PERIMETROS_REGA
> ERROR 1: SQL Expression Parsing Error: syntax error
> ERROR 1: SQL statement failed, or returned no layer result:
> SELECT * FROM GDBMAN.SREGA_PERIMETROS_REGA
> FAILURE:
> Unable to open datasource `sde_Regadio_SQL.vrt' with the following drivers.
>
> Any idea why this is?

I guess it is because of the . in the table name. You should surround the
table name by simple quote character.

SELECT * FROM 'USER.TABLE1'

>
> Thanks,
> Duarte