Deploying Sqlite and Unity3D

Hi Uniterz. I have little problem with Unity that it seems it does not deploy the sqlite into the folder.

So in the code, I pointed the variable into the database’s folder path

var connectionString : String = "URI=file://Assets/Database/gallery.db";

Full code below

import System.Data; 
import Mono.Data.SqliteClient;

var description : String;
var artist: String;
var objectID:int;

function Update () {
}

function GetGalleryDescription (galleryID:int) { 
   var connectionString : String = "URI=file://Assets/Database/gallery.db"; 
   var dbcon : IDbConnection = new SqliteConnection ( connectionString ); 
   dbcon.Open(); 
   var dbcmd : IDbCommand = dbcon.CreateCommand (); 
   var sql : String = "SELECT name,description from galleryMaster where id='"+galleryID+"'"; 
   dbcmd.CommandText = sql; 
   var reader : IDataReader = dbcmd.ExecuteReader(); 
   while( reader.Read() ) { 
		//Set each of the variable to the field extracted from the database
      description = reader.GetString (1); 
	  artist=reader.GetString(0);
   } 
   // clean up 
   reader.Close(); 
   reader = null; 
   dbcmd.Dispose(); 
   dbcmd = null; 
   dbcon.Close(); 
   dbcon = null; 
}

The code above works in the Unity editor except when I deploy it.

But when I deploy the Unity file, it does not include the database file into the Unity’s deploy folder.

So is there anyway where I can deploy the Unity3D and the sqlite database too?

I really appreciate your help guys. God Bless

Assets that are not directly used in the scene or linked in as prefabs are stripped from the built project. You can get the game’s data folder using the Application.dataPath variable (it is different depending on what platform you are using). You should be able to access the sqlite file if you manually place it in this folder after the build.

Thank you for the reply Andeeeee. I’ve been waiting so long for a reply =D

SO first, to be honest i’m doing a Unity project for a company. At the moment I am doing a Unity Desktop version. Then soon or later, the file will be transfered into Unity Iphone. So first perhaps I would like to know how Application.dataPath would be done in the Unity Desktop. Please give me a simple example. The example in Unity does not really help me.

:smile: Thanks and God Bless

I have done that but it still does not work.
After the build I created a folder named “Database” and put all those important files into that folder.
I did not see any result came out :frowning:

Thanks for the advice. I look forward to your reply Andeee

ih guys,

i’m new to unity, am trying to connect a sqlite database into my unity, but when i was connecting am getting errors like

Assets/userScript.js(20,28): BCE0018: The name ‘IDbConnection’ does not denote a valid type (‘not found’). Did you mean 'System.Runtime.InteropServices.ComTypes.ITypeComp '?

can you explain it??? wats it???

See the following thread:
http://forum.unity3d.com/threads/62150-Saving-and-Loading-XML-collections-example

Near the last post, I have a package that includes a SQLITECLASS that does the work, or example of how to do it and what files are needed are in the package. Warning though, all of my experience to date with using sqlite and U3 is that writing is painfully slow but reading is fast. You seriously might want to consider XML or something else, but anyway, it is in the package, have fun.

What a headache.

Life would be so easy with persistent database functions inside Unity.

I opened a feedback on this : http://feedback.unity3d.com/forums/15792-unity/suggestions/1251047-sqlite-embed-in-unity?ref=title

If you agree, vote it

hi guys can you help me…i using sqlite i can play it…but i cant build it…what do you think is the problem
im currently working on windows 32 bit using unity 4.3.4 sqlite3…

using UnityEngine;
using System.Collections;
using System.IO;
using System.Data;
using Mono.Data.SqliteClient;


