Edit SQLite data outside ArcGIS Pro

2630
13
Jump to solution
04-05-2021 05:02 PM
Labels (1)
MitchKrupp2
New Contributor II

I've created an SQLite geodatabase in ArcGIS Pro (2.7), added a text field for an attribute and then created a few polygons and filled in the attribute as a test. Works great.

I've been unable to edit those text attributes outside of ArcGIS Pro. I've tried using DB Browser for SQLite and I've tried linking to external data from within MS Access. ArcGIS Pro is closed when I've tried this.

The error message in DB Browser is "Error changing data: no such function: UpdateIndexEntry".

Does anyone know if SQLite attributes can be edited outside of ArcGIS Pro?

13 Replies
ErikLash1
Occasional Contributor

Thanks for the replies. Using the DLL solution looks promising. Will try it out. 

In my case it's not a spatial function thats needed though. It's a non-spatial table edit that's not working.

We bang the entire dataset up against a 3'rd party API which returns a new data table containing the results of the bangup. We then import that table using ESRI tools into a database.

Update query later performed to add certain values from the returned table to certain rows in the spatial table based upon a non-spatial table join and a where clause. 

SQL logic works fine if we take the extra step of moving the data from the mobile geodatabase to an enterprise geodatabase and then moving it back. Extra steps using extra tools as a workaround to functionality that should already be available in SQLite and is available in the non-ESRI SQlite.

Alternatively, using ESRI tools to create a permanent table join, perform table update in python, delete extra fields works. Again extra steps when SQLite can handle the entire process with 2 lines of SQL code already. And magnitudes of time slower in performance than acting directly on the database through SQL.

The SQL is fairly basic, summarized in this example:

UPDATE table1
SET columnX = (SELECT columnY FROM table2 WHERE table1.column1 = table2.column1);

As a precursor to this we run an unmatched query using a left out join to determine what data gets sent to the API for return with new field data for update/integration into the SQLite DB.

Ideal solution would be one where scheduling or manually running a single SQL script to run on the database would handle the weekly data update.

This kind of functionality is also something we are hoping someday extends to file geodatabases as well.

Support requirement is for offline users who are often working with no internet connection.



0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Features classes in mobile geodatabases have additional triggers to update spatial indexes after edits are made.  The triggers execute anytime updates are made to the feature classes, regardless of whether the spatial data is modified.  So, editing non-spatial data in a feature class will cause the trigger to execute, which will throw an error if the DLL containing the function the trigger is using isn't registered.

0 Kudos
ErikLash1
Occasional Contributor

Thanks for the valuable information in this thread. Super important disclaimers noted here that ESRI should be making public to potential users of SQLite made from Pro.

I've been stuck trying to make a simple update query work (like was totally possible with MS Access) in a Mobile Geodatabase and getting the "no such function: UpdateIndexEntry" error.  Spent the last day trying to troubleshoot my machine b/c the query functions perfectly fine in SQL Fiddle and DB Fiddle using test data via SQLite but was erroring out locally in DB Browser and DBeaver.

If we can't update attribute data in SQLite it's a show stopper limitation for the work I need to be doing with attribute tables on feature classes - updating hundreds of thousands of attributes manually not an option, nor is field calculator due to the slowness of Python [SQL not available for SQLite in Field Calculator]. Updatecursors cumbersome, again because of the Python translation that's needed to work with DBs to undertake the kind of work that we need done. SQL native is simplest, fastest, and best approach. 

0 Kudos
DavidAnderson_1701
Occasional Contributor

My answer to this involves taking off the ESRI GIS hat and putting on a database design hat.  A trigger is just a SQL statement.  Triggers can be dropped and recreated with just two commands.  In looking at the table, there are two Update triggers, ones that fires when the shape is updated, one that fires on any update. Both update the spatial index.  My solution is to drop the trigger that fires on any update.  It is not necessary since there is a trigger for a Shape update.  IMHO the spatial index does not need to be updated when a non spatial attribute changes. 
If you are really concerned, the any update trigger can be created again.  Just make sure to copy of the DDL SQL statement before doing the trigger delete.

0 Kudos