Trying to understand why SQLite3_Step would return SQLITE_OK instead of SQLITE_DONE

[keegan-gehrig]

I'm not really sure if this is an issue, which is why I'm bringing it up as a Q&A discussion (and I apologize if this is something that has already been brought up & answered before, though I didn't see anything when I checked), but I am just trying to see if I can gain any sort of understanding into the response codes provided by the SQLite3.Step method and what it means when a response code of SQLITE_OK (0) is returned instead of SQLITE_DONE (101) and why that would be considered an error, and not a success. This will help me better understand how I should be handling this type of response code in my own app and what the appropriate course of action to take would be.

I'm working on a Xamarin.Forms app that is currently using version 2.0.3 of the SQLitePCLRaw.bundle_green, SQLitePCLRaw.core, & SQLitePCLRaw.lib.e_sqlite3.android NuGet packages (and also version 1.1.14 of the SQLitePCLRaw.provider.e_sqlite3.android & SQLitePCLRaw.provider.sqlite3.ios_unified NuGet packages). Its also using version 1.7.335 of the sqlite-net-pcl NuGet package by praeclarum (https://github.com/praeclarum/sqlite-net). I know that there are newer versions available and I am in the process of working on upgrading the XF app to .NET MAUI (since XF has reached end of life), so I will be taking advantage of the latest versions of these NuGets soon. Maybe the behavior I've noticed in the XF app won't be present in the .NET MAUI app because of these newer versions, but I still wanted to bring it up just in case, anyways.

In the app that I'm working on, I will sometimes see an exception reported to AppCenter when either a "ROLLBACK" or "RELEASE" command is run that reports a SQLiteException of "not an error". This seems to happen quite intermittently and its not something that I can consistently reproduce with a specific set of steps or anything. So more often than not, these statements do result in a "Done" response code and everything works properly. Does anyone know what conditions would happen that would cause these commands to return "OK" instead of done, though? And what would that mean? Does it mean that the command statement still ran successfully and that I can consider things a success? Or is it possible that when I get this response code back, it means that the statements didn't actually run (or are still waiting to be run)? Would it be okay to try running the statement again, if this is the case?

Is this potentially caused by something similar to what was reported here: #394? In my case, the stack traces don't involve the SQLiteConnection.SetKey method, but rather the SQLiteConnection.RunInTransaction method that leads to either of the following paths:

SQLite.SQLiteException: not an error at SQLiteCommand.ExecuteNonQuery () at SQLiteConnection.Execute (System.String query, System.Object[] args) at SQLiteConnection.DoSavePointExecute (System.String savepoint, System.String cmd) at SQLiteConnection.Release (System.String savepoint) at SQLiteConnection.RunInTransaction (System.Action action) at SQLiteConnectionExtensions.InsertOrReplaceAll (SQLite.SQLiteConnection target, System.Collections.IEnumerable objects) at MobileSQLiteAsyncConnection+<>c__DisplayClass14_0.<InsertOrReplaceAllAsync>b__0 () at Task.InnerInvoke () at Task.Execute ()

or

SQLite.SQLiteException: not an error at SQLiteCommand.ExecuteNonQuery () at SQLiteConnection.Execute (System.String query, System.Object[] args) at SQLiteConnection.RollbackTo (System.String savepoint, System.Boolean noThrow) at SQLiteConnection.Rollback () at SQLiteConnection.RunInTransaction (System.Action action) at SQLiteConnectionExtensions.InsertOrReplaceAll (SQLite.SQLiteConnection target, System.Collections.IEnumerable objects) at MobileSQLiteAsyncConnection+<>c__DisplayClass14_0.<InsertOrReplaceAllAsync>b__0 () at Task.InnerInvoke () at Task.Execute ()

I hope my questions make sense, and I appreciate any insight I can get into any of this. I'd be happy to explain better or provide more details as needed as well.