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

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

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

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

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.
ReturnsConnection handle, -1 if failed.


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


mysql_connect

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).
Returns1 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

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.
Returns1 on success, 0 on fail.


public OnGameModeExit( )
{
	mysql_close(connection);
}


mysql_select_db

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.
Returns1 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

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.
Returns0 on success, error code (non-zero) on fail.


No example yet...


mysql_query_array

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_store_result

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_free_result

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_result_stored

Use this function to check if a result is stored.


Parameters:
(MySQL:handle)
MySQL:handleThe connection handle this will be preformed on.
Returns1 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

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.
Returns1 if the field was fetched, 0 if not.


No example yet...


mysql_fetch_field_num

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.
Returns1 if the field was fetched, 0 if not.


No example yet...


mysql_fetch_row

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.
Returns1 if the field row fetched, 0 if not.


No example yet...


mysql_real_escape_string

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.
Returns1 if escaped, 0 on fail.


No example yet...


mysql_num_rows

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.
ReturnsThe number of rows in the table of the query.


No example yet...


mysql_num_fields

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.
ReturnsThe number of fields from the row in the table of the query.


No example yet...


mysql_affected_rows

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.
ReturnsThe number of affected rows in the table of the query.


No example yet...


mysql_insert_id

Use this function to get the generated AUTO_INCREMENT value of the last inserted row.
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.
ReturnsThe value of the last generated AUTO_INCREMENT.


No example yet...


mysql_ping

Use this function to check the connection to the mysql database.
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.
Returns0 if the connection is alive, nonzero if not. See this for more info.


No example yet...


mysql_error

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.
Returns1 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

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.
ReturnsThe 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

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.
ReturnsThe number of warnings generated during the last execution statement.


No example yet...


mysql_info

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_stat

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_get_server_info

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_get_host_info

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_data_seek

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_set_character_set

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_get_character_set

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.
Returns1 on success, 0 on fail.


No example yet...


mysql_fetch_int

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.
ReturnsThe integer value returned from the mysql query.


No example yet...


mysql_fetch_float

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.
ReturnsThe float value returned from the mysql query.


No example yet...


mysql_fetch_string

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.
Returns1 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