Sqlite layer not displaying

[Damian_Hite]

Hi,

This issue is similar to the following post in that I am seeing SQLite error (1): near “AND”: syntax error when trying to refresh the map after adding sqlitefeaturelayer. The layer does not display. Data in the table is good and I can query it using system.data.sqlite commands and return data no problem.

GetAllFeatures exception for SqliteFeatureLayer

Hi, I’m trying to get all features from SqliteFeatureLayer using the following which worked in v9. SqliteFeatureLayer sql = layer as SqliteFeatureLayer; sql.Open(); Collection theseFeatures = sql.FeatureSource.GetAllFeatures(ReturningColumnsType.AllColumns); sql.Close(); Here’s the error. System.Data.SQLite.SQLiteException HResult=0x800007BF Message=SQL logic error near “AND”: syntax error Source=System.Data.SQLite StackTrace: at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection c…

The error also persists if I try and getallfeatures just as in the post.

My where clause has no “AND” in it, so can you please tell me what this syntax issue could be? What queries are behind the scenes?

I am using an rtree index which I have implemented identically to other tables in the same model that display their data on the map. They appear to be populated properly so doesn’t seem there is any mismatches that would affect the display.

Thanks,
Damian

[Kyle_Day]

Hey @Damian_Hite,

Usually this means that there is something wrong with the WhereClause. We take your WhereClause that you provide and add it at the end of our queries, replacing the instance of “WHERE” with “AND”. So, depending on your WhereClause for that layer, you might need to change it. Can you provide me with the WhereClause that you have supplied so that I can try to recreate it on my side? Better yet, if you can provide me with a simple project that recreates the issue, I can debug it further more accurately.

Thanks,
Kyle

[Damian_Hite]

Hi Kyle,

I really don’t have any idea what has happened, but the next day the layer appeared just fine and I have not had the error since. Even other layers I have subsequently created appear fine.

I do think there are some issues with indexing getting corrupted when you move features or add/drop them. Is there are hard fast efficient approach to making sure indexes stay synchronized? There isn’t much in the user guide about this.

Thanks,
Damian

[Kyle_Day]

Hey @Damian_Hite,

That’s interesting that you are no longer getting that error. Maybe some random disconnect or strange state that the database was in.

With reindexing your database, I wonder if the issues you are experiencing is because when you are adding/removing features and then subsequently reindexing, that these are not wrapped in a “BeginTrasaction” and “CommitTransaction”? Ideally, when performing operations that modify the database, the flow should look similar to:

layer.Open();
(layer.FeatureSource as SqliteFeatureSource).BeginTransaction();

// Add or remove features
// Reindex

(layer.FeatureSource as SqliteFeatureSource).CommitTransaction();
layer.Close();

While it’s in transaction, everything should be stale until the commit is called, keeping everything in synch. Hopefully that helps.

Thanks,
Kyle

[Damian_Hite]

Hi Kyle,

I end up doing my own transaction updates with sqlite directly, but they are enclosed in Begin and Comit transactions.

If I have some geometry in a sqlite table and I delete something, does it leave the corresponding entries in the index? Could those entries cause other elements that are subsequently added to become out of sequence?

Thanks,
Damian

[Kyle_Day]

Hey @Damian_Hite,

So, in the background, when you call layer.FeatureSource.DeleteFeature("123");, we automatically remove it from the index table as well within the same transaction. So, the index shouldn’t come out of sequence. But if you are running your own Sqlite queries yourself, you should also make sure to remove the entry from the index as well to avoid it from becoming inefficient over time.

Thanks,
Kyle