Hi,
I’m acessing a MySQL database via a script in Unity. The script needs to access a database in real time and update the status of an object. Since the database needs to be opened an closed, what would be the best way to do this? Are there any good examples on how to do this? What should be inside Start() and what should be placed inside FixedUpdate()?
Kind regards, Samuel Lourenço
Often it is suggested to access the database via UnityWebRequest, where you use server side code (such as PHP) to actually do the database access. You should be able to search for Unity side examples of this, especially UnityWebRequest examples, on this forum. Writing web server code to talk to MySQL is generally outside the scope of this forum, but is easily found with a google search.
FixedUpdate is for running code in preparation for the physics update, which immediately follows FixedUpdate. Since accessing a database has nothing to do with physics, it is unlikely to be the right place for this.
Thanks! I was thinking about using Update(), but probably it would be too fast. I wish to only access the database from time to time (say, each 100ms). However, I guess I could use Time.deltaTime for this?
As for the PHP, I don’t think we have a server for this. I’ll have to see.
Don’t do it. You will need to open up your database for EVERYONE. For WRITING. Recipe for a disaster and 0 day database hack.
There is no good way to allow anyone to write into your database. Do not do it.
Absolutely nothing. If you ignore all the warnings and you access your database from Unity, do not do it from any update cycle. At least not every frame. You will kill it in a second. Relational databases aren’t built for this. Use some Memcached or Redis or something similar in-memory cache (and not even those every frame) and replicate the data once in a while.
Or call the mentioned HTTP,. write a REST API on the server and call that. And that service then can call in the database async.
Hi Lurking-Ninja. I will be only reading from the database. However, synchronous reads might be a problem, and there may be security issues as well.
However, I’m not familiar with Memcached, Redis or Rest. Please, can you clarify?
Kind regards, Samuel Lourenço
versus
But anyway, you shouldn’t open up your database beyond localhost. Not even for reading.
What is memcached: https://memcached.org/
What is Redis: https://redis.io/topics/introduction
What is REST: https://www.redhat.com/en/topics/api/what-is-a-rest-api
So you do only read (in “real time”) AND you don’t host it anywhere, so it’s local data ?!? I assume you meant you don’t have a server with PHP installed.
Please explain what you are trying to achieve. Maybe there is a better solution than a database.
To clarity, I’ll read from the database to update the status of an object represented in Unity. It will be done locally, in localhost.
Unfortunately, I can’t change that. By boss wants direct access to a local SQL database. No PHP involved.
Okay, hm, I assume you are making some sort of frontend for an existing application/data which uses mysql. Maybe you can cache the data, update it in memory and only write if really necessary. You might want to look into mysql connectors to establish a connection: Database Access | Mono, ADO.NET - Wikipedia. In short, you should use some sort of abstraction.
So you’re building a game server and you just forgot to mention it. I hope. Because I hope you don’t want to install MySQL on people’s machines…
It is still a very bad idea to talk to the DB directly from Unity, but I guess it is what it is.
https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html
You should connect to the DB when the server boots up and close the connection when it goes off.
You should still use an intermediate memory cache.
Yes, it is a frontend of sorts. I convinced my colleague and superior to go with the PHP approach. It is less messy. But for now, for a local application, it works.
I’m doing queries between one second intervals, to a local server. It works, but it is messy. I don’t like the idea of copying a dll to the Assets folder. In the future, this will have to be implemented with a PHP server. Cleaner and better that way.