MySQL Plugin

From SA-MP Wiki

Jump to: navigation, search

MySQL Plugin Documentation For SA:MP MySQL Plugin Version 2.0 By StrickenKid

Plugin Topic And Download

Plugin Version 2.0 Has Been Released.

Contents

Plugin Callbacks

OnMysqlQuery

Description:

This callback is called when a threaded query execution has finished.


Parameters:
(resultid, spareid, MySQL:handle)
resultidThe result id passed from the mysql_query function.
spareidAn extra variable passed from the mysql_query function.
MySQL:handleThe mysql handle the query was executed on.


This callback does not handle returns.

#define MYSQL_RESULT_LOGIN 1
#define MYSQL_RESULT_REGISTER 2
 
// in a login command
mysql_query("SELECT * FROM `accounts` WHERE `username` = 'MyCoolNick' LIMIT 0,1", MYSQL_RESULT_LOGIN, playerid, connection);
 
// in a register command
mysql_query("INSERT INTO `accounts` (username, password) VALUES ('MyCoolNick', 'MyCoolPassword')", MYSQL_RESULT_REGISTER, playerid, connection);
 
public OnMysqlQuery(resultid, spareid, MySQL:handle)
{
	switch (resultid)
	{
		case MYSQL_RESULT_LOGIN:
		{
			// process login with spareid as playerid
		}
		case MYSQL_RESULT_REGISTER:
		{
			// process register with spareid as playerid
		}
	}
	return 1;
}


OnMysqlQueryArray

Description:

This callback is the same as OnMysqlQuery, except its used with mysql_query_array to pass an array of extra variables.


Parameters:
(resultid, extravars[], MySQL:handle)
resultidThe result id passed from the mysql_query_array function.
extravars[]An array of extra variables passed from the mysql_query_array function.
MySQL:handleThe mysql handle the query was executed on.


This callback does not handle returns.

#define MYSQL_RESULT_LOGIN 1
#define MYSQL_RESULT_REGISTER 2
 
// in a login command
mysql_query_array("SELECT * FROM `accounts` WHERE `username` = 'MyCoolNick' LIMIT 0,1", MYSQL_RESULT_LOGIN,
                    {playerid, someotherid, anotherid}, connection);
 
// in a register command
mysql_query("INSERT INTO `accounts` (username, password) VALUES ('MyCoolNick', 'MyCoolPassword')", MYSQL_RESULT_REGISTER,
                    {playerid, someotherid, anotherid}, connection);
 
public OnMysqlQueryArray(resultid, extravars[], MySQL:handle)
{
	switch (resultid)
	{
		case MYSQL_RESULT_LOGIN:
		{
			// process login with:
			// extravars[0] = playerid
			// extravars[1] = someotherid
			// extravars[2] = anotherid
		}
		case MYSQL_RESULT_REGISTER:
		{
			// process register with:
			// extravars[0] = playerid
			// extravars[1] = someotherid
			// extravars[2] = anotherid
		}
	}
	return 1;
}


OnMysqlError

Description:

This callback is called when an error happens with a mysql function or query.


Parameters:
(error[], errorid, MySQL:handle)
error[]The error dump string explaining the error.
erroridThe error code of the error.
MySQL:handleThe mysql handle the error happened on.


This callback does not handle returns.

// lets say we're trying to connect to a mysql server that is not online
 
new MySQL:connection = mysql_init(LOG_ONLY_ERRORS, 1);
mysql_connect("1.2.3.4", "user", "pass", "dbname", connection);
 
public OnMysqlError(error[], errorid, MySQL:handle)
{
	// error[] = "Failed to connect. Can't connect to MySQL server on '1.2.3.4' (10060)."
	// errorid = 2003
}

Plugin Functions

Image:32px-Ambox_warning_orange.png

Note

Every function (except mysql_init) has a MySQL handle parameter, the examples use this, but it is not required. The handle defaults at 0 if you choose not to use it.


mysql_init

Description:

Initializes mysql connection and returns connection handle.


Parameters:
(logtype, printerrors)
logtypeThe logging type for the mysql connection.
printerrorsToggle whether you want mysql errors to be printed to the server window.

