Execute Sql?

[KrisFoster44]

IWorkspace provides an ExecuteSql function. Any chance for this functionality to be added to runtime for a .geodatabase for custom tables, queries, etc? Or should we just assume we need our own Sqlite functionality in our apps if we want this functionality?

[robertrader]

The ArcObjects Workspace has ExecuteSQL. The ArcGIS Pro SDK has the ArcGIS.Core.Data.DatabaseClient.ExecuteStatement

When will the runtime have a way to run SQL in the SQLLite datasources. This is need din the runtime, since the path is different for each os and the way to load the ST_GEOMETRY for sqlite requires path file location information. Since the SQLite connection is already initialized by runtime, with ST_GEOMETRY enabled, having an injection system makes for re-usable code.

[robertrader]

Cases:

1 SQL intersect result sets that can then be used to as mask or feature sources.

2 Enhancing data on the mobile client with caching of workflow management processing, that may use polygons for event processing and historical information.

Note: Fire and forget is what is available every where else. Meaning execution of the SQL will result in something, but the results would be accessed via the standard APIs.

[robertrader]

If a sample of how to get the ST_Geometry location on each OS's install was made available, we could work around this.

[robertrader]

3 SQLite, has may parameters available for tuning performance. As a business partner trying to enhance the data experience for end users, we may need to set the connection busy timeout or other parameters which are connection specific as part of processing done.

https://www.sqlite.org/pragma.html#toc

[robertrader]

4 We are being asked to move all of our future processing work, data enhancement, to the client side rather than in the ArcSDE database. I personally support this network as a service model and other functionality only being available to write to through end points. It however means we are going to need to do complex logic translation moving from the SQL model of pl/sql or transact sql to a SQLite set of functions. Although we can initialize a workspace using the cross platform libraries, we will need to tweak the cross connection logic at the database level. Putting in a cross connection lock or other functionality is historically done in the data source used, to make sure multiple processes stay in sync, even across multiple machines. Yes, I do envision a scenario where there are runtime processes on multiple machines hitting the same SQLite datastore to quickly perform a data enhancement task.That cross machine logic could take advantage of SQLite’s locking mechanism, similar to what we do with SDE today.

[rrader55]

Yea about time lets do it