MySQL/R33

From SA-MP Wiki

Jump to: navigation, search
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.


MySQL Plugin
Plugin
AuthorBlueG / maddinat0r
Released29/11/2008
Latest VersionR39-6 (27/08/2016)
Development Status Active
License License
Forum Topic

For the latest functions go here.

Documentation for BlueG's MySQL plugin version R33 to R39-6. Forum topic and download links can be found here.

Contents

ORM functions

Image:32px-Ambox_warning_orange.png

Note

A good tutorial for this system can be found here.


orm_create

Description:

Creates an ORM instance and returns its id.


Parameters:
(const table[], connectionHandle = 1)
const table[]The name of the table you wish to control.
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

ORM id on success, 0 on failure.


public OnPlayerConnect(playerid)
{
	new ORM:orm_id = Player[playerid][ORM_ID] = orm_create("players");
	return 1;
}


orm_destroy

Description:

Destroys an ORM instance.


Parameters:
(ORM:id)
ORM:idThe id of the ORM instance.


Return Values:

This function does not return any specific values.


public OnPlayerDisconnect(playerid, reason)
{
	orm_destroy(Player[playerid][ORM_ID]);
	return 1;
}


orm_errno

Description:

Returns the error-id of the last ORM operation.


Parameters:
(ORM:id)
ORM:idThe id of the ORM instance.


Return Values:

Error id.


Available errors
Error Meaning
ERROR_OK No error happened.
ERROR_NO_DATA No data has been found in the table.
orm_select(Player[playerid][ORM_ID], "OnStuffSelected", "d", playerid);
 
