Queries

Four ways to send a query

Native	Order	Answer
MySQL_Send	After the queries sent before it.	None.
MySQL_Query	After the queries sent before it.	A public on a later server tick.
MySQL_ParallelQuery	Any connection of the pool, out of order.	A public on a later server tick.
MySQL_Run	After the queries sent before it.	At once, the server waits for it.

Every handle has one connection that runs the ordered queries one after the other, so a query that reads always sees what the queries sent before it wrote. The pool setting adds connections for MySQL_ParallelQuery, which suits reads that need not wait for the rest, such as a list of houses at start up. Without a pool, a parallel query runs in order like the others.

MySQL_Run stops the server until the database answers, so it suits the tables made when the gamemode starts, not anything a player does.

The public

The public of a query takes the context it was sent with, a player, a vehicle or an index into an array of the script. It is called also when the query failed, and MySQL_GetErrno tells which.

new MySQL:gDatabase;

forward OnHouseRead(houseid);

stock LoadHouse(houseid) {
    MySQL_Query(gDatabase, "OnHouseRead", houseid, "SELECT owner, price FROM houses WHERE id = %i", houseid);
}

public OnHouseRead(houseid) {
    new
        owner[MAX_PLAYER_NAME + 1]
    ;

    if (MySQL_GetErrno() != 0 || MySQL_GetRowCount() == 0) {
        return 1;
    }

    MySQL_GetString(0, "owner", owner);
    printf("House %i belongs to %s and costs $%i.", houseid, owner, MySQL_GetInt(0, "price"));

    return 1;
}

A public is only called while the script that sent the query is loaded. A query whose script unloaded runs, and its answer is dropped.

Formatting

The component formats the query itself, with no length limit and no array in the script. %q escapes a string for the connection and puts it between quotes, %e only escapes it, and %s writes it as it is, for names of tables and columns the script chose. Every specifier is listed in Specifiers.

new MySQL:gDatabase;

stock BanPlayer(playerid, const reason[]) {
    new
        name[MAX_PLAYER_NAME + 1]
    ;

    GetPlayerName(playerid, name, sizeof (name));
    MySQL_Send(gDatabase, "INSERT INTO bans (name, reason, until) VALUES (%q, %q, NOW() + INTERVAL %i DAY)", name, reason, 7);
}

A string from a player always goes through %q or %e. MySQL_Format writes a formatted query into an array, for a script that keeps or prints it.

Queries built in pieces

An insert of many rows is built a piece at a time with MySQL_CreateQuery and MySQL_AppendQuery, which take the same specifiers. MySQL_TrimQuery removes the comma after the last row, and MySQL_SendQuery sends it and forgets it.

new MySQL:gDatabase;

stock SavePositions() {
    new
        Query:query = MySQL_CreateQuery(gDatabase, "INSERT INTO positions (player, x, y, z) VALUES "),
        Float:x,
        Float:y,
        Float:z,
        count
    ;

    for (new i; i != MAX_PLAYERS; ++i) {
        if (!IsPlayerConnected(i)) {
            continue;
        }

        GetPlayerPos(i, x, y, z);
        MySQL_AppendQuery(query, "(%i, %f, %f, %f),", i, x, y, z);
        ++count;
    }

    if (count == 0) {
        MySQL_DestroyQuery(query);
        return;
    }

    MySQL_TrimQuery(query, 1);
    MySQL_AppendQuery(query, " ON DUPLICATE KEY UPDATE x = VALUES(x), y = VALUES(y), z = VALUES(z)");
    MySQL_SendQuery(query);
}

A statement made of parts that grow together keeps each part in a query of its own and joins them with %Q, which writes the text of a query being built. Saving several tables at once in one round trip, with multi_statements on, is done that way. A prepared statement with a batch does the same work with less on the game thread. See Prepared statements.

Closing

MySQL_Close runs the queries still waiting, without their publics, and closes the handle. A script that unloads closes its handles itself, so a gamemode that ends saves what it sent before.