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 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.
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.
Update: This summary how to setup the SQLite in Unity3D free edition.
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?
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.
Hi! But, how I may to find this dll? Could you help e?
I’ve replied your email that you send through the contact form. I hope that suffice to help you.
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?
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.
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
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.
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.
Yea, I guess it’s never a good practice. Thanks for dropping some line. 😀
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
Yes, you’re right.
And I’m glad I’m useful somewhere. 🙂
Will this work in the free version of Unity? It errors saying that the dll’s are only supported in unity pro
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.
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.
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.
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.
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 🙂
Nope, only the database file.
Nevermind, I’ll send you an email with the working builds but it’s a MAC version…
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
Thank you so much for this. I will repost your comment in new post. 🙂
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 🙁
License for data? ❓
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 !
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 😉
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.
(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!
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.
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
How do I send it?
To your yahoo.fr email?
Yes please, if it is possible?
No problem. 🙂
I’ve send the Unity package.
Tell me if you didn’t get it.
thank you for all!!
i have this error The type `SqliteConnection’ does not contain a constructor that takes `1′ arguments
Maybe you should change your variable to “float” or “int”.
sorry my bad, i wrote your code in SqliteConnection class, it has same name. i have question again, how to setup build path of sqlite database?
I don’t fully understand your question. ❓
I like it when folks get together and share ideas. Great site, stick with
it!
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 ?
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?
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. 🙁
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…
It’s been reported by many.
And even on Pro version of Unity it doesn’t work.
Nothing I can do from here. 🙁
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
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.
Friend, I have the same problem, as could resolve this problem, what’s plugin, I need your help