MySQL

From SA-MP Wiki

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)

[edit]

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.

[edit]

mysql_debug

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

Parameters:

(enable)

enable

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

Returns

This function doesn't return a specific value

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

[edit]

mysql_connect

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.

Returns

Connection handle.

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

[edit]

mysql_close

Use this function to close database connection.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Returns

1 on success, 0 on fail.

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

[edit]

mysql_ping

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.

Returns

1 if alive, 0 if dead.

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

[edit]

mysql_stat

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.

Returns

This function doesn't return a specific value

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

[edit]

mysql_set_charset

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.

Returns

This function doesn't return a specific value

mysql_set_charset("utf8_unicode_ci");

[edit]

mysql_get_charset

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.

Returns

This function doesn't return a specific value

new charset[20]; mysql_get_charset(charset);

[edit]

mysql_reconnect

Use this function to reconnect to selected connection handle.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Returns

This function doesn't return a specific value

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

[edit]

mysql_reload

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.

Returns

This function doesn't return a specific value

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

[edit]

mysql_format

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.

Returns

This function doesn't return a specific value

[edit]

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);

[edit]

mysql_query

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.

Returns

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);

[edit]

mysql_query_callback

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.

Returns

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
}

[edit]

mysql_store_result

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.

Returns

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();

[edit]

mysql_free_result

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

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Returns

This function doesn't return a specific value

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

[edit]

mysql_real_escape_string

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.

Note: Database connection is required before using this function.

Note: It does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

Returns

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
	// ...
}

[edit]

mysql_errno

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.

Returns

Error code, 0 if no error occurred.

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

[edit]

mysql_warning_count

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

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Returns

Number of warnings, 0 if none.

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

[edit]

mysql_affected_rows

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.

Returns

Number of affected rows.

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

[edit]

mysql_num_rows

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.

Returns

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);

[edit]

mysql_num_fields

Returns the number of columns in a result set.

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Returns

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();

[edit]

mysql_insert_id

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.

Returns

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());

[edit]

mysql_field_count

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.

Returns

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();

[edit]

mysql_fetch_int

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

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Returns

Integer from the result set.

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

[edit]

mysql_fetch_float

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.

Returns

This function doesn't return a specific value

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

[edit]

mysql_fetch_row_format

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

Note: This function also moves the internal row pointer to the next row.

Note: 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.

Returns

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();

[edit]

mysql_retrieve_row

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.

Note: This function also moves the internal row pointer to the next row.

Note: This function has a macro: mysql_next_row().

Parameters:

(connectionHandle)

connectionHandle

The connection handle this will be processed on.

Returns

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();

[edit]

mysql_fetch_field

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

Returns

This function doesn't return a specific value

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

[edit]

mysql_fetch_field_row

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.

Returns

This function doesn't return a specific value

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

[edit]

Plugin callbacks

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

[edit]

OnQueryFinish

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.

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

[edit]

OnQueryError

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.

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 "http://wiki.sa-mp.com/wiki/MySQL"

MySQL

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