SQLite Class - Easier Database Stuff

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

Code (csharp):

1.  
2. import          System.Data;  // we import our  data class
3. import          Mono.Data.SqliteClient; // we import our sqlite client
4.  
5. class dbAccess {
6.     // variables for basic query access
7.     private var connection : String;
8.     private var dbcon : IDbConnection;
9.     private var dbcmd : IDbCommand;
10.     private var reader : IDataReader;
11.    
12.     function OpenDB(p : String){
13.     connection = "URI=file:" + p; // we set the connection to our database
14.     dbcon = new SqliteConnection(connection);
15.     dbcon.Open();
16.     }
17.    
18.     function BasicQuery(q : String, r : boolean){ // run a baic Sqlite query
19.         dbcmd = dbcon.CreateCommand(); // create empty command
20.         dbcmd.CommandText = q; // fill the command
21.         reader = dbcmd.ExecuteReader(); // execute command which returns a reader
22.         if(r){ // if we want to return the reader
23.         return reader; // return the reader
24.         }
25.     }
26.    
27.     function CreateTable(name : String, col : Array, colType : Array){ // Create a table, name, column array, column type array
28.         var query : String;
29.         query  = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0];
30.         for(var i=1; i<col.length; i++){
31.             query += ", " + col[i] + " " + colType[i];
32.         }
33.         query += ")";
34.         dbcmd = dbcon.CreateCommand(); // create empty command
35.         dbcmd.CommandText = query; // fill the command
36.         reader = dbcmd.ExecuteReader(); // execute command which returns a reader
37.    
38.     }
39.    
40.     function InsertIntoSingle(tableName : String, colName : String, value : String){ // single insert
41.         var query : String;
42.         query = "INSERT INTO " + tableName + "(" + colName + ") " + "VALUES (" + value + ")";
43.         dbcmd = dbcon.CreateCommand(); // create empty command
44.         dbcmd.CommandText = query; // fill the command
45.         reader = dbcmd.ExecuteReader(); // execute command which returns a reader
46.     }
47.    
48.     function InsertIntoSpecific(tableName : String, col : Array, values : Array){ // Specific insert with col and values
49.         var query : String;
50.         query = "INSERT INTO " + tableName + "(" + col[0];
51.         for(var i=1; i<col.length; i++){
52.             query += ", " + col[i];
53.         }
54.         query += ") VALUES (" + values[0];
55.         for(i=1; i<values.length; i++){
56.             query += ", " + values[i];
57.         }
58.         query += ")";
59.         dbcmd = dbcon.CreateCommand();
60.         dbcmd.CommandText = query;
61.         reader = dbcmd.ExecuteReader();
62.     }
63.    
64.     function InsertInto(tableName : String, values : Array){ // basic Insert with just values
65.         var query : String;
66.         query = "INSERT INTO " + tableName + " VALUES (" + values[0];
67.         for(var i=1; i<values.length; i++){
68.             query += ", " + values[i];
69.         }
70.         query += ")";
71.         dbcmd = dbcon.CreateCommand();
72.         dbcmd.CommandText = query;
73.         reader = dbcmd.ExecuteReader();
74.     }
75.    
76.     function SingleSelectWhere(tableName : String, itemToSelect : String, wCol : String, wPar : String, wValue : String){ // Selects a single Item
77.         var query : String;
78.         query = "SELECT " + itemToSelect + " FROM " + tableName + " WHERE " + wCol + wPar + wValue;
79.         dbcmd = dbcon.CreateCommand();
80.         dbcmd.CommandText = query;
81.         reader = dbcmd.ExecuteReader();
82.         var readArray = new Array();
83.         while(reader.Read()){
84.             readArray.Push(reader.GetString(0)); // Fill array with all matches
85.         }
86.         return readArray; // return matches
87.     }
88.  
89.    
90.     function CloseDB(){
91.         reader.Close(); // clean everything up
92.         reader = null;
93.         dbcmd.Dispose();
94.         dbcmd = null;
95.         dbcon.Close();
96.         dbcon = null;
97.     }
98.    
99. }

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

Code (csharp):

