Setup Database (Sqlite) for Unity

Setup Database (SQLite) for Unity

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));
}

How to mitigate , char breaking Unity connection to SQLite database

You need to look for what arguably are breaking characters. Commas. Semi colons. Quote marks. In your case caused by locale of a float number. To the English world 5.5. To some countries 5,5. Im sure there are other breaking characters it will be documented. Its not clear from your code if you are using odbc or a direct sql or what but each will have documented a list of special characters

Trying to insert into an SQLite database in c# on unity

You are calling the ExecuteNonQuery() on the command variable which does not contain any command.

Get rid of the line:

command.ExecuteNonQuery();

The following line is what actually executes the SqliteCommand that you populated:

 IDataReader reader = dbcmd.ExecuteReader();

---

Related Topics