Reading database and/or spreadsheets

Hi,
How do you load a database file or spreadsheet into unity?
Can you use SQL at all?

I'm coming from using Dxstudio where you can import a database as an asset and then query it using SQL commands.

I've had a look through the docs but I can't see anything obvious about this. The closest I've found is mention of parsing XML using WWW class.
I really don't need to load it at runtime the file doesn't need to be written too, I just need to read a bunch of data into a set of arrays. Something similar to the textAsset would do, but a bit more sophisticated.

 

You can use any Mono DLL you can find that can access databases. Maybe there is someting in System.Data?

This will increase your webplayer though as the dll will have to be included in the build.

Regards,
Marc

 

Sorry is System.Data a unity command, i don't see it in the help?

 

Generating source code from your spreadsheet data is a technique that will often solve this kind of problem.

Add a column to the sheet which is numbered from zero to the length of the array. Then you can use either the spreadsheet's text functions or a text editor's grep find-and-replace to change things like:-

0 "apple"
1 "banana"

...into:-

arr[0] = "apple";
arr[1] = "banana";

...etc. This kind of thing always sounds a bit low-tech, but it is probably quicker than finding and coding for a data handling library, and it will avoid the overhead if you are targetting the web player.

 

No its a Mono .Net packages.

 

See here: http://go-mono.com/docs/index.aspx

 

i spent my whole last $%#^%$*^ weekend figuring out how to connect to a MySQL database, so it would be cruel not to share it. took me ages to find out the right DLLs and figure out the right connection strings. :? the mono documentation really sucks and so do microsoft's .NET docs, which doesn't really help.

anyway... you'll need to include System.Data.dll and MySql.Data.MySqlClient.dll in your assets folder, or at least i had to anyway. i've attached them in case you need them.

Code (csharp):

1. using UnityEngine;
2. using System;
3. using System.Collections;
4. using System.Data;
5. using MySql.Data.MySqlClient;
6.  
7. public class SQLDatabase : MonoBehaviour {
8.     // Global variables
9.     private static IDbConnection dbConnection;
10.    
11.     // Initialisation
12.     public void Start() {
13.         openSqlConnection();
14.     }
15.    
16.     // On quit
17.     public void OnApplicationQuit() {
18.         closeSqlConnection();
19.     }
20.    
21.     // Connect to database
22.     private static void openSqlConnection() {
23.         string connectionString = "Server=localhost;" +
24.             "Database=yourDatabaseName;" +
25.             "User ID=yourUserId;" +
26.             "Password=yourPassword;" +
27.             "Pooling=false";
28.         dbConnection = new MySqlConnection(connectionString);
29.         dbConnection.Open();
30.         Debug.Log("Connected to database.");
31.     }
32.    
33.     // Disconnect from database
34.     private static void closeSqlConnection() {
35.         dbConnection.Close();
36.         dbConnection = null;
37.         Debug.Log("Disconnected from database.");
38.     }
39.  
40.     // MySQL Query
41.     public static void doQuery(string sqlQuery) {
42.         IDbCommand dbCommand = dbConnection.CreateCommand();
43.         dbCommand.CommandText = sqlQuery;
44.         IDataReader reader = dbCommand.ExecuteReader();
45.         reader.Close();
46.         reader = null;
47.         dbCommand.Dispose();
48.         dbCommand = null;
49.     }
50. }

you can iterate through returned rows with:

Code (csharp):

1. while (reader.Read()) {
2.     string myVar = (string) reader["columnName"];
3. }

let me know if any of this is unclear. i'm still trying to switch it to integrated security so i don't have to hard code my password, but MySql.Data.MySqlClient doesn't seem to like the IntegratedSecurity keyword.

 

Cheers for all the help. I'll have to look into all this. Its somewhat more hardcore than I'd hoped. Maybe this is something they'll think about simplifying in future versions.

@andeeee, nice hack, that might do to get started with some simple cases.

 

Much less hardcore version:

If you stick this in an script called Foo.js, then any other object in the scene can call Foo.SaveTextFile(...) and Foo.LoadTextFile(...) as desired.

