Setup Database (SQLite) for Unity

Question

I have looked at too many tutorials to list and they all recommend the same thing. However, they have not helped to solve my problem.

I am trying to include in my project an SQLite DB, and when building for PC, MAC & Linux Standalone (testing on a Windows machine), the database works as expected. When testing on an Android device, I get the following errors.

   E/Unity: ArgumentException: Invalid ConnectionString format for parameter "/storage/emulated/0/Android/data/com.tbltools.tbl_project/files/TBLDatabase.db"
          at Mono.Data.Sqlite.SqliteConnection.ParseConnectionString (System.String connectionString) [0x00000] in <filename unknown>:0 
          at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in <filename unknown>:0 
          at UIHandler+<RequestAllStudentNames>c__Iterator2.MoveNext () [0x00000] in <filename unknown>:0 
          at UnityEngine.SetupCoroutine.InvokeMoveNext (IEnumerator enumerator, IntPtr returnValueAddress) [0x00000] in <filename unknown>:0 

I thought that making an amendment to the connectionString should be simple enough, but that hasn't solved my problem. This is what I have so far:

   if (Application.platform != RuntimePlatform.Android)
        {
            // The name of the db.
             tblDatabase = "URI=file:" + Application.dataPath + "/TBLDatabase.db"; //returns the complete path to database file exist.
        }
        else
        {
              tblDatabase = Application.persistentDataPath + "/TBLDatabase.db";

            if (!File.Exists(tblDatabase))
            {
                // if it doesn't ->
                Debug.LogWarning("File \"" + tblDatabase + "\" does not exist. Attempting to create from \"" + Application.dataPath + "!/assets/" + "TBLDatabase.db");
                // open StreamingAssets directory and load the db ->

                // #if UNITY_ANDROID
                var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + "TBLDatabase.db");  // 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(tblDatabase, loadDb.bytes);
            }
        }
        //open db connection
        var connection = new SqliteConnection(tblDatabase);
        connection.Open();
        var command = connection.CreateCommand();

