idea SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries in ArcGIS Pro Ideas

SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

Bud — Fri, 23 Dec 2022 16:22:43 GMT

If I understand correctly, the SQL syntax in the FGDB API (see: SQL for reporting and analysis on file geodatabases) is only partially supported in FGDB views and expressions.

It seems weird to me that there would be SQL syntax that is available to the FGDB API, but not available to SQL queries in ArcGIS Pro (FGDB views and SQL expressions).

Could SQL queries in Pro be enhanced so that they fully support the more advanced SQL syntax in the FGDB API?

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

JoshuaBixby — Fri, 23 Dec 2022 15:53:16 GMT

Esri has a bigger problem with that documentation you reference because ArcObjects has nothing to do with ArcGIS Pro. According to ArcObjects Help for .NET developers (ArcObjects .NET 10.8 SDK), "ArcObjects is a library of Component Object Model (COM) components that make up the foundation of ArcGIS [Desktop/ArcMap]." The ArcGIS Pro SDK for .NET is a completely different SDK than ArcObjects SDK, and there is a chance the code examples don't even transfer over. It looks like Esri got sloppy and just fork-lifted the Desktop document over as Pro documentation.

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries - Status changed to: Needs Clarification

SSWoodward — Tue, 10 Jan 2023 23:10:10 GMT

Thanks for the Idea, @Bud.

It's my understanding that all of the SQL currently supported in the FGDB API is also supported in Pro. To help us better understand the limitations you are running up against, would you be able to share some SQL statements used in your workflows that are not supported in ArcGIS Pro?

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

Bud — Wed, 08 Feb 2023 02:06:55 GMT

@SSWoodward

Regarding, SQL for reporting and analysis on file geodatabases, I don’t think these SQL keywords work in FGDB definition queries/subqueries:

CASE
COALESCE
JOIN
NULLIF
ORDER BY

Here’s an example of something I want to do in a FGDB definition query, but can’t, due to FGDB SQL limitations:

https://community.esri.com/t5/arcgis-pro-ideas/one-to-first-joins-control-what-related-record-is/idc-p/1239993/highlight/true#M22488

Works for SQLite/mobile geodatabases, but not file geodatabases:

roadinsptable.objectid IN (
     SELECT objectid
       FROM roadinsptable r2
      WHERE r2.asset_id = roadinsptable.asset_id
   ORDER BY date_ DESC, condition DESC
      LIMIT 1
            )

I’m aware that FGDB SQL doesn’t have LIMIT, not even in the FGDB API. There are likely other ways to accomplish it using SQL, without LIMIT, in databases that have full SQL support. But those alternatives wouldn’t be possible in FGDB definition queries due to the limited FGDB SQL.

Part of the problem is that I’m not actually sure if SQL for reporting and analysis on file geodatabases actually pertains to the FGDB API. It’s a confusing page in a confusing place with no context.

Edit:

Here's another example of the useful stuff we can do in geodatabases like enterprise and mobile, but not in file geodatabases: Force bar chart to show missing years within 10-year range (system year + 9). The queries generate filler rows so that there are rows/bars for each year in the 10-year range.

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

JoshuaBixby — Mon, 16 Jan 2023 18:20:24 GMT

@Bud , regarding your specific query (excluding the LIMIT aspect of it), the query doesn't work because of limitations with SQL joins with file geodatabases. At least for the File Geodatabase API, Esri has always listed 2 known issues: GitHub - Esri/file-geodatabase-api: ... The File Geodatabase C++ API for Windows, MacOS and Linux

Known Issues
Concurrent access from Windows and Linux clients to the same File GeoDatabase can corrupt data. This combination should be avoided.
SQL joins are not supported.

It is likely that limitation in the FGDB API isn't just an FGDB API limitation, i.e., file geodatabases overall do not support SQL joins. Is a bullet point on a GitHub page sufficient documentation? I don't think so, I am guessing you don't think so, but it seems Esri does think so since they haven't materially improved their FGDB SQL documentation for many years.

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

Bud — Mon, 16 Jan 2023 19:21:53 GMT

@JoshuaBixby

Meanwhile, SQL joins appear to be supported in FGDB views:

select 
    *
from
    table_a a
inner join
    table_b b
    on a.id_a = b.id_b

ArcGIS Pro 3.0.3

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

JoshuaBixby — Mon, 16 Jan 2023 21:28:13 GMT

I just tried your example on Pro 3.0.3, and I get "An invalid SQL statement was used" error. I am able to create the tables and create the view, but I can't add it to a map. Are you able to script out your steps so I can try running the code exactly the same way because something in our workflows is obviously different if it works for you but I get an error.

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

Bud — Wed, 18 Jan 2023 16:28:36 GMT

Edited.

It seems there is a bug in ArcGIS Pro 3.0.3.

I tried to repeat my steps, but I got the same error you did when adding the view to the map:

Failed to add data, unsupported data type.
An invalid SQL statement was used.

Workaround:

Create the tables in a FGDB, but then copy the tables to a new FGDB (using right-click copy/paste in Catalog). In the new FGDB, I'm able to create the view, add the view to the map, and open the attribute table.

Does that workaround work for you?

Note: The bug doesn't appear to be specific to the INNER JOIN view we're testing. I got the same error when creating a simpler view:

select 
    *
from
    table_a

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

Bud — Wed, 24 Jan 2024 16:10:18 GMT

@SSWoodward

This idea is out of date and can be closed. I think most of the issues that were brought up in this post are covered in other, more specific ideas, ENHs, and bug reports.

Re: SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries - Status changed to: Closed

SSWoodward — Thu, 04 Apr 2024 19:57:07 GMT