Parsing spreadsheets or Windows 3.x style .ini files is pretty trivial using Mono string functions.

Note this is JavaScript using Mono (so it may be useful for understanding how to use Mono from JS).

And finally, this is part of my console code which basically provides a console that, among other things, provides a global persistent data store available to everything in the game (making handling "inventories" and the like pretty trivial). I think I posted an early version of this somewhere...

Code (csharp):

1. import System;
2. import System.IO;
3.  
4. static function SaveTextFile ( fileName : String, fileContent : String ) {
5.     var sw : StreamWriter = new StreamWriter ( Application.dataPath + "/" + fileName );
6.     sw.Write ( fileContent );
7.     sw.Close ();
8.     print ( "Saved " + Application.dataPath + "/" + fileName );
9. }
10.  
11. static function LoadTextFile ( fileName : String ) {
12.     var t : String = "";
13.     var line : String = "-";
14.     try {
15.         var sr : StreamReader = new StreamReader ( Application.dataPath + "/" + fileName );
16.         line = sr.ReadLine();
17.         while (line != null) {
18.             t += line;
19.             line = sr.ReadLine();
20.             if ( line != null ) {
21.                 t += "\n";
22.             }
23.         }
24.         sr.Close();
25.         print ( "Loaded " + Application.dataPath + "/" + fileName );
26.     }
27.     catch (e) {
28.         print ( "Error: " + Application.dataPath + "/" + fileName );
29.     }
30.     return t;
31. }

 

Nekharoth, this is great! You might have saved me from a weeks work right there =)

I too have to place the DLL files directly in the asset folder in order to get it working. I wish I could create a folder for DLL files inside the asset folder, but Unity can't find the files if I try.

~ce

 

That's weird - this does work for me. I have all DLLs in a folder of a subfolder somehere hidden real deep ;-)

