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

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!

 

There's a .NET implementation available. Installation instructions here. Tutorial on use here.

 

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.

 

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.

 

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?

 

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.

 

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.

 

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.

 

I attempted to retrofit this project https://github.com/tutsplus/self-hosted-php-sql-leaderboard which uses PHP. Didn't really get anywhere with it though. Was trying to figure out if I could somehow use the WWW class to connect directly to the DB.

 

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?

 

Great I'll give it a shot- thanks!

 

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.

 

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.

 

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