Search Unity

  1. Get the latest news, tutorials and offers directly to your inbox with our newsletters. Sign up now.
    Dismiss Notice

Unity and Sqlite

Discussion in 'Editor & General Support' started by Stick, Dec 19, 2007.

  1. Stick

    Stick

    Joined:
    Dec 14, 2007
    Posts:
    37
    I am making a turn based game and need database support. I really want to use Sqlite, which Mono currently supports, supposedly. My master server has no problem accessing an Sqlite database with the 1.6.2 Mono framework.

    When I include the "using Mono.Data.Sqlite;", the Unity compiler complains saying that it does not recognize the namespace "Data" in Mono. This tells me that their is no reference to the Data.Sqlite namespace in Unity. How do I include a reference to this namespace, so I can utilize Mono's libraries for Sqlite?
     
  2. Eric5h5

    Eric5h5

    Volunteer Moderator Moderator

    Joined:
    Jul 19, 2006
    Posts:
    32,291
    Interestingly, doing "import Mono.Data.Sqlite;" in Javascript works. Doing "import System.Data;" also works, but "using System.Data;" in C# doesn't work either.

    --Eric
     
  3. JFo

    JFo

    Joined:
    Dec 9, 2007
    Posts:
    217
    I encountered this same problem: SQLite is not working with C# in Unity.

    Digging the problem little more I found out that to get Unity compile SQLite related C# code, "compile_any.pl" Perl script in "/Applications/Unity/Unity.app/Contents/Frameworks MonoCompiler.framework" have to be modified:

    Original line in "sub csharp_compiler" (line number 111):
    Code (csharp):
    1.  
    2.     my @base_args=($mono_path, $mcs_path, '-debug', '-target:library', '-nowarn:0169', '-out:'.$output,  );
    Modified line:

    Code (csharp):
    1.     my @base_args=($mono_path, $mcs_path, '-r:System.Data.dll', '-r:Mono.Data.SqliteClient.dll', '-debug', '-target:library', '-nowarn:0169', '-out:'.$output,  );
    As seen, by adding hardcoded references to "System.Data.dll" and "Mono.Data.Sqlite.dll" makes things work.

    I sent a bug report, but I don't know how high priority this is to Unity team. Meanwhile, if you feel adventurous, you can try the attached SQLite test scene (readme.txt included in the zip)..

    And yes, if someone knows how to do this without hacking Unity's internal Perl script, I would really like to know..
     

    Attached Files:

  4. AngryAnt

    AngryAnt

    Keyboard Operator Moderator

    Joined:
    Oct 25, 2005
    Posts:
    3,045
    If you want less of a hack, you should be able to simply include the dlls in your assets folder. They should be located at "/Library/Frameworks/Mono.framework/(your version)/lib/mono/gac/...". I've attached mine which are from mono 1.9.
     

    Attached Files:

  5. JFo

    JFo

    Joined:
    Dec 9, 2007
    Posts:
    217
    Thanks, AngryAnt! It seems to be better solution. 8) I'll try that..

    Anyway, it's weird if sqlite works without any additional steps with JS but not C#..
     
  6. Stick

    Stick

    Joined:
    Dec 14, 2007
    Posts:
    37
    Thanks JFo and AngryAnt. I ended up going a different route with my project. I really like sqlite, and the advantages it offers, but due to my original database being in MySQL and much of my database needing to integrate with some of my other website databases I decided it was best to simply export the game tables into one CVS file and import it as a text asset in Unity. That solution is working great for me. I did have SQLite working in Unity though before switching back to MySQL though. It worked well by putting the DLL into the Unity asset folder.
     
  7. thylaxene

    thylaxene

    Joined:
    Oct 10, 2005
    Posts:
    713
    thanks for the heads up about SQLite. Very cool. I ported the C# stuff to JS... mainly as an exercise for myself. But I might as well share in case someone else needs it in the future.


    Code (csharp):
    1. import          System.Data;
    2. import          Mono.Data.SqliteClient;
    3.  
    4. private var     labelText : String = "Name Surname\n--------------------------\n";
    5.  
    6. function Start () {
    7.     var connectionString : String = "URI=file:SqliteTestDB.db";
    8.     var dbcon : IDbConnection = new SqliteConnection ( connectionString );
    9.     dbcon.Open();
    10.     var dbcmd : IDbCommand = dbcon.CreateCommand ();
    11.     // requires a table to be created named employee
    12.     // with columns firstname and lastname
    13.     // such as, CREATE TABLE employee ( firstname varchar(32), lastname varchar(32) );
    14.     var sql : String = "SELECT firstname, lastname " + "FROM employee";
    15.     dbcmd.CommandText = sql;
    16.     var reader : IDataReader = dbcmd.ExecuteReader();
    17.     while( reader.Read() ) {
    18.         var FirstName : String = reader.GetString (0);
    19.         var LastName : String = reader.GetString (1);
    20.         labelText += (FirstName + " " + LastName + "\n");
    21.     }
    22.     // clean up
    23.     reader.Close();
    24.     reader = null;
    25.     dbcmd.Dispose();
    26.     dbcmd = null;
    27.     dbcon.Close();
    28.     dbcon = null;
    29. }
    30.  
    31. function OnGUI () {
    32.     GUI.Label (Rect (Screen.width/2-100, Screen.height/2-80, 200, 200), labelText);
    33. }
    34.  
    Cheers
     
  8. jashan

    jashan

    Joined:
    Mar 9, 2007
    Posts:
    3,294
    As I've just found out (via very painful experience), it may not be the best solution to use the DLLs from the Mono installation. See also:

    http://forum.unity3d.com/viewtopic.php?p=83500#83500

    In a nutshell: It seems there are different versions of these DLLs out there, and mono seems to not "scream loud" if those versions don't match. And: It seems there is an incompatibility between the more recent System.Data.dll that comes with Mono and Unity. At least when using this DLL to access SQL Server 2005 as I did (in my case, this lead to Unity and standalone players crashing when I closed the standalone player / Unity after I had accessed the database)...

    I guess "the recommendation" is probably to use the DLLs that come with Unity, from:

    Code (csharp):
    1. /Applications/Unity/Unity.app/Contents/Frameworks/Mono.framework
    ... might be different behavior for different databases, of course - with SQL Server 2005, this is a problem. Could be that with other databases, it's the other way round and using Monos most recent DLLs is preferable to using those that come with Unity (and of course, if you're using a database that's not support by Mono "out of the box", you'll have to use a separate DLL anyways)...

    Sunny regards,
    Jashan
     
  9. thylaxene

    thylaxene

    Joined:
    Oct 10, 2005
    Posts:
    713
    [edit] see below.
     
  10. thylaxene

    thylaxene

    Joined:
    Oct 10, 2005
    Posts:
    713
    oops, just had a thought under windows do I need to install this:

    http://www.sqlite.org/download.html

    sorry for the wasted bandwidth... just had a serious blonde moment then! I put the sqlite.dll in the plugins folder and all is good again! :roll:

    Cheers.
     
  11. Ekzuzy

    Ekzuzy

    Joined:
    May 28, 2008
    Posts:
    34
    Hi! I have been working recently with with MySQL database. I've copied System.Data.dll from Unity package to assets folder in my project (not in the root ;-)), also place my other C# library and MySQL.data.dll. Everything is working fine in Unity editor but when I build it to standalone it doesn't work. I built it to web player, to Windows standalone and nothing. Works great in editor and doesn't work in standalone. In Windows, in log file is written:

    NotSupportedException: CodePage 1252 not supported
    System.Text.Encoding.GetEncoding (Int32 codePage)
    MySql.Data.MySqlClient.Driver..ctor (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings)
    MySql.Data.MySqlClient.NativeDriver..ctor (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings)
    MySql.Data.MySqlClient.Driver.Create (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings)
    MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection ()
    MySql.Data.MySqlClient.MySqlPool.GetPooledConnection ()
    MySql.Data.MySqlClient.MySqlPool.GetConnection ()

    What should I do? Do You have any ideas?
     
  12. thylaxene

    thylaxene

    Joined:
    Oct 10, 2005
    Posts:
    713
    did you put the DLLs in the plugins folder in your assets folder? If you don't have one just create a folder called "Plugins" and put all you DLLs in there.

    Cheers.
     
  13. Ekzuzy

    Ekzuzy

    Joined:
    May 28, 2008
    Posts:
    34
    Yup, I've tested everything. In assets folder, then in plugins subfolder. Even tested copying libraries in standalone build to different folders and subfolders. Nothing helped... I'm starting to think that this error has nothing to do with CodePage but rather with libraries versions or something completly other, but surely not connected with CodePage.
     
  14. monark

    monark

    Joined:
    May 2, 2008
    Posts:
    1,567
    Did you ever get this working?
     
  15. duke

    duke

    Joined:
    Jan 10, 2007
    Posts:
    763
    This works fine and dandy for me (Unity 2.5, WinXP). I'm a bit perplexed as to why sqlite doesn't have a boolean type though..
     
  16. monark

    monark

    Joined:
    May 2, 2008
    Posts:
    1,567
    Make sure to try it on Vista too that's where I hit issues.
     
  17. chano

    chano

    Joined:
    Feb 11, 2009
    Posts:
    7
    I have this running under Vista.
    How I did it:
    Copy System.Data.dll and Mono.Data.SqliteClient.dll from C:\Program Files\Unity\Editor\Data\Frameworks\Mono.framework to your projects Assets/Plugins folder.

    Go to http://www.sqlite.org/download.html and get sqlitedll-3_6_12.zip, extract and put the dll in the Assets/Plugins folder.

    Add this to your script:
    using System.Data;
    using Mono.Data.SqliteClient;

    I had some issues at first as well, I think I was grabbing and importing Mono.Data.Sqlite.dll instead of Mono.Data.SqliteClient.dll.

    Confirmed working on Vista anyway.
     
  18. monark

    monark

    Joined:
    May 2, 2008
    Posts:
    1,567
    No I was using Mono.Data.SqliteClient.dll.

    What I found was it seem to work, but intermittently it would hang the exe. I wonder if the dll you have is a newer one.

    [edit] just checked, it does look like a nerwer build, hopefully the issues have now been fixed.
     
  19. chano

    chano

    Joined:
    Feb 11, 2009
    Posts:
    7
    Ok, I haven't tested this for a long time, just verified that I got it working. Ok here's to hoping it won't hang the exe then! :wink:
     
  20. chano

    chano

    Joined:
    Feb 11, 2009
    Posts:
    7
    Testing this on Vista 32 bits does indeed show that there are problems running sqlite on Vista.
    XP works fine, testing the same build on Vista crashes the application.
    Anyone solved this problem?
     
  21. col000r

    col000r

    Joined:
    Mar 27, 2008
    Posts:
    682
    Any update on this? Did 2.6 change anything? If I use SQLite will it work in builds on all platforms?
     
  22. Gaiyamato

    Gaiyamato

    Joined:
    Nov 19, 2009
    Posts:
    95
    Reading over this I am completely lost. I downloaded SQLite but the dll file has different names than you guys mentioned here and I cannot find any way to access it at all.
    I have it in my plugins folder.
    Also where do I place the .def file??

    Are you guys talking about different software here?
     
  23. Gaiyamato

    Gaiyamato

    Joined:
    Nov 19, 2009
    Posts:
    95
    Wait.. I have 2.6 Indie.. NM plugins wont work for me. :(
     
  24. DylanWest

    DylanWest

    Joined:
    Sep 30, 2009
    Posts:
    32
    So you mean that there's no way to get sqlite to work in a standalone build if you only have 2.6 Indie? I hope not.
     
  25. Gaiyamato

    Gaiyamato

    Joined:
    Nov 19, 2009
    Posts:
    95
    Yeah I was informed while I was trying to do all of this that plugins don't work in the indie version. Only simple files can be run, not things like sqlLite. Which means we are stuck with the crappy networking and data management that unity has by default.
    Though someone did make an ok networking tool using the scripting which will run inside unity. Allows me to make a proper server in python/java/c++ to communicate to. So that is what I am doing.
    With that I can then use mySQL on the server end no worries, and I have worked out a way to use hash tables to store a lot of database data in a relevant manner and I have been developing a method for minimal client queries to ensure a decent speed.

    The IP-Networking script is floating about on the forums here somewhere.
     
  26. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,601
    You have access to System.Net and as such to the full TCP and UDP socket capabilities -> full network capabilities
     
  27. Gaiyamato

    Gaiyamato

    Joined:
    Nov 19, 2009
    Posts:
    95
    True.
    I meant the pre-made out-of-the-box networking tools that are part of the Unity API.
     
  28. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,601
    Which is wrong, as you are not stuck, you can implement your own, use Lidgren, Photon, Neutron, SFS Pro, ...


    The only thing you can't do is using unmanaged code, but thats it.
    so there really is no restriction, its just a matter of wanting to solve it ;)
     
  29. Gaiyamato

    Gaiyamato

    Joined:
    Nov 19, 2009
    Posts:
    95
    You really are stuck building from the ground up with indie version as you cannot use the plugins.
    Also I have found it a huge pain to get things like NetworkView to talk to custom networking scripts. So wound up making all of my own stuff from scratch.

    If I had Pro it would be a snap. Pro is definately worth buying.
    But if your a small indie developer or just someone fiddling about (or using it in a University course like we did) the Networking is a nightmarish level of work. There might as well not even be anything in the engine for it.
    In fact the Uni course supervisor told us not to bother with Networking in Unity as it is far too much work unless you have a team, a good budget and heck of a lot of time. lol.
    He was right. Those of us who tried to make some simple networking functionality for our games were all driven to madness with the crazy S*** Unity does.
    The Networking examples not only don't show you how to use it correctly but none of the examples even work. lol.

    It has taken me 6 months of work to get vaguely stable networking that can do what the Unity Networking claims it does and actually get decent network speeds. I'm still not happy with it.

    So yeah, networking in Unity is a nightmare. So sure, while you do have sockets to play with, it is a long way from working and for most of the indie developers is just not worth the hassle.
     
  30. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,601
    gave it a short go

    first of all: the code can never work.
    it references a file that won't be present as such on the iphone at all.
    unhappily b5 seems to have an issue with the streaming asset folder (its not included in the build)

    second: there seem to be some other probs.
    I'm right now uploading a new archive with a u3 b5 iphone targeted project and xcode project (you need to drop the db manually over there) working

    file is at http://mirror.dreamora.net/sqlitetest_u3_iphone.zip

    New location of the file is http://dl.dropbox.com/u/83083/Unity/sqlitetest_u3_iphone.zip
     
    Last edited: Apr 11, 2012
  31. ibyte

    ibyte

    Joined:
    Aug 14, 2009
    Posts:
    991
    Hi dreamora,

    First off - thank you very much for providing a working sample.

    It is interesting that at this stage (Unity3 Beta 5) for success on the iPhone you need to use

    Mono.Data.Sqlite

    vs

    Mono.Data.SqliteClient

    that most other posts up to this point reference.

    iByte
     
  32. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,601
    Yeah I first had the same error you mentioned above and went on from there on.

    I primarily put it in because over the course of testing the issue, I've experimented with DbLinq along other things and all had it.
    But thinking about it it makes sense. you can't use a client to a db system you don't even have present.
     
  33. ibyte

    ibyte

    Joined:
    Aug 14, 2009
    Posts:
    991
    Mono.Data.SQLiteClient doesn't seem to be required (at least not to open or read reecords).

    I removed the dll and things still worked.

    iByte
     
  34. Barbur

    Barbur

    Joined:
    Oct 30, 2009
    Posts:
    160
    Hello all,

    I have been trying all the steps you are well explaining on the posts but with no luck to get this working. I am working with Unity iphone Advanced version 1.7.0f1. On the Unity console when I try to use some Sqlite functions I get this error:

    I don't know what anything els to try. My last option is to use that weird php trick to update and get values from a database.
    Any idea what this error means?

    Thanks a lot :)

    EDIT 1: Ok I finally took the dlls from another folder inside Unity.app and now works fine. I took them from Unity.app/Contents/Frameworks/MonoiPhone.framework/

    -------------------------------------------------------------------------------------

    EDIT 2: Now I get another error but only when I generate the XCode project. I get this error:
    I am trying to add the System.Data.SqlClient resource into my project. On the editor works well, connects to the DB without a problem. The problem is on the generate XCode project process. Any idea someone?
     
    Last edited: Dec 1, 2010
  35. Barbur

    Barbur

    Joined:
    Oct 30, 2009
    Posts:
    160
    Ok now I changed from Unity 1.7 to the last Unity version 3.1.0 and this still does not work but at least the errors that I am getting are different. The problem now is on the editor. I cannot create a good connection string. I checked your code Dreamora and you are using a URI, so this creates a db file on your local machine. What I need is to connect to an existing online data base on a static IP. This is my connection string:

    Where server is the static IP where I stored my database.

    I get the error: System.InvalidOperationException:Invalid connection String. This connection string worked with the Unity 1.7 version. Any idea what can I do?
     
  36. zumwalt

    zumwalt

    Joined:
    Apr 18, 2007
    Posts:
    2,237
    Because Mono.Data.Sqlite is what you need and the connection string is
    And you need to have the following files in your Plugins folder:

     
  37. ultrasahand

    ultrasahand

    Joined:
    Apr 13, 2011
    Posts:
    1
    Hello guys , This is my first post , I've been reading unity3d forum for along time now , I have two question if anyone could help me ...

    1. How do I determine how long the query takes to select or insert a row.
    2. I know the whole idea of closing db connection to free physical memory but why do we use dbcon.Close() since we going to need the connection again and again for later in the game , I mean is there going to open a dbcon.Open( for every query I use ????

    I started to create my own gta style gaming and I'm far ahead in coding (thanks to you guys), I'm stuck at way-finding which I hope sqlite is the best choice for generating traffic jam
     
  38. namoricoo

    namoricoo

    Joined:
    Apr 14, 2011
    Posts:
    534
    Brother ultrasahand, do a search on tob for UniSqlite, and that will solve all of the problems you are having. You don't have to keep track of those things, it will do it all for you.
     
  39. sonicviz

    sonicviz

    Joined:
    May 19, 2009
    Posts:
    1,043
    Hi Dreamora,
    Thanks for this iPhone sqlite demo. I just started looking at how to integrate a db into Unity3D and this is really helpful.
    I'm running on latest Unity 3.3 and it runs fine on a mac in the editor.

    Not sure I quite get the process for building the xcode project and getting the final target build working though.
    In xcode v4.0.1 w/ iOS sdk 4.3 I get an error with RegisterMonoModules.h no such file or directory

    Any quick clues?

    Cheers,
    pc
     
  40. GamerUnity

    GamerUnity

    Joined:
    Apr 21, 2011
    Posts:
    12
    I have a problem with SQLite+Android:
    It works on the "Unity Editor" olso on "the desktop" version of my project but when i turn it on my android device i dont get the data
    I dont know what is the problem!!!!!!!!!!!!!!!
    It must some changes fot the script or it is the same??
     
    Last edited: Apr 24, 2011
  41. Gillissie

    Gillissie

    Joined:
    May 16, 2011
    Posts:
    172
    I think I have what I need to get this working, but there is no explanation about where the database file needs to be located in the project in order to connect to it. I have tried putting it in the root of the Assets folder and using just the filename in the connection string, but no luck. Someone must know how to do this. Thanks.

    [Edit] Nevermind. I just realized that it goes OUTSIDE the Assets folder. I guess you have to manually include it with the build.
     
    Last edited: May 16, 2011
  42. Alexis_87

    Alexis_87

    Joined:
    Jul 13, 2011
    Posts:
    6
    Hello everyone,
    If someone can tell me where to put the database (in which folder) and where they put the dll (in which folder), to compile on IOS?
     
  43. Rustam-Ganeyev

    Rustam-Ganeyev

    Joined:
    Jul 18, 2011
    Posts:
    29
    put dlls to Assets/Plugins folder. You can put your database wherether you want. As for me, i do smth like this:
    Code (csharp):
    1. path = Application.persistentDataPath + "dbname.db";
    that's it.
     
  44. Alexis_87

    Alexis_87

    Joined:
    Jul 13, 2011
    Posts:
    6
    Okay, i put the dlls in the Assets/Plugins folder and the database in project_name folder.
    When I try to build as IOS platform it return me an error.

    Maybe am I wrong with the libraries imports?

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

    function Start () {

    connectionString = "URI=file:Assets/gate.sqlite";
    dbcon = new SqliteConnection ( connectionString );

    dbcon.Open();
    ...
    ...
    }
     
  45. rambramdt

    rambramdt

    Joined:
    Jul 15, 2011
    Posts:
    14
    This is a MonoDroid issue. I have given up a while back and started using Siaqodb. It is an object database and not free, but its development team made it available specifically for Unity. I am glad they did.
     
  46. Waro

    Waro

    Joined:
    Jun 22, 2011
    Posts:
    10
    In IOS try

    connectionString = "URI=file:" + Application.dataPath.Replace("/Data","") +"/" + myDataBase.sqlite;

    And add the db in xcode project
     
  47. Waro

    Waro

    Joined:
    Jun 22, 2011
    Posts:
    10
    In IOS try

    connectionString = "URI=file:" + Application.dataPath.Replace("/Data","") +"/" + myDataBase.sqlite;

    And add the db in xcode project
     
  48. niks07

    niks07

    Joined:
    Oct 11, 2011
    Posts:
    11
    hello all.

    i am new in unity.

    i want to create a game in which i need to create local database.
    i read many a posts on unity/google but i cant find anything very clear and fast..

    so any one can tell me the clear steps for creating a local database in unity-game for iphone/ipad...???

    here is the code that i used but it only creates the database file while i run it on MAC..

    //==============

    /*--- this code i am using to create database------*/

    import System.Data; // we import our data class
    import Mono.Data.Sqlite; // we import sqlite

    public class dbAccess
    {
    // variables for basic query access
    var connection : String;
    var dbcon : IDbConnection;
    var dbcmd : IDbCommand;
    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 basic 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
    }
    }

    // This returns a 2 dimensional ArrayList with all the
    // data from the table requested
    function ReadFullTable(tableName : String)
    {
    var query : String;
    query = "SELECT * FROM " + tableName;
    dbcmd = dbcon.CreateCommand();
    dbcmd.CommandText = query;
    reader = dbcmd.ExecuteReader();
    var readArray = new ArrayList();
    while(reader.Read())
    {
    var lineArray = new ArrayList();
    for (var i : int = 0; i < reader.FieldCount; i++)
    lineArray.Add(reader.GetValue(i)); // This reads the entries in a row
    readArray.Add(lineArray); // This makes an array of all the rows
    }
    return readArray; // return matches
    }

    // This function deletes all the data in the given table. Forever. WATCH OUT! Use sparingly, if at all
    function DeleteTableContents(tableName : String)
    {
    var query : String;
    query = "DELETE FROM " + tableName;
    dbcmd = dbcon.CreateCommand();
    dbcmd.CommandText = query;
    reader = dbcmd.ExecuteReader();
    }

    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 + " " + colType;
    }
    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;
    }
    query += ") VALUES (" + values[0];
    for(i=1; i<values.length; i++)
    {
    query += ", " + values;
    }
    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;
    }
    query += ")";
    dbcmd = dbcon.CreateCommand();
    dbcmd.CommandText = query;
    reader = dbcmd.ExecuteReader();
    }

    // This function reads a single column
    // wCol is the WHERE column, wPar is the operator you want to use to compare with,
    // and wValue is the value you want to compare against.
    // Ex. - SingleSelectWhere("puppies", "breed", "earType", "=", "floppy")
    // returns an array of matches from the command: SELECT breed FROM puppies WHERE earType = floppy;
    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;
    }
    }

    //============

    /*--- this code i am using to get my GUI on screen------*/


    /* Script for testing out SQLite in Javascript
    This script is a GUI script - attach it to your main camera.
    It creates/opens a SQLite database, and with the GUI you can read and write to it.
    */

    // This is the file path of the database file we want to use
    // Right now, it'll load TestDB.sqdb in the project's root folder.
    // If one doesn't exist, it will be automatically created.

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

    // This is the name of the table we want to use
    public var TableName : String = "TestTable";
    var db : dbAccess;

    function Start()
    {
    // Give ourselves a dbAccess object to work with, and open it
    db = new dbAccess();
    db.OpenDB(DatabaseName);
    // Let's make sure we've got a table to work with as well!
    var tableName = TableName;
    var columnNames = new Array("firstName","lastName");
    var columnValues = new Array("text","text");

    try
    {
    db.CreateTable(tableName,columnNames,columnValues);
    }

    catch(e)// Do nothing - our table was already created
    {
    //- we don't care about the error, we just don't want to see it
    }
    }

    // These variables just hold info to display in our GUI
    var firstName : String = "First Name";
    var lastName : String = "Last Name";
    var DatabaseEntryStringWidth = 100;
    var scrollPosition : Vector2;
    var databaseData : ArrayList = new ArrayList();

    // This GUI provides us with a way to enter data into our database
    // as well as a way to view it

    function OnGUI()
    {
    GUI.Box(Rect (25,25,Screen.width - 50, Screen.height - 50),"");
    GUILayout.BeginArea(Rect(50, 50, Screen.width - 100, Screen.height - 100));

    // This first block allows us to enter new entries into our table
    GUILayout.BeginHorizontal();
    firstName = GUILayout.TextField(firstName, GUILayout.Width (DatabaseEntryStringWidth));
    lastName = GUILayout.TextField(lastName, GUILayout.Width (DatabaseEntryStringWidth));
    GUILayout.EndHorizontal();

    if (GUILayout.Button("Add to database"))
    {
    // Insert the data
    InsertRow(firstName,lastName);
    // And update the readout of the database
    databaseData = ReadFullTable();
    }
    // This second block gives us a button that will display/refresh the contents of our database
    GUILayout.BeginHorizontal();
    if (GUILayout.Button ("Read Database"))
    databaseData = ReadFullTable();
    if (GUILayout.Button("Clear"))
    databaseData.Clear();
    GUILayout.EndHorizontal();

    GUILayout.Label("Database Contents");
    scrollPosition = GUILayout.BeginScrollView(scrollPosition, GUILayout.Height(100));
    for (var line : ArrayList in databaseData)
    {
    GUILayout.BeginHorizontal();
    for (var s in line)
    {
    GUILayout.Label(s.ToString(), GUILayout.Width(DatabaseEntryStringWidth));
    }
    GUILayout.EndHorizontal();
    }

    GUILayout.EndScrollView();

    if (GUILayout.Button("Delete All Data"))
    {
    DeleteTableContents();
    databaseData = ReadFullTable();
    }

    GUILayout.EndArea();
    }

    // Wrapper function for inserting our specific entries into our specific database and table for this file
    function InsertRow(firstName, lastName)
    {
    var values = new Array(("'"+firstName+"'"),("'"+lastName+"'"));
    db.InsertInto(TableName, values);
    }

    // Wrapper function, so we only mess with our table.
    function ReadFullTable()
    {
    return db.ReadFullTable(TableName);
    }

    // Another wrapper function...
    function DeleteTableContents()
    {
    db.DeleteTableContents(TableName);
    }

    Thanks
     
    Last edited: Oct 12, 2011
  49. techmage

    techmage

    Joined:
    Oct 31, 2009
    Posts:
    2,111
    Could anyone give me an update on the state of sqlite on Unity iOS Pro? Does it work well, reliably? Or would I be better off using a objective c sqlite library and writing a plugin for it to work with unity?
     
  50. Ntero

    Ntero

    Joined:
    Apr 29, 2010
    Posts:
    1,436
    Unity iOS can handle SQLite even with Byte-Code Stripping on.

    There are some things you need however
    1)
    Copy (...Unity.app/Contents/Frameworks/Mono/lib/mono/unity/Mono.Data.Sqlite.dll) to ([ProjectFolder]/Assets/Plugins/Mono.Data.Sqlite.dll) (If you want to not use the .NET subset use the folder 2.0 instead of unity

    2)
    Create a file in assets called 'link.xml' and inside that write:
    Code (csharp):
    1.  
    2. <linker>
    3. <assembly fullname="mscorlib">
    4. <type fullname="System.Globalization.CultureInfo" preserve="all"/>
    5. </assembly>
    6. </linker>
    7.  
    Then you can start including Mono.Data.Sqlite and run under the .NET Subset with Byte Code Stripping on. There are about 5 other required DLLs but they should manually include. If not every time you see an error about a DLL not found put it in plugins from the same Unity.app folder.

    Edit: The extra DLLs that should be added implicitly from the Unity Folder: System.Data, System.Core, System.Transactions, System.Xml.

    So far it's been working for us like a charm for months.
     
    Last edited: Oct 19, 2011
unityunity