Edit SQLite data outside ArcGIS Pro

[MitchKrupp2]

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?

[jcarlson]

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.

[MitchKrupp2]

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.

[jcarlson]

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.

[Robert_LeClair]

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.

[MitchKrupp2]

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.

[Robert_LeClair]

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!

[JoshuaBixby]

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.

[MitchKrupp2]

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. 

[JoshuaBixby · Accepted solution]

@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