Unity 3D + Android + SQLite Examples

Unity 3D + Android + SQLite Examples

  1. fruki

    fruki

    New Member

    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. public void OpenDB(string p)
    3. {
    4.     connection = “URI=file:” + Application.persistentDataPath + “/” + p; // we set the connection to our database
    5.     //connection = “URI=file:” + p; // like this will NOT work on android
    6.     Debug.Log(connection);
    7.     dbcon = new SqliteConnection(connection);
    8.     dbcon.Open();
    9. }

    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
    2. string filepath = Application.persistentDataPath + “/” + p;
    3. if(!File.Exists(filepath))
    4. {
    5.     // if it doesn’t ->
    6.     // open StreamingAssets directory and load the db ->
    7.     WWW loadDB = new WWW(“jar:file://” + Application.dataPath + “!/assets/” + p);
    8.  // this is the path to your StreamingAssets in android
    9.     while(!loadDB.isDone) {}
    10. // CAREFUL here, for safety reasons you shouldn’t let this while loop unattended,
    11. // place a timer and error check
    12.     // then save to Application.persistentDataPath
    13.     File.WriteAllBytes(filepath, loadDB.bytes);
    14. }
    15. //open db connection
    16. connection = “URI=file:” + filepath;
    17. dbcon = new SqliteConnection(connection);
    18. 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

    Attached Files:

    Last edited: Dec 7, 2011
  2. mpavlinsky

    mpavlinsky

    New Member

    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.

  3. MonoSapiens

    MonoSapiens

    New Member

  4. Meltdown

    Meltdown

    Member

    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?

  5. Fayer

    Fayer

    New Member

    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?

  6. 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

  7. ina

    ina

    Member

    How does this compare to MonoSapiens? MonoSQLite

  8. 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();

    Last edited: Sep 11, 2012
  9. liliyanlyn

    liliyanlyn

    New Member

    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.

  10. Compguru910

    Compguru910

    New Member

    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.

  11. tredpro

    tredpro

    Member

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

  12. hollym16

    hollym16

    Member

    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?

  13. joao_pm

    joao_pm

    New Member

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

  14. i am currently working on tis.thanks guys

  15. ganesh

    ganesh

    Member

    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'”);
    2.         // Concatenate all the elements into a StringBuilder.
    3.         builderdisplay = new StringBuilder();
    4.         for (int i = 0; i < result2.GetLength(0); i++)
    5.         {
    6.             for (int j = 0; j < result2.GetLength(1); j++)
    7.             {
    8.                 builderdisplay[2].Append(result2[i,j]);
    9.                 builderdisplay[2].Append(‘ ‘);
    10.             }
    11.             subjectnamed.text = builderdisplay.ToString ();
    12.         }
  16. ganesh

    ganesh

    Member

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

  17. Simball

    Simball

    Member

    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?

  18. Meltdown

    Meltdown

    Member

    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.

  19. thready

    thready

    Member

    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!

    Attached Files:

  20. monching21

    monching21

    Member

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