Return Values:

Connection handle, -1 if failed.


public OnGameModeInit( )
{
	new MySQL:connection = mysql_init(LOG_ONLY_ERRORS, 1);
	// ...
}


mysql_connect

Description:

Use this function to connect to the mysql database.


Parameters:
(const host[], const user[], const pass[], const db[], MySQL:handle, auto_reconnect)
const host[]The address of the mysql database.
const user[]The username for the account you want to connect with.
const pass[]The password for the account you want to connect with.
const db[]The database you want to connect to within the mysql server. (This can be changed with mysql_select_db).
MySQL:handleThe connection handle this will be preformed on.
auto_reconnectOptional; Toggle for auto reconnect on connection lost, happens after mysql_ping is called. (default is off).

Return Values:

1 on success, 0 on fail.


public OnGameModeInit( )
{
	new MySQL:connection = mysql_init(LOG_ONLY_ERRORS, 1);
	mysql_connect("hostname", "username", "password", "database", connection, 1);
	// ...
}

mysql_close

Description:

Use this function to close the connection of the current connected mysql database.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


public OnGameModeExit( )
{
	mysql_close(connection);
}


mysql_select_db

Description:

Use this function to select a new database within the mysql server.


Parameters:
(const db[], MySQL:handle)
const db[]The name of the database you want to connect to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


public OnGameModeInit()
{
    new MySQL:connection = mysql_init(LOG_ONLY_ERRORS, 1);
    mysql_connect("hostname", "username", "password", "myfirstdatabase", connection);
 
    new sql = mysql_query(...); // Will be executed on myfirstdatabase
 
    mysql_select_db("myseconddatabase");
 
    sql = mysql_query(...); // Will be executed on myseconddatabase
}
//Example by Luka P.

mysql_query

Description:

Use this function to execute a query on your mysql database.


Parameters:
(const query[], resultid, spareid, MySQL:handle)
const query[]The query you want to execute.
resultidOptional; (This will cause your query to be executed in a separate thread.) The id of the result to be passed into the query callback.
spareidOptional; An extra variable that would be passed to the query callback.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

0 on success, error code (non-zero) on fail.


No example yet...


mysql_query_array

Description:

This function is the same as mysql_query, except you can pass an array of exra variables instead of one.


