topic Re: Edit SQLite data outside ArcGIS Pro in ArcGIS Pro Questions

Edit SQLite data outside ArcGIS Pro

MitchKrupp2 — Tue, 06 Apr 2021 00:08:06 GMT

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?

Re: Edit SQLite data outside ArcGIS Pro

jcarlson — Tue, 06 Apr 2021 02:14:08 GMT

You should be able to edit SQLite outside of Pro. However, the compatibility between Pro and SQLite, SpatiaLite, and GeoPackage databases still seems a bit lacking to me. I've found that when such databases are created in Pro, they run into the same issues you're experiencing.

Use some other software to create the database first. There are a number of ways you might do it, but the GeoPackage Implementations page is a good start. (I know it's for GeoPackage, but GeoPackage kind of is SQLite, and many of the entries in this list support both.)

If you use an existing DB and simply interact with it in Pro, odds are you'll run into fewer issues.

Re: Edit SQLite data outside ArcGIS Pro

Robert_LeClair — Tue, 06 Apr 2021 15:49:08 GMT

Outside of the Esri application world, I found a 12/18/2020 article about mobile geodatabases and SQLite and the message "no such function: UpdateIndexEntry" message. In the comments, the author mentions that he believes drivers need to be constructed with access to the functions that manage the indexes.

Related I created a Mobile Geodatabase in ArcGIS Pro that uses SQLite, copy/pasted data into the geodatabase, added a field, calculated a field, etc. - works well in ArcGIS Pro.

Re: Edit SQLite data outside ArcGIS Pro

MitchKrupp2 — Tue, 06 Apr 2021 16:25:22 GMT

Thanks for your reply Josh. I tried your advice, created the database outside ArcGIS Pro and spatially enabled it. Then added a few polygons in Pro, added some text attributes, saved and quit. Still the same errors if I try to edit the attributes outside Pro.

Re: Edit SQLite data outside ArcGIS Pro

MitchKrupp2 — Tue, 06 Apr 2021 16:34:28 GMT

Thanks for your reply Robert.

The way ESRI is pushing SQLite as the go to solution for replacing the personal database I would have thought they'd have worked this out. As it stands now I see little reason to actually use SQLite for storing GIS data. It's just as locked in to the ESRI way of seeing the universe as the FileGeodatabase, i.e. unusable from outside ArcGIS.

I sure hope I'm missing something here because the way it is now the only way one can work with attributes is by purchasing more ESRI software.

Re: Edit SQLite data outside ArcGIS Pro

Robert_LeClair — Tue, 06 Apr 2021 16:55:46 GMT

You're welcome Mitch - as a caveat to my reply - I work for Esri Training Services as a Senior Instructor and have zero pull on any ArcGIS Pro development nor know the decision making of what is or what is not supported. With that said...

1. It is possible to connect to Microsoft Access databases in ArcGIS Pro using an OLEDB connection. This was introduced in ArcGIS Pro. 2.6.
2. Outside of the Esri domain (maybe) but guessing if a driver update for SQLite databases and indexes is created, then "maybe" one can edit them outside of Esri software. But that's outside of my wheelhouse/expertise.
3. There is a File Geodatabase API located here for developers but probably is not what you're looking to do based upon your question.

Hope this helps!

Re: Edit SQLite data outside ArcGIS Pro

JoshuaBixby — Tue, 06 Apr 2021 17:05:15 GMT

UpdateIndexEntry is a custom function written by Esri and implemented within some DLL, I can't say which one because they don't say. When a geodatabase model is created in SQLite, certain triggers are created on tables to maintain integrity and make the geodatabase model function. In this case, there is a trigger that is being executed when you try to update the field, and when updating outside of ArcGIS applications, the UpdateIndexEntry function isn't available.

Re: Edit SQLite data outside ArcGIS Pro

jcarlson — Tue, 06 Apr 2021 17:32:56 GMT

Hm. That's very odd, that Pro is altering the database when you use it. I have a few SQLite databases that I keep around for testing purposes. They were initially created in QGIS, but they work just fine between Pro and other programs. But then, perhaps Q is applying different functions than Pro does.

Re: Edit SQLite data outside ArcGIS Pro

MitchKrupp2 — Tue, 06 Apr 2021 17:33:36 GMT

Thanks for the info Joshua. I use SQLite for other things, but for ArcGIS looks like I'll be putting it back on the shelf.

Re: Edit SQLite data outside ArcGIS Pro

ErikLash1 — Tue, 20 Jul 2021 22:08:05 GMT

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.

Re: Edit SQLite data outside ArcGIS Pro

JoshuaBixby — Thu, 22 Jul 2021 13:53:33 GMT

@MitchKrupp2, as a follow-up to my earlier response, it turns out starting with ArcGIS Pro 2.8 that Esri is officially saying which DLL contains the functions you need, and they describe how to get it and use it.

Load ST_Geometry to a mobile geodatabase for SQL access—ArcGIS Pro | Documentation

Re: Edit SQLite data outside ArcGIS Pro

ErikLash1 — Thu, 22 Jul 2021 18:42:19 GMT

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.

Re: Edit SQLite data outside ArcGIS Pro

JoshuaBixby — Fri, 23 Jul 2021 15:12:28 GMT

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.

Re: Edit SQLite data outside ArcGIS Pro

DavidAnderson_1701 — Thu, 10 Mar 2022 22:22:53 GMT

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.