Search Unity

  1. Welcome to the Unity Forums! Please take the time to read our Code of Conduct to familiarize yourself with the forum rules and how to post constructively.
  2. We have updated the language to the Editor Terms based on feedback from our employees and community. Learn more..
    Dismiss Notice
  3. Dismiss Notice

How do I connect to a MySql databse without PHP (or any middleman)

Discussion in 'Scripting' started by ccurry_unity, Jan 19, 2018.

  1. ccurry_unity

    ccurry_unity

    Joined:
    Nov 2, 2017
    Posts:
    28
    Let me preface by saying I've researched this and I fully appreciate that would be a security risk. However, this is for an internal game that will be played on our dedicated server. I would use PHP, except that we don't have it installed (and apparently will take some doing to get it going).

    So! I'm trying to figure out how to simply connect to a MySQL database we have to create a leaderboard. Trying to sort through how to add / get scores / names as well.

    Basically this without the PHP bits.

    Thanks in advance!
     
  2. GroZZleR

    GroZZleR

    Joined:
    Feb 1, 2015
    Posts:
    3,201
    Ryiah likes this.
  3. ccurry_unity

    ccurry_unity

    Joined:
    Nov 2, 2017
    Posts:
    28
    That's good to know, but I'm really looking for something without a middleman- just directly connecting to MySQL via Unity. I know this isn't generally recommended for something outward facing, but again this will be internal only on our dedicated server.
     
  4. lordofduct

    lordofduct

    Joined:
    Oct 3, 2011
    Posts:
    8,380
    MySql Connector isn't really a middleman... in that it's not another piece of software perse (well that depends on what you define software as).

    Rather it's a .net library.

    Technically you could write your own code that created the necessary data connections and pushed and pulled the data as required to the ports on which mysql runs... but you're effectively writing the MySQL Connector yourself. And why re-invent the wheel when it already exists. That's what dynamic libraries are for.
     
    DonLoquacious, Kiwasi and Ryiah like this.
  5. ccurry_unity

    ccurry_unity

    Joined:
    Nov 2, 2017
    Posts:
    28
    Ah I see. We don't have .Net on our server either. Really just looking to connect directly from Unity to a MySQL database without anything in between. Is that just not doable?
     
  6. Suddoha

    Suddoha

    Joined:
    Nov 9, 2013
    Posts:
    2,824
    I think there's a misunderstanding. The client would use the .Net library, the server has nothing to do with it. The library just provides the overall mechanisms to speak to your DB. It could as well be a Java client using purely java code, a wrapper implementation forwarding everything to native .DLLs or other libs. The server shouldn't be dependent on which language, system etc you use.
     
  7. GroZZleR

    GroZZleR

    Joined:
    Feb 1, 2015
    Posts:
    3,201
    What you're basically saying is: I'd like to build a set of character text but I can't use String or I'd like to create a collection of like-objects but I can't use List<>. The MySQL connector is just a library, like UnityEngine's library of objects, for manipulating data (your database). There is no middleman involved.
     
  8. JeffDUnity3D

    JeffDUnity3D

    Unity Technologies

    Joined:
    May 2, 2017
    Posts:
    14,446
    Can you elaborate "However, this is for an internal game that will be played on our dedicated server." Is the game running on the server? Who is playing the game, and how. I suspect you mean a multiplayer game, and your server is the network host?
     
  9. ccurry_unity

    ccurry_unity

    Joined:
    Nov 2, 2017
    Posts:
    28
    Happy to. This is a simple 1 player game that will be hosted on our servers (not available to the outside world). We have a MySQL DB set up on our servers, but don't have PHP or .NET installed. I'm trying to make a basic leaderboard so we can compete internally. My hope was that I could come up with a simple solution that connects directly to the MySQL database from Unity and gets / pushes simple name / score value pairs.

    I'll definitely take another look at that .net library.

    Apologies for my pretty rudimentary understanding of databases. Just trying to hack it all together :)

    Edit: I should clarify that this is a WebGL project if I haven't already.
     
  10. JeffDUnity3D

    JeffDUnity3D

    Unity Technologies

    Joined:
    May 2, 2017
    Posts:
    14,446
    Do you have the Unity project working currently, without the db access?
     
    LaneFox likes this.
  11. ccurry_unity

    ccurry_unity

    Joined:
    Nov 2, 2017
    Posts:
    28
  12. JeffDUnity3D

    JeffDUnity3D

    Unity Technologies

    Joined:
    May 2, 2017
    Posts:
    14,446
    Do you have the Unity project working currently, without the db access? I would suggest that you get a simple Unity game example working first, and add database connectivity to your (already working) project, and build on success rather than start with a more complicated scenario.
     
    LaneFox likes this.
  13. ccurry_unity

    ccurry_unity

    Joined:
    Nov 2, 2017
    Posts:
    28
    I do have a project without DB access. I just don't know how to add database connectivity to my existing project (without PHP). Per Suddoha it sounds like maybe GroZZler's idea could work, so I'll take a look at that. Apart from that, is there an even simpler way to do it?
     
  14. JeffDUnity3D

    JeffDUnity3D

    Unity Technologies

    Joined:
    May 2, 2017
    Posts:
    14,446
    Nope, you need to use a .NET library as suggested. Your Unity project is already using .NET
     
  15. ccurry_unity

    ccurry_unity

    Joined:
    Nov 2, 2017
    Posts:
    28
    Great I'll give it a shot- thanks!
     
  16. tr00don2018

    tr00don2018

    Joined:
    Dec 23, 2018
    Posts:
    2
    Here is a step-by-step example using Unity 2018.2 and .Net 4.x, assuming that you have already created and set your MySQL db up for access on localhost, and that you have also installed MySQL Connector NET:

    - Launch Unity and create a new project named MySQLtest;
    - Navigate to GameObject|CreateEmpty (default name = GameObject);
    - Navigate to Assets|Create|C# Script (default name = NewBehaviourScript);
    - Navigate to Edit|Project Settings|Player; in panel Other Settings, Configuration, set Scripting Runtime Version to .Net 4.x Equivalent, then in the pop-up dialog click Restart; click Save when prompted;
    - After restart, in the SampleScene panel click GameObject to select the empty created; in the Inspector panel click Add Component, Scripts, and select New Behaviour Script;
    - Copy file MySql.Data.dll from your MySQL Connector NET installation tree (e.g., C:\Program Files\Connector NET 6.6.5\Assemblies\v4.0\MySql.Data.dll) to folder MySQLtest\Assets in your Unity project;
    - Re-open MySQLtest in Unity; in Assets, double click NewBehaviourScript to open the script in Visual Studio;
    - Replace the content of file NewBehaviourScript.cs with the following code, editing text between angle brackets for your setup:
    Code (CSharp):
    1. using MySql.Data.MySqlClient;
    2. using System;
    3. using UnityEngine;
    4.  
    5. public class NewBehaviourScript : MonoBehaviour
    6. {
    7.     MySqlConnection conn = null;
    8.     MySqlCommand cmd = null;
    9.  
    10.     // Connect to db and prepare command
    11.     void Start()
    12.     {
    13.         MySqlConnection conn = new MySqlConnection("SERVER=localhost;DATABASE=<your_db_name>;UID=<your_db_user_name>;PASSWORD=<your_db_user_passwd>;");
    14.         if (conn != null)
    15.         {
    16.             conn.Open();
    17.  
    18.             cmd = conn.CreateCommand();
    19.             if (cmd != null)
    20.             {
    21.                 cmd.CommandTimeout = 1000;
    22.             }
    23.             else
    24.             {
    25.                 Debug.Log("Cannot create SQL command.");
    26.             }
    27.         }
    28.         else
    29.         {
    30.             Debug.Log("Cannot open database.");
    31.         }
    32.     }
    33.  
    34.     // In each frame run SQL command(s)
    35.     void Update()
    36.     {
    37.         if (cmd != null)
    38.         {
    39.             try
    40.             {
    41.                 cmd.CommandText = <your_SQL_command_string>;
    42.  
    43.                 // Run SQL command, do stuff with result...
    44.  
    45.             }
    46.             catch (MySqlException eMySQL)
    47.             {
    48.                 Debug.Log(String.Format("MySQL exception with message: {0}", eMySQL.Message));
    49.             }
    50.             catch (Exception ex)
    51.             {
    52.                 Debug.Log(String.Format("General exception with message: {0}", ex.Message));
    53.             }
    54.             finally
    55.             {
    56.                 // Placeholder
    57.             }
    58.         }
    59.     }
    60.  
    61.   // Disconnect from db and do housekeeping
    62.     void OnApplicationQuit()
    63.     {
    64.         if (cmd != null)
    65.         {
    66.             cmd.Dispose();
    67.         }
    68.  
    69.         if (conn != null)
    70.         {
    71.             conn.Close();
    72.             conn.Dispose();
    73.         }
    74.     }
    75. }
    76.  
    - Close Visual Studio;
    - In Unity, click Play to run your game.

    These instructions are for MySQL Server 5.6, MySQL Connector NET 6.3, and Unity 2018.2. My attempt to do the same using MySQL Server 8.0, MySQL Connector NET 8.0, and the latest Unity 2018.3.x iteration failed with some socket-related errors.
     
    Last edited: Dec 30, 2018
  17. Gilead7

    Gilead7

    Joined:
    Apr 2, 2009
    Posts:
    27
    Depending on your server, you could install WAMP or XAMP or one of the "all in one" programs which gives you php, mysql, and some admin tools.
     
  18. Da_Elf

    Da_Elf

    Joined:
    Jan 21, 2017
    Posts:
    15
    I'm also interested in this.
    Currently I'm working on an App as a point of sale system using Livecode. The MySQL is local on the computer at the cashout area but you can also walk around with a microsoft surface with a hybrid of the app that uses the main computers IP address to send and receive information from the MySQL software running on that computer. With Livecode i do not need XAMP or WAMP installed. All i do is install mySQL Community and voila it works. However microsoft surface costs a lot of money and id prefer to port to android. However LiveCode has been a nightmare trying to set it up to export to Android and I have been able to make quite a few android games in unity so i was looking for a solution that would link from unity to mysql on an android build that would need only an ip address (and username and password and port number naturally)
    So after aaaaaaallll of that. whats the best solution. The last time I did one of these it worked to a point when tested on the PC where i could get a COUNT(id) working but it did not want to get any other information. Kept freezing. As for the build to android. That did not work at all. Not even the COUNT(id)
    Im using both 2019 and 2020 by the way
     
    Last edited: Jul 31, 2020