My structure is: MyProject/Assets/JCsLibrary/Assemblies/*.dll - and that works just fine.

 

Oh, my mistake. It works for me as well now. Very nice!

 

Also you can use SQLite within Unity... very cool I must say:

http://forum.unity3d.com/viewtopic.php?t=8432&highlight=sqlite

Also there is a SQLite manager (use it to create your DBs) available for Firefox:

https://addons.mozilla.org/en-US/firefox/addon/5817

Cheers.

 

Nekharoth,

I've posted question in other topic and then found this one. I'm also using MySQL, found proper dlls on the Internet. I've added them to my assets folder in my project and everything works fine but when I build it to standalone it doesn't work. Neither on MacOS, neither on Windows. In Windows 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.TryToGetDriver ()

I don't know how to resolve this problem. I've tried libraries that You have place in this topic, other from Internet.
It is strange that I was using this library in Windows, to build .NET dll in Visual Studio. Everything worked in my test project. The same libraries were working in editor, in Unity. But not in compiled standalones. I don't know what is going on. Unity is great but sometimes working with different libraries and standalones is painfull. Very painfull :x

 

Ekzuzy - that issue is at the top of my list of unresolved bugs, so I unfortunately I can't be of much help to you. I'll be sure to let you know if I fix it, but I'm a little stumped too - this goes beyond my field of knowledge.

It would be great if someone from Unity could advise how we can resolve this issue. 8)

 

I too ran into the SQL standalone problem. Did anyone solve this?

~ce

 

I still haven't resolved this issue either. I was hoping that someone with more Mono knowledge than myself could clarify what was causing the problem...

 

Are you sure you're getting the same DLL in the standalone as the one the editor uses? I once ran into similar problem, and the cause of the problem was that I had one version of the DLL in the project root folder (not in the assets folder - assuming it would be ignored if I put it there), and I had another version of the DLL in the assets folder.

I don't exactly remember which way around it was - but either the editor used the version in the project folder (instead of using the version in the asset folder); or during builds, the version from the project was copied into the standalone instead of using the one from the assets folder.

Either way, that's a while ago and was filed as a bug that I think got fixed (just never had time to re-test this ... also because when I had this problem I got really ugly crashes and didn't want to go back there ;-) ).

But stuff working in the editor and not in the standalone very likely could have to do with different DLLs being used (you could probably also check the DLLs included with Unity and make sure you're using those instead of others - the "other" problem I had was that I had included a DLL from a more recent Mono version instead of using the one that came with Unity; that's what was causing me those really ugly crashes).

 

I did try this eventually and although it ran fine on Mac and XP standalones Vista caused problems and was never stable. Haven't tried again yet with 2.5, though there is no obvious reason why it would have improved.

 

If anyone is using Windows and need a nice light DB, check out VistaDB. It doesn't run as a service, just a DLL reference and it comes with .NET Mono assemblies. You can reference it directly from Unity mono code or build backend .NET classes to act as your DAL to it.

I use the free Express edition but eventually I'll break down and buy the full version.

 

For some reason codepage 1252 is not supported in standalone builds, only in the editor.
Solved this problem by:
* downloading and installing the mono installer from http://www.go-mono.com/mono-downloads/download.html
* find the dll's called I18N.dll and I18N.West.dll
* put them in your projects Assets folder
* build the standalone and it should work


We checked the default encoding in the editor and in the Windows standalone like this: Debug.Log(Encoding.GetEncoding(0).ToString());

Editor output: I18N.West.CP1252
Win standalone output: System.Text.UTF8Encoding

Feels weird that it differs, anyone have any thoughts on this?

 

Thanks for the solution chano! It worked here like a charm.

This also works beautifully if you simply copy I18N.dll and I18N.West.dll from the mono framework directory of Unity (C:\Program Files\Unity\Editor\Data\Frameworks\Mono.framework).
No need to get the newest version of mono for this.

About the test of printing the encoding:
Both in the editor and in the standalone build, with the DLLs from Unity's framework folder I get:
I18N.West.CP1252

 

could someone post a javascript version of this code, if possible at all?

 

I'm unable to connect to the online MySql Database, it says Access denied for user@localhost. It is working fine for my localdatabase, both database versions are same.

 

*Bump*

Also looking for a JS sample using these DLLs to connect/query MySQL, if possible.

 

sounds like the required remote access is not configured.
by default mysql / sql dbs in generally do not allow remote access.
exceptions have to be created on a per ip - user base

 

This script was great, I was connected and up in running in 5 minutes!

In regards to getting things running on MySQL remotely.

If you setup a utility like Navicat (www.navicat.com) on the machine (via localhost)

You can click the "Manage users" and set the "Hostname" value on the account to % This will allow the user to connect from any IP or any hostname ("%" is the wildcard character in MySQL).

 

Ok, I made a Javascript version:

Code (csharp):

1.  
2.  
3. // Global variables
4. private static var dbConnection : MySql.Data.MySqlClient.MySqlConnection;
5.  
6. function Start()
7. {
8.     OpenSqlConnection();
9. }
10.  
11. function OnApplicationQuit()
12. {
13.     CloseSqlConnection();
14. }
15.  
16.  
17. // Connect to database
18. function OpenSqlConnection()
19. {
20.     var connectionString = "Server=localhost;" +
21.         "Database=YourDatabase;" +
22.         "User ID=YourUserID;" +
23.         "Password=YourPassword;" +
24.         "Pooling=false";
25.     try
26.     {
27.         dbConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
28.         dbConnection.Open();
29.     }
30.     catch(err)
31.     {
32.         Debug.LogError(err);
33.     }
34.     Debug.Log("Connected to database.");
35. }
36.  
37. // Disconnect from database
38. function CloseSqlConnection()
39. {
40.     dbConnection.Close();
41.     dbConnection = null;
42.     Debug.Log("Disconnected from database.");
43. }
44.  
45.  
46. // MySQL Query
47. function DoQuery(sqlQuery : String)
48. {
49.     var dbCommand : System.Data.IDbCommand = dbConnection.CreateCommand();
50.     dbCommand.CommandText = sqlQuery;
51.  
52.     var reader : System.Data.IDataReader = dbCommand.ExecuteReader();
53.     reader.Close();
54.     reader = null;
55.  
56.     dbCommand.Dispose();
57.     dbCommand = null;
58. }
59.  

 

...underdog, and others.

Now I am confused... It could be a matter of context.
My Unity App is web based running on a HOST Server and a separate SQLServer Database Server.

To access a database:
I thought the UnityScript Application.ExternalCall had to be used to call a function in the BrowserBased JavaScript(DOM) on the HTML page the player is running on, which in turn would trigger a function in the code-behind page(C#,VB, ...) that would return the data query result or write the database.

I assumed the BrowserBased JavaScript(DOM) function would have to be an async callback(to prevent a page redraw).

On a read query(or write confirmation) I am not sure how the data would be retrieved by the Unity Player at that point... perhaps triggering the SendMessage() Unity Plugin function.

...and what I see here, shows that if you include in the Unity Build the correct library(DLL) for your database type(SQLServer in my case), you can access data directly via UnityScript(You call it Javascript in your post), …and I assume that there is no page redraw if direct to the player, no need for an async callback.
Too easy?!

Can someone clear my fog. Are both methods valid for a Web based 3 tiers setup?
Is there a potentially slow lengthy load of the player if it includes the SQL DLLs?
Please comment. Thank you.

 

If you like the idea of offering a wildcard distribution wide account so they can access your DB you could do that, at least with those databases which don't require native code dlls (which means you can't use mysql).

Don't forget, the webplayer as well as the page are executed on the client, not your server.
Thats why you normally use WWW <-> php <-> database as the php layer runs under your control in your secured environment, remove the requirement of the wirldcard access

 

dreamora, thank you... I just came back to this thread to update my post with my findings that match your advice.

See Duck's comment in Tobias post on that URL (LOL)

http://answers.unity3d.com/question...-send-and-recieve-data-from-to-a-mysql-server

above link points to the WWW approach.
http://answers.unity3d.com/questions/3560

But, if I use the WWW GET/POST approach... does it pass data back and forth without refreshing the page the player is running on?
Data is flowing back and forth to the Unity App directly?
The HTML page the player is running on is not affected? (A refresh would be undesired as it would break the user's experience and the game would also restart)

the http:.... URL would point to a pageless/codebehind file? never done one of those, not sure how I would do that if it is the way to implement it.
.

 

Yes it does.
Similar to how you can do jquery calls from the browsers js DOM without reloading the page.

What you would have is your normal page where the player is embedded (and whatever else you want in there)

and a PHP or ASP.NET or something else page whereever else you want (optimally same domain for domain security policy reasons), to which you talk through the WWW class.

look for the highscore example posted on unifycommunity.com and other places, that should give you an idea about how the flow would look like

 

Is it worth the hassle to use SQLLite to gain speed and ease of managment? (...if SQLLite can be accessed via WWW class?)

 

JS version of the script doesn't find reference to MySql.Data and System.Data. How can i fix this?

 

Hi to all,
Is this work in iOS?
thanks in advance

 

Hi all!

I'm having this error come up, working with Unity 3.4. I just copied the two DLL's to my Assets folder, created a new C# script and pasted the code from the first page. When Unity finished re-compling the scripts, it yelled at me:

Thanks!

Update:
Nevermind!

I found a solution. I needed to change my "Player Settings-->Optimization-->Api Compatibility Level = .NET 2.0"


However, I don't quite understand how to use this. Here is some code I have. Could somebody clarify how I read the data in my database?

Code (csharp):

1.  
2. using UnityEngine;
3. using System.Collections;
4. using SQLDatabase;
5.  
6. public class AddClassData : MonoBehaviour {
7.  
8.     // Use this for initialization
9.     void Start ()
10.     {
11.         openSqlConnection();
12.         doQuery("SELECT * FROM Classes ORDER BY SubLevel, Name");
13.        
14.     }
15. }
16.  

I see the while() in the above post, but I'm not sure where to put that and how to access multiple columns.

Thank you again!

 

From what I understand of your question, you just need to read up on the basics of SQL. Try here: http://w3schools.com/sql/sql_intro.asp

 

I know how to construct a query, I am unsure of how to access that data using the:

Code (csharp):

1.  
2. SQL = SQLDatabase.doQuery("SELECT * FROM Classes WHERE PARENT_ID = pID ORDER BY SubLevel, Name;");
3.         while (reader.Read())
4.         {
5.             int ID = reader["ID"];
6.             string name = (string) reader["Name"];
7.             int parentID = reader["PARENT_ID"];
8.             int subLevel = reader["SubLevel"];
9.            
10.  

I am not sure where reader is defined, nor what type it should be. I don't know if I am allowed to type ID, parentID, and subLevel as type INT as above.

I am comfortable accessing databases from PHP,

Code (csharp):

1.  
2. $query = mysql_query ("SELECT * FROM Classes WHERE PARENT_ID = pID ORDER BY SubLevel, Name";
3. while ($result = mysql_fetch_assoc($query))
4. {
5. .....
6. }
7.  

Working with C# is pretty new to me however.

Thanks!

 

reader is an IDataReader. If you look at the sample code:

Code (csharp):

1.  
2. .
3. .
4. .
5.     public static void doQuery(string sqlQuery) {
6.         IDbCommand dbCommand = dbConnection.CreateCommand();
7.         dbCommand.CommandText = sqlQuery;
8.         IDataReader reader = dbCommand.ExecuteReader();
9.         reader.Close();
10.         reader = null;
11.         dbCommand.Dispose();
12.         dbCommand = null;
13.     }
14. .
15. .
16. .
17.  

It makes a temporary IDataReader to get any results from the query. However the sample code doesn't really do anything with it, so it was simply noted, if you want to check if it returned data you just do the Read() method on your IDataReader. You'd just have to change the code a bit to do that.

 

i am working on windows with mySql database and i need to locate my dll files (System.data MySql.data) where they are located?

 

Thanks, Thanks, Thanks!! are the best!

 

Hi, I'm having trouble with my connectionString I think, because when I run my program in unity, I get the error No such host is given. Can you maybe give an example of a connection string that doesn't use a local host server? thx

 

THANKS THANKS THANKS THANKS!!!!!!!

 

Its very simple to connect to database from unity and php as a server side language....​

Code (CSharp):

1. WWW type = new WWW ("127.0.0.1/gettype.php");
2.         while (!type.isDone) {
3.         }
4.         string itemsDataString = type.text;
5.         string[] typeItems = itemsDataString.Split (';');
6.         TypeDD.options.Clear ();// this is clearing drop down
7.         foreach (string str in typeItems) { // this is setting drop down with all the records of a particular field
8.             typeDD.options.Add (new Dropdown.OptionData (str));//filling dropdown with records
9.         }

and is connected to php side ie:-

Code (CSharp):

1. <?php
2. include_once "_connection1.php";
3.  
4.     $sql = "SELECT type_column FROM some_type";
5.     $result = $conn->query($sql);
6.     if ($result->num_rows > 0) {
7.         // output data of each row
8.         while($row = $result->fetch_assoc()) {
9.             echo ";".$row["type_column"];
10.         }
11.     }
12.     $conn->close();
13. ?>
14.  

and this is connection file (php file):-

Code (CSharp):

1. <?php
2. $conn = new mysqli("localhost", "root", "", "some_db");
3.  
4. if ($conn->connect_error) {
5.     die("Connection failed: " . $conn->connect_error);
6. }
7. ?>

 

you have given example for drop down menu can you please say me how to do same with text.
I want databases values in ui text field.
Please help me.
Thanks in advance.

 

That is very easy to do..
you just need to assign one variable for example as below

Code (CSharp):

1.  
2. using UnityEngine;
3. using UnityEngine.UI;
4. public class example : MonoBehaviour
5. {
6. Public InputField fieldOne; //Drag input field to inspector
7. void Start()
8. {
9. //Get typeItems array as per example above
10. string temp = typeItems[0];
11. fieldOne.text = temp;
12. }
13. }