Sqlite Database does not work on Unity Games compiled for Android

Question

I’m using the Sqlite as a game database made in Unity that works perfectly on Unity Editor, but when I build for Android the game simply does not access the bank (nothing is informed or released).

What I can do to make the sqlite database accessible through Android as well as Unity Editor?

Programs/Versions used:
- Unity 5.3.1f1
-Sqlite Precompiled Binaries for Windows 64 (sqlite-dll-Win64-x64-3100200.zip (688.01 Kib))

Folder Structure:

-- Assets/Database/MyDataBase.db
-- Assets/Scripts/PerguntaScript.cs
-- Assets/Plugins/Mono.Data.Sqlite.dll
-- Assets/Plugins/sqlite3.def
-- Assets/Plugins/sqlite3.dll
-- Assets/Plugins/System.Data.dll

Questionascript.Cs (Script accessing Sqlite database)

using UnityEngine;
using UnityEngine.UI;
using System.Collections;
using System;
using System.Data;
using Mono.Data.Sqlite;

public class PerguntaScript : MonoBehaviour {

    private string connectionString;

    void Start () {
        connectionString = "URI=file:" + Application.dataPath + "/Database/DoencasDB.db";
    }

    private void GetPergunta(){
        using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
            dbConnection.Open();

            using(IDbCommand dbCommand = dbConnection.CreateCommand()){
                string sqlQuery = "SELECT * FROM " + sqliteTabelasPerguntas[sqliteIndexTabelaPergunta] + " WHERE doenca_id=@id LIMIT 1";
                dbCommand.Parameters.Add(new SqliteParameter("@id", doencaId));
                dbCommand.CommandText = sqlQuery;

                using(IDataReader reader = dbCommand.ExecuteReader()){
                    while(reader.Read()){
                        pergunta = reader.GetString(1);
                        alternativasId[0] = reader.GetInt32(3);
                        alternativasId[1] = reader.GetInt32(4);
                        alternativasId[2] = reader.GetInt32(5);
                        alternativasId[3] = reader.GetInt32(6);
                        alternativaCorretaId = reader.GetInt32(7);
                    }
                    dbConnection.Close();
                    reader.Close();
                }
            }
        }
    }

OBS: Tested on Android: 4.4 and 5.0

Answer 1

To make it work outside the editing environment (in this case on mobile) I had to make the following changes:

1) verify which environment I am in (by compiling comments I determine which code will go to the compiled).

2) verify that the file has already been opened and stored by the application (unzipped from the jar (check Unity documentation)) if it is not necessary to open the database and store it in the appropriate location (expressed by: Application.persistentDataPath)

public void GenerateConnectionString(string DatabaseName)
{
    #if UNITY_EDITOR
        string dbPath = Application.dataPath + "/StreamingAssets/" + DatabaseName;
    #else
        //check if file exists in Application.persistentDataPath
        string filepath = Application.persistentDataPath + "/" + DatabaseName;

        if (!File.Exists(filepath) || new System.IO.FileInfo(filepath).Length == 0)
        {
            // if it doesn't ->
            // open StreamingAssets directory and load the db ->
            #if UNITY_ANDROID
                WWW loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName);  // this is the path to your StreamingAssets in android
                while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
                // then save to Application.persistentDataPath
                File.WriteAllBytes(filepath, loadDb.bytes);
            #elif UNITY_IOS
                var loadDb = Application.dataPath + "/Raw/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
            #elif UNITY_WP8
                var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
            #elif UNITY_WINRT
                var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
            #endif
        }
        var dbPath = filepath;
    #endif
    connectionString = "URI=file:" + dbPath;
}

Answer 2

You need to add the Sqlite connection libraries to your project, the file I believe is not currently available on the official Sqlite website (but you can easily find it there), it is a file with the extension .so.

You should add it in /Assets/Plugins/Android

Unlike many libraries we use as .dll Android uses the .so.

Source: Unity documentation.

Here in the Soen a problem identical to your.