public OnStuffSelected(playerid)
{
	switch(orm_errno(Player[playerid][ORM_ID])
	{
		case ERROR_OK:
			printf("There is no error.");
		case ERROR_NO_DATA:
			printf("No data in the table found.");
	}
	return 1;
}

orm_apply_cache

Description:

Applies the data of the active cache to an ORM instance.


Parameters:
(ORM:id, row)
ORM:idThe id of the ORM instance.
rowThe row index to take the cache data from.


Return Values:

1 on success, 0 on failure.


new query[128];
format(query, sizeof(query), "SELECT * FROM `players` WHERE `id` = '%d'", Player[playerid][ID]);
mysql_tquery(MySQL, query, "OnStuffSelected", "d", playerid);
 
public OnStuffSelected(playerid)
{
	orm_apply_cache(Player[playerid][ORM_ID], 0);
	printf("Player %s has %d Money and is on PosX with %f.", Player[playerid][Name], Player[playerid][Money], Player[playerid][PosX]);
	return 1;
}


orm_select

Description:

Sends a SELECT query and applies the retrieved data to the previously registered variables.


Parameters:
(ORM:id, callback[] = "", format[] = "", {Float, _}:...)
ORM:idThe id of the ORM instance.
callback[]The name of the callback to call when the operation is done (optional).
format[]The format specifier for the callback (optional).
{Float, _}:...Indefinite number of parameters to pass to the callback (optional).


Return Values:

1 on success, 0 on failure.


orm_select(Player[playerid][ORM_ID], "OnPlayerDataLoaded", "d", playerid);
 
public OnPlayerDataLoaded(playerid)
{
	printf("Player %s has %d Money and is on PosX with %f.", Player[playerid][Name], Player[playerid][Money], Player[playerid][PosX]);
	return 1;
}


orm_update

Description:

Sends an UPDATE query with the current values of the registered variables.


Parameters:
(ORM:id)
ORM:idThe id of the ORM instance.


Return Values:

1 on success, 0 on failure.


orm_update(Player[playerid][ORM_ID]);
//this generates a query like "UPDATE `players` SET ┬┤name┬┤='PlayerName', `money`='23141', `pos_x`='231.432' WHERE `id`='42'" and executes it


orm_insert

Description:

Sends an INSERT query with the current values of the registered variables.


Parameters:
(ORM:id, callback[] = "", format[] = "", {Float, _}:...)
ORM:idThe id of the ORM instance.
callback[]The name of the callback to call when the operation is done (optional).
format[]The format specifier for the callback (optional).
{Float, _}:...Indefinite number of parameters to pass to the callback (optional).


Return Values:

1 on success, 0 on failure.


orm_insert(Player[playerid][ORM_ID], "OnPlayerRegistered", "d", playerid);
 
public OnPlayerRegistered(playerid)
{
	printf("Player %s has registered with id %d.", Player[playerid][Name], Player[playerid][ID]);
	return 1;
}


orm_delete

Description:

Sends a DELETE query.


Parameters:
(ORM:id, bool:clearvars = true)
ORM:idThe id of the ORM instance.
bool:clearvarsTrue if the values of the registered variables should be set to zero (optional).


Return Values:

1 on success, 0 on failure.


orm_delete(Player[playerid][ORM_ID]);
//this generates a query like "DELETE FROM `players` WHERE `id`='42'" and executes it


orm_load

Description:

Fetches data from a table and applies it to the previously registered variables. This function is technically the same as orm_select().


Parameters:
(ORM:id, callback[] = "", format[] = "", {Float, _}:...)
ORM:idThe id of the ORM instance.
callback[]The name of the callback to call when the operation is done (optional).
format[]The format specifier for the callback (optional).
{Float, _}:...Indefinite number of parameters to pass to the callback (optional).


Return Values:

1 on success, 0 on failure.


orm_load(Player[playerid][ORM_ID], "OnPlayerDataLoaded", "d", playerid);
 
public OnPlayerDataLoaded(playerid)
{
	printf("Player %s has %d Money and is on PosX with %f.", Player[playerid][Name], Player[playerid][Money], Player[playerid][PosX]);
	return 1;
}


orm_save

Description:

Saves data to a table. This function is a combination of orm_insert() and orm_update(). If the previously specified key variable has a valid value, orm_save calls orm_update, else orm_insert.


Parameters:
(ORM:id, callback[] = "", format[] = "", {Float, _}:...)
ORM:idThe id of the ORM instance.
callback[]The name of the callback to call when the operation is done (optional).
format[]The format specifier for the callback (optional).
{Float, _}:...Indefinite number of parameters to pass to the callback (optional).


Return Values:

1 on success, 0 on failure.


Player[playerid][Money] = GetPlayerMoney(playerid);
orm_save(Player[playerid][ORM_ID]);


orm_addvar_int

Description:

Registers an integer variable to an ORM instance and links it to the specified field.


Parameters:
(ORM:id, &var, varname[])
ORM:idThe id of the ORM instance.
&varThe variable to register.
varname[]The name of the field in the MySQL table.


Return Values:

1 if successfully added, 0 if not.


new ORM:ormid = orm_create("players");
 
orm_addvar_int(ormid, Player[playerid][ID], "id");
orm_addvar_int(ormid, Player[playerid][Money], "money");


orm_addvar_float

Description:

Registers a floating point variable to an ORM instance and links it to the specified field.


Parameters:
(ORM:id, &var, varname[])
ORM:idThe id of the ORM instance.
&Float:varThe variable to register.
varname[]The name of the field in the MySQL table.


Return Values:

1 if successfully added, 0 if not.


new ORM:ormid = orm_create("players");
 
orm_addvar_float(ormid, Player[playerid][PosX], "pos_x");
orm_addvar_float(ormid, Player[playerid][PosY], "pos_y");


orm_addvar_string

Description:

Registers a string variable to an ORM instance and links it to the specified field.


Parameters:
(ORM:id, var[], var_maxlen, varname[])
ORM:idThe id of the ORM instance.
var[]The variable to register.
var_maxlenThe size of the registered variable.
varname[]The name of the field in the MySQL table.


Return Values:

1 if successfully added, 0 if not.


enum E_PLAYER {
	// ...
	Name[MAX_PLAYER_NAME],
	Password[129]
};
new Player[MAX_PLAYERS][E_PLAYER];
 
// ...
 
new ORM:ormid = orm_create("players");
 
orm_addvar_string(ormid, Player[playerid][Name], MAX_PLAYER_NAME, "name");
orm_addvar_string(ormid, Player[playerid][Password], 129, "passwd");


orm_delvar

Description:

Removes a previously registered variable from the specified ORM instance by its field name.


Parameters:
(ORM:id, varname[])
ORM:idThe id of the ORM instance.
varname[]The name of the field.


Return Values:

1 if successfully deleted, 0 if not.


new ORM:ormid = orm_create("players");
 
orm_addvar_int(ormid, Player[playerid][ID], "id");
// ...
orm_delvar(ormid, "id"); //returns true, variable "Player[playerid][ID]" has been removed
orm_delvar(ormid, "id"); //returns false, because the variable couldn't be found as it was already removed


orm_setkey

Description:

Sets a previously registered variable as key specified by the field name the variable has been linked to.


Parameters:
(ORM:id, varname[])
ORM:idThe id of the ORM instance.
varname[]The name of the field in the MySQL table.


Return Values:

This function does not return any specific values.


new ORM:ormid = orm_create("players");
 
orm_addvar_int(ormid, Player[playerid][ID], "id");
orm_addvar_float(ormid, Player[playerid][PosX], "pos_x");
// ...
orm_setkey(ormid, "id");



MySQL functions

Image:32px-Ambox_warning_orange.png

Note

Almost every function has a connectionHandle parameter. If you use only one database connection you don't need to mind it. Connection handle is 1 by default.


mysql_log

Description:

Controls what information will be logged.


Image:32px-Ambox_warning_orange.png

Note

Logging is always enabled by default, even if you don't call mysql_log.


Parameters:
(E_LOGLEVEL:loglevel = LOG_ERROR | LOG_WARNING, E_LOGTYPE:logtype = LOG_TYPE_TEXT)
E_LOGLEVEL:loglevelSpecifies what type of log messages will be logged (optional).
E_LOGTYPE:logtypeType of the logging (optional).


Return Values:

This function does not return any specific values.


Log levels

Log type Description
LOG_NONE Logs absolutely nothing.
LOG_ERROR Logs errors.
LOG_WARNING Logs warnings.
LOG_DEBUG Logs debug messages.
LOG_ALL Logs everything.
Image:32px-Circle-style-warning.png

Important
Note

The log type LOG_ALL was added in R35 and can't be used in any version below.


Log types

Log type Description
LOG_TYPE_TEXT Writes log messages into a text file.
LOG_TYPE_HTML Writes log messages into a visual HTML file.


public OnGameModeInit()
{
	mysql_log(LOG_ERROR | LOG_WARNING, LOG_TYPE_HTML); //logs errors and warnings into a nice HTML file
	//...
	mysql_log(LOG_ALL); //logs everything (errors, warnings and debug messages) into a regular text file
	return 1;
}

mysql_connect

Description:

Connects to a MySQL server and database.


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

Important
Notes

  • Do not use this function to reconnect to the database. Use mysql_reconnect() instead.
  • The parameter pool_size was added in R35 and can't be used in any version below.


Parameters:
(const host[], const user[], const database[], const password[], port = 3306, bool:autoreconnect = true, pool_size = 2)
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.
portPort of the MySQL server (optional).
bool:autoreconnectTrue to enable, false to disable automatic reconnection (optional).
pool_sizeSize of the connection pool to use for mysql_pquery() (optional).


Return Values:

Connection handle.



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


mysql_close

Description:

Closes the MySQL connection.


Image:32px-Ambox_warning_orange.png

Note

The `wait` parameter has been removed in R36. `mysql_close` will now always wait until all queued queries are executed.


Parameters:
(connectionHandle = 1, bool:wait = true)
connectionHandleThe connection handle this will be processed on (optional).
bool:waitTrue if to wait until all queued queries are executed (optional).


Return Values:

1 on success, 0 on fail.


public OnGameModeExit()
{
	mysql_tquery(MySQL, "UPDATE `players` SET `is_online` = '0'", "", "");
 
	mysql_close(MySQL); //mysql_close will now halt the server until the query we just sent is executed.
	return 1;
}


mysql_reconnect

Description:

Reconnects to the selected connection handle.


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


Return Values:

This function does not return any specific values.


//connection was lost for some reason, let's reconnect
mysql_reconnect();


mysql_unprocessed_queries

Description:

Returns the number of unprocessed (threaded) queries.


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


Return Values:

Number of unprocessed queries.


printf("There are %d unprocessed queries.", mysql_unprocessed_queries());

mysql_current_handle

Description:

Returns the connection id currently used by the active cache.


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

Important
Note

This native was added in R35 and can't be used in any version below.


This function does not take any parameters.

Return Values:

ID of currently used connection or 0 if there is none.


forward OnHouseDataLoaded();
 
public OnGameModeInit()
{
	mysql_tquery(MySQL, "SELECT * FROM `houses`", "OnHouseDataLoaded");
 
	printf("Current connection id: %d". mysql_current_handle());
	//this would print "Current connection id: 0", since there is no active cache and thus no active connection
 
	return 1;
}
 
public OnHouseDataLoaded()
{
	printf("House data loaded on connection id %d.", mysql_current_handle());
	//this would print "House data loaded on connection id 1."
	return 1;
}

mysql_option

Description:

Sets global options regarding the MySQL plugin.


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

Important
Note

This native was added in R35 and can't be used in any version below.


Parameters:
(E_MYSQL_OPTION:type, value)
E_MYSQL_OPTION:typeOption to change.
valueValue the option should be set to.


Return Values:

This function does not return any specific values.


Options

Option Description
DUPLICATE_CONNECTIONS Allows to create multiple connections to the same database and server.
LOG_TRUNCATE_DATA Controls whether MySQL data (that means queries and fetched result data) should be truncated (queries to 64 characters, result data to 1024 characters) in the log file or not (true by default).
public OnGameModeInit()
{
	mysql_option(DUPLICATE_CONNECTIONS, true); //allows the use of dupl. connections
 
	new MySQL1 = mysql_connect("127.0.0.1", "root", "mydatabase", "mypass");
	new MySQL2 = mysql_connect("127.0.0.1", "root", "mydatabase", "mypass");
	printf("connection1: %d, connection2: %d", MySQL1, MySQL2);
	//output: "connection1: 1, connection2: 2", without allowing duplicate connections, both variables would be '1'
 
	return 1;
}

mysql_errno

Description:

Returns the error code of the error message from the previous MySQL operation.


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


Return Values:

Error code, 0 if no error occurred, -1 if invalid connection handle.


mysql_connect("127.0.0.1", "root", "mydatabase", "mypass");
if(mysql_errno() != 0) 
	print("Could not connect to database!");


mysql_escape_string

Description:

Escapes special characters in a string for the 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() with the '%e' specifier) 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 = 1, max_len = sizeof(destination))
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 (optional).
max_lenThe size of the destination (optional).