1.  
2. var db : dbAccess;
3. function Start(){
4.     db = new dbAccess();
5.     db.OpenDB("myDB.sqdb");
6.     var tableName = "myTable";
7.     var columnNames = new Array("firstName","lastName");
8.     var columnValues = new Array("text","text");
9.     db.CreateTable(tableName,columnNames,columnValues);
10.     db.CloseDB();
11. }

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

Code (csharp):

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

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.

Code (csharp):

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

 

Hi.

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

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.

 

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

 

Hi,

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

 

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.

 

Do you develop for webplayer or standalone ?


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

 

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.

 

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

 

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.

 

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

 

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.

 

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:

Code (csharp):

1.  
2. void Start() {
3.    db = new dbController();
4.    db.OpenDB("CasusBelliDB.sqdb");
5.  
6.    List<string> result = new List<string>();
7.    result = db.SingleSelectWhere("test","name","name","=", "'Matt'"); // Process SQL
8.  
9.    guiText.text = result[0];
10.  
11.    db.CloseDB(); // Close DB connection
12. }
13.  

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.

 

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

 

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.

 

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.AddReferencedAssembliesRecurse (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.AddReferencedAssembliesRecurse (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.AddReferencedAssembliesRecurse (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.AddReferencedAssembliesRecurse (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.FindAssembliesReferencedBy (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.BuildPlayerWithDefaultSettings (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)

 

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/index.php?title=SQLite

 

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/index.php?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.

Code (csharp):

1.  
2. using UnityEngine;
3. using System;
4. using Mono.Data.Sqlite;
5.  
6.  
7. public class DbAccess
8. {
9.     private SqliteConnection dbConnection;
10.     private SqliteCommand dbCommand;
11.     private SqliteDataReader reader;
12.  
13.  
14.     /// <summary>
15.     ///  string to connect. The simpliest one looks like "URI=file:filename.db"
16.     /// </summary>
17.     /// <param name="connectionString">
18.     /// A <see cref="System.String"/>
19.     /// </param>
20.     public DbAccess (string connectionString)
21.     {
22.         OpenDB (connectionString);
23.     }
24.  
25.     /// <summary>
26.     ///  The same as <see cref="DbAccess#Dbaccess" / >
27.     /// </summary>
28.     /// <param name="connectionString">
29.     /// A <see cref="System.String"/>
30.     /// </param>
31.     public void OpenDB (string connectionString)
32.     {
33.         dbConnection = new SqliteConnection (connectionString);
34.         dbConnection.Open ();
35.         Debug.Log ("Connected to db");
36.     }
37.  
38.     /// <summary>
39.     /// Closes connection to db
40.     /// </summary>
41.     public void CloseSqlConnection ()
42.     {
43.         if (dbCommand != null) {
44.             dbCommand.Dispose ();
45.         }
46.         dbCommand = null;
47.         if (reader != null) {
48.             reader.Dispose ();
49.         }
50.         reader = null;
51.         if (dbConnection != null) {
52.             dbConnection.Close ();
53.         }
54.         dbConnection = null;
55.         Debug.Log ("Disconnected from db.");
56.     }
57.  
58.  
59.     /// <summary>
60.     ///  Executes query given by sqlQuery
61.     /// </summary>
62.     /// <param name="sqlQuery">
63.     /// query
64.     /// A <see cref="System.String"/>
65.     /// </param>
66.     /// <returns>
67.     /// null, if any error
68.     /// result of query, otherwise
69.     /// A <see cref="SqliteDataReader"/>
70.     /// </returns>
71.     public SqliteDataReader ExecuteQuery (string sqlQuery)
72.     {
73.         dbCommand = dbConnection.CreateCommand ();
74.         dbCommand.CommandText = sqlQuery;
75.        
76.         reader = dbCommand.ExecuteReader ();
77.        
78.        
79.         return reader;
80.     }
81.  
82.     /// <summary>
83.     ///  Selects everything from table
84.     /// </summary>
85.     /// <param name="tableName">
86.     /// name of table
87.     /// A <see cref="System.String"/>
88.     /// </param>
89.     /// <returns>
90.     /// result of query
91.     /// A <see cref="SqliteDataReader"/>
92.     /// </returns>
93.     public SqliteDataReader ReadFullTable (string tableName)
94.     {
95.         string query = "SELECT * FROM " + tableName;
96.         return ExecuteQuery (query);
97.     }
98.  
99.     /// <summary>
100.     /// Inserts data into table
101.     /// </summary>
102.     /// <param name="tableName">
103.     /// name of table to insert data
104.     /// A <see cref="System.String"/>
105.     /// </param>
106.     /// <param name="values">
107.     /// array of data in string representation
108.     /// A <see cref="System.String[]"/>
109.     /// </param>
110.     /// <returns>
111.     /// result of query
112.     /// A <see cref="SqliteDataReader"/>
113.     /// </returns>
114.     public SqliteDataReader InsertInto (string tableName, string[] values)
115.     {
116.         string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
117.         for (int i = 1; i < values.Length; ++i) {
118.             query += ", " + values[i];
119.         }
120.         query += ")";
121.         return ExecuteQuery (query);
122.     }
123.  
124.     /// <summary>
125.     /// Inserts data into specific columns of table
126.     /// </summary>
127.     /// <param name="tableName">
128.     /// name of table
129.     /// A <see cref="System.String"/>
130.     /// </param>
131.     /// <param name="cols">
132.     /// name of columns
133.     /// A <see cref="System.String[]"/>
134.     /// </param>
135.     /// <param name="values">
136.     /// values
137.     /// A <see cref="System.String[]"/>
138.     /// </param>
139.     /// <returns>
140.     /// result of query
141.     /// A <see cref="SqliteDataReader"/>
142.     /// </returns>
143.     public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values)
144.     {
145.         if (cols.Length != values.Length) {
146.             throw new SqliteException ("columns.Length != values.Length");
147.         }
148.         string query = "INSERT INTO " + tableName + "(" + cols[0];
149.         for (int i = 1; i < cols.Length; ++i) {
150.             query += ", " + cols[i];
151.         }
152.         query += ") VALUES (" + values[0];
153.         for (int i = 1; i < values.Length; ++i) {
154.             query += ", " + values[i];
155.         }
156.         query += ")";
157.         return ExecuteQuery (query);
158.     }
159.  
160.     /// <summary>
161.     /// deletes any data from table
162.     /// </summary>
163.     /// <param name="tableName">
164.     /// table name
165.     /// A <see cref="System.String"/>
166.     /// </param>
167.     /// <returns>
168.     /// result of query
169.     /// A <see cref="SqliteDataReader"/>
170.     /// </returns>
171.     public SqliteDataReader DeleteContents (string tableName)
172.     {
173.         string query = "DELETE FROM " + tableName;
174.         return ExecuteQuery (query);
175.     }
176.  
177.     /// <summary>
178.     /// Creates table with specified columns
179.     /// </summary>
180.     /// <param name="name">
181.     /// table name to be created
182.     /// A <see cref="System.String"/>
183.     /// </param>
184.     /// <param name="col">
185.     /// array, containing names of columns
186.     /// A <see cref="System.String[]"/>
187.     /// </param>
188.     /// <param name="colType">
189.     /// array, containing types of columns
190.     /// A <see cref="System.String[]"/>
191.     /// </param>
192.     /// <returns>
193.     /// result of query
194.     /// A <see cref="SqliteDataReader"/>
195.     /// </returns>
196.     public SqliteDataReader CreateTable (string name, string[] col, string[] colType)
197.     {
198.         if (col.Length != colType.Length) {
199.             throw new SqliteException ("columns.Length != colType.Length");
200.         }
201.         string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
202.         for (int i = 1; i < col.Length; ++i) {
203.             query += ", " + col[i] + " " + colType[i];
204.         }
205.         query += ")";
206.         return ExecuteQuery (query);
207.     }
208.  
209.     /// <summary>
210.     /// Selects from table with specified parameters.
211.     /// Ex: SelectWhere("puppies", new string[] = {"breed"}, new string[] = {"earType"}, new string[] = {"="}, new string[] = {"floppy"});
212.     /// the same as command: SELECT breed FROM puppies WHERE earType = floppy
213.     /// </summary>
214.     /// <param name="tableName">
215.     /// name of table to select
216.     /// A <see cref="System.String"/>
217.     /// </param>
218.     /// <param name="items">
219.     /// item names
220.     /// A <see cref="System.String[]"/>
221.     /// </param>
222.     /// <param name="col">
223.     /// array, containing columns of parameters
224.     /// A <see cref="System.String[]"/>
225.     /// </param>
226.     /// <param name="operation">
227.     /// A <see cref="System.String[]"/>
228.     /// </param>
229.     /// <param name="values">
230.     /// A <see cref="System.String[]"/>
231.     /// </param>
232.     /// <returns>
233.     /// result of query
234.     /// A <see cref="SqliteDataReader"/>
235.     /// </returns>
236.     public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)
237.     {
238.         if (col.Length != operation.Length || operation.Length != values.Length) {
239.             throw new SqliteException ("col.Length != operation.Length != values.Length");
240.         }
241.         string query = "SELECT " + items[0];
242.         for (int i = 1; i < items.Length; ++i) {
243.             query += ", " + items[i];
244.         }
245.         query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
246.         for (int i = 1; i < col.Length; ++i) {
247.             query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
248.         }
249.        
250.         return ExecuteQuery (query);
251.        
252.     }
253.    
254.    
255.    
256. }
257.  

If you find any bugs please report to this thread.

 

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

 

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.

 

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

 

I couldn't find the dlls in the mono.
Could you please send me the download address for those dlls.

 

My problem is connetction can't close.
When I use 'CloseSqlConnection()'

throw :

InvalidOperationException: Collection was modified; enumeration operation may not execute.
System.Collections.Generic.List`1+Enumerator[Mono.Data.Sqlite.SqliteCommand].VerifyState ()
System.Collections.Generic.List`1+Enumerator[Mono.Data.Sqlite.SqliteCommand].MoveNext ()
Mono.Data.Sqlite.SqliteConnection.Close ()
(wrapper remoting-invoke-with-check) Mono.Data.Sqlite.SqliteConnection:Close ()
DBAccess.CloseSqlConnection () (at Assets/Resources/Scripts/DataBase/DBAccess.cs:139)

 

please, check this asset http://u3d.as/content/orange-tree/sqlite-kit/3ka
That is 100% managed code, full SQLite3 support, all platforms.
No native dependencies.
all platforms: WebPlayer, PC, Mac, Android, IPhone

 

Im also looking for those dlls, do you know where I can find them?,
thanks in advance...+

 

If I want to create a table how to do it?
Also, how do I judge a Sqlite database, a table has been created, if you have already created, you no longer created only open if it was not created to create it?
Look forward to your reply, thank you!
Online waiting!

 

I have used javascript and I am developing app. for iPhone. I have copied my database file to xCode project.

When I run on device I got this error.

SqliteException: Attempt to write a read-only database

attempt to write a readonly database
at Mono.Data.Sqlite.SQLite3.Reset (Mono.Data.Sqlite.SqliteStatement stmt) [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SQLite3.Step (Mono.Data.Sqlite.SqliteStatement stmt) [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteDataReader.NextResult () [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteDataReader..ctor (Mono.Data.Sqlite.SqliteCommand cmd, CommandBehavior behave) [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteCommand.ExecuteReader (CommandBehavior behavior) [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteCommand.ExecuteNonQuery () [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteTransaction..ctor (Mono.Data.Sqlite.SqliteConnection connection, Boolean deferredLock) [0x00000] in <filename unknown>:0

I also checked package data which is installed on device.
But no database file is installed on device.
Waiting for reply.
Thanks in advance.

 

Sorry guys..
I solved it...very quickly than expected.
I have changed my connection string now everything works fine.
Don't need to copy database file to xCode project.

if(Application.platform == RuntimePlatform.IPhonePlayer)
{
//connection = "URI=file:" + Application.dataPath.Replace("/Data","/") + databaseName;
connection = "URI=file:" + Application.persistentDataPath + "/" + databaseName;
}

 

So in case anyone wants a quick C# version of dbAccess:

 

I'm not sure what I'm doing wrong but I cant access the elements for the array im trying this:

Declaring variables:

public ArrayList datosddbb = new ArrayList();
public DBAccess ddbb = new DBAccess();
....

void Start () {

ddbb.OpenDB("database");
datosddbb = ddbb.ReadFullTable("items");
Debug.Log("capaticy:"+datosddbb.Capacity); --> THIS WORKS,it returns the number of rows in my DDBB
Debug.Log(datosddbb[0][0]); --> THis fails "Can not apply indexing[] to a an expression of type object

}
Any insight would be greatly appreciated,this is driving me nuts. thanks in advanced

 

Just a question. Will this work on WP8 platform? I've been searching for a sqlite plugin that will work on WP8, so far to no avail. Please if anyone has any idea it would be a great help.

 

sir can you translate this in C# code? i saw your dbAccess script in javascript , i amazed that you converted it in C#, can you convert the script below in C#? tnx for help!


public var DatabaseName : String = "TestDB.sqdb";


public var TableName : String = "TestTable";
var db : dbAccess;

function Start() {

db = new dbAccess();
db.OpenDB(DatabaseName);

var tableName = TableName;
var columnNames = new Array("firstName","lastName");
var columnValues = new Array("text","text");
try {
db.CreateTable(tableName,columnNames,columnValues);
}

• catch(e) {

}
}

 

whats the problem sir if i have an error like this..
Error building Player: Extracting referenced dlls failed.
i working on windows..the API compatibility of my player is .NET 2.0 subset

 

I'm not a DB programmer, I'm starting with SQLite. I'm trying to understand how the things works. So in my first test I'm trying to create a "age" column. Here is the "ScriptThatUsesTheDatabase.js" modified.

Unity gives this error message: "SqliteException: SQLite error
table TestTable has 2 columns but 3 values were supplied". Where should I manage to create a third column? Should I modify "dbAccess.js", reading the code i can't figure where?

Thanks in advance.

Code (JavaScript):

1. #pragma strict
2. /*  Script for testing out SQLite in Javascript
3.           2011 - Alan Chatham
4.           Released into the public domain
5.         This script is a GUI script - attach it to your main camera.
6.         It creates/opens a SQLite database, and with the GUI you can read and write to it.
7.                                         */
8. // This is the file path of the database file we want to use
9. // Right now, it'll load TestDB.sqdb in the project's root folder.
10. // If one doesn't exist, it will be automatically created.
11. public var DatabaseName : String = "TestDB.sqdb";
12. // This is the name of the table we want to use
13. public var TableName : String = "TestTable";
14. var db : dbAccess;
15. function Start() {
16.     // Give ourselves a dbAccess object to work with, and open it
17.     db = new dbAccess();
18.     db.OpenDB(DatabaseName);
19.     // Let's make sure we've got a table to work with as well!
20.     var tableName = TableName;
21.     //var columnNames = new Array("firstName","lastName");
22.     var columnNames = new Array("firstName","lastName", "age"); // o negocio e mais embaixo
23.     //var columnValues = new Array("text","text");
24.     var columnValues = new Array("text","text", "text");
25.     try {
26.         db.CreateTable(tableName,columnNames,columnValues);
27.     }
28.     catch(e) {// Do nothing - our table was already created
29.         //- we don't care about the error, we just don't want to see it
30.     }
31. }
32. // These variables just hold info to display in our GUI
33. var firstName : String = "First Name";
34. var lastName : String = "Last Name";
35. var age : String = "Age";
36. var DatabaseEntryStringWidth = 100;
37. var scrollPosition : Vector2;
38. var databaseData : ArrayList = new ArrayList();
39. // This GUI provides us with a way to enter data into our database
40. //  as well as a way to view it
41. function OnGUI() {
42.     GUI.Box(Rect (25,25,Screen.width - 50, Screen.height - 50),"");
43.     GUILayout.BeginArea(Rect(50, 50, Screen.width - 100, Screen.height - 100));
44.     // This first block allows us to enter new entries into our table
45.         GUILayout.BeginHorizontal();
46.             firstName = GUILayout.TextField(firstName, GUILayout.Width (DatabaseEntryStringWidth));
47.             lastName = GUILayout.TextField(lastName, GUILayout.Width (DatabaseEntryStringWidth));
48.             age = GUILayout.TextField(age, GUILayout.Width (DatabaseEntryStringWidth));
49.         GUILayout.EndHorizontal();
50.         if (GUILayout.Button("Add to database")) {
51.             // Insert the data
52.            //InsertRow(firstName,lastName);
53.            InsertRow(firstName,lastName,age);
54.             // And update the readout of the database
55.             databaseData = ReadFullTable();
56.         }
57.         // This second block gives us a button that will display/refresh the contents of our database
58.         GUILayout.BeginHorizontal();
59.             if (GUILayout.Button ("Read Database"))
60.                 databaseData = ReadFullTable();
61.             if (GUILayout.Button("Clear"))
62.                 databaseData.Clear();
63.         GUILayout.EndHorizontal();
64.         GUILayout.Label("Database Contents");
65.         scrollPosition = GUILayout.BeginScrollView(scrollPosition, GUILayout.Height(100));
66.             for (var line : ArrayList in databaseData) {
67.                 GUILayout.BeginHorizontal();
68.                 for (var s in line) {
69.                     GUILayout.Label(s.ToString(), GUILayout.Width(DatabaseEntryStringWidth));
70.                 }
71.                 GUILayout.EndHorizontal();
72.             }
73.         GUILayout.EndScrollView();
74.         if (GUILayout.Button("Delete All Data")) {
75.             DeleteTableContents();
76.             databaseData = ReadFullTable();
77.         }
78.     GUILayout.EndArea();
79. }
80. // Wrapper function for inserting our specific entries into our specific database and table for this file
81. //function InsertRow(firstName:String, lastName:String) {
82. function InsertRow(firstName:String, lastName:String, age:String) { //
83.   //var values = new Array(("'"+firstName+"'"),("'"+lastName+"'"));
84.     var values = new Array(("'"+firstName+"'"),("'"+lastName+"'"),("'"+age+"'"));
85.     db.InsertInto(TableName, values);
86. }
87. // Wrapper function, so we only mess with our table.
88. function ReadFullTable() {
89.     return db.ReadFullTable(TableName);
90. }
91. // Another wrapper function...
92. function DeleteTableContents() {
93.     db.DeleteTableContents(TableName);
94. }

 

Using SQLite Manager on Firefox I could create more columns, the script on Unity recognize the new ones.

 

Hey,
Seems like it works in game mode (created the GUI)
but the error is shown.:: 1=
(DllNotFoundException: sqlite3
Mono.Data.Sqlite.SQLite3.Open (System.String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool)
Mono.Data.Sqlite.SqliteConnection.Open ()
dbAccess.OpenDB (System.String p) (at Assets/dbAccess.js:16)
ScriptThatUsesTheDatabase.Start () (at Assets/ScriptThatUsesTheDatabase.js:16))


i kept the dll file in plugins folder...


2=(
InvalidOperationException: Database is not open
Mono.Data.Sqlite.SqliteCommand.InitializeForReader ()
Mono.Data.Sqlite.SqliteCommand.ExecuteReader (CommandBehavior behavior)
Mono.Data.Sqlite.SqliteCommand.ExecuteDbDataReader (CommandBehavior behavior)
System.Data.Common.DbCommand.ExecuteReader ()
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader ()
dbAccess.ReadFullTable (System.String tableName) (at Assets/dbAccess.js:35)
ScriptThatUsesTheDatabase.ReadFullTable () (at Assets/ScriptThatUsesTheDatabase.js:88)
ScriptThatUsesTheDatabase.OnGUI () (at Assets/ScriptThatUsesTheDatabase.js:56)
)



can u help me figure it out

 

I see, maybe you have to change your dll file, where did you get it from?,
Also, if I recall correctly, you also need a .so file, I made this more than a year ago so I cant say it for sure, hope it helps,

 

its still working on unity 5.4.0?
for me everything works fine in the editor, but when I make a build, my SQL stuff doesn't work.
and i already put the sqlite3.dll on the plugins folder, and i check in the build and it is there too.
Any toughts?

 

if we've made a table and its contents in the database, whether it can open on android device?
or we must create a new table and its contents using a script?

 

This works for me. I have a x86 version and a x64 version inside folders like this,

plugins/x86/sqlite3

plugins/x86_64/sqlite3

Then the x86 version is set in the inspector to check the options 'Any platform' and 'x86'


The x86_64 version is set in the inspector to check the options 'Editor', 'Standalone' and 'x86_64'