FGDB API not respecting Select clause of SQL

[MarkTurnbull]

I am writing some code that will run an SQL query against the FGDB using the FGDB API 1.2. The SQL will be defined at runtime. I am finding that the EnumRows class is not filtered on the fields in the select clause of the SQL statement, the EnumRows always has attributes for all fields even with a SQL statement like "SELECT CITY_NAME, POP1990 FROM Cities WHERE TYPE = 'city' AND OBJECTID < 10". Because the EnumRows class's fields are not filtered, the GetFieldInformation returns information about all fields in the table not the fields in the select clause.

Am I missing something in the use of the SQL comnponents of the API? If not, how do I determine field names, types, etc. on a SQL query supplied at runtime?

Thanks

[VinceAngelo]

Unfortunately, this is a "feature" of the API. 

However, the Table::Search methods accept a comma-delimited list of column names, so parsing
that list isn't going to be a particularly difficult task, and the attribute getters work by field name,
so you'll need that list handy anyway.  Looking up types  by name is via GetFieldInformation on
either the Row or the Table.

- V

[MarkTurnbull]

Vince,

I dont understand how this can be a "feature" of the API, its not how I would expect the results of an SQL to appear.

So I guess what your saying is that I iterate through the EnumRows and populate a table with only the fields that I have to parse from the select clause, is that correct?
The difficult part is then parsing the select clause, the fields could have aliases, SQL functions, etc., These all need to be catered for. I also assume that iterating through a large number of EnumRows might have some performance impact.

Is there any examples around of what you have suggested?

Thanks
Mark

[MarkTurnbull]

Hi Vince,

I replied before having a look at the reference.

OK, I can see how you can get a EnumRows from a table using the search method and I found an example of how to use the search method in the Display example provided in the SDK.

If I extract the select clause from the sql and put that into the subfields argument of the search method, with SQL functions, aliases, etc. will that work?

As the search method is based on a table how would I handle an SQL with a join in it?

Thanks
Mark

[VinceAngelo]

You're forgetting that there is no SQL database sitting behind the FGDB format -- You can't select anything
but the fields that are in the file.  I doubt you could do a join (except the old-fashioned way). Aliasing
doesn't matter, since you can display whatever you like as long as you retrieve by actual column name
(except for OID and Shape, of course).  Computed columns still can be computed by your C++ app,
you'll just need to code that yourself.

The good news is the performance benefit of not having to do all the select column management -- The
purpose of the FGDB API is to provide simple open access to read and write to an otherwise closed format.
If you want all the capabilities of a database, then you'll need to use one instead.

- V

[MarkTurnbull]

Hi Vince,

I am not forgetting that it is a "File" Geodatabase, but the API offers SQL functionality and it is clearly documented that the SQL supports SQL functions and Joins.

But your suggested work around for what appears to me to be a design fault in what is returned in the EnumRows class from the ExecuteQuery method is not going to work.

It appears to me that I need to go back to my original executeQuery method that returns all fields and then add code to filter the fields based on if they can be found in the select clause of the SQL.

Has anybody else been able to solve this issue with what I am suggesting?

Mark

[VinceAngelo]

I did a search on "join" and found the reference to SQL expressions in the HTML doc.  Yes, the
Geodatabase::ExecuteSQL method shows support for complex queries, but the key part that
struck my eye was "supports a limited subset of the SQL 92 standard".

It appears that the WHERE side of the expression is far more sophisticated than the SELECT list
(which is probably better than the reverse), but you'll still have to either collect information in
your runtime query builder or parse the column list before processing the EnumRows.  I'd
recommend implementing this as a wrapper class that returns a customized FieldInfo array.
I'd be curious what happens if you join two tables with geometry and/or OID columns -- since
the getters don't allow field names, the API would have to pick the source somehow.

- V

[LanceShipman]

From the readme:   "* File GDB SQL SELECT does not support ORDER BY or joins (NIM064472)." If you need additional SQL support, please request it on the ArcGIS Ideas site (http://ideas.arcgis.com/) and it will be evaluated for a future release. The doc was written before we determined that joins would not make the cut. I'll update the SQL doc in the next maintenance release.

[MarkTurnbull]

Thanks for the feedback, Lance.

OK, found the reference in the readme file. But this does not explain why SQL functions dont work and why the resultant EnumRows object returns all fields without respecting the select clause in the SQL.

Mark

[DavidSousa]

The explanation is very simple:  We have not had the opportunity to implement those SQL features yet.  We do give consideration to ideas and suggestions from users, and a future release may well have these things implemented.