Return Values:

Length of escaped string.


Image:32px-Ambox_warning_orange.png

Notes

  • A 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.


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

Important
Notes

  • You have to provide the size (max_len) by yourself if you use an enum-array as destination.
  • If the destination cannot hold the whole text escaped, the function will silently fail and leave destination unchanged.


enum E_PLAYER {
	// ...
	LastMsg[128],
	// ...
};
new PlayerInfo[MAX_PLAYERS][E_PLAYER];
// ...
public OnPlayerText(playerid, text[])
{
	new escape[140*2];
	mysql_escape_string(text, escape); //string is now safe to be put in a query
	// ...
	mysql_escape_string(text, PlayerInfo[playerid][LastMsg], MySQL, 128); //correctly saves the escaped string into an enum-array
	return 1;
}

mysql_format

Description:

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


Parameters:
(connectionHandle, output[], len, format[], {Float,_}:...)
connectionHandleThe connection handle this will be processed on.
output[]The string to save the result to.
lenThe size of the output.
format[]The format string.
{Float,_}:...Indefinite number of arguments.


Return Values:

Length of the formatted string.


Format strings

Placeholder Meaning
%e Escapes data directly without the need to call mysql_escape_string() before.
%s Inserts a string.
%d / %i Inserts an integer number.
%f Inserts a floating point number.
%X Inserts a hexadecimal number in uppercase.
%x Inserts a hexadecimal number in lowercase.
%b Inserts a binary number.

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

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

