Prepared Statements

A prepared statement is sent to the server once, which parses it and plans it. After that, each run sends only its values, in binary, and the server never escapes or parses them. A value cannot change what the statement does, whatever a player typed.

Preparing

MySQL_Prepare takes the same specifiers as a query, each one a value. See Specifiers.

new
    MySQL:gDatabase,
    Statement:gAddAccount,
    Statement:gSaveScore
;

public OnGameModeInit() {
    gDatabase = MySQL_ConnectFile("mysql.ini");
    gAddAccount = MySQL_Prepare(gDatabase, "INSERT INTO accounts (name, hash) VALUES (%q, %q)");
    gSaveScore = MySQL_Prepare(gDatabase, "UPDATE accounts SET score = %i WHERE id = %i");

    return 1;
}

stock AddAccount(const name[], const hash[]) {
    MySQL_Execute(gAddAccount, "", 0, name, hash);
}

stock SaveScore(playerid, accountid) {
    MySQL_Execute(gSaveScore, "", 0, GetPlayerScore(playerid), accountid);
}

A statement runs on the connection of the handle that runs queries in order, so it keeps its place among the queries sent before and after it. It is prepared again on its own when the connection is lost and opens again, and when the server asks for it after a table it uses changed. An error in the text, such as a column that does not exist, is reported through OnMySQLError when it is first prepared.

Running once

MySQL_Execute runs a statement with its values, and calls a public as MySQL_Query does. MySQL_RunStatement waits for it, as MySQL_Run does. A SELECT gives a result read with the same natives as a query.

new Statement:gFindAccount;

forward OnAccountFound(playerid);

public OnPlayerConnect(playerid) {
    new
        name[MAX_PLAYER_NAME + 1]
    ;

    GetPlayerName(playerid, name, sizeof (name));
    MySQL_Execute(gFindAccount, "OnAccountFound", playerid, name);

    return 1;
}

public OnAccountFound(playerid) {
    if (MySQL_GetRowCount() == 0) {
        return SendClientMessage(playerid, -1, "Type /register and a password to play.");
    }

    SetPlayerScore(playerid, MySQL_GetInt(0, "score"));

    return 1;
}

Batches

A batch gathers rows of values for one statement and sends them together. MySQL_CreateBatch starts it, MySQL_AddRow copies a row into it, and MySQL_ExecuteBatch sends it and forgets it.

new
    Statement:gSaveScore,
    gAccountId[MAX_PLAYERS]
;

forward OnScoresSaved(rows);

stock SaveScores() {
    new const
        Batch:batch = MySQL_CreateBatch(gSaveScore)
    ;

    for (new i; i != MAX_PLAYERS; ++i) {
        if (IsPlayerConnected(i) && gAccountId[i] != 0) {
            MySQL_AddRow(batch, GetPlayerScore(i), gAccountId[i]);
        }
    }

    MySQL_ExecuteBatch(batch, "OnScoresSaved", MySQL_GetBatchRows(batch));
}

public OnScoresSaved(rows) {
    if (MySQL_GetErrno() != 0) {
        printf("%i scores were not saved: error %i.", rows, MySQL_GetErrno());
    }

    return 1;
}

On MariaDB 10.2 and later the rows go to the server as arrays of values, in one command, so the server runs the statement once for all of them. A batch larger than max_allowed_packet is split into as many commands as it needs. On MySQL and older MariaDB servers the rows run one by one on the same prepared statement, which still spares the parsing.

A batch sent in more than one command runs inside a transaction of its own, so it applies all its rows or none. When a transaction is open already, the batch is part of it. An empty batch is not sent to the server, but its public is still called, in order with the rest.

Inside the public, MySQL_GetAffectedRows gives the rows of the whole batch, and MySQL_GetInsertID the id of its first row.

Statements or queries

Work	Best fit
A query sent now and then, such as a ban.	A query with %q.
The same query for every player, item or vehicle.	A statement.
Many rows at once, such as saving every item of a player.	A statement with a batch.
A query whose tables or columns change.	A query built in pieces, as a statement cannot have a name as a value.

A batch costs less on the game thread than the same rows written into a query, as the values are copied and never formatted, and the server runs it faster. See Performance.

Closing

MySQL_CloseStatement closes a statement after what was sent before it, and forgets its batches. The statements of a script are closed when it unloads or when their handle is closed.