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): private IDbConnection dbcon; ... public void OpenDB(string p) { connection = "URI=file:" + Application.persistentDataPath + "/" + p; // we set the connection to our database //connection = "URI=file:" + p; // like this will NOT work on android Debug.Log(connection); dbcon = new SqliteConnection(connection); dbcon.Open(); } 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): // check if file exists in Application.persistentDataPath string filepath = Application.persistentDataPath + "/" + p; if(!File.Exists(filepath)) { // if it doesn't -> // open StreamingAssets directory and load the db -> WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/" + p); // this is the path to your StreamingAssets in android while(!loadDB.isDone) {} // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check // then save to Application.persistentDataPath File.WriteAllBytes(filepath, loadDB.bytes); } //open db connection connection = "URI=file:" + filepath; dbcon = new SqliteConnection(connection); 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
If you like I could sent an example code to you. You will find how easy work with database by http://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.
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?
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): string[,] result2 = SingleSelectWhere("petdef", "value", "word", "=", "'primary'"); // Concatenate all the elements into a StringBuilder. builderdisplay = new StringBuilder(); for (int i = 0; i < result2.GetLength(0); i++) { for (int j = 0; j < result2.GetLength(1); j++) { builderdisplay[2].Append(result2[i,j]); builderdisplay[2].Append(' '); } subjectnamed.text = builderdisplay.ToString (); }
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
Hi fruki. Could you please compile libsqlite.so for Intel cpu also? We have found that current version cannot work with tablets that have Intel processor (System.DllNotFoundException: libSQLite3.so exception raises). Galaxy Tab 3 for example. Or maybe you can share source code?))
Hi everybody i have one problem, my database simply wont create on my Samsung Galaxy S3 but it creates on PC. So it would be nice if someone can help me a bit... this is my code: Code (CSharp): void Start() { connectionString = "URI=file:" + Application.persistentDataPath + "/" + "gameDB"; OpenDatabase(connectionString); } Code (CSharp): void OpenDatabase(string dbFilePath) { try { dbConnection = new SqliteConnection(dbFilePath); if(dbConnection != null) { dbConnection.Open(); using (dbCommand = new SqliteCommand(dbConnection)) { dbCommand.CommandText = "CREATE TABLE IF NOT EXISTS PlayedGames (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, Score INTEGER NOT NULL DEFAULT 0)"; dbCommand.Prepare(); dbCommand.ExecuteNonQuery(); } } } catch (SqliteException ex) { WriteToFile(ex.StackTrace); } catch (System.Exception ex) { WriteToFile(ex.Message); } finally { if(reader != null) { reader.Close(); reader = null; } if(dbCommand != null) { dbCommand.Dispose(); dbCommand = null; } if(dbConnection != null) { dbConnection.Close(); dbConnection = null; } } } After this code runs no database is created on Android and in catch block i get "sqlite3" message. What could that mean? Is it possible maybe because i put everything in folder /Plugins/ and not in /Assets/Plugins/ ?
the Plugin folder must indeed be placed into Assets folder Anyhow, since this is an old thread and targetted for just Android, I suggest you to take also a peek at this all-purpose solution
hello everyone i used the script of dbAcces proposed by fruki and this one. it works normally but the problem is that after building i din't work even when i click to the buttons nothing happen can someone help please Code (CSharp): using UnityEngine; using System; using System.Collections; using System.Collections.Generic; using System.Data; using Mono.Data.SqliteClient; public class usethedatabase : MonoBehaviour { public string DatabaseName = "DBprodt.sqdb"; // This is the name of the table we want to use public string TableName = "produittable"; private dbAccess db ; void Start () { // Give ourselves a dbAccess object to work with, and open it db = new dbAccess (); db.OpenDB (DatabaseName); // Let's make sure we've got a table to work with as well! string tableName = TableName; string[] columnNames = new string[]{"firstName", "lastName"}; string[] columnValues = new string[]{"text", "text"}; //try //{ db.CreateTable (tableName, columnNames, columnValues); //} } // These variables just hold info to display in our GUI private string firstName = "First Name"; private string lastName = "Last Name"; private int DatabaseEntryStringWidth = 100; private Vector2 scrollPosition ; private List<List<object>> databaseData = new List<List<object>> (); // This GUI provides us with a way to enter data into our database // as well as a way to view it void OnGUI () { GUI.Box (new Rect (25, 25, Screen.width - 50, Screen.height - 50), ""); GUILayout.BeginArea (new Rect (50, 50, Screen.width - 100, Screen.height - 100)); // This first block allows us to enter new entries into our table GUILayout.BeginHorizontal (); firstName = GUILayout.TextField (firstName, GUILayout.Width (DatabaseEntryStringWidth)); lastName = GUILayout.TextField (lastName, GUILayout.Width (DatabaseEntryStringWidth)); GUILayout.EndHorizontal (); if (GUILayout.Button ("Add to database")) { // Insert the data InsertRow (firstName, lastName); // And update the readout of the database databaseData = ReadFullTable (); } // This second block gives us a button that will display/refresh the contents of our database GUILayout.BeginHorizontal (); if (GUILayout.Button ("Read Database")) { databaseData = ReadFullTable (); } if (GUILayout.Button ("Clear")) { databaseData.Clear (); } GUILayout.EndHorizontal (); GUILayout.Label ("Database Contents"); scrollPosition = GUILayout.BeginScrollView (scrollPosition, GUILayout.Height (100)); foreach (List<object> line in databaseData) { GUILayout.BeginHorizontal (); foreach (object s in line) { GUILayout.Label (s.ToString (), GUILayout.Width (DatabaseEntryStringWidth)); } GUILayout.EndHorizontal (); } GUILayout.EndScrollView (); if (GUILayout.Button ("Delete All Data")) { DeleteTableContents (); databaseData = ReadFullTable (); } GUILayout.EndArea (); } // Wrapper function for inserting our specific entries into our specific database and table for this file private void InsertRow (string firstName, string lastName) { string[] values = new string[]{("'" + firstName + "'"), ("'" + lastName + "'")}; db.InsertInto (TableName, values); } // Wrapper function, so we only mess with our table. private List<List<object>> ReadFullTable () { return db.ReadFullTable (TableName); } // Another wrapper function... private void DeleteTableContents () { db.DeleteTableContents (TableName); } }
Hey there Am a newbie developing unity 3d app for android platform and even for windows, am using unity v4.5 pro and I have chosen SQLite with SQLite browser to create my "local" database in unity 3D (in Csharp), just because SQLite is serverless (most important point),free,and does'nt require any configuration (just a matter of DLLs integration). Well, a part of my educational project is to find a way how to modify in my database and adding new values for example after building it ( when it is runnig maybe )..I still don't know how to do that but am keeping learning for the while and i 'll get my point if somebody helps me guys . I have two questions 1- Is there any other suggestions for choosing other way to create my local database which must run later on android (serverless,free,matches with android platform)!? Or maybe any other addition that may works like SQLiteHelper For SQLite but i can iintegrate it with unity ofcourse..? I dont know ..any help please 2- I found the example bellow of code project and I tried to build it on both windows and android..Well it works fine on unity editor but for build connexion is lost ..i Tried but i coudn't resolve that problem Please any advices or help would be appreciated.(Here is the code) (5 classes) "Class CustomerEntity" Code (CSharp): using UnityEngine; using System.Collections; using System; /// <summary> /// Entity class of Customer /// Mapped manually (!!!) from SampleDB /// </summary> public class CustomerEntity { #region Fields private int iD; private string userName; #endregion #region Properties /// <summary> /// Gets or sets the identifier. /// </summary> /// <value> /// The identifier. /// </value> public int ID { get { return iD; } set { iD = value; } } /// <summary> /// Gets or sets the name of the user. /// </summary> /// <value> /// The name of the user. /// </value> public string UserName { get { return userName; } set { userName = value; } } /// <summary> /// Prevents a default instance of the <see cref="CustomerEntity"/> class from being created. /// </summary> private CustomerEntity() { } /// <summary> /// Initializes a new instance of the <see cref="CustomerEntity"/> class. /// </summary> /// <param name="iD">The identifier</param> /// <param name="userName">Name of the user.</param> public CustomerEntity(int iD, string userName) { if (string.IsNullOrEmpty(userName)) throw new ArgumentNullException("userName cannot be null or empty !"); this.iD = iD; this.userName = userName; } #endregion } "Class CustomerEntityHelper" Code (CSharp): using UnityEngine; using System.Collections; using System.Collections.Generic; using System.Data; using Mono.Data.Sqlite; using System; /// <summary> /// CustomerEntityHelper Help you to retrieve the named Entity /// </summary> public class CustomerEntityHelper : DBHelper { public List<CustomerEntity> GetAllCustomers() { List<CustomerEntity> result = new List<CustomerEntity>(); try { using (IDbConnection dbcon = new SqliteConnection(base.ConnString) as IDbConnection) { dbcon.Open(); IDbCommand dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = "SELECT ID, UserName FROM Customer"; ; using (IDataReader reader = dbcmd.ExecuteReader()) { while (reader.Read()) { int id = reader.GetInt32(0); string userName = reader.GetString(1); CustomerEntity customer = new CustomerEntity(id, userName); result.Add(customer); } reader.Close(); } dbcon.Close(); } } catch (Exception ex) { Debug.LogError("GetAllCustomers thrown an error : " + ex.ToString()); } return result; } } "Class ConnectionResult " Code (CSharp): using UnityEngine; using System.Collections; /// <summary> /// ConnectionResult /// </summary> public class ConnectionResult { /// <summary> /// ConnectionStatus /// </summary> public enum ConnectionStatus { Connected, NotConnected } private string connectionStringUsed; private string errorMessage; private ConnectionStatus status; public ConnectionStatus Status { get { return status; } set { status = value; } } public string ConnectionStringUsed { get { return connectionStringUsed; } set { connectionStringUsed = value; } } public string ErrorMessage { get { return errorMessage; } set { errorMessage = value; } } } "Class DBHelper" Code (CSharp): using UnityEngine; using System.Collections; using System; using System.Data; using Mono.Data.Sqlite; using System.IO; /// <summary> /// DBHelper /// </summary> public class DBHelper { #region Fields private string connString; private string databaseCompletePath;//the path in your Unity Project and the DB Name #endregion #region Properties /// <summary> /// Gets the connection string. /// </summary> /// <value> /// The connection string. /// </value> public string ConnString { get { return connString; } } #endregion #region Constructors /// <summary> /// Initializes a new instance of the <see cref="DBHelper"/> class. /// </summary>s public DBHelper() { this.databaseCompletePath = "/SampleDB"; this.connString = string.Format("URI=file:{0}{1}", Application.dataPath,this. databaseCompletePath); } /// <summary> /// Initializes a new instance of the <see cref="DBHelper"/> class. /// </summary> /// <param name="specificConnectionString">The specific connection string.</param> /// <example>connection string could be: YourFloder1/Yourfolder2/YourDBName</example> public DBHelper(string specificConnectionString) { this.databaseCompletePath = specificConnectionString; this.connString = string.Format("URI=file:{0}/{1}",Application.dataPath,specificConnectionString); } #endregion #region Methods /// <summary> /// Checks the connection. /// </summary> /// <returns></returns> public ConnectionResult CheckConnection() { ConnectionResult result = new ConnectionResult(); result.ConnectionStringUsed = this.connString; try { using (IDbConnection dbcon = new SqliteConnection(this.connString) as IDbConnection) { //check if db file exist (with a bad name, sqlite try to create the DB and we don't want this behavior!) if (File.Exists(string.Format("{0}{1}", Application.dataPath,this.databaseCompletePath))) { dbcon.Open(); //Open connection to the database. result.Status = ConnectionResult.ConnectionStatus.Connected; dbcon.Close(); } else throw new Exception("Database was not Found !"); } } catch (Exception ex) { result.ErrorMessage = ex.ToString(); result.Status = ConnectionResult.ConnectionStatus.NotConnected; } return result; } #endregion } "Class SampleDB " Code (CSharp): using UnityEngine; using System.Collections; using System.Text; using System; public class SampleDB : MonoBehaviour { private DBHelper dbHelper; private bool isDBConnected; private string checkResultMessage; private string customerMessage; /// <summary> /// Starts this instance. /// </summary> void Start() { this.checkResultMessage = string.Empty; this.dbHelper = new DBHelper();//use the embedded DBHelper connection string. this.isDBConnected = false; // before check, we consider the DB status as not connected. } void OnGUI() { int heightLocation = 100; //used for GUI StringBuilder sbCustomer = new StringBuilder();// used to add string if (GUI.Button(new Rect(10, 10, 200, 30), "Check Connection")) { checkResultMessage = string.Empty; ConnectionResult result = this.dbHelper.CheckConnection(); this.isDBConnected = (result.Status == ConnectionResult.ConnectionStatus.Connected); checkResultMessage = (result.Status == ConnectionResult.ConnectionStatus.Connected) ? string.Format("Connection OK with connection string {0}", result.ConnectionStringUsed) : string.Format("Connection ERROR: {0}",result.ErrorMessage) ; } GUI.Label(new Rect(10, 50, 600, 60), checkResultMessage); if (this.isDBConnected) { if (GUI.Button(new Rect(220, 10, 200, 30), "Get customers")) { CustomerEntityHelper customerHelper = new CustomerEntityHelper(); foreach (CustomerEntity customer in customerHelper.GetAllCustomers()) { sbCustomer.Append(string.Format("{0} - ID: {1}{2}", customer.UserName, customer.ID,Environment.NewLine)); heightLocation += 40; } this.customerMessage = sbCustomer.ToString(); } } GUI.Label(new Rect(10, 50, 600, 60), checkResultMessage); GUI.Label(new Rect(10, 10 + heightLocation, 300, 600), this.customerMessage); } } BTW: I am new in the Forum and That's my first post,sorry for my bad english .Thank you even for reading this whole stuff
Hi, There is a plugin which is very easy to use without much knowledge about SQL, you should look at this plugin http://u3d.as/htX
#Fruki.So basically the database is P. this works great for Pc but fails on Android any solution. Is www. the only solution to the loading assets at runtime.
I ran into several problems , i kind off merged two solutions together and got it to work some how. My main problem was when i was trying to load an existing database form streaming assets on an android device with a split build (sounds daunting lol). What i realized was the file paths were screwed up and the database being copied was an empty database with no tables or information. I changed the OpenDB function in the dbAccess.cs class as per the following code. I have tested the following code on an android device with the split build and it all works fine. Hope this is useful This is the link to the project and this is the link for the class from where i copied the path logic. Code (CSharp): public void OpenDB(string DatabaseName) { #if UNITY_EDITOR var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName); #else // check if file exists in Application.persistentDataPath var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName); if (!File.Exists(filepath)) { Debug.Log("Database not in Persistent path"); // if it doesn't -> // open StreamingAssets directory and load the db -> #if UNITY_ANDROID var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName); // this is the path to your StreamingAssets in android while (!loadDb.isDone) { } // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check // then save to Application.persistentDataPath File.WriteAllBytes(filepath, loadDb.bytes); #elif UNITY_IOS var loadDb = Application.dataPath + "/Raw/" + DatabaseName; // this is the path to your StreamingAssets in iOS // then save to Application.persistentDataPath File.Copy(loadDb, filepath); #elif UNITY_WP8 var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS // then save to Application.persistentDataPath File.Copy(loadDb, filepath); #elif UNITY_WINRT var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS // then save to Application.persistentDataPath File.Copy(loadDb, filepath); #endif Debug.Log("Database written"); } var dbPath = filepath; #endif //_connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create); Debug.Log("Final PATH: " + dbPath); //open db connection connection = "URI=file:" + dbPath; Debug.Log("Stablishing connection to: " + connection); dbcon = new SqliteConnection(connection); dbcon.Open(); }
Hi, Can anyone tell me, where the DataBase is Created in Android. Cuz, I problem I have is, When I update the database with some new info, in my PC it works fine, but in Android, even after deleting the app, old DataBase still exists. So somehow I need to write a code for replacing existing DataBase or delete the old one. Can anybody help me with the code or any method?
Hello, I have still the same problem. On Unity I can show all my entries from database in the game view, but if i build an apk and copy it to android (I must change settings from .Net2.0 subset to .Net2.0) and start the application the entries doesn't appears on view. I had copy the database to the StreamingAssets Folder under the Folder Assets. But it doesn't work. I don't know what I can do. Here is the code: Code (CSharp): public void SetDBPath() { string DatabaseName = "HighscoreDB.sqlite"; #if UNITY_EDITOR var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName); #else // check if file exists in Application.persistentDataPath var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName); if (!File.Exists(filepath)) { Debug.Log("Database not in Persistent path"); // if it doesn't -> // open StreamingAssets directory and load the db -> #if UNITY_ANDROID var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName); // this is the path to your StreamingAssets in android while (!loadDb.isDone) { } // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check // then save to Application.persistentDataPath File.WriteAllBytes(filepath, loadDb.bytes); #elif UNITY_IOS var loadDb = Application.dataPath + "/Raw/" + DatabaseName; // this is the path to your StreamingAssets in iOS // then save to Application.persistentDataPath File.Copy(loadDb, filepath); #elif UNITY_WP8 var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS // then save to Application.persistentDataPath File.Copy(loadDb, filepath); #elif UNITY_WINRT var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS // then save to Application.persistentDataPath File.Copy(loadDb, filepath); #endif Debug.Log("Database written"); } var dbPath = filepath; #endif connectionString = "URI=file:" + dbPath; } ... private void GetScores() { using (IDbConnection dbConnection = new SqliteConnection(connectionString)) { dbConnection.Open(); using (IDbCommand dbCmd = dbConnection.CreateCommand()) { string sqlQuery = "SELECT * FROM HighscoresGame1"; dbCmd.CommandText = sqlQuery; using (IDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { highScores.Add(new HighScoreNew(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2))); } dbConnection.Close(); reader.Close(); } } } highScores.Sort(); } ...
Ok, it works. I changed in the Player settings the writePermission under the Android Label to External (SD Card) ;-)
Hi, sorry can you give me the code? I tryed your solution but on Unity I can show all my entries from database in the game view, but when I build an apk and copy it to android it no work. Can you help me? PS. Your "Plugins" folder is composed by "Android" , "x64" , "x86" folder or only "Android" ?
Code (CSharp): using System.Collections; using System.Collections.Generic; using UnityEngine; using System; using System.Data; using Mono.Data.Sqlite; using UnityEngine.UI; using System.IO; public class DBManager : MonoBehaviour { //string filepath = Application.persistentDataPath + "/HighScoreDB.sqlite"; //private string connString; public int topRank; private List<HighScores> highscores = new List<HighScores>(); public Transform scoreParent; public GameObject scorePrefab; public int saveScores; public InputField EnterNames; public GameObject nameDialog; private string connection; public string connectionString; void Start() { SetDBPath(); //connString = "URI=file:" + Application.dataPath + "/HighScoreDB.sqlite"; deleteExtraScores(); //AddScore("Test",350); showScores(); //Debug.Log(connString); } void Update() { if (Input.GetKeyDown(KeyCode.Escape)) { nameDialog.SetActive(!nameDialog.activeSelf); } } public void SetDBPath() { string DatabaseName = "HighScoreDB.sqlite"; #if UNITY_EDITOR var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName); #else // check if file exists in Application.persistentDataPath var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName); if (!File.Exists(filepath)) { Debug.Log("Database not in Persistent path"); // if it doesn't -> // open StreamingAssets directory and load the db -> #if UNITY_ANDROID var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName); // this is the path to your StreamingAssets in android while (!loadDb.isDone) { } // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check // then save to Application.persistentDataPath File.WriteAllBytes(filepath, loadDb.bytes); #endif Debug.Log("Database written"); } var dbPath = filepath; #endif connectionString = "URI=file:" + dbPath; } public void enterName() { if(EnterNames.text != string.Empty) { int score = UnityEngine.Random.Range(1,1000); AddScore(EnterNames.text, score); EnterNames.text = string.Empty; showScores(); } } private void GetScore() { highscores.Clear(); using (IDbConnection dbConnection = new SqliteConnection(connectionString)) { dbConnection.Open(); using (IDbCommand dbCmd = dbConnection.CreateCommand()) { string sqlQuery = "SELECT * FROM Highscores"; dbCmd.CommandText = sqlQuery; using (IDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { highscores.Add(new HighScores(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetDateTime(3))); } dbConnection.Close(); reader.Close(); } } } highscores.Sort(); } private void AddScore(string pName,int pScore) { GetScore(); int hsCount = highscores.Count; if (highscores.Count >0) { HighScores lowestScore = highscores[highscores.Count -1]; if (lowestScore != null && saveScores >0 && highscores.Count >= saveScores && pScore > lowestScore.Scores) { DeleteScore(lowestScore.IDS); hsCount--; } } if (hsCount < saveScores) { using (IDbConnection dbConnection = new SqliteConnection(connectionString)) { dbConnection.Open(); using (IDbCommand dbCmd = dbConnection.CreateCommand()) { string sqlQuery = String.Format("INSERT INTO Highscores(PlayerName,HighScore) VALUES(\"{0}\", \"{1}\"); ", pName, pScore); dbCmd.CommandText = sqlQuery; dbCmd.ExecuteScalar(); dbConnection.Close(); } } } } private void DeleteScore(int pID) { using (IDbConnection dbConnection = new SqliteConnection(connectionString)) { dbConnection.Open(); using (IDbCommand dbCmd = dbConnection.CreateCommand()) { string sqlQuery = String.Format("DELETE FROM Highscores WHERE PlayerID = \"{0} \"", pID); dbCmd.CommandText = sqlQuery; dbCmd.ExecuteScalar(); dbConnection.Close(); } } } private void showScores() { GetScore(); foreach (GameObject score in GameObject.FindGameObjectsWithTag("Score")) { Destroy(score); } for (int i =0; i < topRank; i++) { if( i <= highscores.Count - 1) { GameObject tmpObject = Instantiate(scorePrefab); HighScores tmpScore = highscores[i]; tmpObject.GetComponent<HighScoreScript>().setScore(tmpScore.Names, tmpScore.Scores.ToString(), "#" + (i + 1).ToString()); tmpObject.transform.SetParent(scoreParent); } } } private void deleteExtraScores() { GetScore(); if (saveScores <= highscores.Count) { int deleteCount = highscores.Count - saveScores; highscores.Reverse(); using (IDbConnection dbConnection = new SqliteConnection(connectionString)) { dbConnection.Open(); using (IDbCommand dbCmd = dbConnection.CreateCommand()) { for (int i = 0; i < deleteCount; i++) { string sqlQuery = String.Format("DELETE FROM Highscores WHERE PlayerID = \"{0} \"", highscores[i].IDS); dbCmd.CommandText = sqlQuery; dbCmd.ExecuteScalar(); } dbConnection.Close(); } } } } } I need your help guys.. everything works fine on my PC and even in my Android Unity Remote 5 but when I'm trying to upload the game as an apk, the database does not Load.. There are no errors so I'm getting really confused. Please help. Thank you
Hello, Thank's for this post is helped me so much during several months. I have : - Create Plugins folder and place into it following files : "Mono.Data.Sqlite.dll" "sqlite3.dll" "System.Data.dll" "sqlite3.def" - Create Plugins/Android folder and place into it : "libsqlite3.so" - Modify settings from .Net2.0 subset to .Net2.0 in player preference And that work ! Now, I have upgraded my phone from Android 7.0 to Android 8.0 and that work no more.. I tried : - Write Protection to External SD - Modify Minimum API lvl from LVL 16 to LVL23 - Modify import setting of libsqlite3.so to ARM64 (I have a Samsung Galaxy S7) - Create different folder in Plugins/Android/libs like /x86 or /armeabi-v7a or /arm64-v8a and place into it the libsqlite3.so with the adapted import setting - Compile the source code of sqlite for create my own libsqlite3.so (but I have abandonned) - Find a way to autorize all permission in the AndroidManifest file (but I have abandonned) On the smartPhone of a friend (Samsung Galaxy S6 Android 7.0) all works fine.. Someone could help me please ? The error is the following : System.DllNotFoundException : sqlite3 at (wrapper managed-to-native) Mono.Data.Sqlite.Unsafe.NativeMethods:sqlite3_open_v2 (byte[],inptr&,int,intptr) at Mono.Data.Sqlite.SQLite3.Open (System.String strFilename, SQLiteOpenFlagsEnum Flags, Int32 maxPoolSize, Boolean usePool) [0x00000] in <filename unknown>:0 at Mono.Data.SqliteConnection.Open () [0x00000] in <filename unknown>:0 at BaseDeDonne.Connection () [0x00000] in <filename unknown>:0
Hi, i am new one in unity. i need to connect Sqlite database in Android Device. in unity Editor is Working fine but Android device i am getting error ="Connection string format is invalid" ... here is my code. kindly help anyone Code (CSharp): try { if (Application.platform != RuntimePlatform.Android) { connection = Application.dataPath + "/StreamingAssets/Database.db"; if (!File.Exists(connection)) { File.Create(connection); } connection = "URI=file:" + connection; } else { connection = Application.persistentDataPath + "/absdb.s3db"; if (!File.Exists(connection)) { // if it doesn't -> // open StreamingAssets directory and load the db -> WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/absdb.s3db"); // this is the path to your StreamingAssets in android while (!loadDB.isDone) { } // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check // then save to Application.persistentDataPath File.WriteAllBytes(connection, loadDB.bytes); } } SqliteConnection con = new SqliteConnection(connection); con.Open(); SqliteCommand CreateLifecmd= new SqliteCommand("CREATE TABLE Lifes( id INTEGER PRIMARY KEY AUTOINCREMENT,Lifes INTEGER not null); ",con); CreateLifecmd.ExecuteNonQuery(); SqliteCommand CreateLevelscmd = new SqliteCommand("CREATE TABLE Levels( id INTEGER PRIMARY KEY AUTOINCREMENT,UnlockLevels INTEGER not null); ", con); CreateLevelscmd.ExecuteNonQuery(); SqliteCommand insertLifecmd = new SqliteCommand("INSERT INTO Lifes (Lifes) Values (2)", con); insertLifecmd.ExecuteNonQuery(); SqliteCommand InsertLevelscmd = new SqliteCommand("INSERT INTO Levels (UnlockLevels) Values (1)", con); InsertLevelscmd.ExecuteNonQuery(); con.Open(); } catch(Exception ex) { UiTExt.text = connection + "----" + ex.Message; }
The author of the post - God! Without exaggeration. I suffered with a F***ing SQLite for a week. Only after reading this post everything works fine. Thank you very much!!!
Tutorial SQLite Unity3d 2018 or hegher( Android , Windows Phone , Windows , IOS, WINRT ) How to Connection Database Sqlite how to Create Table , Select , Insert , Update , Delete , Search How to read data from on unity Solving all error in unity for assembly reference: 1- error "The type or namespace name Data' does not exist in the namespace 'Mono'. Are you missing an assembly reference?" 2- and error "The type or namespace name MONO' does not exist in the namespace Data'. Are you missing an assembly reference?" - using Mono.Data.Sqlite; - using System; - using System.Data; - using System.IO; - using UnityEngine.UI; learning how: * SQLite Admin to create database and tables ....... * SQLite DLL to support unity .s3db extension and compile on unity for windows 32bit or 64bit. Github example :https://github.com/walidabazo/SQLiteUnity3d_Android