mysql_pquery

Description:

Sends a query which will be executed in another thread concurrently and calls the callback (if there is one) when the execution is finished.


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

Important
Notes

  • This native was added in R35 and can't be used in any version below.
  • This type of query doesn't support transactions.


Image:32px-Ambox_warning_orange.png

Note

The difference between this native and mysql_tquery() is, that this type of query uses multi-threading, thus it's faster depending on how many connections are used. The number of connections can be specified in mysql_connect() through the pool_size parameter. Each connection resembles a thread.


Parameters:
(connectionHandle, query[], callback[] = "", format[] = "", {Float,_}:...)
connectionHandleThe connection handle this will be processed on.
query[]The query you want to execute.
callback[]The query you want to process (optional).
format[]The format specifier string (optional).
{Float,_}:...Indefinite number of arguments (optional).


Return Values:

1 on success, 0 on fail.


Image:32px-Ambox_warning_orange.png

Notes

  • The callback must be a public function. That means it has to be forwarded.
  • The length of the format specifier must be the same as the number of arguments passed, or otherwise the query won't be executed!


Format specifiers

Specifier Meaning
d/i integer number
s string
f floating point number
forward OnPlayerDataLoaded(playerid);
 
public OnPlayerConnect(playerid)
{
	new query[128], pname[MAX_PLAYER_NAME];
	GetPlayerName(playerid, pname, MAX_PLAYER_NAME);
	mysql_format(MySQL, query, sizeof(query), "SELECT * FROM `players` WHERE `Name` = '%e' LIMIT 1", pname);
	mysql_pquery(MySQL, query, "OnPlayerDataLoaded", "d", playerid);
	return 1;
}
 
