topic Re: Mobile GDB database view gets created even if SQL is invalid/error in ArcGIS Pro Questions

Mobile GDB database view gets created even if SQL is invalid/error

Bud — Mon, 17 Apr 2023 08:23:44 GMT

ArcGIS Pro 3.1.1; mobile geodatabase

Scenario: I try to create a database view, but I make a mistake in the SQL, so the view is invalid.

select * from test_tblx

ArcGIS Pro throws an error because the view is invalid:

ERROR 999999: Something unexpected caused the tool to fail. Contact Esri Technical Support (http://esriurl.com/support) to Report a Bug, and refer to the error help for potential solutions or workarounds.
DBMS table not found [no such table: main.test_tblx] [test_vw6][STATE_ID = 0]
DBMS table not found [DBMS table not found] [main.test_vw6]
Failed to execute (CreateDatabaseView).

That error is expected.

However, if I refresh the mobile geodatabase in Catalog, I see that the view was actually created, despite the error. That's not expected.

Note: If the view was valid, then I wouldn't need to refresh the mobile geodatabase. The view would have shown up automatically without refreshing. But if the view is invalid, then I do need refresh the mobile geodatabase in order to see it.

Likewise, I can't add the broken view to the map:

Video:

(view in My Videos)

Why does the view get created even if the SQL is invalid?
And if that behavior is expected for some reason, why doesn't Catalog automatically refresh the GDB to show the new view (like it would if the view was valid)?

Re: Mobile GDB database view gets created even if SQL is invalid/error

JoshuaBixby — Mon, 17 Apr 2023 13:57:51 GMT

Too funny, I just logged a defect on this issue a couple weeks ago: BUG-000157051 for ArcGIS Pro (esri.com)

Re: Mobile GDB database view gets created even if SQL is invalid/error

Bud — Mon, 17 Apr 2023 14:51:59 GMT

Haha. Nice. Thanks.

From BUG-000157051:
...and the table view cannot be deleted.

That helps. I had noticed that certain views couldn't be deleted. I hadn't put my finger on what scenario lead to those undeletable views. Good to know.

Edit: I suppose Esri Support could add this post's URL to that bug report/case -- just as an added reference.

Also, FYI, here are a few other mobile GDB-related bugs I've found/submitted:

BUG-000155819 - Attribute table of joined layer in Mobile Geodatabase does not open when a definition query with a subquery is applied to the join table.
BUG-000155830 - Failed to open Mobile geodatabase feature attribute table after applying join from a SQL queried standalone table with error: Underlying DBMS Error [ambiguous column name objectid]
BUG-000157223 - The View Icon shows as Standalone table instead as Feature Class in Mobile Geodatabase
BUG-000156931 - Unable to select features using the Explore or selection tool from a view in a mobile geodatabase in ArcGIS Pro.
- Related:
  BUG-000157376 - Unable to select features using the Explore or selection tool from a view with a join in a File geodatabase in ArcGIS Pro
BUG-000157475 - Cannot open table from the Catalog pane for a spatial view found in a mobile geodatabase.

I've got a few other ENHs and open tickets related to mobile geodatabases too. I track that stuff in a Word doc, since my MyEsri account isn't linked to my organization's MyEsri account. So I could send that Word doc to you if needed.

Re: Mobile GDB database view gets created even if SQL is invalid/error

JoshuaBixby — Mon, 17 Apr 2023 15:08:14 GMT

The views that can't be deleted from ArcGIS Pro can be deleted from any SQLite tool just fine. They can't be deleted in ArcGIS Pro because they are created in SQLite but not populated in various GDB system tables, so the delete tools can't really figure out what to do. The solution to the delete issue is to have them never created in SQLite in the first place if the SQL is invalid.

Re: Mobile GDB database view gets created even if SQL is invalid/error

Bud — Tue, 18 Apr 2023 00:08:56 GMT

It's a strange issue.

In comparison, if I create a valid view using a SQL client like DBeaver, then ArcGIS Pro can delete that view without issue. Even though it was created outside of ArcGIS Pro and so no system tables would have been updated.. But as you said, if ArcGIS Pro creates an invalid view, ArcGIS Pro can't delete that view.

It seems kind of inconsistent to me.