SQLite Unity and Android - Multiple issues and how I fixed them

1 year 6 months ago

I just published my 5th app Easy Culling.  It is a HUD for a bass tournament fisher.  It also keeps a history of their past tournaments, with multiple fields like weather, date, notes.. etc. Heres a quick walkthrough of some challenges I faced in the development of the app.

The main functional requirements:

  • The app needs to store and recall a bunch of information
  • The app must work on iOS and android

Currently I am making all my apps in Unity.  The simplicity of publishing for both platforms with one codebase is invaluable, plus its free.  After searching on how to save a bunch of data, I landed on SQLite. It provides a database that I can preconfigure and copy locally to their device.  I wouldn't need the app to ever connect to the web, which is good for an app built for anglers on their boats.

I got sqliter from the Unity asset store to connect to the database and built the thing in a couple weeks.  I built the database in another program and put it in the streaming assets folder.  When the app runs, if it isn't on their device, I then copy it to their persistentdatapath and we are reading and writing.  The app worked great in the unity editor and my iPhone, but when I put it on my junky android device.. it crashed.

It was maddening, there are a million posts out there for this, each with their own solutions that may have worked during the years.  I recoded a bunch, put in a bunch more assets, updated unity, and recoded again.  I could not make it work. Eventually I had to sit down, and go through the whole process.

First step is copying the database.

You cant use a simple file copy  File.Copy(sourcePath, destinationPath, true);.  It has something to do with how android packages their apks.  Eventually I figured out and proved that the WWW method worked with File.writeallbytes.

Even after doing this I would still get a data malformed error when I installed the apk on my actual device.

Second step was to make sure I could read and write to the DB.

The copied database worked in the android emulators, but when I copied it to my phone it crashed.  After alot of fumbling around I made a small database with one table and one field and it worked.  This got me away from all of the library problems.  The sqlite library was working, my database wouldn't work.

Eventually I settled on the solution to create a blank database, copy it to the device, and then run create table sql queries that I exported from my SQLite program.

It started working.  I still got data malformed errors if I didn't do a connection.close().. so after closing all of those I was finally done.  I rebuilt the apk and got it back up.

Sometimes code problems get me really anxious.  There are so many combinations of technologies and situations, and of course the code you write is unique.  You can't always ask someone for help.  Sometimes either you solve it, or it can't be done.  I'm glad I figured this one out.  Maybe writing this helps someone with the same issue.  Either way, if you are a bass tournament angler you can check out the app at easyculling.com.

< Return to Code Listing