public OnPlayerDataLoaded(playerid)
{
	//Query processed, you can now execute cache functions (like cache_get_row) here.
	new NumRows = cache_num_rows();
	printf("There are %d players with the same name.", NumRows);
	return 1;
}

mysql_tquery

Description:

Sends a query which will be executed in another thread and calls the callback (if there is one) when the execution is finished.


Parameters:
(connectionHandle, query[], callback[] = "", format[] = "", {Float,_}:...)
connectionHandleThe connection handle this will be processed on.
query[]The query you want to execute.
callback[]The query you want to process (optional).
format[]The format specifier string (optional).
{Float,_}:...Indefinite number of arguments (optional).


Return Values:

1 on success, 0 on fail.


Image:32px-Ambox_warning_orange.png

Notes

  • The callback must be a public function. That means it has to be forwarded.
  • The length of the format specifier must be the same as the number of arguments passed, or otherwise the query won't be executed!


Format specifiers

Specifier Meaning
d/i integer number
s string
f floating point number
forward OnPlayerDataLoaded(playerid);
 
public OnPlayerConnect(playerid)
{
	new query[128], pname[MAX_PLAYER_NAME];
	GetPlayerName(playerid, pname, sizeof(pname));
	mysql_format(MySQL, query, sizeof(query), "SELECT * FROM `players` WHERE `Name` = '%e'", pname);
	mysql_tquery(MySQL, query, "OnPlayerDataLoaded", "d", playerid);
	return 1;
}
 
public OnPlayerDataLoaded(playerid)
{
	//Query processed, you can now execute cache functions (like cache_get_row) here.
	new NumRows = cache_num_rows();
	printf("There are %d players with the same name.", NumRows);
	return 1;
}


mysql_query

Description:

This native sends a non-threaded query to the MySQL server. The SA:MP server (the main PAWN thread) waits until the query has been executed and then returns the cache handle.


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

Important
Notes

  • It's highly recommended to thread all your queries (even INSERT & UPDATE).
  • Use cache_delete() if you don't need the query's result anymore or you will experience memory leaks.


Parameters:
(conhandle, query[], bool:use_cache = true)
conhandleThe connection handle this will be processed on.
query[]The query you want to execute.
bool:use_cacheSet to true if you intend to use the cache/result mysql_query returns (optional).


Return Values:

A valid cache-id if use_cache is set to true.


Image:32px-Ambox_warning_orange.png

Note

If use_cache is set to false, there won't be any valid cache to use, so all cache-related natives wont work. You also don't need to call cache_delete() in this case.


new Cache:result = mysql_query(MySQL, "SELECT COUNT(*) FROM `players`");
printf("There are %d players in the database.", cache_get_row_int(0, 0));
cache_delete(result);

mysql_stat

Description:

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


