MySQL/R6

From SA-MP Wiki

This article is outdated. It may use methods or functions which no longer exist or which are deemed obsolete by the community. Caution is advised. There is a new wiki page for newer versions of this plugin.

Documentation for G-sTyLeZzZ's MySQL plugin version R6-2 (R7 will be supported soon). Forum topic and download links can be found here.

Lastest release: R7 (6/02/2012)

Plugin functions

Note

Every function except mysql_connect(...) and mysql_debug() has connectionHandle parameter. If you use only one database connection you don't need to mind it. Connection handle is 1 by default.

mysql_debug

Description:

You can enable plugin debug (mysql_log.txt) with this function.

Parameters:

(enable)

enable

1 to enable, 0 to disable (defaults to 1).

Return Values:

This function does not return any specific values.

public OnGameModeInit()
{
	mysql_debug(1); //enable debug
	//...
	mysql_debug(0); //disable debug
	return 1;
}

mysql_connect

Description:

Use this function to connect to MySQL server and database.

Important
Note

Do not use this function to reconnect to the database. Use mysql_reconnect() instead.

Parameters:

(const host[], const user[], const database[], const password[])

const host[]

IP or hostname of the MySQL server.

const user[]

Username of the account you want to connect to.

const database[]

Name of the database you want to connect to.

const password[]

Password of the account you want to connect to.

Return Values:

Connection handle.

public OnGameModeInit()
{
	new mysql = mysql_connect("127.0.0.1","root","mydatabase","mypass");
	// ...
}

mysql_close

Description:

Use this function to close database connection.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

1 on success, 0 on fail.

public OnGameModeExit()
{
	mysql_close();
	return 1;
}

mysql_ping

Description:

Checks whether or not the connection to the server is working. This function can be used by scripts that remain idle for a long while, to check whether or not the server has closed the connection.

Important
Note

Connection state check (and reconnect if needed) happens automatically incase you are using threaded callback, so you don't need to perform any manual checks.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

1 if alive, 0 if dead.

if(mysql_ping()) print("MySQL connection is still alive!");
// ...
if(!mysql_ping()) print("MySQL connection is dead!");

mysql_stat

Description:

Use this function to get current statistics of the MySQL server.

Parameters:

(const destination[], connectionHandle)

const destination[]

The string to store extracted data in.

connectionHandle

The connection handle this will be processed on.

Return Values:

This function does not return any specific values.

new stats[150]; mysql_stat(stats); print(stats);
 
//Output would be something like:
Uptime: 380  Threads: 1  Questions: 3  Slow queries: 0  Opens: 12  Flush tables: 1  
Open tables: 6  Queries per second avg: 0.008

mysql_set_charset

Description:

Use this function to change character set that connection will use. Very useful for servers which often process data with foreign characters.

Parameters:

(charset[],connectionHandle)

charset[]

Code of the character set you want to use.

connectionHandle

The connection handle this will be processed on.

Return Values:

This function does not return any specific values.

mysql_set_charset("utf8_unicode_ci");

mysql_get_charset

Description:

Use this function to get the current character in use.

Parameters:

(destination[], connectionHandle)

destination[]

The string to store extracted data in.

connectionHandle

The connection handle this will be processed on.

Return Values:

This function does not return any specific values.

new charset[20]; mysql_get_charset(charset);

mysql_reconnect

Description:

Use this function to reconnect to selected connection handle.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

This function does not return any specific values.

//connection was lost for some reason
mysql_reconnect();

mysql_reload

Description:

Asks the MySQL server to reload the grant tables. The connected user must have the RELOAD privilege.

Important
Note

This function was fixed in version R5 and will not work in earlier versions!

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

This function does not return any specific values.

mysql_reload();
 
//this query has the same functionality as mysql_reload function
mysql_query("FLUSH PRIVILEGES");

mysql_format

Description:

Allows you to format a string which you can safely use in a query.

Important
Note

This function was added in version R6-2 and will not work in earlier versions!

Note

Precisions such as %2.f/%10.s are not supported yet.

Parameters:

(connectionHandle, output[], format[], {Float,_}:...)

connectionHandle

The connection handle this will be processed on.

output[]

The string to output the result to.

format[]

The format string.

{Float,_}:...

Indefinite number of arguments of any tag.

Return Values:

