Search Unity

  1. The 2022.1 beta is now available for testing. To find out what's new, have a look at our 2022.1 beta blog post.
    Dismiss Notice

SQLite Class - Easier Database Stuff

Discussion in 'Scripting' started by dklompmaker, Aug 14, 2009.

  1. dklompmaker

    dklompmaker

    Joined:
    Sep 12, 2008
    Posts:
    80
    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. }
     
    pachermann likes this.
  2. manolo2111

    manolo2111

    Joined:
    Nov 26, 2009
    Posts:
    1
    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.
     
  3. joelmgallant

    joelmgallant

    Joined:
    Mar 9, 2009
    Posts:
    113
    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
     
  4. Jokerminator

    Jokerminator

    Joined:
    Jun 30, 2006
    Posts:
    263
    Hi,

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

    pavees

    Joined:
    Jan 1, 2009
    Posts:
    116
    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.
     
  6. Jokerminator

    Jokerminator

    Joined:
    Jun 30, 2006
    Posts:
    263
    Do you develop for webplayer or standalone ?


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

    iphonedev

    Joined:
    Oct 27, 2009
    Posts:
    31
    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.
     
  8. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,601
    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
     
  9. iphonedev

    iphonedev

    Joined:
    Oct 27, 2009
    Posts:
    31
    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.
     
  10. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,601
    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
     
  11. fallingbrickwork

    fallingbrickwork

    Joined:
    Mar 16, 2009
    Posts:
    1,072
    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.
     
  12. fallingbrickwork

    fallingbrickwork

    Joined:
    Mar 16, 2009
    Posts:
    1,072
    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.
     
  13. bigteef

    bigteef

    Joined:
    Aug 20, 2009
    Posts:
    20
    fallingbrickwork, were you able to resolve this? i'm seeing similar behavior in windows 7.
     
  14. by0log1c

    by0log1c

    Joined:
    Jan 30, 2011
    Posts:
    40
    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.
     
  15. hellokitty

    hellokitty

    Joined:
    Jun 21, 2011
    Posts:
    2
    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)
     
  16. AlanChatham

    AlanChatham

    Joined:
    Jul 4, 2011
    Posts:
    15
  17. Rustam-Ganeyev

    Rustam-Ganeyev

    Joined:
    Jul 18, 2011
    Posts:
    29
    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.
     
  18. deerendee

    deerendee

    Joined:
    Nov 26, 2011
    Posts:
    3
    how to do the UPDATE? is it similar with INSERT syntaxs? thanks!
     
  19. thuron

    thuron

    Joined:
    Mar 20, 2011
    Posts:
    46
    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.
     
  20. bioduds

    bioduds

    Joined:
    Nov 19, 2011
    Posts:
    16
  21. c&c&z&z

    c&c&z&z

    Joined:
    Aug 1, 2012
    Posts:
    12
    I couldn't find the dlls in the mono.
    Could you please send me the download address for those dlls.
     
  22. c&c&z&z

    c&c&z&z

    Joined:
    Aug 1, 2012
    Posts:
    12
    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)
     
    Last edited: Aug 23, 2012
  23. Oksana-Iashchuk

    Oksana-Iashchuk

    Joined:
    Sep 10, 2012
    Posts:
    111
  24. alonsoGarrote

    alonsoGarrote

    Joined:
    Jun 23, 2012
    Posts:
    25
    Im also looking for those dlls, do you know where I can find them?,
    thanks in advance...+
     
  25. jiezhixing008

    jiezhixing008

    Joined:
    Dec 15, 2012
    Posts:
    2
    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!
     
  26. Foram

    Foram

    Joined:
    Apr 5, 2013
    Posts:
    12
    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.
     
  27. Foram

    Foram

    Joined:
    Apr 5, 2013
    Posts:
    12
    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;
    }
     
  28. dave_mm0

    dave_mm0

    Joined:
    Feb 8, 2013
    Posts:
    26
    So in case anyone wants a quick C# version of dbAccess:

     
  29. Mikael-Madrid

    Mikael-Madrid

    Joined:
    Nov 2, 2012
    Posts:
    26
    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
     
  30. CORPSEGRINDER

    CORPSEGRINDER

    Joined:
    Feb 6, 2014
    Posts:
    6
    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.
     
  31. RenzGazmin

    RenzGazmin

    Joined:
    Aug 22, 2014
    Posts:
    1
    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) {

    }
    }
     
  32. Rbert

    Rbert

    Joined:
    Jul 13, 2014
    Posts:
    28
    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
     
  33. rodrigozol

    rodrigozol

    Joined:
    Apr 4, 2009
    Posts:
    26
    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. }
     
    pachermann likes this.
  34. rodrigozol

    rodrigozol

    Joined:
    Apr 4, 2009
    Posts:
    26
    Using SQLite Manager on Firefox I could create more columns, the script on Unity recognize the new ones.
     
  35. abhishek20502025

    abhishek20502025

    Joined:
    May 19, 2015
    Posts:
    9
    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
     
  36. alonsoGarrote

    alonsoGarrote

    Joined:
    Jun 23, 2012
    Posts:
    25
    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,
     
  37. victor-biosphera

    victor-biosphera

    Joined:
    Jun 21, 2010
    Posts:
    67
    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?
     
  38. bighoz

    bighoz

    Joined:
    Oct 11, 2016
    Posts:
    3
    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?
     
  39. gpred

    gpred

    Joined:
    Jul 4, 2013
    Posts:
    6
    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'
     
unityunity