Parameters:
(destination[], connectionHandle = 1, max_len = sizeof(destination))
const destination[]The string to store extracted data in.
connectionHandleThe connection handle this will be processed on (optional).
max_lenThe size of the destination string (optional).


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_get_charset

Description:

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


Parameters:
(destination[], connectionHandle = 1, max_len = sizeof(destination))
destination[]The string to store extracted data in.
connectionHandleThe connection handle this will be processed on (optional).
max_lenThe size of the destination string (optional).


Return Values:

1 on success, 0 on fail.


new charset[20];
mysql_get_charset(charset);


mysql_set_charset

Description:

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


Parameters:
(charset[], connectionHandle = 1)
charset[]Code of the character set you want to use.
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

1 on success, 0 on fail.


mysql_set_charset("utf8");

Cache functions

Image:32px-Ambox_warning_orange.png

Note

Make sure you use these functions (except cache_delete() and cache_set_active()) only if there is an active cache available.


cache_get_data

Description:

Assigns the number of rows and fields to two variables.


Parameters:
(&num_rows, &num_fields, connectionHandle = 1)
&num_rowsThe variable the number of rows will be assigned to.
&num_fieldsThe variable the number of fields will be assigned to.
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

This function does not return any specific values.


new rows, fields;
cache_get_data(rows, fields);
printf("There are %d rows and %d fields in the current result set (aka cache).", rows, fields);


cache_get_row_count

Description:

Returns the number of rows.


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


Return Values:

Number of rows.


printf("There are %d rows in the current result set.", cache_get_row_count());


cache_get_field_count

Description:

Returns the number of fields.


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


Return Values:

Number of fields.


printf("There are %d fields in the current result set.", cache_get_field_count());


cache_get_field_name

Description:

Retrieves a field's name specified by an index.


Parameters:
(field_index, destination[], connectionHandle = 1, max_len = sizeof(destination))
field_indexThe index of the field whose name to retrieve.
destination[]The string to store the name into.
connectionHandleThe connection handle this will be processed on (optional).
max_lenThe size of the destination string (optional).


Return Values:

This function does not return any specific values.


new field_name[32];
cache_get_field_name(0, field_name);
printf("The first field name in the current result set is '%s'.", field_name);


cache_get_row

Description:

Retrieves a value from the result set as a string.


Parameters:
(row, field_idx, destination[], connectionHandle = 1, max_len = sizeof(destination))
rowThe row's index (starts at '0').
field_idxThe index of the field (starts at '0').
destination[]The string to store the data into.
connectionHandleThe connection handle this will be processed on (optional).
max_lenThe size of the destination string (optional).


Return Values:

This function does not return any specific values.


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

Important
Note

You have to provide the size (max_len) by yourself if you use an enum-array as destination.


new dest[128];
cache_get_row(0, 0, dest);
printf("The very first value in the current result set is '%s'.", dest);


cache_get_row_int

Description:

Retrieves a value from the result set as a integer (normal number).


Parameters:
(row, field_idx, connectionHandle = 1)
rowThe row's index (starts at '0').
field_idxThe index of the field (starts at '0').
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

Retrieved value as integer (number).


new int_dest = cache_get_row_int(3, 0);
printf("The number stored in the fourth row and first field is '%d'.", int_dest);


cache_get_row_float

Description:

Retrieves a value from the result set as a floating point number.


Parameters:
(row, field_idx, connectionHandle = 1)
rowThe row's index (starts at '0').
field_idxThe index of the field (starts at '0').
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

Retrieved value as floating point number.


new Float:float_dest = cache_get_row_float(3, 4);
printf("The floating point number stored in the fourth row and fifth field is '%f'.", float_dest);


cache_get_field_content

Description:

Retrieves a value from the result set as a string.


Parameters:
(row, const field_name[], destination[], connectionHandle = 1, max_len = sizeof(destination))
rowThe row's index (starts at '0').
const field_name[]The field's name.
destination[]The string to store the data into.
connectionHandleThe connection handle this will be processed on (optional).
max_lenThe size of the destination string (optional).


Return Values:

This function does not return any specific values.


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

Important
Note

You have to provide the size (max_len) by yourself if you use an enum-array as destination.


new dest[128];
cache_get_field_content(0, "name", dest);
printf("The value in the field 'name' is '%s'.", dest);


