[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;
}