[Abagail]

MySQL: Managing mysql connections

This is a small, simple tutorial regarding MySQL connections. It basically teaches you some tips and tricks for BlueG's MySQL plugin(r33 and further).

Using multiple database connections(r35+):

By default, the MySQL plug-in will only allow one connection. However, using mysql_option we can toggle this to allow for multiple simultanoues connections.

We simply do:

pawn Code:

mysql_option(DUPLICATE_CONNECTIONS, true);

What this does is set a variable that tells the plugin to allow / deny the usage of multiple MySQL connections. Obviously, if we set the boolean value to true, it will allow and with false deny.

This allows the following code to work properly, and not just replace the current MySQL connection like it would if DUPLICATE_CONNECTIONS wasn't enabled.

pawn Code:

mysql_connections[0] = mysql_connect("127.0.0.1", "root", "database", "");
mysql_connections[1] = mysql_connect("127.0.0.1", "root", "second_database", "");

Now, accordingly if we were to disable the use of multiple database connections using two mysql_connect natives would just replace the prior connection keeping the same connection handle.

Closing a mysql connection:
This part is very simple. Basically, all one has to do to close a MySQL connection is use mysql_close. An example is shown below,

pawn Code:

mysql_close(mysql_connections[0]);

And to reconnect a connected handle we can simply do:

pawn Code:

mysql_reconnect(mysql_connections[0]);

Checking if a connection handle is valid or not:

To check if a connection handle is valid, we can simply use mysql_errno(connectionhandle). If it's not valid, -1 will be returned.

Example:

pawn Code:

mysql_connections[0] = mysql_connect("127.0.0.1", "root", "", "database");
if(mysql_errno(mysql_connections[0]) == -1)
{
     CallLocalFunction("On_MySQLConnectFail", "d", mysql_connections[0]);
}

If you believe anything was unclear, or would like documentation on more MySQL functions.

EDIT: Some useful functions

pawn Code:

stock IsValidConnectionHandle(connectionhandle=1)
{
       if(mysql_errno(connectionhandle)== -1) return 0;
       return 1;
}

[Ryan_Bowe]

I've been doing this since the release of R35 but it's nice to see a tutorial on it.

[Abagail]

Quote:

Originally Posted by Ryan_Bowe I've been doing this since the release of R35 but it's nice to see a tutorial on it.

Thanks! I know there may be some documentation on this, but this as a whole should help people new to MySQL scripting / SA-MP mysql usage out a bit.

[Vince]

Multiple connections usually aren't necessary, but when you do use them you must pay extreme attention to include the correct connectionHandle in every function at all times otherwise you'll likely get weird screwups.

I am also not entirely sure if you need to set the option at all if different databases are concerned. I do use this option, but I connect to the same database, albeit with a different user every time. Makes auditing easier. Each active script has its own user with its own specific rights as to what tables it can access.

[Abagail]

Well, from my knowledge - you HAVE to enable it if you want to have any connection handle other than 1. I can't confirm this however.

[Ryan_Bowe]

When I first attempted to create multiple connections without setting the option, they were all set to 1.

[Abagail]

Yeah, that's what it should do. Are the connections just using a different database as Vince said? I'd be interested to see if that changes anything.

[Ryan_Bowe]

I can test in a little bit. On my laptop at the moment.

[Luis-]

There's a typo in your tutorial.

These are wrong.

pawn Code:

mysql_connections[0] = mysql_connect("127.0.0.1", "root", "", "database");
mysql_connections[1] = mysql_connect("127.0.0.1", "root", "", "second_database");

Should be this;

pawn Code:

mysql_connections[0] = mysql_connect("127.0.0.1", "root", "database", "");
mysql_connections[1] = mysql_connect("127.0.0.1", "root", "second_database", "");

[Abagail]

Didn't notice, thank you! Should be fixed now.