cache_get_field_content_int

Description:

Retrieves a value from the result set as an integer (normal number).


Parameters:
(row, const field_name[], connectionHandle = 1)
rowThe row's index (starts at '0').
const field_name[]The field's name.
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

Retrieved value as integer (normal number).


new int_dest = cache_get_field_content_int(2, "money");
printf("The value in the third row and in the field 'money' is '%d'.", int_dest);


cache_get_field_content_float

Description:

Retrieves a value from the result set as an floating point number.


Parameters:
(row, const field_name[], connectionHandle = 1)
rowThe row's index (starts at '0').
const field_name[]The field's name.
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

Retrieved value as floating point number.


new Float:float_dest = cache_get_field_content_float(3, "pos_x");
printf("The value in the fourth row and in the field 'pos_x' is '%f'.", float_dest);


cache_save

Description:

Saves the active cache in the memory and returns an cache-id to access it for later use.


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


Return Values:

Valid cache-id on success or 0 on failure.


enum E_PLAYER {
	ID,
	Name[MAX_PLAYER_NAME],
	Cache:Data,
	// ...
};
new Player[MAX_PLAYERS][E_PLAYER];
 
 
public OnPlayerConnect(playerid)
{
	new query[128];
	GetPlayerName(playerid, Player[playerid][Name], MAX_PLAYER_NAME);
	mysql_format(MySQL, query, sizeof(query), "SELECT * FROM `players` WHERE `name` = '%e' LIMIT 1", Player[playerid][Name]);
	mysql_tquery(MySQL, query, "OnPlayerDataLoaded", "d", playerid);
 
	return 1;
}
 
forward OnPlayerDataLoaded(playerid);
public OnPlayerDataLoaded(playerid)
{
	if(cache_num_rows() == 1)
	{
 
		//save the cache for later use
		Player[playerid][Data] = cache_save();
 
		//show login dialog
		// ShowPlayerDialog(playerid, ...
	}
	// else //show register dialog
		// ShowPlayerDialog(playerid, ...
	return 1;
}


cache_delete

Description:

Deletes the specified cache from the memory.


Parameters:
(Cache:cache_id, connectionHandle = 1)
Cache:cache_idThe cache-id which should be deleted.
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

1 on success, 0 on fail.


enum E_PLAYER {
	ID,
	Name[MAX_PLAYER_NAME],
	Cache:Data,
	// ...
};
new Player[MAX_PLAYERS][E_PLAYER];
 
 
public OnPlayerDisconnect(playerid, reason)
{
	cache_delete(Player[playerid][Data]);
	// ...
	return 1;
}


cache_set_active

Description:

Sets the specified cache as the active cache.


Image:32px-Ambox_warning_orange.png

Note

If you specify '0' as cache-id, the active cache will be unset, thus there won't be any cache active.


Parameters:
(Cache:cache_id, connectionHandle = 1)
Cache:cache_idThe cache-id which should be set as active.
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

1 on success, 0 on fail.


enum E_PLAYER {
	ID,
	Name[MAX_PLAYER_NAME],
	Cache:Data,
	Money,
	Float:PosX,
	// ...
};
new Player[MAX_PLAYERS][E_PLAYER];
 
 
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
	switch(dialogid)
	{
		// ...
		case DIALOG_LOGIN:
		{
			//if password matches
			cache_set_active(Player[playerid][Data]);
 
			Player[playerid][Money] = cache_get_field_content_int(0, "money");
			Player[playerid][PosX] = cache_get_field_content_float(0, "pos_x");
			// ...
			cache_set_active(Cache:0); //unset active cache
		}
		// ...
		default:
			return 0;
	}
	return 1;
}


cache_is_valid

Description:

Checks if the specified cache is valid.


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

Important
Note

This native was added in R35 and can't be used in any version below.


Parameters:
(Cache:cache_id, connectionHandle = 1)
Cache:cache_idThe cache-id which should be checked.
connectionHandleThe connection handle this will be processed on (optional).


Return Values:

1 if valid, 0 if invalid.


enum E_PLAYER {
	ID,
	Name[MAX_PLAYER_NAME],
	Cache:Data,
	// ...
};
new Player[MAX_PLAYERS][E_PLAYER];
 
