MySQL/R6

From SA-MP Wiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 08:50, 7 February 2020
RoboN1X (Talk | contribs)
(mysql_query_callback - return value)
← Previous diff
Revision as of 12:24, 13 February 2020
RoboN1X (Talk | contribs)
(return value, and mysql_fetch_int)
Next diff →
Line 78: Line 78:
<pawn> <pawn>
-if(mysql_ping()) print("MySQL connection is still alive!");+if(mysql_ping() == 1) print("MySQL connection is still alive!");
// ... // ...
-if(!mysql_ping()) print("MySQL connection is dead!");+if(mysql_ping() != 1) print("MySQL connection is dead!");
</pawn> </pawn>
Line 108: Line 108:
{{Param|connectionHandle|The connection handle this will be processed on.}} {{Param|connectionHandle|The connection handle this will be processed on.}}
-{{NoReturn}}+{{Returns|Always 0}}
<pawn>mysql_set_charset("utf8_unicode_ci");</pawn> <pawn>mysql_set_charset("utf8_unicode_ci");</pawn>
Line 120: Line 120:
{{Param|connectionHandle|The connection handle this will be processed on.}} {{Param|connectionHandle|The connection handle this will be processed on.}}
-{{NoReturn}}+{{Returns|Always 0}}
<pawn>new charset[20]; mysql_get_charset(charset);</pawn> <pawn>new charset[20]; mysql_get_charset(charset);</pawn>
Line 175: Line 175:
| %s || Inserts a string. | %s || Inserts a string.
|- |-
-| %d || Inserts an integer (whole) number+| %d or %i || Inserts an integer (whole) number
|- |-
| %f || Inserts a floating point 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. The values for the placeholders follow in the exact same order as parameters in the call.
Line 396: Line 394:
== mysql_fetch_int == == mysql_fetch_int ==
-{{Description|Use this function to retrieve single integer value from the result set.}}+{{Description|Use this function to retrieve single integer (positive number) from the result set.}}
 +{{Note|This function only works with numerical digits (0-9) and may not work with negative value from result.}}
{{Parameters|connectionHandle}} {{Parameters|connectionHandle}}
{{Param|connectionHandle|The connection handle this will be processed on.}} {{Param|connectionHandle|The connection handle this will be processed on.}}
-{{Returns|Integer from the result set.}}+{{Returns|Integer from the result set. -1 if not numeric.}}
<pawn>mysql_query("SELECT myid FROM players LIMIT 1"); <pawn>mysql_query("SELECT myid FROM players LIMIT 1");

Revision as of 12:24, 13 February 2020

Image:50px-Ambox_outdated_serious.png 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)

Contents

Plugin functions

Image:32px-Ambox_warning_orange.png

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


Image:32px-Circle-style-warning.png

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)
connectionHandleThe connection handle this will be processed on.


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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.


Image:32px-Circle-style-warning.png

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)
connectionHandleThe connection handle this will be processed on.


Return Values:

1 if alive, 0 if invalid handle, -1 if dead.


if(mysql_ping() == 1) print("MySQL connection is still alive!");
// ...
if(mysql_ping() != 1) 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.
connectionHandleThe connection handle this will be processed on.


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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.
connectionHandleThe connection handle this will be processed on.


Return Values:

Always 0


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.
connectionHandleThe connection handle this will be processed on.


Return Values:

Always 0


new charset[20]; mysql_get_charset(charset);


mysql_reconnect

Description:

Use this function to reconnect to selected connection handle.


Parameters:
(connectionHandle)
connectionHandleThe connection handle this will be processed on.


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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


Image:32px-Circle-style-warning.png

Important
Note

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


Parameters:
(connectionHandle)
connectionHandleThe connection handle this will be processed on.


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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.


Image:32px-Circle-style-warning.png

Important
Note

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


Image:32px-Ambox_warning_orange.png

Note

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


Parameters:
(connectionHandle, output[], format[], {Float,_}:...)
connectionHandleThe 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 or %i Inserts an integer (whole) number
%f Inserts a floating point number.

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.


Image:32px-Circle-style-warning.png

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.
resultidOptional (if you specify this query will be processed in a separate thread).
extraidOptional (extra variable that would be processed to the callback.
connectionHandleThe connection handle this will be processed on.


Image:32px-Ambox_warning_orange.png

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: The function was executed successfully.
  • 0: The function failed to execute.


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


Image:32px-Circle-style-warning.png

Important
Note

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


Parameters:
(index, query[], callback[], extraid, connectionHandle)
indexExtra variable which is processed to the callback.
query[]The query you want to process.
callback[]Name of the function to call.
extraidOptional (extra variable that would be processed to the callback.
connectionHandleThe connection handle this will be processed on.


Image:32px-Ambox_warning_orange.png

Note

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


Return Values:

Thread index or 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.


Image:32px-Ambox_warning_orange.png

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)
connectionHandleThe connection handle this will be processed on.


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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)
connectionHandleThe connection handle this will be processed on.


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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.


Image:32px-Circle-style-warning.png

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.
connectionHandleThe connection handle this will be processed on.


Image:32px-Ambox_warning_orange.png

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)
connectionHandleThe 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)
connectionHandleThe 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)
connectionHandleThe connection handle this will be processed on.


Image:32px-Ambox_warning_orange.png

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)
connectionHandleThe 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)
connectionHandleThe 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)
connectionHandleThe 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)
connectionHandleThe 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 (positive number) from the result set.
Image:32px-Ambox_warning_orange.png

Note

This function only works with numerical digits (0-9) and may not work with negative value from result.


Parameters:
(connectionHandle)
connectionHandleThe connection handle this will be processed on.


Return Values:

Integer from the result set. -1 if not numeric.


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:resultA float to store the result, passed by reference.
connectionHandleThe connection handle this will be processed on.


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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.


Image:32px-Ambox_warning_orange.png

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


Image:32px-Ambox_warning_orange.png

Notes

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


Parameters:
(connectionHandle)
connectionHandleThe 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)
numberIndex of the field.
dest[]The string where fetched data will be stored in.
connectionHandleThe connection handle this will be processed on.


Image:32px-Circle-style-warning.png

Important
Note

If invalid field number is used plugin will crash.


Image:32px-Ambox_warning_orange.png

Note

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


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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.


Image:32px-Ambox_warning_orange.png

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.
connectionHandleThe connection handle this will be processed on.


Return Values:

  • 1: The function was executed successfully.
  • 0: The function failed to execute.


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

Image:32px-Ambox_warning_orange.png

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.
resultidThread ID you specified.
extraidExtra variable passed from mysql_query function.
connectionHandleThe 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.


Image:32px-Circle-style-warning.png

Important
Note

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


Image:32px-Ambox_warning_orange.png

Note

Common error codes for client and server.


Parameters:
(errorid, error[], resultid, extraid, callback[], query[], connectionHandle)
erroridID of the error.
error[]Name of the error.
resultidThread ID you specified.
extraidExtra 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.
connectionHandleThe 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;
}
Personal tools