This function does not return any specific values.

Format strings

Placeholder	Meaning
%e	Escapes data directly without the need to call mysql_escape_string() before
%s	Inserts a string.
%d	Inserts an integer (whole) number
%f	Inserts a floating point number.
%i	Inserts an integer.

The values for the placeholders follow in the exact same order as parameters in the call.

new szDestination[100];
mysql_format(connectionHandle, szDestination, "SELECT * FROM `%s` WHERE `bar` = '%e' AND `foobar` = '%f' LIMIT %d", "foobar", "escape'me\"please", 1.2345, 1337);
// the variable 'szDestination' contains now the formatted query (including the escaped string)
mysql_query(szDestination);

mysql_query

Description:

This is basically the main function of this plugin. You can send unique query (multiple queries are not supported) to the currently active database on the server. You can learn how to write a proper query here.

Important
Note

It's highly recommended to thread all your queries (even INSERT & UPDATE). You can find a simple usage explained here and fresh example here.

Parameters:

(query[], resultid, extraid, connectionHandle)

query[]

The query you want to process.

resultid

Optional (if you specify this query will be processed in a separate thread).

extraid

Optional (extra variable that would be processed to the callback.

connectionHandle

The connection handle this will be processed on.

Note

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

Return Values:

1 on success, 0 on fail.

//If you do not plan on using threads, simply do not mind optional parameters
mysql_query("SELECT * FROM `mytable` WHERE condition=true");
 
//But if you do plan on using threads, check this example
#define THREAD_MYSELECT  (1)
mysql_query("SELECT * FROM `mytable` WHERE condition=true",THREAD_MYSELECT,playerid);
//This query would be processed in a separate thread.
//After processing is done, OnQueryFinish would be called with these parameters:
//OnQueryFinish("SELECT * FROM `mytable` WHERE condition=true", 1, 0, 1);

mysql_query_callback

Description:

Very similar to the mysql_query(), but here you can specify custom callback for your threaded query.

Important
Note

This function was added in version R5 and will not work in earlier versions!

Parameters:

(index, query[], callback[], extraid, connectionHandle)

index

Extra variable which is processed to the callback.

query[]

The query you want to process.

callback[]

Name of the function to call.

extraid

Optional (extra variable that would be processed to the callback.

connectionHandle

The connection handle this will be processed on.

Note

The callback must be public function. That means it has to be forwarded.

Return Values:

1 on success, 0 on fail.

forward MySqlCallback(query[], index, extraid, connectionHandle);
 
public OnPlayerConnect(playerid)
{
	mysql_query_callback(playerid,"SELECT * FROM `mybuildings`","MySqlCallback");
}
 
public MySqlCallback(query[], index, extraid, connectionHandle)
{
	//Query processed, you can execute your code now
}

mysql_store_result

Description:

Use this function to store result after using SELECT, SHOW, DESCRIBE, EXPLAIN or CHECK TABLE query.

Note

You have to use this function before fetching the result. Also don't forget to free result when you don't need it anymore with mysql_free_result() or you will get "commands out of sync" errors.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

1 on success, 0 on fail.

mysql_query("SELECT * FROM `players` WHERE name='iamcool'");
mysql_store_result();
//do what you have to do
mysql_free_result();

mysql_free_result

Description:

Use this function to free result after using mysql_store_result().

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

This function does not return any specific values.

mysql_query("SELECT * FROM `players` WHERE name='iamcool'");
mysql_store_result();
//do what you have to do
mysql_free_result();

mysql_real_escape_string

Description:

Use this function to escape special characters in a string for use in a SQL statement. It prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

Important
Note

Always use this function (if you don't use mysql_format()) before inserting user inputs in a query. You can be victim of a SQL injection if you do not do so.

Parameters:

(const source[], destination[], connectionHandle)

const source[]

The string you want to be escaped.

destination[]

The string to store escaped data in.

connectionHandle

The connection handle this will be processed on.

Notes

Database connection is required before using this function.
It does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

Return Values:

Number of escaped characters.

public OnPlayerText(playerd, text[])
{
	new escape[140];
	mysql_real_escape_string(text,escape); //string is now safe to be put in a query
	// ...
}

mysql_errno

Description:

Use this function to get the error code of the error message from the previous MySQL operation.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

Error code, 0 if no error occurred.

mysql_query("SELECT * FROM `mytable`");
if(mysql_errno() == 0) print("Query processed!");

mysql_warning_count

Description:

Use this function to get number of warnings from the previous query.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

Number of warnings, 0 if none.

mysql_query("UPDATE `notable` SET something=2 WHERE nofield=0");
if(mysql_warning_count()) print("Warnings occurred!");

mysql_affected_rows

Description:

Use this function get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Note

If the last query was a DELETE query with no WHERE clause, all of the records will have been deleted from the table but this function will return zero.

Return Values:

Number of affected rows.

mysql_query("DELETE FROM mylogs WHERE log_id > 10");
printf("%d logs deleted!",mysql_affected_rows());

mysql_num_rows

Description:

Retrieves the number of rows from a result set. This command is only valid for statements like SELECT or SHOW that return an actual result set. To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use mysql_affected_rows().

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

Number of rows.

mysql_query("SELECT NULL FROM table1");
mysql_store_result(); 
new rows = mysql_num_rows(); 
mysql_free_result();
 
printf("There are %d rows in table1",rows);

mysql_num_fields

Description:

Returns the number of columns in a result set.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

Number of fields.

mysql_query("SELECT field1,field2,field3 FROM table2");
mysql_store_result();
 
printf("%d fields were selected.",mysql_num_fields());
 
mysql_free_result();

mysql_insert_id

Description:

Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

ID generated for an AUTO_INCREMENT column.

mysql_query("INSERT INTO `players` (name,password) VALUES ('Ownage',MD5('mypass')");
printf("New player registered with ID %d",mysql_insert_id());

mysql_field_count

Description:

Use this function to get the number of columns for the most recent query on the connection.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

Number of columns.

mysql_query("SELECT * FROM mytable");
mysql_store_result();
 
printf("There are %d columns in the result set.",mysql_field_count());
 
mysql_free_Result();

mysql_fetch_int

Description:

Use this function to retrieve single integer value from the result set.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

Integer from the result set.

mysql_query("SELECT myid FROM players LIMIT 1");
mysql_store_result();
 
new myid = mysql_fetch_int();
 
mysql_free_result();

mysql_fetch_float

Description:

Use this function to retrieve single float value from the result set.

Parameters:

(&Float:result, connectionHandle)

&Float:result

A float to store the result, passed by reference.

connectionHandle

The connection handle this will be processed on.

Return Values:

This function does not return any specific values.

mysql_query("SELECT health FROM players LIMIT 1");
mysql_store_result();
 
new Float:health;
mysql_fetch_float(health);
 
mysql_free_result();

mysql_fetch_row_format

Description:

Use this function to fetch whole row from the result set.

Notes

This function also moves the internal row pointer to the next row.
This function has a macro: mysql_fetch_row(string[]); default delimeter is used for macro.

Parameters:

(string[], const delimiter[], connectionHandle)

string[]

The string to store extracted data in.

const delimiter[]

Optional (custom delimeter character, defaults to I (vertical bar, pipe)).

connectionHandle

The connection handle this will be processed on.

Return Values:

1 if fetched, 0 if there are no more rows to be fetched.

mysql_query("SELECT * FROM players WHERE name='Myname'");
mysql_store_result(); new resultline[200];
 
if(mysql_fetch_row_format(resultline))
{
	//you can now use sscanf, explode, split or strtok to split result
	sscanf(resultline,"p<|>dds",PlayerInfo[playerid][pLevel],PlayerInfo[playerid][pAdmin],PlayerInfo[playerid][pRandomString]);
}
else SendClientMessage(playerid,0xFFFFFF,"Not in database!");
 
mysql_free_result();

mysql_retrieve_row

Description:

Use this function to move internal row pointer to the next row. We could basically call this function mysql_next_row(). This function is very useful when we plan on using mysql_fetch_field() or mysql_fetch_field_row() later on, so we don't need to extract whole result in pawn.

Notes

This function also moves the internal row pointer to the next row.
This function has a macro: mysql_next_row().

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Return Values:

1 row changed, 0 if there are no more rows.

mysql_query("SELECT * FROM houses");
mysql_ store_result();
 
while(mysql_retrieve_row()) //this will be running until all rows are processed
{
	//you can use mysql_fetch_field and mysql_fetch_field_row here
}
 
mysql_free_result();

mysql_fetch_field

Description:

Use this function to get name of specific field.

Parameters:

(number, dest[], connectionHandle)

number

Index of the field.

dest[]

The string where fetched data will be stored in.

connectionHandle

The connection handle this will be processed on.

Important
Note

If invalid field number is used plugin will crash.

Note

First field has number (index) zero (0).

Return Values:

This function does not return any specific values.

if(mysql_retrieve_row())
{
	new field[20];
	for(new i, num = mysql_num_fields(); i < num; i++)
	{
		mysql_fetch_field(i,field); //get name of all fields
	}
}

mysql_fetch_field_row

Description:

Use this function to fetch data in field addressed by its name.

Note

This function has a macro: mysql_get_field(const fieldname[], string[]).

Parameters:

(string[], const fieldname[], connectionHandle)

string[]

The string where fetched data will be stored in.

const fieldname[]

Name of the field to be fetched.

connectionHandle

The connection handle this will be processed on.

Return Values:

This function does not return any specific values.

if(mysql_retrieve_row())
{
	new result[50];
	mysql_fetch_field_row(result,"username");
	//or if you use macro
	mysql_get_field("username",result);
}

Plugin callbacks

Note

This is only for advanced users aka those who use threaded queries.

OnQueryFinish

Description:

This callback is called if integer different than (-1) is inserted in 'resultid' parameter of the mysql_query().

Parameters:

(query[], resultid, extraid, connectionHandle)

query[]

Query which was processed.

resultid

Thread ID you specified.

extraid

Extra variable passed from mysql_query function.

connectionHandle

The connection handle this was processed on.

Return Values:

This callback does not handle returns.

#define THREAD_LOADPLAYER (5)
 
public OnPlayerConnect(playerid)
{
	mysql_query("SELECT * FROM players WHERE name='Myname'",THREAD_LOADPLAYER,playerid);
	return 1;
}
 
public OnQueryFinish(query[], resultid, extraid, connectionHandle)
{
	switch(resultid)
	{
		case THREAD_LOADPLAYER:
		{
			mysql_store_result();
			if(IsPlayerConnected(extraid))
			{
				//Execute your code
			}
			mysql_free_result();
		}
	}
	return 1;
}

OnQueryError

Description:

This callback is called when error occurs when processing a query.

Important
Note

This callback was added in version R5 and will not work in earlier versions!

Note

Common error codes for client and server.

Parameters:

(errorid, error[], resultid, extraid, callback[], query[], connectionHandle)

errorid

ID of the error.

error[]

Name of the error.

resultid

Thread ID you specified.

extraid

Extra variable passed from mysql_query function.

callback[]

Name of the callback from mysql_query_callback function. It equals to "NULL" if it's not used.

query[]

Query which was processed.

connectionHandle

The connection handle this was processed on.

Return Values:

This callback does not handle returns.

public OnQueryError(errorid, error[], resultid, extraid, callback[], query[], connectionHandle)
{
	switch(errorid)
	{
		case CR_COMMAND_OUT_OF_SYNC:
		{
			printf("Commands out of sync for thread ID: %d",resultid);
		}
		case ER_SYNTAX_ERROR:
		{
			printf("Something is wrong in your syntax, query: %s",query);
		}
	}
	return 1;
}

Retrieved from "https://sampwiki.blast.hk/wiki/MySQL/R6"

Categories: Outdated | Plugin Documentation

MySQL/R6

From SA-MP Wiki

Contents

Plugin functions

mysql_debug

mysql_connect

mysql_close

mysql_ping

mysql_stat

mysql_set_charset

mysql_get_charset

mysql_reconnect

mysql_reload

mysql_format

Format strings

mysql_query

mysql_query_callback

mysql_store_result

mysql_free_result

mysql_real_escape_string

mysql_errno

mysql_warning_count

mysql_affected_rows

mysql_num_rows

mysql_num_fields

mysql_insert_id

mysql_field_count

mysql_fetch_int

mysql_fetch_float

mysql_fetch_row_format

mysql_retrieve_row

mysql_fetch_field

mysql_fetch_field_row

Plugin callbacks

OnQueryFinish

OnQueryError

Views

Personal tools

Navigation

Search

Toolbox