Parameters:
((const query[], resultid, {Float,_}:extravars[], MySQL:handle)
const query[]The query you want to execute.
resultidOptional; (This will cause your query to be executed in a separate thread.) The id of the result to be passed into the query callback.
{Float,_}:extravars[]An array of extra variables that would be passed to the query callback.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_store_result

Description:

Use this function to store a result from a query executed. Required to use before fetching of any results.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_free_result

Description:

Use this function to free a result previously stored with mysql_store_result().


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_result_stored

Description:

Use this function to check if a result is stored.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 if theres a result stored, 0 if not.


if(mysql_result_stored()) 
    print("The result is stored");
else 
    print("The result isn't stored");

mysql_fetch_field

Description:

Use this function to fetch a single field from a query.


Parameters:
(const fieldname[], dest[], MySQL:handle)
const fieldname[]The name of the field you want to fetch.
dest[]The destenation string the field will be stored to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 if the field was fetched, 0 if not.


No example yet...


mysql_fetch_field_num

Description:

Use this function to fetch a single field from a query by the field number (index).


Parameters:
(const fieldname[], dest[], MySQL:handle)
fieldnumThe number index of the field you want to fetch.
dest[]The destenation string the field will be stored to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 if the field was fetched, 0 if not.


No example yet...


mysql_fetch_row

Description:

Use this function to fetch a whole row from a query.


Parameters:
(dest[], const splitter[], MySQL:handle)
dest[]The destination string the row will be stored to.
const splitter[]The splitter you want to seperate each field in the row with (default:"
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 if the field row fetched, 0 if not.


No example yet...


mysql_real_escape_string

Description:

Use this function to escape a string so its safe for a mysql query, this is really useful for raw data like names and passwords.


Parameters:
(const string[], dest[], MySQL:handle)
const string[]The string you want to be escaped.
dest[]The destination string the new escaped string will be stored to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 if escaped, 0 on fail.


No example yet...


mysql_num_rows

Description:

Use this function to get the number of rows from the table of the query.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

The number of rows in the table of the query.


No example yet...


mysql_num_fields

Description:

Use this function to get the number of fields from the selected row the table of the query.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

The number of fields from the row in the table of the query.


No example yet...


mysql_affected_rows

Description:

Use this function to get the number of affected rows from the table of the query.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

The number of affected rows in the table of the query.


No example yet...


mysql_insert_id

Description:

Use this function to get the generated AUTO_INCREMENT value of the last inserted row.

Description:

Note: This function will only work if its used after an "INSERT" query and that table has an AUTO_INCREMENT.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

The value of the last generated AUTO_INCREMENT.


No example yet...


mysql_ping

Description:

Use this function to check the connection to the mysql database.

Description:

Note: If auto_reconnect was enabled in mysql_connect, calling this function causes the plugin to auto reconnect if connection was lost.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

0 if the connection is alive, nonzero if not. See this for more info.


No example yet...


mysql_error

Description:

Use this function to get the current error dump, if any.


Parameters:
(dest[], MySQL:handle)
dest[]The destenation string the error will be saved to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


if(!mysql_ping())
{
	print("MYSQL: Connection is alive!");
	return 1;
}
else
{
	print("MYSQL: Connection is dead.");
	return 1;
}

mysql_errno

Description:

Use this function to get the error code value of the last error from any function call.


Parameters:
(dest[], MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

The error code value of the last error from any function call. See here for more documentation about error codes.


No example yet...


mysql_warning_count

Description:

Use this function to get the number of warnings generated during the last execution statement.


Parameters:
(dest[], MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

The number of warnings generated during the last execution statement.


No example yet...


mysql_info

Description:

Use this function to get the information generated from the last execution statement.


Parameters:
(dest[], MySQL:handle)
dest[]The destenation string the information will be saved to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_stat

Description:

Use this function to get the status of the mysql server; uptime, threads, open tables, etc...


Parameters:
(dest[], MySQL:handle)
dest[]The destenation string the information will be saved to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_get_server_info

Description:

Use this function to get the server version.


Parameters:
(dest[], MySQL:handle)
dest[]The destenation string the information will be saved to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_get_host_info

Description:

Use this function to get the type of connection in use, and the host of the database.


Parameters:
(dest[], MySQL:handle)
dest[]The destination string the information will be saved to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_data_seek

Description:

Use this function to seek to a specified row number to fetch it.


Parameters:
(rownum, MySQL:handle)
rownumThe row number you want to seek to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_set_character_set

Description:

Use this function to set a character set of Unicode characters that the plugin will use. Very useful for non-english servers that have different languages of data.


Parameters:
(const csname[], MySQL:handle)
const csname[]The name of the character set you want to use.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_get_character_set

Description:

Use this function to get the current character set in use by the database.


Parameters:
(csname[], MySQL:handle)
csname[]The destination string the character set name will be saved to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 on success, 0 on fail.


No example yet...


mysql_fetch_int

Description:

Use this function to retrieve a single-int value from a mysql query.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

The integer value returned from the mysql query.


No example yet...


mysql_fetch_float

Description:

Use this function to retrieve a single-float value from a mysql query.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.

Return Values:

The float value returned from the mysql query.


No example yet...


mysql_fetch_string

Description:

Use this function to retrieve a single-string value from a mysql query.


Parameters:
(dest[], MySQL:handle)
dest[]The destination string the result will be saved to.
MySQL:handleThe connection handle this will be preformed on.

Return Values:

1 if the string result was fetched, 0 if not.


No example yet...

Others

Logging Types

Note: Logging logs to mysql_log.txt located in server directory.

LOG_OFF			Log nothing
LOG_ALL			Log everything
LOG_ONLY_ERRORS		Log only errors

--StrickenKid 01:59, 4 August 2010 (CEST)

Personal tools