Accessing SQLite database in Unity with a coroutine

Question

I've created a menu in Unity which is populated by results from an SQLite DB. However when I create the menu, the whole game freezes for a moment while it queries the DB.

To fix this, I'm trying to separate the creation of the menu and the populating of it with data (i.e. the menu will just say "loading" until the query is complete).

I have been trying to use a yield-return co-routine to do this but the game is still freezing. Below I have some pseudo-code illustrating what I am doing...

void createMenu () {

    // code to create menu... 

    StartCoroutine(getData());

}

IEnumerator getData () {

    List<string> sqlResults = Database.query("SELECT * FROM table");

    yield return null;

    updateMenu();

}

void updateMenu() {

   // replaces "loading" strings with sql data results 

}

Am I going about this the wrong way, or am I using a coroutine incorrectly?

score 4 · Accepted Answer · answered Sep 29 '17 at 11:55

4

It looks like the database operation is blocking the main thread. Run it in a new Thread with the ThreadPool.QueueUserWorkItem function. When it's done, use UnityThread.executeInUpdate function from this post to call the updateMenu() function. You need to get the UnityThread class from this post in order to use this solution.

void Awake()
{
    UnityThread.initUnityThread();
}

void runQuery(string query)
{
    ThreadPool.QueueUserWorkItem(RunInNewThread, query);
}

private void RunInNewThread(object a)
{
    string query = (string)a;
    List<string> sqlResults = Database.query(query);


    //We are in another Thread. Use executeInUpdate to run in Unity main Thread
    UnityThread.executeInUpdate(() =>
    {
        updateMenu();
    });
}

USAGE:

runQuery("SELECT * FROM table");

answered Sep 29 '17 at 11:55

Programmer

121,791
22
236
328

So you're saying there's no way to do this natively with Unity? – aadu Sep 29 '17 at 12:02
Your comment doesn't make sense. This answer is doing it "natively". So, call `runQuery("SELECT * FROM table");` from the `createMenu` function. It should not block or freeze unity. If not then the problem is in the `updateMenu()` function and you should post that as well. – Programmer Sep 29 '17 at 12:05
Well I just meant that I have to include that guy's whole UnityThread class to get it working. It's long enough to be an external DLL. – aadu Sep 29 '17 at 12:08
[lockstock](https://stackoverflow.com/a/46488441/3785314) answer should work but it is doing **the-same** thing I did except that it is **less** performant than my answer because it is creating new `Thread` each time instead of using `ThreadPool`. Also, it is not Threadsafe like the `UnityThread` script I made but it should work. – Programmer Sep 29 '17 at 12:08
`UnityThread` is written by me and is there so that you don't have to write yours. It makes using Thread in Unity easier and safe. That's it. You can write your own wrapper if you don't want to use it. The main goal of it is to simplify using Thread in Unity. – Programmer Sep 29 '17 at 12:10

score 2 · Answer 2 · answered Sep 29 '17 at 12:03

2

Yes putting this code in a coroutine as you have will not change anything. Unity is single threaded, however you can start a separate thread to do some work as long as their are no calls to any Unity functions executed in that thread.

Try this:

IEnumerator getData () {

    List<string> sqlResults;

    var thread = new System.Threading.Thread(() => {
        sqlResults = Database.query("SELECT * FROM table");
    };

    thread.Start();

    while(thread.IsAlive){
        yield return null;
    }

    updateMenu();
}

answered Sep 29 '17 at 12:03

lockstock

2,359
3
23
39

Your answer solved the problem too, but @Programmer got their first :) – aadu Sep 29 '17 at 12:21
Now that `UnityThread` seems to be unsupported I was attempting to use `async/await` which was a no go so this is a nice work-around. Thinking about it more, `UnityThread` was likely replaced by the `Unity Job System` oh well. – Jacksonkr Feb 02 '22 at 20:37

score 0 · Answer 3 · answered Sep 29 '17 at 11:35

0

So the problem here is that you are synchronizing with the server and not asyncying and which will make you wait for the connection or query (if it gives you something wrong or its a wrong query you'll wait till the timeout of httprequest or db request). Try to use async functions instead of enumerator or than a enumator with async fucntion call which will let you being on the menu while it is retrieving the information.

answered Sep 29 '17 at 11:35

life.cpp

30
7

But it's an SQLite database. There is no server involved? It's just accessing a database file. – aadu Sep 29 '17 at 11:40
Do you have a link to an example on how to write an async function? The Unity documentation is useless – aadu Sep 29 '17 at 11:53
https://www.codeproject.com/Articles/1121822/Using-Async-Await-Task-Methods-With-SQL-Queries-NE Check there – life.cpp Sep 29 '17 at 12:12

Accessing SQLite database in Unity with a coroutine

3 Answers3

Linked