• Tag Archives sqlite
  • 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