Select Page

Using SQLite Database In Unity3D 4.3.3f

by Jan 23, 2014Games Development47 comments

What I want is to create an inventory system for my game. I read through all the options from the world wide web and there’s many options that I can choose. Some of them are using XML, JSON and then SQLite database which then attract my attention and interest.

I’ve experience working with MySQL when I was developing plugin and theme for WordPress. I know how JSON works, and never like it since the very first encounter with it. XML? Never work with it and I was thinking if one day I want to move on into “online gaming” the SQLite will be easily dumped into and SQL that can be use by MySQL. So the decision was made.

It wasn’t an easy road to take. I’ve found many example that doesn’t work and spilled out errors which make I almost give up. Then I found something work, but it’s in Javascript which will be against my principle of using C# only. Beside, using 2 language will give a lot of errors later that is hard for my level of experience to debug.

But finally, I check out the official Mono website. Unity scripting was backed by Mono actually. It was this page. SQLite – Mono. It’s not an exact implementation though but was easier that I thought.

Here’s the code.

[code language=”csharp”]

usingΒ UnityEngine;
usingΒ System.Collections;
usingΒ Mono.Data.Sqlite;
usingΒ System.Data;
usingΒ System;

publicΒ classΒ DBAccessΒ :Β MonoBehaviourΒ {

//Β UseΒ thisΒ forΒ initialization
voidΒ StartΒ ()Β {

stringΒ connectionStringΒ =Β "URI=file:"Β +Application.dataPathΒ +Β "/GameMaster";Β //PathΒ toΒ database.
IDbConnectionΒ dbcon;
dbconΒ =Β (IDbConnection)Β newΒ SqliteConnection(connectionString);

dbcon.Open();Β //OpenΒ connectionΒ toΒ theΒ database.

IDbCommandΒ dbcmdΒ =Β dbcon.CreateCommand();

stringΒ sqlΒ =Β "SELECTΒ firstname,Β lastnameΒ "Β +Β "FROMΒ addressbook";

dbcmd.CommandTextΒ =Β sql;

IDataReaderΒ readerΒ =Β dbcmd.ExecuteReader();

while(reader.Read())Β {
stringΒ FirstNameΒ =Β reader.GetStringΒ (0);
stringΒ LastNameΒ =Β reader.GetStringΒ (1);
Console.WriteLine("Name:Β "Β +
FirstNameΒ +Β "Β "Β +Β LastName);
Debug.LogΒ (FirstNameΒ +Β LastName);
}

//Β cleanΒ up
reader.Close();
readerΒ =Β null;
dbcmd.Dispose();
dbcmdΒ =Β null;
dbcon.Close();
dbconΒ =Β null;

}

//Β UpdateΒ isΒ calledΒ onceΒ perΒ frame
voidΒ UpdateΒ ()Β {

}

voidΒ OnGUIΒ ()Β {

stringΒ connectionStringΒ =Β "URI=file:"Β +Application.dataPathΒ +Β "/GameMaster";Β //PathΒ toΒ database.
IDbConnectionΒ dbcon;
dbconΒ =Β (IDbConnection)Β newΒ SqliteConnection(connectionString);

dbcon.Open();Β //OpenΒ connectionΒ toΒ theΒ database.

IDbCommandΒ dbcmdΒ =Β dbcon.CreateCommand();

stringΒ sqlΒ =Β "SELECTΒ firstnameΒ FROMΒ addressbookΒ WHEREΒ rowid=1";

dbcmd.CommandTextΒ =Β sql;

IDataReaderΒ readerΒ =Β dbcmd.ExecuteReader();

while(reader.Read())Β {
stringΒ FirstNameΒ =Β reader.GetStringΒ (0);
//stringΒ LastNameΒ =Β reader.GetStringΒ (1);
//Console.WriteLine("Name:Β "Β +
//FirstNameΒ +Β "Β "Β +Β LastName);
//Debug.LogΒ (FirstNameΒ +Β LastName);

GUI.BoxΒ (newΒ RectΒ (Screen.widthΒ –Β 270,Β Screen.heightΒ –Β 55,Β 260,Β 30),Β "CopyrightΒ "+FirstName+"Β 2014");
}

//Β cleanΒ up
reader.Close();
readerΒ =Β null;
dbcmd.Dispose();
dbcmdΒ =Β null;
dbcon.Close();
dbconΒ =Β null;

}
}