public class play : MonoBehaviour {
    public GUISkin style;
    public Texture[] icon;
    public string answer;
    public string x = "";
    public ArrayList Let = new ArrayList ();
    public ArrayList letters = new ArrayList ();
    public string l = "QWERTYUIOPASDFGHJKLZXCVBNM";
    public string c = "";
    public string[] files;
    public ArrayList exampleFiles;
    public string whatToGuess;
    public ArrayList isAvailable;
    public ArrayList toBeRemoved;
    //file
    public string word;
    public string tagalog;
    public string english;
    public string complete;
    public bool hasRun = false;
    public bool[] completed = new bool[6];
    public bool fin;
    public bool nahulaan=false;
    public int cc;
    public int lvl;
    public bool ok;
    public int maxlvl;
    void OnGUI () {
        if (ok == true)
        {
           
            GUI.skin = style;
            try
            {
                //buttons image
                GUI.Box (new Rect (370, 330, 100, 30), "");
                if(GUI.Button(new Rect(540,288,145,40), "NEXT WORD")) {
                    Application.LoadLevel(0);
                }
                if (GUI.Button (new Rect (220,70,150,150),new GUIContent (icon[0])))
                {
                   
                }
                if (GUI.Button (new Rect (380,70,150,150),new GUIContent (icon[1])))
                {
                   
                }
                if (GUI.Button (new Rect (540,70,150,150),new GUIContent (icon[2])))
                {
                   
                }
                if (GUI.Button (new Rect (700,70,150,150),new GUIContent (icon[3])))
                {
                   
                }
               
                //1st six buttons
                if(fin==false)
                {
                    for (int i=0; i<6; i++) {
                        if (isAvailable [i] == "1") {
                            if ((GUI.Button (new Rect (400 + ((i % 6) * 45), 250, 40, 40), "" + letters [i])) && (Let.Count < answer.Length)) {
                                Let.Add (letters [i]);
                                isAvailable [i] = 0;
                                toBeRemoved.Add (i);
                            }
                        }
                        else
                        {
                            GUI.Button (new Rect (400 + ((i % 6) * 45), 250, 40, 40), "");
                        }
                    }
                    //2nd six buttons
                    for (int i=6; i<12; i++) {
                        if (isAvailable [i] == "1") {
                            if ((GUI.Button (new Rect (400 + ((i % 6) * 45), 293, 40, 40), "" + letters [i])) && (Let.Count < answer.Length)) {
                                Let.Add (letters [i]);
                                isAvailable [i] = 0;
                                toBeRemoved.Add (i);
                            }
                        }
                        else
                        {
                            GUI.Button (new Rect (400 + ((i % 6) * 45), 293, 40, 40), "");
                        }
                       
                    }
                    //BOX
                    for (int i=0; i<answer.Length; i++) {
                        if (i < 6) {
                            GUI.Button (new Rect (300 + ((i % 6) * 40), 200, 35, 35), "");
                        } else if ((i >= 6) && (i < 12)) {
                            GUI.Button (new Rect (540 + ((i % 6) * 40), 200, 35, 35), "");
                        }
                    }
                    //revert
                    if ((GUI.Button (new Rect (540, 250, 40, 40), "<"))&&(nahulaan==false)) {
                        if (Let.Count > 0) {
                            Let.RemoveAt (Let.Count - 1);
                            isAvailable [(int)toBeRemoved [toBeRemoved.Count - 1]] = "1";
                            toBeRemoved.RemoveAt (toBeRemoved.Count - 1);
                        }                   
                    }
                    //ANSWERS
                    x = "";
                    for (int i=0; i<Let.Count; i++) {
                        if (i < 6) {
                            GUI.Button (new Rect (300 + ((i % 6) * 40), 200, 35, 35), "" + Let [i]);
                        } else if ((i >= 6) && (i < 12)) {
                            GUI.Button (new Rect (540 + ((i % 6) * 40), 200, 35, 35), "" + Let [i]);
                        }
                       
                        x = x + Let [i];
                    }
                    if ((x == answer)&&(nahulaan==false)) {
                       
                        string _strDBName = "URI=file:"+Application.dataPath+"/dbfile";
                       
                        IDbConnection _connection = new SqliteConnection(_strDBName);
                        IDbCommand _command = _connection .CreateCommand();
                        string sql;
                       
                        _connection.Open();
                       
                        //                    sql = "CREATE TABLE IF NOT EXISTS words (word VARCHAR(20), tagalog VARCHAR(50), english VARCHAR(50), level INT(), primary key(word))";
                        //                    _command.CommandText = sql;
                        //                    _command.ExecuteNonQuery();
                       
                        _command.CommandText = "INSERT INTO answered VALUES ('"+answer+"')";
                        _connection.Open ();
                        _command.ExecuteNonQuery();
                        _connection.Close();
                        nahulaan = true;
                        x = "";   
                    }
                    if(nahulaan==true)
                    {
                        //meanings!!
                        GUI.Box (new Rect (370, 330, 100, 30), "CORRECT!");
                       
                        GUI.Label (new Rect (200, 370, 300, 50), tagalog);
                        GUI.Label (new Rect (400, 370, 500, 100), english);
                       
                       
                    }
                }
               
               
            }
            catch(IOException errr)
            {
                Debug.Log(errr.Message);
            }

        }

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

        cc = 0;

        isAvailable = new ArrayList ();
        toBeRemoved = new ArrayList ();
        exampleFiles = new ArrayList ();

        string _strDBName = "URI=file:" + Application.dataPath + "/dbfile";

        IDbConnection _connection = new SqliteConnection (_strDBName);
        IDbCommand _command = _connection .CreateCommand ();
        string sql;

        _connection.Open ();

        sql = "CREATE TABLE IF NOT EXISTS words (word VARCHAR(20), tagalog VARCHAR(50), english VARCHAR(50))";
        _command.CommandText = sql;
        _command.ExecuteNonQuery ();

        _command.CommandText = "select level from words order by level DESC limit 1;";
        _connection.Open ();
        IDataReader _reader = _command.ExecuteReader ();
        while (_reader.Read())
        {
            maxlvl = _reader.GetInt32(0);
        }

        _command.CommandText = "select * from lvlmax order by level DESC limit 1;";
        _connection.Open ();
        _reader = _command.ExecuteReader ();
        while (_reader.Read())
        {
            lvl = _reader.GetInt32(0);
            Debug.Log (lvl);
        }


        _command.CommandText = "SELECT count(*) FROM words WHERE word not in (select word from answered) and level ='"+lvl+"' limit 1;";
        _connection.Open ();
        _reader = _command.ExecuteReader ();
        while (_reader.Read())
        {
            if(_reader.GetInt32(0)>0)
            {

                _command.CommandText = "SELECT * FROM words WHERE word not in (select word from answered) and level ='"+lvl+"' limit 1;";
                _reader = _command.ExecuteReader ();
               
                while (_reader.Read())
                {
                    word = _reader.GetString(0);
                    tagalog = _reader.GetString(1);
                    english = _reader.GetString(2);
                }           
                _connection.Close ();
               
                for (int i=0; i<12; i++)
                    isAvailable.Add ("1");
               
                ArrayList lstExampleFiles = new ArrayList();
                lstExampleFiles.AddRange( exampleFiles );
                int d2 = Random.Range (0, lstExampleFiles.Count);
                Debug.Log (word);
                answer = word;
                c = answer;
                for (int i=0; i<c.Length; i++) {
                    letters.Add(c.Substring(i,1));
                }
                int rr = 0;
                for (int i=letters.Count; i<12; i++) {
                    rr = Random.Range(0,l.Length);
                    letters.Add(l.Substring(rr,1));
                }
                letters = ShuffleList (letters);       
                icon = new Texture[4];
                for(int i=0; i<4;i++)
                {
                    icon[i]= (Texture)Resources.Load(answer+"/"+(i+1));
                }
                ok = true;
            }
            else
            {
                if(maxlvl>lvl)
                {
                    //per lvl



                    Debug.Log("CONGRATULATION. NATAPOS MO ANG LVL : " +lvl);


                    sql = "insert into lvlmax values("+(lvl+1)+");";
                    _command.CommandText = sql;
                    _command.ExecuteNonQuery ();
                    ok = false;

                }
                else
                {
                    //all


                    Debug.Log("CONGRATS YOW. NATAPOS MO");

                    Application.LoadLevel(0);
                }
            }
        }   

    }
   
    // Update is called once per frame
    void Update () {
       
    }
   
    private ArrayList ShuffleList(ArrayList inputList)
    {
        ArrayList randomList = new ArrayList();
       
       
        int randomIndex = 0;
        while (inputList.Count > 0)
        {
            randomIndex = Random.Range (0, inputList.Count-1); //Choose a random object in the list
            randomList.Add(inputList[randomIndex]); //add it to the new, random list
            inputList.RemoveAt(randomIndex); //remove to avoid duplicates
        }
       
        return randomList; //return the new random list
    }
}