AGS game connecting to an online MySQL database

Started by WHAM, Tue 17/03/2015 11:11:07

Previous topic - Next topic

WHAM

Is it possible for me to have an AGS game write data into an online MySQL database?
Simply writing a new line in the database would be enough for my basic purposes, since I am working on a project requiring ability to maintain a scoreboard.

I was uncertain whether to ask  this here, in the hopes of being directed towards more information (searching came up with stuff circa 2005) or to ask in the recruitment forum if someone would be willing the help me out with this matter.
Wrongthinker and anticitizen one. Utterly untrustworthy. Pending removal to memory hole.

Snarky

#1
See this thread. To connect from AGS you would probably use the TCP/IP plugin (the link in the original thread is broken, but I'm sure it's still around somewhere).

Monsieur OUXX

#2
The problem with online connections is that the TCP/IP plugin is just what it is -- Well... a TCP/IP plugin. That means (almost) the lowest possible level of remote communication.

Then you still have to build your own communication system on top of it, to match the kind of protocol/connection that you want to connect to. I recall a guy did that with IRC: he used the TCP/IP plugin to send the connection request, the handshake, the formatted data packages, etc.

Feel free to do the same with SQL, but... I'd really recommend using some out-of-the-box C++ (or C# or whatever) SQL client code, and then force-fit it into an AGS engine plugin. You might save yourself a lot of trouble, not to mention you might have something that eventually works ;)


======

EDIT: why not lua for AGS? I'm sure there is a mySQL for Lua packahe floating around in the interwebs. Maybe it's even part of the core packages.



 

Wyz

I have to disadvise having clients directly connect to a SQL server; there are some security risks involved with that. A better idea is to have a webserver as an intermediate. You don't really need a website just a script your game can interact with. The script would in turn connect to the database (and only have access to the relevant bits).

For this to work your game needs to be able to communicate via HTTP. You can use a TCP/IP plugin for that (for instance the one I made [link]; although still in alpha). I believe Khris has had success with this approach and you can find more info about that in the thread I mentioned.

I would love to make a module for this someday. :-\
Life is like an adventure without the pixel hunts.

Monsieur OUXX

Wyz gave very good advice (good practice, and it would actually make it easier).

However it depends on what you're trying to achieve. Did you want the mySQL database locally, to store some game data?
 

Snarky

I feel the need to point out that I argued the same thing in the earlier discussion I linked to... :)

Wyz

Quote from: Monsieur OUXX on Thu 19/03/2015 08:46:18
However it depends on what you're trying to achieve. Did you want the mySQL database locally, to store some game data?

A fair point; if done locally there isn't much of a security risk but MySQL is a bit big to ship along a game. In that case I'd advise SQLite or something similar. Although you probably don't need a database if all you want to save is high scores; a simple text file would do in most cases.
Life is like an adventure without the pixel hunts.

Monsieur OUXX

Quote from: Snarky on Thu 19/03/2015 09:24:37
I feel the need to point out that I argued the same thing in the earlier discussion I linked to... :)

;)
 

WHAM

Ah, I've been away for a couple of days.

On the basic level, I want to have an online scoreboard to keep track of high scores from the game.
In the game, the player can enter a name and that, along with a few other bits of data, are then sent to the database, which can either be viewed on a website I can build, or perhaps there could even be a poll in the game that fetches the top 10 or top 100 scores from the board and displays them (again, via an online script, as Wyz wisely suggested).

Building an online script that acts and a bridge between the game and the database sounds like a very smart idea, though as I read through the older threads, it's looking like I might not have the know-how to make this happen. I'll keep looking this over as I continue to build the game, might even open up a help wanted thread at a later stage if necessary.

Thanks everyone, Wyz especially for the link. :)
Wrongthinker and anticitizen one. Utterly untrustworthy. Pending removal to memory hole.

Monsieur OUXX

#9
Quote from: WHAM on Tue 24/03/2015 09:05:30
Building an online script that acts and a bridge between the game and the database sounds like a very smart idea

Yeah, that's definitely what you want.

Don't be scared, it's not as hard as you think. You don't need a script, I'd say. Just an HTTP form on some webpage. --> It shouldn't be too hard to implement some sort of http POST using the TCP/IP plugin.

However maybe you'll need to add a password of some sort. I'll let you read articles about http forms to understand how to securely post stuff. If you want to do things right, you'll want to obfuscate the password in your AGS game -- That will take you exactly 5 minutes.

The authentication work is mostly done on the webpage side (you can copy and paste any php code snippet from the Interwebs into your page). That won't be very secure, though, because it will be averagely easy to hack that page (I guess it's easy to incercept the http data sent from an AGS game). But who cares, if it's for casual high scores.

 

WHAM

I've made HTML forms that used PHP scripts to write into a database before, so that part I'm already cool with.
I'll come back to this thread once I get the ball rolling on this. :D
Wrongthinker and anticitizen one. Utterly untrustworthy. Pending removal to memory hole.

Khris

Just found this, and since I successfully used this for FloatyRog, I'll share my code:

Code: ags
void Send() {
  Socket *server = Socket.CreateTCP();
  SockAddr *sockaddr = SockAddr.CreateFromString("http://khris.agser.me");
  sockaddr.Port = 80;
  server.Connect(sockaddr);
  String name = tbName.Text;
  String param1 = String.Format("param1=%s&param2=%d\r\n", name,  current);
  String len = String.Format("Content-Length: %d\r\n\r\n", param1.Length);
  String message =          "POST /floatyrog/index.php HTTP/1.1\r\n";
  message = message.Append( "Host: khris.agser.me\r\n");
  message = message.Append( "Content-Type: application/x-www-form-urlencoded\r\n");
  message = message.Append( len);
  message = message.Append( param1);
  message = message.Append( "\r\n");
  server.Send(message);
  server.Close();
}


(I'm not actually using param1 and param2 btw ;), and it is recommended to add a security key of some sort as additional parameter to prevent people from adding fake data.)

jhonberg

:cheesy::cheesy::cheesy::cheesy: thank you for this post.
Quote from: Khris on Tue 21/04/2015 11:58:25
Just found this, and since I successfully used this for FloatyRog, I'll share my code:

Code: ags
void Send() {
  Socket *server = Socket.CreateTCP();
  SockAddr *sockaddr = SockAddr.CreateFromString("http://khris.agser.me");
  sockaddr.Port = 80;
  server.Connect(sockaddr);
  String name = tbName.Text;
  String param1 = String.Format("param1=%s&param2=%d\r\n", name,  current);
  String len = String.Format("Content-Length: %d\r\n\r\n", param1.Length);
  String message =          "POST /floatyrog/index.php HTTP/1.1\r\n";
  message = message.Append( "Host: khris.agser.me\r\n");
  message = message.Append( "Content-Type: application/x-www-form-urlencoded\r\n");
  message = message.Append( len);
  message = message.Append( param1);
  message = message.Append( "\r\n");
  server.Send(message);
  server.Close();
}


(I'm not actually using param1 and param2 btw ;), and it is recommended to add a security key of some sort as additional parameter to prevent people from adding fake data.)

SMF spam blocked by CleanTalk