Tag: MySQL

How to add MySQL capabilities to a Unity application

In this short tutorial, I’m going to explain how you can add MySQL capabilities to your Unity application. This will allow you to connect to a MySQL database from your application and fill the database with some data (e.g. users’ scores). To do so, you first of all need to obtain a MySQL Connector. Not every version of the Connector, however, will work with Unity. To obtain the Connector do the following steps:

  1. Go to https://dev.mysql.com/downloads/connector/net/
  2. Click on the “Looking for previous GA?” link.
  3. Select Version: 6.7.9 (This is the last version that contains the library compiled with the .Net 2.0 Framework)
  4. Select Platform: .Net & Mono
  5. Click on the “Download” button.
  6. Extract the files to a directory of your choice.

Next, we will create a new database schema with a table that can store string values. You can do this using MySQL Workbench.

  1. Open MySQL Workbench
  2. Connect to the local database.
  3. Create a new schema in the connected server and call it unity.
  4. Set unity schema as default schema by clicking on it with the right mouse button and selecting “Set as Default Schema”.
  5. Create a new table in the unity schema, call it scores, and define 3 columns: idscores as INT, playername as VARCHAR(45), and playscore as INT.
  6. Set the following flags for the idscores column: Primary Key, Not Null, and Auto Increment.
  7. Set the following flag for the columns playername and playerscore: Not Null.

Now we need a new user who will have privileges to write in and read from the unity schema.

  1. Click, in the navigator panel of the MySQL Workbench, on the User and Privileges link.
  2. Click on the button “Add Account”.
  3. Define a username of your choice.
  4. Leave Authentification Type as Standard (I had a problem to connect to the DB from a Linux machine while using other Authentification Type)
  5. Leave Limit to Hosts Matching as %. This will allow you to connect from any machine.
  6. Define a password of your choice.
  7. Leave Account Limits and Administrative Roles settings unchanged.
  8. On the Schema Privileges panel click on the button “Add Entry…”.
  9. Activate the option “Selected schema”, select the unity schema, and click on “Ok”.
  10. Next, provide the object rights: SELECT and INSERT for the entry and click on “Apply”.

Finally, we will write a small Unity application that will connect to a MySQL database and write a test-string into a table.

  1. Create a new Unity project
  2. Drag-and-Drop the MySQL.Data.dll file into the Assets folder (You are interested in the library that lies in the v2.0 directory)
  3. Next, you have to copy the System.Data.dll file into the Assets folder. If you work on Windows, then you can find the file in C:\Program Files\Unity\Editor\Data\Mono\lib\mono\2.0
  4. Finally, create a new C# script and put the following code into it:
using UnityEngine; 
using MySql.Data.MySqlClient; 

  public class testSQL : MonoBehaviour { 
    MySqlConnection connection; 

    // Use this for initialization 
    void Start () { 
      SetupSQLConnection(); 
      TestDB(); 
      CloseSQLConnection(); 
    } 

    private void SetupSQLConnection() { 
      if (connection == null) { 
        string connectionString = "SERVER=xxx.xxx.xxx.xxx;" + "DATABASE=unity;" + "UID=unityuser;" + "PASSWORD=yourpassword;"; 
        try { 
          connection = new MySqlConnection(connectionString); 
          connection.Open(); 
        } catch (MySqlException ex) { 
          Debug.LogError("MySQL Error: " + ex.ToString()); 
        } 
      } 
    } 

    private void CloseSQLConnection() { 
      if (connection != null) { 
        connection.Close(); 
      } 
    } 

    public void TestDB() { 
      string commandText = string.Format("INSERT INTO scores (playername, playerscore) VALUES ({0}, {1})", "'megaplayer'", 10); 
      if (connection != null) { 
        MySqlCommand command = connection.CreateCommand(); 
        command.CommandText = commandText; 
        try { 
          command.ExecuteNonQuery(); 
        } catch (System.Exception ex) { 
          Debug.LogError("MySQL error: " + ex.ToString()); 
        } 
      } 
     } 
   }

Add an empty GameObject to your scene and put the script on it. Now each time you start the game a new entry will be added to your database. You can check it in the MySQL Workbench by clicking with the right mouse button on the table, selecting “Select Rows – Limit 1000”, and clicking in the appeared window on the lightning symbol.

%d