public OnPlayerUpdate(playerid)
{
	//if random event...
	cache_delete(Player[playerid][Data]);
 
	return 1;
}
 
public OnPlayerDisconnect(playerid, reason)
{
	if(cache_is_valid(Player[playerid][Data]))
		cache_delete(Player[playerid][Data]);
	// ...
 
	return 1;
}


cache_affected_rows

Description:

Returns the number of affected rows if the query was an INSERT, UPDATE, REPLACE or DELETE query.


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


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_tquery(MySQL, "DELETE FROM mylogs WHERE log_id > 10", "OnLogsDeleted", "");
// ...
public OnLogsDeleted()
{
	printf("%d logs deleted!", cache_affected_rows());
	return 1;
}


cache_insert_id

Description:

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


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


Return Values:

ID generated for an AUTO_INCREMENT column.


mysql_tquery(MySQL, "INSERT INTO `players` (`name`, `password`) VALUES ('Ownage', MD5('mypass'))", "OnPlayerRegister", "d", playerid);
// ...
public OnPlayerRegister(playerid)
{
	printf("New player registered with ID '%d'.", cache_insert_id());
	return 1;
}


cache_warning_count

Description:

Returns the number of warnings the sent query generated.


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


Return Values:

Number of warnings, 0 if none.


mysql_tquery(MySQL, "UPDATE `notable` SET something=42 WHERE nofield=0", "OnStuffUpdated", "");
// ...
public OnStuffUpdated()
{
	if(cache_warning_count())
		printf("Some warnings occured!!");
 
	return 1;
}


cache_get_query_exec_time

Description:

Returns the time the query took to be executed.


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

Important
Note

This native was added in R36 and can't be used in any version below.


Parameters:
(E_EXECTIME_UNIT:unit = UNIT_MICROSECONDS)
E_EXECTIME_UNIT:unitTime unit which should be used for the execution time (optional).


Return Values:

Execution time as positive number.


Image:32px-Ambox_warning_orange.png

Note

Buffer overflows can easily occur if using microseconds as unit in combination with long queries.


Time units

Unit
UNIT_MILLISECONDS
UNIT_MICROSECONDS
mysql_tquery(MySQL, "SELECT * FROM `data`", "OnDataRetrieved");
// ...
public OnDataRetrieved()
{
	printf("The query \"SELECT * FROM `data`\" took %d milliseconds / %d microseconds to execute.",
		cache_get_query_exec_time(UNIT_MILLISECONDS), cache_get_query_exec_time(UNIT_MICROSECONDS));
 
	//output:
	//	The query "SELECT * FROM `data`" took 9 milliseconds / 9311 microseconds to execute.
	return 1;
}


cache_get_query_string

Description:

Returns the query which was executed as string.


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

Important
Note

This native was added in R36 and can't be used in any version below.


Parameters:
(destination[], max_len = sizeof(destination))
destination[]The string to store the query into.
max_lenThe maximal size of the destination string (optional).


Return Values:

This function does not return any specific values.


mysql_tquery(MySQL, "SELECT * FROM `data`", "OnDataRetrieved");
// ...
public OnDataRetrieved()
{
	new query[64];
	cache_get_query_string(query);
	printf("Executed query: \"%s\"", query);
	//output:
	//	Executed query: "SELECT * FROM `data`"
	return 1;
}



Plugin callbacks

OnQueryError

Description:

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


Image:32px-Ambox_warning_orange.png

Note

Common error codes for client and server.


Parameters:
(errorid, error[], callback[], query[], connectionHandle)
erroridID of the error.
error[]Error message.
callback[]Name of the callback. It equals to "" (nothing, empty) if it's not used.
query[]Query which was executed.
connectionHandleThe connection handle this was processed on.


Return Values:

This callback does not handle returns.


public OnQueryError(errorid, error[], callback[], query[], connectionHandle)
{
	switch(errorid)
	{
		case CR_SERVER_GONE_ERROR:
		{
			printf("Lost connection to server, trying reconnect...");
			mysql_reconnect(connectionHandle);
		}
		case ER_SYNTAX_ERROR:
		{
			printf("Something is wrong in your syntax, query: %s",query);
		}
	}
	return 1;
}
Personal tools