MySQL/R33

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.

MySQL Plugin Plugin
Author	BlueG / maddinat0r
Released	29/11/2008
Latest Version	R39-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.

1 ORM functions
2 MySQL functions
3 Cache functions
4 Plugin callbacks
- 4.1 OnQueryError

ORM functions

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.

connectionHandle

The 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:id

The 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:id

The 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:id

The id of the ORM instance.

row	The 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:id

The 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:id

The 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:id

The 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:id

The id of the ORM instance.

bool:clearvars

True 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:id

The 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:id

The 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:id

The id of the ORM instance.

&var	The 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:id

The id of the ORM instance.

&Float:var

The 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:id

The id of the ORM instance.

var[]

The variable to register.

var_maxlen

The 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:id

The 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:id

The 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

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.

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:loglevel

Specifies what type of log messages will be logged (optional).

E_LOGTYPE:logtype

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

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.

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.

port	Port of the MySQL server (optional).

bool:autoreconnect

True to enable, false to disable automatic reconnection (optional).

pool_size

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

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)

connectionHandle

The connection handle this will be processed on (optional).

bool:wait

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

connectionHandle

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

connectionHandle

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

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.

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:type

Option to change.

value

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

connectionHandle

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

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.

connectionHandle

The connection handle this will be processed on (optional).

max_len

The size of the destination (optional).

Return Values:

Length of escaped string.

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.

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,_}:...)

connectionHandle

The connection handle this will be processed on.

output[]

The string to save the result to.

len	The 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.

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.

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,_}:...)

connectionHandle

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

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,_}:...)

connectionHandle

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

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.

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)

conhandle

The connection handle this will be processed on.

query[]

The query you want to execute.

bool:use_cache

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

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.

connectionHandle

The connection handle this will be processed on (optional).

max_len

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

connectionHandle

The connection handle this will be processed on (optional).

max_len

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

connectionHandle

The connection handle this will be processed on (optional).

Return Values:

1 on success, 0 on fail.

mysql_set_charset("utf8");

Cache functions

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_rows

The variable the number of rows will be assigned to.

&num_fields

The variable the number of fields will be assigned to.

connectionHandle

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

connectionHandle

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

connectionHandle

The 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_index

The index of the field whose name to retrieve.

destination[]

The string to store the name into.

connectionHandle

The connection handle this will be processed on (optional).

max_len

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

row	The row's index (starts at '0').

field_idx

The index of the field (starts at '0').

destination[]

The string to store the data into.

connectionHandle

The connection handle this will be processed on (optional).

max_len

The size of the destination string (optional).

Return Values:

This function does not return any specific values.

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)

row	The row's index (starts at '0').

field_idx

The index of the field (starts at '0').

connectionHandle

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

row	The row's index (starts at '0').

field_idx

The index of the field (starts at '0').

connectionHandle

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

row	The row's index (starts at '0').

const field_name[]

The field's name.

destination[]

The string to store the data into.

connectionHandle

The connection handle this will be processed on (optional).

max_len

The size of the destination string (optional).

Return Values:

This function does not return any specific values.

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)

row	The row's index (starts at '0').

const field_name[]

The field's name.

connectionHandle

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

row	The row's index (starts at '0').

const field_name[]

The field's name.

connectionHandle

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

connectionHandle

The 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_id

The cache-id which should be deleted.

connectionHandle

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

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_id

The cache-id which should be set as active.

connectionHandle

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

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_id

The cache-id which should be checked.

connectionHandle

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

connectionHandle

The connection handle this will be processed on (optional).

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)

connectionHandle

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

connectionHandle

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

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:unit

Time unit which should be used for the execution time (optional).

Return Values:

Execution time as positive number.

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.

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_len

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

Note

Common error codes for client and server.

Parameters:

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

errorid

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

connectionHandle

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

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

Categories: Outdated | Plugin Documentation

MySQL/R33

From SA-MP Wiki

Contents

ORM functions

orm_create

orm_destroy

orm_errno

orm_apply_cache

orm_select

orm_update

orm_insert

orm_delete

orm_load

orm_save

orm_addvar_int

orm_addvar_float

orm_addvar_string

orm_delvar

orm_setkey

MySQL functions

mysql_log

Log levels

Log types

mysql_connect

mysql_close

mysql_reconnect

mysql_unprocessed_queries

mysql_current_handle

mysql_option

Options

mysql_errno

mysql_escape_string

mysql_format

Format strings

mysql_pquery

Format specifiers

mysql_tquery

Format specifiers

mysql_query

mysql_stat

mysql_get_charset

mysql_set_charset

Cache functions

cache_get_data

cache_get_row_count

cache_get_field_count

cache_get_field_name

cache_get_row

cache_get_row_int

cache_get_row_float

cache_get_field_content

cache_get_field_content_int

cache_get_field_content_float

cache_save

cache_delete

cache_set_active

cache_is_valid

cache_affected_rows

cache_insert_id

cache_warning_count

cache_get_query_exec_time

Time units

cache_get_query_string

Plugin callbacks

OnQueryError

Views

Personal tools

Navigation

Search

Toolbox