[/code]

In

[code language=”csharp”]

void start () {

}

[/code]

The code suppose to read the value in the database and print the value using the debug log in Unity.

The Database Table Data

The Database Table Data

The debug log print the value correctly.

The debug log print the value correctly.

Then the code;

[code language=”csharp”]

void OnGui () {

}

[/code]

is where I want to test the implementation. I manage to print out the value and show it on screen, but it happen to be a very bad practice. Some nice guys at Unity community forum point out to me that I should read and get all the data in the variable at the start() and then just get the string value whenever I need them in the code.

It’s different when working with PHP, when developing the WordPress plugin and theme. That environment, I execute SQL queries whenever I need them, anywhere, anytime I want. And the execution is as simple as 1 line of code. (Because I like using overloading method, tried that in C# but I can’t get it to work yet.)

Print my name taken from the database.

Print my name taken from the database.

But now, I have to think out a framework, the flow chart of how I’m going to actually use this database system. It will take some time but after this, I have a framework that I can use for my future game. πŸ™‚

Update 30th August, 2014 : Download the codes from GitHub, link below.

https://github.com/PangeranWiguan/W.G.-Unity3D-SQLite

Update: This summary how to setup the SQLite in Unity3D free edition.

47 Comments

  1. dominic

    I’m trying to get sqlite into my Unity 4.3.x. Do you have a step by step instruction on how I can accomplish that?

    Reply
    • Pangeran Wiguan @ WG Creative Solutions

      Please take a look at the code header.
      You need to copy the mono.sqlite and system.data into your assets folder and also the SQLite dll into your asset folder for the code to work.

      Reply
      • Reginaldo

        Hi! But, how I may to find this dll? Could you help e?

        Reply
      • Ahmad Farisi

        Can u explain to me about this? where can I get the mono.sqlite and system.data?
        And how about SQLite dll, where can i find it out? Would u like to help me please?

        Reply
        • Pangeran Wiguan @ WG Creative Solutions

          You can find the mono.sqlite at your Mono directory inside your computer.
          It’s already been there when you install unity.
          system.data is inside Unity directory.

          For SQLite.dll, download it from SQLite official website.

          Reply
  2. g2-0476bd231df5a4f2701d1bfb51b6c1ea

    Where is the database file name?, in your examples only appear
    the database path location

    stringΒ connectionStringΒ =Β “URI=file:”Β +Application.dataPathΒ +Β “/GameMaster”;Β //PathΒ toΒ database

    Do I need a specific name for the database?

    connectionString = “URI=file:”+Application.dataPath+”/dbfile.sqli3”
    or a special extension is not required
    and you Database file is “GameMaser”

    thanks for your attention

    Reply
    • Pangeran Wiguan @ WG Creative Solutions

      I create the databse, table and the table value using SQLite Database Browser.
      The Database was dumped as GameMaster (no .sqlite extension).
      I think that’s SQLite 3 feature.
      Then that code simply open the filename “GameMaster” which is my database.

      If you create the database using SQL queries and using .db or .sqlite extension, then you should include that, for example;

      string connectionString = ”URI=file:” +Application.dataPath + ”/GameMaster.db”;

      I hope it works for you.

      Reply
  3. Jrod

    Thank you for posting this… very helpful!

    You mention “I execute SQL queries whenever I need them, anywhere, anytime I want”. This is also not a very good practice in PHP land. I have not written any PHP for years (moved on to Ruby, Javascript, Python) but when I was working with PHP I would always create classes to wrap all database interaction and then just call the appropriate functions on the data class when I needed to access the database.

    Reply
  4. CodeAssembler

    I guess this has nothing to do with actual SQL coding but just thought to remember OnGUI() is like Update() it runs continuously so you might want to have a bool variable to prevent the whole SLQ code to execute constantly. I’m amazed actually it can run like that without crashing the device. Something like: bool hasExecuted = false; global and then : an if inside OnGUI() like If (!hasExecuted) then you process the code and flag hasExecuted = true; when finish the code inside the if() at OnGUI() so that it only execute once. Unless you change on purpose the value of hasExecuted = false; at some other point of the code to trigger an update for the screen.

    Then a huge thank you for being so kind as to share this, because its a lot of time and efforts indeed you are saving us (the community) with this post. So thanks a lot.

    – Luis

    Reply
  5. Frozenfire

    Will this work in the free version of Unity? It errors saying that the dll’s are only supported in unity pro

    Reply
    • Pangeran Wiguan @ WG Creative Solutions

      I use free version of Unity 3D 4.3.3.
      The “f” means it’s a free version.
      Works with no flaws.
      Put the DLL in your assets folder for it to work.

      Reply
      • Frozenfire

        I have tried with Mono.Data.Sqlite.dll and Mono.Data.SqliteClient.dll and System.Data.dll in Assets/Plugins/ and in the editor I can access the database fine but I still get these kind of errors in the console

        License error. This plugin is only supported in Unity Pro!
        Mono.Data.SqliteClient.SqliteConnection:SetConnectionString(String)

        And it will not let me build an executable from it.

        Reply
      • Frozenfire

        Update

        I can get rid of those errors in the editor when in Play mode by moving the DLLs into the base root Assets/ folder. However when I go to build I get errors

        ArgumentException: The Assembly System.Configuration is referenced by System.Data. But the dll is not allowed to be included or could not be found.
        UnityEditor.AssemblyHelper.AddReferencedAssembliesRecurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache, BuildTarget target) (at C:/BuildAgent/work/d3d49558e4d408f4/Editor/Mono/AssemblyHelper.cs:114)
        UnityEditor.AssemblyHelper.AddReferencedAssembliesRecurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache, BuildTarget target) (at C:/BuildAgent/work/d3d49558e4d408f4/Editor/Mono/AssemblyHelper.cs:117)
        UnityEditor.AssemblyHelper.AddReferencedAssembliesRecurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache, BuildTarget target) (at C:/BuildAgent/work/d3d49558e4d408f4/Editor/Mono/AssemblyHelper.cs:117)
        UnityEditor.AssemblyHelper.FindAssembliesReferencedBy (System.String[] paths, System.String[] foldersToSearch, BuildTarget target) (at C:/BuildAgent/work/d3d49558e4d408f4/Editor/Mono/AssemblyHelper.cs:149)
        UnityEditor.BuildPlayerWindow:BuildPlayerAndRun()

        and

        Error building Player: Extracting referenced dlls failed.

        Reply
  6. Pangeran Wiguan @ WG Creative Solutions

    Yes, I was about to ask you to move the DLLs into the root folder of the “assets” folder.

    Then you have to manually copy the database assets into your builds.

    If you want the example file I can send it to you through your email. πŸ™‚

    I don’t know how to explain to you since I’m not a programmer.

    Reply
    • Frozenfire

      Manually copy the database assets into my builds as in the same folder as the executable?

      Please do send it to my email, this is the closest I’ve gotten to getting a databse on Unity free πŸ™‚

      Reply
  7. Frozenfire

    Thanks for the help. Here is a quick rundown for anyone else who is having troubles. Note these are the steps for windows, but I haven’t gotten around to mac yet.

    1. Download a Precompiled DLL of SQLite http://www.sqlite.org/download.html
    2. Move the sqlite3.dll and sqlite3.def to Assets/Plugins in your unity project
    3. Download SQLite Browser http://sourceforge.net/projects/sqlitebrowser/
    4. Create a database in your Assets/ folder in your unity project with SQLite broswer
    5. Copy System.Data.dll and Mono.Data.Sqlite.dll from C:Program Files (x86)UnityEditorDataMonolibmono2.0 and paste them in your Assets/ folder in your unity project
    6. Access the database as above, only where “/GameMaster” is put “/YourDbName”. Note if you did this correctly MonoDevelop should have all of the methods when you type reader.
    7. When building you must copy your database file to the folder that automatically is created called yourProject_Data wherever you saved the executable

    Reply
      • Andross

        Hum I don’t understand, it’s work for one build only and now I have the same error message with the licence for the data πŸ™

        Reply
      • Andross

        Yes, when I have the error message “ArgumentException: The Assembly System.Configuration is referenced by System.Data. But the dll is not allowed to be included or could not be found. …. ” I try to use the .NET 2.0 in API compatibily level. It appears that it is only for Android pro πŸ™ may you share your player setting in order to see if it is one other setting which could made this issue? Thanks a lot for all !

        Reply
      • Andross

        Here the error message :
        “License error. This plugin is only supported in Unity Pro!

        Mono.Data.Sqlite.SqliteConnection:Open()” I have unity 4.5.1, so i think it’s not possible for us. But thank for your tuto,I will use it when I have the money to pay this licence πŸ˜‰

        Reply
        • Pangeran Wiguan @ WG Creative Solutions

          I am using free version man. Maybe I’ll try it again later. I ditch using SQLite for now because I’m using plyGame framework which already handle the data for me.

          Reply
      • Andross

        (I’m very sorry to write comment after comment!!).
        I will test your solution with one other version : 4.3.1 and see what happen (I will tell you). The error message display when I tempt to build the game for android. but the execution works fine. Do you have build your project?
        The plygame seems to be very usefull for no-dev but I don’t think this is the fixe.

        PS : I don’t know your site before soon, and I like it!

        Reply
        • Pangeran Wiguan @ WG Creative Solutions

          I’ve tested my old project in Unity 4.3.1f and open it in Unity 4.5.1 and it still work fine man.

          Perhaps you might want to take a look at my project?

          I don’t mind send you the package.

          Reply
      • andross

        Oh if you say that it’s works with recent version, yes I want your project if it is ok for you. Just to test.
        Thanks a lot

        Reply
      • andross

        Yes please, if it is possible?

        Reply
  8. Andross

    thank you for all!!

    Reply
  9. Roylisto Putra Pradana

    i have this error The type `SqliteConnection’ does not contain a constructor that takes `1′ arguments

    Reply
  10. Bruna

    I like it when folks get together and share ideas. Great site, stick with
    it!

    Reply
  11. Prashant

    I built and run your sample project in android but it doesn’t fetch anything from the database. It works fine in the editor. Any suggestions how to build this in android ?

    Reply
  12. milad3d

    Thank you so much for this…How we can insert data from a TextBox (GUI) and write it into data base …Could you write a method for do this?

    Reply
    • Pangeran Wiguan

      The new Unity Gui have the “OnClick” event.

      But before that, you need to create a functions that write the value into the database.

      Learn some SQL queries to do that.

      I honestly don’t use this solution anymore, I’m so sorry. πŸ™

      Reply
      • Milad3d

        Thank you I learned how to make a function for write to database with β€œOnClick” event. πŸ™‚ in your project and it works very good in PC πŸ™‚ BUT I want to Build it for Android but in my Android Device when I run it ,The Database dosen’t work πŸ™ Could you help me plz…

        Reply
        • Pangeran Wiguan

          It’s been reported by many.
          And even on Pro version of Unity it doesn’t work.
          Nothing I can do from here. πŸ™

          Reply
  13. fuji

    Hello, i have created a demo for a game with a database in sql with your plugin, it s work fine in Unity 4.6 but when i compile apk for test on my device or emulator dont work

    I try debug it but i can t can you help me plz

    using UnityEngine;
    using System.Collections;
    using Mono.Data.Sqlite;
    using System.Data;
    using System;
    using System.IO;
    using UnityEngine.UI;

    public class DBAccess : MonoBehaviour {

    private string connectionString;
    IDbConnection dbcon;
    public IDataReader reader;
    IDbCommand dbcmd;
    int Id = 0;

    public GameObject test;

    void Awake ()
    {
    GameObject.DontDestroyOnLoad(this.gameObject);
    test.GetComponent().text = “aa”;
    OpenDB();
    test.GetComponent().text = “bb”;
    dbcmd = dbcon.CreateCommand();
    test.GetComponent().text = “cc”;
    string select = “SELECT * FROM users”;
    dbcmd.CommandText = select;
    test.GetComponent().text = “dd”;
    reader = dbcmd.ExecuteReader();
    test.GetComponent().text = “ee”;
    bool Breader = reader.Read();
    test.GetComponent().text = “ff”;
    if (Breader == false)
    {
    test.GetComponent().text = “gg”;
    string sql = “INSERT INTO users (login, password, token, money, level, life, stamina, xp, last_level_finish, position_x, position_y, position_z) VALUES (”, ”, 0, 0, 1, 100, 100,0,0,0,0, 0)”;
    ExecuteQuery(sql);
    test.GetComponent().text = “hh”;
    }
    test.GetComponent().text = “ii”;
    Id = reader.GetInt32(0);
    test.GetComponent().text = Id.ToString();
    /* else
    {
    int id = reader.GetInt32(0);
    string sqls = “UPDATE users SET login = ‘xxx’ WHERE id= ” + id.ToString();
    ExecuteQuery(sqls);
    }*/
    }

    public void OpenDB()
    {
    // #if UNITY_EDITOR
    // connectionString = “URI=file:” + Application.dataPath + “/StreamingAssets/” + “catgiri.db”; //Path to database.
    //#elif UNITY_ANDROID
    // check if file exists in Application.persistentDataPath
    string filepath = Application.persistentDataPath + “/” + “catgiri.db”;
    //test.GetComponent().text = filepath;
    if (!File.Exists(filepath))
    {
    Debug.LogWarning(“File \”” + filepath + “\” does not exist. Attempting to create from \”” +
    Application.dataPath + “!/assets/” + “catgiri.db”);
    //test.GetComponent().text = “1”;
    // if it doesn’t ->
    // open StreamingAssets directory and load the db ->
    WWW loadDB = new WWW(“jar:file://” + Application.dataPath + “!/assets/” + “catgiri.db”);
    while (!loadDB.isDone) { }
    // then save to Application.persistentDataPath
    File.WriteAllBytes(filepath, loadDB.bytes);
    }
    //open db connection
    connectionString = “URI=file:” + filepath;

    // #endif
    Debug.Log(“Stablishing connection to: ” + connectionString);
    dbcon = (IDbConnection)new SqliteConnection(connectionString);
    test.GetComponent().text = “xx”;
    dbcon.Open();

    }

    It is my code for connection, this works fine in Unity but not android, i don t know where is the error,

    filepath is ok, the database is in folder, and debug say xx seems to stop at :

    dbcon = (IDbConnection)new SqliteConnection(connectionString);
    test.GetComponent().text = “xx”;
    dbcon.Open();

    I don t understand where is the problem can you help me ?

    thanks

    Reply
    • Pangeran Wiguan

      Hi, yes that’s the known issue here.
      It can play in Unity but not on mobile.

      I already long ditched this method and simply use other available plugin like plyGame.

      Reply
      • Rafael

        Friend, I have the same problem, as could resolve this problem, what’s plugin, I need your help

        Reply

Leave a Reply