I have used adb shell and pulled the DB from my Android device and everything is as expected (the DB does exist and it isn't empty).

I believe I have all the relevant dll files, but if anyone could give me some guidance I would appreciate it.

***************************************************EDIT**********************************************

I have since made the following alterations based on advice given.

I am now calling the following method to start my connection and handle DB requestsStartCoroutine(RunDbCode(dbFileName, jsonStudentID, jsonIndiNames, jsonIndiStudentNumbers));

Then I have the following method:

IEnumerator RunDbCode(string fileName, List jsonStudentID, List jsonIndiNames, List jsonIndiStudentNumbers)
    {
        //Where to copy the db to
        string dbDestination = Path.Combine(Application.persistentDataPath, "data");
        dbDestination = Path.Combine(dbDestination, fileName);

        //Check if the File do not exist then copy it
        if (!File.Exists(dbDestination))
        {
            //Where the db file is at
            string dbStreamingAsset = Path.Combine(Application.streamingAssetsPath, fileName);

            byte[] result;

            //Read the File from streamingAssets. Use WWW for Android
            if (dbStreamingAsset.Contains("://") || dbStreamingAsset.Contains(":///"))
            {
                WWW www = new WWW(dbStreamingAsset);
                yield return www;
                result = www.bytes;
            }
            else
            {
                result = File.ReadAllBytes(dbStreamingAsset);
            }
            Debug.Log("Loaded db file");

            //Create Directory if it does not exist
            if (!Directory.Exists(Path.GetDirectoryName(dbDestination)))
            {
                Directory.CreateDirectory(Path.GetDirectoryName(dbDestination));
            }

            //Copy the data to the persistentDataPath where the database API can freely access the file
            File.WriteAllBytes(dbDestination, result);
            Debug.Log("Copied db file");
        }

        //Now you can do the database operation
        //open db connection
        var connection = new SqliteConnection(dbDestination);
        connection.Open();
        var command = connection.CreateCommand();

        // Drop the table if it already exists.
        command.CommandText = "DROP TABLE IF EXISTS existing_individual;";
        command.ExecuteNonQuery();

        var sql = "CREATE TABLE existing_individual (studentID VARCHAR(23), fullName VARCHAR(50), studentNumber VARCHAR(20))";
        command.CommandText = sql;
        command.ExecuteNonQuery();

        //Inserting the exisiting student names returned, into the SQLite DB 

        int count = 0;

        foreach (var individuals in jsonStudentID)
        {
            //looping through the existing students registered for the individual quiz - below has been written to avoid SQL injection
            sql = "INSERT INTO existing_individual (studentID, fullName, studentNumber) VALUES (@jsonStudentID, @jsonIndiNames, @jsonIndiStudentNumbers)";
            command.Parameters.AddWithValue("@jsonStudentID", jsonStudentID[count]);
            command.Parameters.AddWithValue("@jsonIndiNames", jsonIndiNames[count]);
            command.Parameters.AddWithValue("@jsonIndiStudentNumbers", jsonIndiStudentNumbers[count]);

            command.CommandText = sql;
            command.ExecuteNonQuery();

            count++;
        }

        //close the connection
        command.Dispose();
        command = null;
        connection.Close();
        connection = null; 
    }

However, I am still getting the following errors:

06-08 15:26:56.498 16300-16315/? E/Unity: ArgumentException: Invalid ConnectionString format for parameter "/storage/emulated/0/Android/data/com.tbltools.tbl_project/files/data/TBLDatabase.db"
      at Mono.Data.Sqlite.SqliteConnection.ParseConnectionString (System.String connectionString) [0x00000] in <filename unknown>:0 
      at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in <filename unknown>:0 
      at UIHandler+<RunDbCode>c__Iterator3.MoveNext () [0x00000] in <filename unknown>:0 
      at UnityEngine.SetupCoroutine.InvokeMoveNext (IEnumerator enumerator, IntPtr returnValueAddress) [0x00000] in <filename unknown>:0 
    UnityEngine.MonoBehaviour:StartCoroutineManaged2(IEnumerator)
    UnityEngine.MonoBehaviour:StartCoroutine(IEnumerator)
    <RequestAllStudentNames>c__Iterator2:MoveNext()
    UnityEngine.SetupCoroutine:InvokeMoveNext(IEnumerator, IntPtr)

    (Filename:  Line: -1)
06-08 15:26:56.502 16300-16315/? E/Unity: ArgumentException: Invalid ConnectionString format for parameter "URI"
      at Mono.Data.Sqlite.SqliteConnection.ParseConnectionString (System.String connectionString) [0x00000] in <filename unknown>:0 
      at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in <filename unknown>:0 
      at UIHandler.CreateIndiButton () [0x00000] in <filename unknown>:0 
      at UIHandler+<RequestAllStudentNames>c__Iterator2.MoveNext () [0x00000] in <filename unknown>:0 
      at UnityEngine.SetupCoroutine.InvokeMoveNext (IEnumerator enumerator, IntPtr returnValueAddress) [0x00000] in <filename unknown>:0 

I have also added my database to the 'StreamingAssets' folder as shown in the image below:

Below also shows an image of my plugins folder that holds my dll files.

Answer 1

Most tutorials on this topic are outdated.

Looked at the code and found few problems but I can't tell if those are the reason you are getting this error. WWW should be used in a coroutine function so that you can yield or wait for loadDb.isDone is finish by adding yield return null inside the while loop. You can also yield the WWW request itself and that's the method I will use in my answer.

Also, jar:file://" + Application.dataPath is an old code. Use Application.streamingAssetsPath for that. Furthermore, you don't need "URI=file:" + Application.dataPath. Just use Application.persistentDataPath for that.

I will just put an instruction on how to do the setup.

Setting up the MANAGED code part:

1.Go to your Unity installation path

<UnityInstallationDirecory>\Editor\Data\Mono\lib\mono\2.0

Copy the following files:

• I18N.MidEast.dll
• I18N.Other.dll
• I18N.Rare.dll
• I18N.West.dll
• Mono.Data.Sqlite.dll
• Mono.Data.SqliteClient.dll
• System.Data.dll

to the project's <ProjectName>\Assets\Plugins path.

This will allow you to compile API from the Mono.Data.Sqlite namespace without any error.

---

Setting up the UNMANAGED code part:

In this step, you will need to get the native Sqlite library. You can get the source code, build it and use it or use already pre-compiled binray.

1.Get the native library for Windows

Download the pre-compiled sqlite3.dll for Windows 64 bit from here, and put it in the <ProjectName>\Assets\Plugins\x86_64 path.

If using Windows 32 bit then get the sqlite3.dll version from here and put it in the <ProjectName>\Assets\Plugins\x86 path.

---

2.Get the native library for Android

Download the pre-compiled libsqlite3.so for Android ARM processor from here and put it in the <ProjectName>\Assets\Plugins\Android\libs\armeabi-v7a path.

Download the pre-compiled libsqlite3.so for Android Intel x86 processor from here and put it in the <ProjectName>\Assets\Plugins\Android\libs\x86 path.

This covers most processors used on Android devices.

---

3.Get the native library for UWP

A.Download the WSA folder then put the WSA folder in the <ProjectName>\Assets\Plugins path. That folder contains the native part.

B.Create 2 files named "mcs.rsp" and "csc.rsp" in the <ProjectName>\Assets path.

C.Add the following inside the "mcs.rsp" and "csc.rsp" files:

-r:I18N.MidEast.dll

-r:I18N.Other.dll

-r:I18N.Rare.dll

-r:I18N.West.dll

-r:Mono.Data.Sqlite.dll

-r:Mono.Data.SqliteClient.dll

-r:System.Data.dll

D.You will have to move the managed dlls to the root folder of the project when building for UWP. So, move I18N.MidEast.dll, I18N.Other.dll, I18N.Rare.dll, I18N.West.dll, Mono.Data.Sqlite.dll, Mono.Data.SqliteClient.dll, System.Data.dll to the <ProjectName> path not <ProjectName>\Assets\Plugins path.

---

4.For iOS, Linux and Mac, it looks like you don't have to download anything else for them or do this step. They usually have the native pre-compiled Sqlite libraries built-in.

---

Including the Database file in the Build:

1.Create a folder in your <ProjectName>\Assets folder and name it StreamingAssets. Spelling counts and it's case sensitive.

2.Put the database file (TBLDatabase.db) in this StreamingAssets folder.

---

Accessing the Database File after building the project

Sqlite cannot work on files in the StreamingAssets folder in a build since that's a read-only path. Also, Android requires that you use the WWW API instead of the standard System.IO API to read from the StreamingAssets folder. You have to copy the db file from Application.streamingAssetsPath/filename.db to Application.persistentDataPath/filename.db.

On some platforms, it is required that you create a folder inside Application.persistentDataPath and save data to that folder instead. Always do that. The folder in the example code below is "data" so that will become Application.persistentDataPath/data/filename.db.

3.Because of the statement above, check if the database file exist in the Application.persistentDataPath/data/filename.db. If it does, use Application.persistentDataPath/data/filename.db as a path for your database operation. If it doesn't, continue from #4.

4.Read and copy the database file from the StreamingAssets folder to Application.persistentDataPath

On some platforms, it is required that you create a folder inside Application.persistentDataPath and save data to that folder instead. Always do that. The folder in the example below is "data".

Detect if this is Android and use WWW read to the file from Application.streamingAssetsPath/filename.db. Use File.ReadAllBytes to read it on anything else other than Android. In your example, you used Application.platform for that. In my example, I will simply check if the path contains "://" or :/// to do that.

5.Once you read the file, write the data you just read to Application.persistentDataPath/data/filename.db with File.WriteAllBytes. Now, you can use this path for your database operation.

6.Prefix "URI=file:" to the Application.persistentDataPath/data/filename.db path and that's the path for that should be used in your database operation with the Sqlite API.

---

It's very important that you understand all these in order to fix it when something changes but I've already done step #3 to #6 below.

IEnumerator RunDbCode(string fileName)
{
    //Where to copy the db to
    string dbDestination = Path.Combine(Application.persistentDataPath, "data");
    dbDestination = Path.Combine(dbDestination, fileName);

    //Check if the File do not exist then copy it
    if (!File.Exists(dbDestination))
    {
        //Where the db file is at
        string dbStreamingAsset = Path.Combine(Application.streamingAssetsPath, fileName);

        byte[] result;

        //Read the File from streamingAssets. Use WWW for Android
        if (dbStreamingAsset.Contains("://") || dbStreamingAsset.Contains(":///"))
        {
            WWW www = new WWW(dbStreamingAsset);
            yield return www;
            result = www.bytes;
        }
        else
        {
            result = File.ReadAllBytes(dbStreamingAsset);
        }
        Debug.Log("Loaded db file");

        //Create Directory if it does not exist
        if (!Directory.Exists(Path.GetDirectoryName(dbDestination)))
        {
            Directory.CreateDirectory(Path.GetDirectoryName(dbDestination));
        }

        //Copy the data to the persistentDataPath where the database API can freely access the file
        File.WriteAllBytes(dbDestination, result);
        Debug.Log("Copied db file");
    }

    try
    {
        //Tell the db final location for debugging
        Debug.Log("DB Path: " + dbDestination.Replace("/", "\\"));
        //Add "URI=file:" to the front of the url beore using it with the Sqlite API
        dbDestination = "URI=file:" + dbDestination;

        //Now you can do the database operation below
        //open db connection
        var connection = new SqliteConnection(dbDestination);
        connection.Open();

        var command = connection.CreateCommand();
        Debug.Log("Success!");
    }
    catch (Exception e)
    {
        Debug.Log("Failed: " + e.Message);
    }
}

Usage:

string dbFileName = "TBLDatabase.db";

void Start()
{
    StartCoroutine(RunDbCode(dbFileName));
}