Hi everybody I just learned some things in the past week about SQLite in Unity3D with Android and I thought I share them as I had to figure out most of it myself. All code in C#.

Set up a database in the android device on runtime

This is pretty easy, if you create a connection to a file that doesn’t exist, it will be created. This is perfect to save data created by your application like scores or savegames. Note that once you created the database, it will stay there until deleted explicilty, so if you created it the first time, you will have to create a table before inserting values.

Code (csharp):

1. private IDbConnection dbcon;
2. …
4. public void OpenDB(string p)
6. {
8.     connection = “URI=file:” + Application.persistentDataPath + “/” + p; // we set the connection to our database
10.     //connection = “URI=file:” + p; // like this will NOT work on android
12.     Debug.Log(connection);
14.     dbcon = new SqliteConnection(connection);
16.     dbcon.Open();
18. }

After this you can populate the database in different ways, I have attached a unitypackage to this post under the name “SQLite” where you can see an example. (note: there are many parts of the code which I took from internet examples)

Load an already populated database in the android device

This is trickier, as Android packs everything into a single file and sends it to the device. This means that if you want to send a file with it, you have to pack it in there and then somehow retrieve the data from the package. This comes in handy when you need a database already filled up with data, like a dictionary or NPC phrases.

The way to do it is to create a folder named “StreamingAssets” in your assets folder. Then place your database inside, and at runtime “stream it” in realtime:

Code (csharp):

1. // check if file exists in Application.persistentDataPath
3. string filepath = Application.persistentDataPath + “/” + p;
5. if(!File.Exists(filepath))
7. {
9.     // if it doesn’t ->
11.     // open StreamingAssets directory and load the db ->
13.     WWW loadDB = new WWW(“jar:file://” + Application.dataPath + “!/assets/” + p);
14.  // this is the path to your StreamingAssets in android
16.     while(!loadDB.isDone) {}
17. // CAREFUL here, for safety reasons you shouldn’t let this while loop unattended,
18. // place a timer and error check
20.     // then save to Application.persistentDataPath
22.     File.WriteAllBytes(filepath, loadDB.bytes);
24. }
28. //open db connection
30. connection = “URI=file:” + filepath;
32. dbcon = new SqliteConnection(connection);
34. dbcon.Open();

You have the full code for this example in “SQLiteLoad”

Hope it helps someone. 

P.S. Some users from this forum helped a lot, you can take a look at this conversation for more information:http://forum.unity3d.com/threads/97043-Sqlite-for-Android-help-please

Thanks very much, this post was very helpful to me. I’m not loading a SQLLite database but the tip off about using the JARURL for accessing StreamingAssets is exactly what I needed for my AssetBundles.

Thanks again.

If you don’t wish to have your work mixed up with non-professional solutions, you can try this asset:http://u3d.as/content/mono-sapiens-ltda/mono-sqlite/2Qw

I’m using it and it really solves my problems.

Hi Fruki,

Thanks for the very helpful post!
I’ve followed everything to a tee, but I’m getting the following error…

System.EntryPointNotFoundException: sqlite3_column_origin_name

Your SQLLiteLoad Package works fine on the Android device.
Yet my own database doesn’t. Any idea what could be causing this issue?

I manage to do the connection and everything but I want to acces the DB from my computer, is there a way to do that?

please, check this ->http://u3d.as/content/orange-tree/sqlite-kit/3ka
That is 100% managed code, full SQLite3 support, all platforms.
No native dependencies. 
There is example scene with test/demo code for platforms: WebPlayer, PC, Mac, Android, IPhone

How does this compare to MonoSapiens? MonoSQLite

If you like I could sent an example code to you. You will find how easy work with database byhttp://u3d.as/content/orange-tree/sqlite-kit/3ka . You will compare by your self. Portability was tested by Unity team on approval stage.


// database file name.
//
string filename = Application.persistentDataPath + “/demo.db”;

// create database object
// 
SQLiteDB db = new SQLiteDB();

// initialize database
//
db.Open(filename); 

// make a query object
//
SQLiteQuery qr = new SQLiteQuery(db, “SELECT * FROM test_values;”);

// read result
//
while( qr.Step() )
{
string astring = qr.GetString(“str_field”);
byte[] ablob = qr.GetBlob(“blob_field”);
}

// release query object
//
qr.Release(); 

// close database
//
db.Close();

hello, i’m a newbie developing unity 3d app for android platform, I want to ask is it possible to making connection to “Streaming Assets” using javascript instead c# ? 
I can compile it well on my unity editor, but when I apply it on my android the application isnt work because cant get how to acess “Streaming Assets” for load the database.

I dont know exactly how Unity interfaces with Android when it comes to databases. So, here is my experience working with preloaded databases. First, you want to make sure that your android database has a table called android_metadata in it with a field called locale. The value of that is of course, the locale that your working with. For english it is en_US. Secondly, for devices prior to 2.3, you cannot side load a database or any other asset that is larger than 1Mb, so keep that in mind when creating your database. The fix for this is splitting the file, then reassembling it on run. Here is how it works for a non Unity android app

Create the database by attempting to open. If one doesnt exit, it is created. 

If the database is empty, open a stream writer and copy the existing database to the app data directory, overwritting the empty DB that android created. Use a for loop if the database has been split due to size issues. 

If anyone wants to see the Android code on how to do this, please let me know and I’ll send you an example.

Im sorry but what area to you past the url to your database?

I’m wanting to use a database to call different models at certain times on an Android app. Will this method make the app over 50mb if you’re calling it from Streaming Assets folder?

Thanks!
This post was really useful for me, almost 3 years after it was written!

i am currently working on tis.thanks guys

hi to all..
i used $SQLite.unitypackage for connect my database in android,its works well.Now I want to display the name of value where word=”primary”.Help me to get result.My db table is lik dis..And my code is this way

kp word value
1 bio poa
2 primary pob
3 primary pos

Code (CSharp):

1. string[,] result2 = SingleSelectWhere(“petdef”, “value”, “word”, “=”, “‘primary'”);
3.         // Concatenate all the elements into a StringBuilder.
4.         builderdisplay = new StringBuilder();
5.         for (int i = 0; i < result2.GetLength(0); i++)
6.         {
7.             for (int j = 0; j < result2.GetLength(1); j++)
8.             {
9.                 builderdisplay[2].Append(result2[i,j]);
10.                 builderdisplay[2].Append(‘ ‘);
11.             }
12.             subjectnamed.text = builderdisplay.ToString ();
13.         }

how to add and access one more field in $SQLite.unitypackage.Any idea about that.

Hi,Meltdown,I met the same problem.It happened when I try to use “SqliteDataAdapter” to fill “dataset” . Can you tell me how you resolved it?

Sorry I can’t remember, I didn’t use SQLlite in the end. But I would suggest finding the correct library. Mono.Data.SqlLite.dll.

There are also some SQLite packages on the asset store that support Android, I would suggest buying one of these instead to save you a headache.

Alternately look at an online hosted solution for your player data, which might be easier, as you can use it for all platforms.

Hi everyone, I’m trying to use the package that fruki provided, and I have only the free version of Unity. I’m getting the errors that I have in the attached image… is it because I need to go pro or are these suggestions likely to work on the free version of unity?

Thanks!

me also, it looks like unity free version does not support the sqlliteclient T_T or im wrong T_T pls help us