koobas.hobune.stream
Building Unity game that includes SQLite database ,How to build Unity game that includes SQLite Database.
I am working on a game using Unity 2019.3.1f1 Personal. The game I made includes a SQLite database and, inside the editor, works as intended with no issues. However, after building the game and running the executable the game does not connect to the database. I am sure it is due to the single error I still get inside the editor though I have no resolution for this issue. Attached is the error and my Plugins folder.
You have two problems here: error in Unity Editor and issue in build.
To solve the first:
The following files must be inside the Plugins folder: Modo.Data.dll, Mono.Data.Sqlite.dll, sqlite3.def and sqlite3.dll. You can download here.
To solve the second:
When you build your project, Unity exports the database EMPTY, that is, without tables. Then do the following:
Into your database manager script, add a function that create the tables you need in your database. Add the "IF NOT EXISTS" clause in every CREATE TABLE to prevent future Sql exceptions.
Call the new function from the Start() function after find the database.
Example DatabaseManager.cs script:
string conn;
string sqlQuery;
IDbConnection dbconn;
IDbCommand dbcmd;
string DATABASE_NAME = "/mydatabase.s3db";
void Start()
{
string filepath = Application.dataPath + DATABASE_NAME;
conn = "URI=file:" + filepath;
dbconn = new SqliteConnection(conn);
CreateTable();
}
private void CreateTable()
{
using (dbconn = new SqliteConnection(conn))
{
dbconn.Open();
dbcmd = dbconn.CreateCommand();
sqlQuery = "CREATE TABLE IF NOT EXISTS [table_name] (" +
"[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
"[name] VARCHAR(255) NOT NULL," +
"[age] INTEGER DEFAULT '18' NOT NULL)";
dbcmd.CommandText = sqlQuery;
dbcmd.ExecuteScalar();
dbconn.Close();
}
}
Go to Player Settings / Other Settings and be sure .Net Standard 2.0 is selected in Api Compatibility Lebel field.
Go to Build Settings and select x86_64 in Architecture field (Windows Platform).
Do a new build and check if game run correctly.
Let me know if this post helped you to solve your issues :D
This was a very usefull post. I got a lot of error lines in visual studio 2019 so I changed it a little bit and now it works for me. @Katsamobus How did you aquire those 4 files? Do the same files work for windows, android and IOS?
EDIT 1: Like Katsamobus said, you need " using System.Data;" but I only realised that when I actually wanted to use the "IDatareader" functions to get data from the database.
Here is my code:
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using $$anonymous$$ono.Data.Sqlite;
using System.Data;
public class DatabaseController : $$anonymous$$onoBehaviour
{
private string DatabaseName = "/database/DatabaseLeerlingInfo.db";
SqliteConnection DatabaseConnection;
SqliteCommand DatabaseCommand;
// Start is called before the first frame update
void Start()
{
string filepath = "URI=file:" + Application.dataPath + DatabaseName;
//string conn = "URI=file:" + filepath;
//var dbconn = new SqliteConnection(conn);
DatabaseConnection = new SqliteConnection(filepath);
CreateTable();
}
private void CreateTable()
{
using (DatabaseConnection)
{
DatabaseConnection.Open();
DatabaseCommand = DatabaseConnection.CreateCommand();
string sqlQuery = "CREATE TABLE IF NOT EXISTS[table_Gebruikers] (id INTEGER PRI$$anonymous$$ARY KEY AUTOINCRE$$anonymous$$ENT NOT NULL, voornaam VARCHAR(20) NOT NULL, andereNamen varchar(50), achternaam VARCHAR(20) NOT NULL, wachtwoord VARCHAR(255) NOT NULL);";
string queryCreateAd$$anonymous$$ = "INSERT INTO table_Gebruikers (voornaam, achternaam, wachtwoord) VALUES ('ad$$anonymous$$', 'ad$$anonymous$$Achternaam', 'ad$$anonymous$$');";
DatabaseCommand.CommandText = sqlQuery;
DatabaseCommand.ExecuteScalar();
DatabaseCommand.CommandText = queryCreateAd$$anonymous$$;
DatabaseCommand.ExecuteScalar();
DatabaseConnection.Close();
}
}
}
This new script works for you??
If not, try this changes:
The errors can be appears because you don't use the System.Data library. That was my fault when I posted my script. In this new script you use SqliteConnection and SqliteCommand to work, but I remember that I got errors using this libraries directly to work. So, I import System.Data library on my script to work with connections and commands for database.
Add "using System.Data;" to import the library.
Convert SqliteConnection to System.Data:
IDbConnection DatabaseConnection = new SqliteConnection(filepath);
IDbCommand Databasecommand = DatabaseConnection.CreateCommand();
About the plugins files, I don't remember how I got them. $$anonymous$$aybe I watched a video tutorial in YT and the autor post a link to get them from his github. I don't know, I'm constantly watching tutorials and downloading files from everywhere hahahah.
And about if they works in multiplatform... I sincerely never tried Sqlite for Android and IOs using Unity Engine, but I found a tutorial that may help you --> click here
I hope my post helped you and others readers :)
yes that script worked for me.
I edited my previous post a bit. I later figured out I needed to add "using System.Data;" not for writing to the database but for reading it using "IDatareader".
this all was done with unity 2019.4.16f1 and visual studio 2019
I dont know much about SQLite database or anything but i saw a link from a unity forum about this problem that helped alot of people. Hope this link works for you too.
https://forum.unity.com/threads/cant-load-system-data-dll.523131/
The latest versions of Unity include the System.Data.dll file so this link is obsolete.
I'm building my game and i have little knowledge about sqlite database you can use this link and study about it. Life saver https://medium.com/@rizasif92/sqlite-and-unity-how-to-do-it-right-31991712190
