topic Re: SQL queries on File Geodatabases in File Geodatabase API Questions

SQL queries on File Geodatabases

kishoremudapaka — Thu, 16 May 2013 04:50:30 GMT

Hi all,

i'm writing a sample program to read values of some fields in the table. i wrote a snippet as below -

SQLStr=L"SELECT STATE_NAME, OBJECTID FROM cities GROUP BY STATE_NAME ORDER BY STATE_NAME";
fgdbError err= geodatabase.ExecuteSQL(SQLStr,true,eRows);
while(eRows.Next(CurrRow) == S_OK)
{
CurrRow.GetOID(Oid);
CurrRow.GetString(L"STATE_NAME",value);
}

when the ExecuteSQL method is called it returned the error code -2147220985 which means FGDB_E_INVALID_SQL!!

the documentaion tells that group by clause is supported.

Can someone please help me resolve the issue??

Thanks in advance,
Kishore.

Re: SQL queries on File Geodatabases

VinceAngelo — Thu, 16 May 2013 09:45:55 GMT

Please read the responses to your previous posts before posting new queries.
The first paragraph of this one and this entire response apply to this post as well.

The documentation states that the FGDB API only implements a subset of SQL.
A search on "GROUP BY" in these Forums returned this reference, which refers
to the ArcObjects implementation (though it makes sense that they would have
the same restriction).

Please post File Geodatabase API questions in the appropriate location in the future.

- V

Re: SQL queries on File Geodatabases

VinceAngelo — Thu, 16 May 2013 10:54:18 GMT

Actually, looking at your query, it isn't valid SQL -- There is no grouping function
on the ObjectID column (not that the FGDB API supports that, either).

- V

Re: SQL queries on File Geodatabases

LanceShipman — Thu, 16 May 2013 16:31:52 GMT

The ArcObjects implementation reference is for 9.2 and is a bit out of date. The File Geodatabase API is based on the ArcGIS 10.0 file geodatabase implementation with some exceptions. Joins are not supported in the API while they are in ArcGIS. GROUP BY was not supported in the file geodatabase in 10.0. GROUP BY was added to ArcGIS at 10.1, but the API has not been updated.

The SQL is invalid you cannot include OBJECTID in the select list. Only the grouping field and summary fields can be included. If you are looking for an list ordered by STATE_NAME and OBJECTID try:

SELECT STATE_NAME, OBJECTID FROM cities ORDER BY BY STATE_NAME, OBJECTID

There is a documentation error in the API documentation that notes GROUP BY, but it is an error and will be corrected in the next release of the API.