SQLite Class - Easier Database Stuff

[dklompmaker]

Hello, I thought I would share my basic Sqlite class for those who are interesting in learning how to use unity with it or those who are looking to implement something similar.

This will help create cleaner code for those who need to use databases.
Ultimately I want to create an editor tool for database so that we can have another tool on top of Unity's awesome-ness.

"Update - 8/14 I added a Create Table Function"
"Update - 8/14 I added a Insert Functions"

The Class

import          System.Data;  // we import our  data class
import          Mono.Data.SqliteClient; // we import our sqlite client
 
class dbAccess {
    // variables for basic query access
    private var connection : String;
    private var dbcon : IDbConnection;
    private var dbcmd : IDbCommand;
    private var reader : IDataReader;
    
    function OpenDB(p : String){
    connection = "URI=file:" + p; // we set the connection to our database
    dbcon = new SqliteConnection(connection);
    dbcon.Open();
    }
    
    function BasicQuery(q : String, r : boolean){ // run a baic Sqlite query
        dbcmd = dbcon.CreateCommand(); // create empty command
        dbcmd.CommandText = q; // fill the command
        reader = dbcmd.ExecuteReader(); // execute command which returns a reader
        if(r){ // if we want to return the reader
        return reader; // return the reader
        }
    }
    
    function CreateTable(name : String, col : Array, colType : Array){ // Create a table, name, column array, column type array
        var query : String;
        query  = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0];
        for(var i=1; i<col.length; i++){
            query += ", " + col[i] + " " + colType[i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand(); // create empty command
        dbcmd.CommandText = query; // fill the command
        reader = dbcmd.ExecuteReader(); // execute command which returns a reader
    
    }
    
    function InsertIntoSingle(tableName : String, colName : String, value : String){ // single insert 
        var query : String;
        query = "INSERT INTO " + tableName + "(" + colName + ") " + "VALUES (" + value + ")";
        dbcmd = dbcon.CreateCommand(); // create empty command
        dbcmd.CommandText = query; // fill the command
        reader = dbcmd.ExecuteReader(); // execute command which returns a reader
    }
    
    function InsertIntoSpecific(tableName : String, col : Array, values : Array){ // Specific insert with col and values
        var query : String;
        query = "INSERT INTO " + tableName + "(" + col[0];
        for(var i=1; i<col.length; i++){
            query += ", " + col[i];
        }
        query += ") VALUES (" + values[0];
        for(i=1; i<values.length; i++){
            query += ", " + values[i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader();
    }
    
    function InsertInto(tableName : String, values : Array){ // basic Insert with just values
        var query : String;
        query = "INSERT INTO " + tableName + " VALUES (" + values[0];
        for(var i=1; i<values.length; i++){
            query += ", " + values[i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader(); 
    }
    
    function SingleSelectWhere(tableName : String, itemToSelect : String, wCol : String, wPar : String, wValue : String){ // Selects a single Item
        var query : String;
        query = "SELECT " + itemToSelect + " FROM " + tableName + " WHERE " + wCol + wPar + wValue; 
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader();
        var readArray = new Array();
        while(reader.Read()){ 
            readArray.Push(reader.GetString(0)); // Fill array with all matches
        }
        return readArray; // return matches
    }
 
    
    function CloseDB(){
        reader.Close(); // clean everything up
        reader = null; 
        dbcmd.Dispose(); 
        dbcmd = null; 
        dbcon.Close(); 
        dbcon = null; 
    }
    
}

An Example of Creating a Table
You can create two arrays, one being the column headers, and another being the data types.

var db : dbAccess;
function Start(){
    db = new dbAccess();
    db.OpenDB("myDB.sqdb");
    var tableName = "myTable";
    var columnNames = new Array("firstName","lastName");
    var columnValues = new Array("text","text");
    db.CreateTable(tableName,columnNames,columnValues);
    db.CloseDB();
}

An Example of Easy Insert
This example is an easy way to add a single record

var db : dbAccess;
function Start(){
    db = new dbAccess();
    db.OpenDB("myDB.sqdb");
    var tableName = "myTable";
    // IMPORTANT remember to add single ' to any strings, do not add them to numbers!
    var values = new Array("'Bob'","'Sagat'");
    db.InsertInto(tableName, values);
    db.CloseDB();
}

An Example of Single WHERE select
I am not done with this class, but this is an example of getting an array of items that match a WHERE clause.

var db : dbAccess;
function Start(){
    db = new dbAccess();
    db.OpenDB("myDB.sqdb");
    var tableName = "myTable";
    // table name, I want to return everyone whose first name is Bob when their last name is = to Sagat, this returs an array
    var resultArray = db.SingleSelectWhere(tableName, "firstName", "lastName","=","'Sagat'"); // Remember the '' on String values
    print(resultArray[0]);
    // of course you can loop through them all if you wish
    db.CloseDB();
    
}

[manolo2111]

Hi.

I'm new on the forum, and this is my first question. Sorry about my english, but I'm not English :P

Well, my question is simple. Can this code connect to all Databases? Can I connect with an Oracle, MSSQL and MySql?

How can I do this with each one?

Thx a lot.

[0turner0]

To answer the above question, no - SQLite creates a file on the hard drive that ACTS like a database.

To praise the further above - Thank you so much for providing this, you saved me hours :P

[DynamicHead]

Hi,

Does this also work with a webplayer build ?
At least to read data..

[pavees]

Can we use this to get the data retrieved from the database and send data to the database. Can anyone suggest a way to get working the basic connection to send a info or data to the database. I am using .Net server and my database is SQL server 2005.

[DynamicHead]

Do you develop for webplayer or standalone ?


Btw. looks like sqlite doesn't work with the webplayer.. since you can only access URIs.

[iphonedev]

Trying to get this class to work. I am having basic error not sure how to solve.

Assets/dbAccess.js(1,17): BCE0021: Namespace 'System.Data' not found, maybe you forgot to add an assembly reference?

What am I missing to get the imports to work?

Thanks in advance.

[dreamora]

he imports system.data in the very first script and should work on unity 2.6
on unity iphone it won't work I think as this relies on stuff thats not present on the iphone even with .net 2.1 enabled, there you would use plugins and use the always present sqlite3 library on the iphone.


as for SQLite + Webplayer: not possible as you can't access files and filestreams at all there and if it is within the project its not accessable.

also you would never access a regular webhosted database (oracle, mssql, mysql) this way as you do not want to make wildcard remote accounts for users to directly connect and hack in no time due to the login details being sent over to by them and stored in the client.
You would instead use client <-> php / asp <-> database

[iphonedev]

Yes I'm trying to use with iPhone.

The use of plugins is only supported by iPhone Advanced. So there is no way to implement sqlite with iPhone Basic?

Thanks in advance.

[dreamora]

See Poor Monkey framework on how to integrate such a thing with iPhone Basic.
As database lookups aren't high frequency that will work fine.

the real work is the xcode side implementation for sqlite

[fallingbrickwork]

I'm having some crashing issue when app running as a Windows standalone (Windows Vista x64).

The Database is working fine within the Editor but when built to a Standalone it is crashing sometimes?! 5 times out of 10 it works fine, but the other half it is crashing when it starts up OR when you quit the app?!

I only have Database calls within Start() methods so maybe it's crashing when SELECTing data and the Database isn't properly connected (i'm only guessing here).

Anyone else had these issues or can point me in the right direction to correcting them. I'm using the latest sqlite dll from the SQLite website and the frameworks from the Unity Installation.

Kind Regards,
Matt.

[fallingbrickwork]

Sorry to bump this one.

I just don't want to design this app too much around a database if it is going to be technically fraught with problems.

I am using the methods from the top of the thread albeit in C# with a class name change and am just calling this:

void Start() {
   db = new dbController();
   db.OpenDB("CasusBelliDB.sqdb");
 
   List<string> result = new List<string>();
   result = db.SingleSelectWhere("test","name","name","=", "'Matt'"); // Process SQL
 
   guiText.text = result[0];
 
   db.CloseDB(); // Close DB connection
}

Like i say, when the app doesn't crash everything is working fine. But sometimes it is crashing on app start and sometimes on app closing. Why would the app crash on closing?? Would the problem be the dll conflicting somewhere? It is working 100% in the editor (but then again, the app isnt having to startup or close down there is it)

Anyway, if anyone has any ideas or has tried SQLite could they let me know. I'll change the app to not use a DB if I can't get this sorted (which will be a shame).

EDIT: I have made a MAC Intel build and it seems to run 100%. Have started and stopped app about 30 times with no crashes. Windows Vista x64 maybe?? I'll try it on my wife laptop (vista 32 bit).

EDIT2: Seems to work fine on Vista x86 also... looks like it's Vista x64!... oh dear... maybe i'm being a numpty and have the incorrect dll's... i may have an embarrased face yet!

Best Regards,
Matt.

[bigteef]

fallingbrickwork, were you able to resolve this? i'm seeing similar behavior in windows 7.

[BY0LOG1C]

Many thanks to dklompmaker.

You just made my day MUCH easier. Haven't had the chance to put it into action yet but understanding what you do is of great help!

Just wanted to show some appreciation.

[hellokitty]

Hi , i have some question，when i "Build & Run" the project
ArgumentException: The Assembly System.Configuration is referenced by System. But the dll is not allowed to be included or could not be found.
UnityEditor.AssemblyHelper.AddReferencedAssemblies Recurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache) (at C:/BuildAgent/work/6bc5f79e0a4296d6/Editor/Mono/AssemblyHelper.cs:52)
UnityEditor.AssemblyHelper.AddReferencedAssemblies Recurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache) (at C:/BuildAgent/work/6bc5f79e0a4296d6/Editor/Mono/AssemblyHelper.cs:55)
UnityEditor.AssemblyHelper.AddReferencedAssemblies Recurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache) (at C:/BuildAgent/work/6bc5f79e0a4296d6/Editor/Mono/AssemblyHelper.cs:55)
UnityEditor.AssemblyHelper.AddReferencedAssemblies Recurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache) (at C:/BuildAgent/work/6bc5f79e0a4296d6/Editor/Mono/AssemblyHelper.cs:55)
UnityEditor.AssemblyHelper.FindAssembliesReference dBy (System.String[] paths, System.String[] foldersToSearch) (at C:/BuildAgent/work/6bc5f79e0a4296d6/Editor/Mono/AssemblyHelper.cs:86)
UnityEditor.BuildPlayerWindow:BuildPlayerAndRun()
Error building Player: Extracting referenced dlls failed.
UnityEditor.BuildPlayerWindow:BuildPlayerAndRun()
Exception: Error building Player: Extracting referenced dlls failed.
UnityEditor.BuildPlayerWindow.BuildPlayerWithDefau ltSettings (Boolean askForBuildLocation, BuildOptions forceOptions) (at C:/BuildAgent/work/6bc5f79e0a4296d6/Editor/Mono/BuildPlayerWindow.cs:341)
UnityEditor.BuildPlayerWindow.BuildPlayerAndRun () (at C:/BuildAgent/work/6bc5f79e0a4296d6/Editor/Mono/BuildPlayerWindow.cs:263)

[AlanChatham]

Hi, if you're trying to get this to work, I've posted a sample project and instructions on the Unify wiki that should get you up and running -
http://www.unifycommunity.com/wiki/i...p?title=SQLite

[rganeyev]

BTW, it looks like working in C#(at least in Pro version which I am using). I've implemented functional from http://www.unifycommunity.com/wiki/i...p?title=SQLite in C#. May need System.Data.dll and Mono.Data.Sqlite.dll (can be found in mono develop libs directory). Here's the code.

using UnityEngine;
using System;
using Mono.Data.Sqlite;
 
 
public class DbAccess
{
    private SqliteConnection dbConnection;
    private SqliteCommand dbCommand;
    private SqliteDataReader reader;
 
 
    /// <summary>
    ///  string to connect. The simpliest one looks like "URI=file:filename.db"
    /// </summary>
    /// <param name="connectionString">
    /// A <see cref="System.String"/>
    /// </param>
    public DbAccess (string connectionString)
    {
        OpenDB (connectionString);
    }
 
    /// <summary>
    ///  The same as <see cref="DbAccess#Dbaccess" / >
    /// </summary>
    /// <param name="connectionString">
    /// A <see cref="System.String"/>
    /// </param>
    public void OpenDB (string connectionString)
    {
        dbConnection = new SqliteConnection (connectionString);
        dbConnection.Open ();
        Debug.Log ("Connected to db");
    }
 
    /// <summary>
    /// Closes connection to db
    /// </summary>
    public void CloseSqlConnection ()
    {
        if (dbCommand != null) {
            dbCommand.Dispose ();
        }
        dbCommand = null;
        if (reader != null) {
            reader.Dispose ();
        }
        reader = null;
        if (dbConnection != null) {
            dbConnection.Close ();
        }
        dbConnection = null;
        Debug.Log ("Disconnected from db.");
    }
 
 
    /// <summary>
    ///  Executes query given by sqlQuery
    /// </summary>
    /// <param name="sqlQuery">
    /// query
    /// A <see cref="System.String"/>
    /// </param>
    /// <returns>
    /// null, if any error
    /// result of query, otherwise
    /// A <see cref="SqliteDataReader"/>
    /// </returns>
    public SqliteDataReader ExecuteQuery (string sqlQuery)
    {
        dbCommand = dbConnection.CreateCommand ();
        dbCommand.CommandText = sqlQuery;
        
        reader = dbCommand.ExecuteReader ();
        
        
        return reader;
    }
 
    /// <summary>
    ///  Selects everything from table
    /// </summary>
    /// <param name="tableName">
    /// name of table 
    /// A <see cref="System.String"/>
    /// </param>
    /// <returns>
    /// result of query
    /// A <see cref="SqliteDataReader"/>
    /// </returns>
    public SqliteDataReader ReadFullTable (string tableName)
    {
        string query = "SELECT * FROM " + tableName;
        return ExecuteQuery (query);
    }
 
    /// <summary>
    /// Inserts data into table
    /// </summary>
    /// <param name="tableName">
    /// name of table to insert data
    /// A <see cref="System.String"/>
    /// </param>
    /// <param name="values">
    /// array of data in string representation
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <returns>
    /// result of query
    /// A <see cref="SqliteDataReader"/>
    /// </returns>
    public SqliteDataReader InsertInto (string tableName, string[] values)
    {
        string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
        for (int i = 1; i < values.Length; ++i) {
            query += ", " + values[i];
        }
        query += ")";
        return ExecuteQuery (query);
    }
 
    /// <summary>
    /// Inserts data into specific columns of table
    /// </summary>
    /// <param name="tableName">
    /// name of table
    /// A <see cref="System.String"/>
    /// </param>
    /// <param name="cols">
    /// name of columns
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <param name="values">
    /// values
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <returns>
    /// result of query
    /// A <see cref="SqliteDataReader"/>
    /// </returns>
    public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values)
    {
        if (cols.Length != values.Length) {
            throw new SqliteException ("columns.Length != values.Length");
        }
        string query = "INSERT INTO " + tableName + "(" + cols[0];
        for (int i = 1; i < cols.Length; ++i) {
            query += ", " + cols[i];
        }
        query += ") VALUES (" + values[0];
        for (int i = 1; i < values.Length; ++i) {
            query += ", " + values[i];
        }
        query += ")";
        return ExecuteQuery (query);
    }
 
    /// <summary>
    /// deletes any data from table
    /// </summary>
    /// <param name="tableName">
    /// table name
    /// A <see cref="System.String"/>
    /// </param>
    /// <returns>
    /// result of query
    /// A <see cref="SqliteDataReader"/>
    /// </returns>
    public SqliteDataReader DeleteContents (string tableName)
    {
        string query = "DELETE FROM " + tableName;
        return ExecuteQuery (query);
    }
 
    /// <summary>
    /// Creates table with specified columns
    /// </summary>
    /// <param name="name">
    /// table name to be created
    /// A <see cref="System.String"/>
    /// </param>
    /// <param name="col">
    /// array, containing names of columns
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <param name="colType">
    /// array, containing types of columns
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <returns>
    /// result of query
    /// A <see cref="SqliteDataReader"/>
    /// </returns>
    public SqliteDataReader CreateTable (string name, string[] col, string[] colType)
    {
        if (col.Length != colType.Length) {
            throw new SqliteException ("columns.Length != colType.Length");
        }
        string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
        for (int i = 1; i < col.Length; ++i) {
            query += ", " + col[i] + " " + colType[i];
        }
        query += ")";
        return ExecuteQuery (query);
    }
 
    /// <summary>
    /// Selects from table with specified parameters.
    /// Ex: SelectWhere("puppies", new string[] = {"breed"}, new string[] = {"earType"}, new string[] = {"="}, new string[] = {"floppy"});
    /// the same as command: SELECT breed FROM puppies WHERE earType = floppy
    /// </summary>
    /// <param name="tableName">
    /// name of table to select
    /// A <see cref="System.String"/>
    /// </param>
    /// <param name="items">
    /// item names 
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <param name="col">
    /// array, containing columns of parameters
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <param name="operation">
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <param name="values">
    /// A <see cref="System.String[]"/>
    /// </param>
    /// <returns>
    /// result of query
    /// A <see cref="SqliteDataReader"/>
    /// </returns>
    public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)
    {
        if (col.Length != operation.Length || operation.Length != values.Length) {
            throw new SqliteException ("col.Length != operation.Length != values.Length");
        }
        string query = "SELECT " + items[0];
        for (int i = 1; i < items.Length; ++i) {
            query += ", " + items[i];
        }
        query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
        for (int i = 1; i < col.Length; ++i) {
            query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
        }
        
        return ExecuteQuery (query);
        
    }
    
    
    
}

If you find any bugs please report to this thread.

[deerendee]

how to do the UPDATE? is it similar with INSERT syntaxs? thanks!

[thuron]

Sorry for bumping this old topic, but whenever I try to use the SingleSelectWhere it tells me the syntax is wrong. When I enter a plain string query without a WHERE clause it works, same with WHERE field=integer but as soon as i try WHERE field=STRING / 'STRING' it does not work... does anyone know how to get this working?

Thanks in advance.

[bioduds]

If all this stuff is making you feel uneasy, take a close look at this asset: http://u3d.as/content/mono-sapiens-ltda/mono-sqlite/2Qw

Its goal is to unify SQLite solution for Unity3D