I've spent a few hours today trying to figure out why SQLite (and SQLite.Net-PCL) wasn't working in a Windows Phone 8 app, but the same code and same library worked in WPF app. If you're facing this exception when working with SQLite.Net-PCL, I hope this post will help you resolve it quicker.
First of all, let's face it - even though we would all love to work on new Windows 10 Universal apps ever since the latest Developer Preview tools came out, most of us are still actually working on supporting and developing Windows Phone 8+ apps that are already in the Store (some are even maintaining Windows Phone 7 apps, even though it's very limiting when trying to have a shared code base). As the apps are moving in the new UAP direction, the question of (enterprise?) database support mainly remains unanswered.
Back in the Windows Phone 8 days LINQ to SQL was nicely documented and well supported, so it was easy to start with. I honestly hope that Microsoft will provide good documentation for working with databases in Windows (10) apps as developers will definitely need it, especially the ones who are just starting with the platform! Database choice for Universal apps seems to be SQLite surrounded with minimalistic open source ORMs since development of Entity Framework 7 for Phone/Store has been stopped for now.
To ensure we meet our primary commitments to ASP.NET 5 we are going to be focusing on rounding out the existing features and the SQL Server provider for EF7.
The amount of work and effort that went into building SQLite-net (and all the relevant forks) is truly impressive. But sometimes you hit a brick wall and just don't know where to go next.
To work with SQLite in Windows Phone 8 (or WinRT Universal apps, the procedure is similar) you first need a Visual Studio extension, a VSIX package with an extension SDK and all other components needed to get started, and it gets updated and published by SQLite when new versions of SQLite come out.
Then you need a client library/ORM such as SQLite.Net-PCL (which is the one I prefer and many recommend) and it makes it easy to connect and work with database without actually writing SQLite queries (you stil can if you want, of course) as strings and worry about various other details. Then ,depending on the platform, you plug in a different platform implementation, which is also provided by the library.
So, all of a sudden everything works in WPF, but not in Windows Phone 8. I get a "SQLite logic error or missing database" exception message, transaction rolls back, nothing works.
Just doing something like
So it seemed that the critical parts were batch insertions and updates.
Logic error or missing database?
Missing database is the easiest thing to check - and of course it's there. So I can scratch that part of the message.
Wow - not something you want to see in your code. :)
Looking through issues on GitHub I found plenty of problems with a similar message:
Inserting data with PrimaryKey: InsertOrReplaceAllAsync() fails, while ExecuteAsync() succeeds - Nope, not my problem, I'm not using the async calls.
UTF-8 strings get encoded incorrectly - Nope, not an encoding problem, works great with WPF
long as PrimaryKey is problem in transactions(SQL logic error)? - Nope, not using long for primary keys
Transaction + guid primary keys does not work under WinRT - Nope, it's failing everywhere, even in the places where I don't use GUID.
InsertOrReplaceAll fails on Windows Phone 8.0 (Silverlight) - Now I'm onto something - my problem seems to be WP8 related and also happening on updating and inserting a batch of objects. The last one even provided the author with the source code of a simple project which isolates the error.
Why don't you use Stack Overflow?
Well, I do. Not only to find solutions to problems, but even more to answer other people's questions and help them. But I missed a question in which one of the comments suggests reverting the Visual Studio extension to one of the older versions! Mainly because it's in the comments and the answer that was upvoted didn't help me, I didn't want to believe that simply going back to a very old VSIX would actually fix this for me.
But it did. Reverting from (current) 22.214.171.124 to 3.8.1 (just for Windows Phone 8, not for other SQLite extensions!) worked.
The old extension is here: https://sqlite.org/2013/sqlite-wp80-winrt-3080100.vsix (if you have a better way of finding all the previous extensions, leave a comment! The downloads page offers only the latest one!)
To get this to work, you'd have to uninstall the current version and ignore the update which will obviously show up in the Visual Studio Extensions and Updates
Still, I have no explanation what actually broke between 3.8.1. and 3.8.2. and what possible consequences using the old version could have.
In conclusion, this type of SQLite exception (logic error) can mean all sorts of things. I hope this blog post will help you narrow it down and spend a lot less than I did on this problem.