SQL

ISource2ModSQL runs queries off the main thread and delivers the results in a callback on the main thread, against a MySQL / MariaDB server or an SQLite file. There are two ways in:

#include "source2mod/ISource2ModSQL.h"

#include <source2mod/api> // the whole API, sql.inc included

Both take a Source2ModSQLConnectionInfo:

struct Source2ModSQLConnectionInfo
{
    const char* host;
    const char* user;
    const char* pass;
    const char* database;

    int port = 3306;
    int timeout = 60;

    /// Which server this is; MySQL unless said otherwise.
    ESource2ModSQLDriver driver = SOURCE2MOD_SQL_DRIVER_MYSQL;
};

driver picks the database:

The API is the same for both. Field types use MySQL's numbering; an SQLite column reports INTEGER as LONGLONG.

In SourcePawn Source2ModSQL.CreateConnection() and CreateServerless() take the same fields as arguments (host, user, pass, database, port = 3306, timeout = 60, driver = SQLDriver_MySQL); the drivers are SQLDriver_MySQL and SQLDriver_SQLite.

Named connections

Server owners keep their connection details in addons/source2mod/configs/databases.json, so a plugin does not ship any:

{
    "default": {
        "driver": "sqlite",
        "database": "source2mod"
    },
    "stats": {
        "driver": "mysql",
        "host": "127.0.0.1",
        "user": "root",
        "pass": "",
        "database": "cs2",
        "port": 3306,
        "timeout": 60
    }
}

Source2ModSQLConnectionInfo info{};
if (!g_pSource2ModSQL->GetNamedConnection("stats", info))
    g_pSource2ModSQL->GetNamedConnection("default", info); // SQLite, works out of the box

ISource2ModSQLConnection conn = Source2ModSQL.CreateNamedConnection("stats");
if (conn == null)
    conn = Source2ModSQL.CreateNamedConnection("default"); // SQLite, works out of the box

SourcePawn makes the connection straight from the name: Source2ModSQL.CreateNamedConnection(name) or CreateNamedServerless(name), null when there is no such entry ("" means "default").

Serverless queries

Create the handle once, typically in Load() / OnPluginStart(). The info is copied, strings included, so it does not have to outlive the call.

ISource2ModSQLServerless* g_pDb = nullptr;

// Load()
g_pDb = g_pSource2ModSQL->CreateServerless(g_PluginID, { "127.0.0.1", "user", "pass", "cs2" });

ISource2ModSQLServerless g_Db;

public void OnPluginStart()
{
    g_Db = Source2ModSQL.CreateServerless("127.0.0.1", "user", "pass", "cs2");
}

Then query wherever you need to. ? becomes an escaped, quoted value; ?? a quoted identifier (a table or column name). A placeholder count that does not match the parameters fails the query instead of running it.

void LoadPlayer(uint64_t steamid)
{
    g_pDb->Query("SELECT name, kills FROM players WHERE steamid = ?", { std::to_string(steamid) },
        [steamid](const Source2ModSQLServerlessResult& r)
    {
        if (!r.m_bSuccess)
        {
            g_Source2ModAPI->ConPrintf("LoadPlayer %llu: %s (%u)\n", steamid, r.m_sError.c_str(), r.m_nErrorCode);
            return;
        }

        ISource2ModSQLResult* set = r.m_pQuery->GetResultSet();
        while (set && set->MoreRows() && set->FetchRow())
        {
            std::string name = set->GetString(0); // copy -- the result dies with the callback
            int kills = set->GetInt(1);
        }
    });
}

void LoadPlayer(CPlayerSlot slot, const char[] steamid)
{
    char params[1][32];
    Source2ModUtils.Copy(params[0], sizeof(params[]), steamid);

    g_Db.QueryWithParams("SELECT name, kills FROM players WHERE steamid = ?", params, 1, OnPlayerLoaded, slot.Get());
}

public void OnPlayerLoaded(bool success, int errorCode, const char[] error, ISource2ModSQLQuery query, any slot)
{
    if (!success)
    {
        Source2ModApi.Log("LoadPlayer slot %d: %s (%d)", slot, error, errorCode);
        return;
    }

    ISource2ModSQLResult set = query.GetResultSet();
    while (set != null && set.MoreRows() && set.FetchRow())
    {
        char name[64];
        set.GetString(0, name, sizeof(name)); // copy -- the result dies with the callback
        int kills = set.GetInt(1);
    }
}

m_bSuccess is about the query, not the rows — a SELECT that matched nothing is a success with an empty result set. On failure m_pQuery is nullptr, m_sError says why and m_nErrorCode holds the driver's error code (0 when the query never reached the database). A SourcePawn callback gets the same as arguments (success, errorCode, error, and query, null on failure); the ? values go to QueryWithParams() as a string array.

All serverless queries of all plugins share one worker thread. On MySQL each query pays a TCP handshake and a login, so do not use this for anything per tick or per shot — keep a connection open instead.

A persistent connection

ISource2ModSQLConnection* g_pConn = nullptr;

// Load()
Source2ModSQLConnectionInfo info{ "127.0.0.1", "user", "pass", "cs2" };
g_pConn = SQL_CREATE_CONNECTION(info); // g_pSource2ModSQL->CreateConnection(g_PluginID, info)

g_pConn->Connect([](bool connected)
{
    if (!connected)
        return; // the reason is in the server log

    g_bDbReady = true;
});

ISource2ModSQLConnection g_Conn;
bool g_bDbReady;

public void OnPluginStart()
{
    g_Conn = Source2ModSQL.CreateConnection("127.0.0.1", "user", "pass", "cs2");

    g_Conn.Connect(OnDbConnected);
}

public void OnDbConnected(bool connected, any data)
{
    if (!connected)
        return; // the reason is in the server log

    g_bDbReady = true;
}

CreateConnection() does not connect by itself. Queries sent before the Connect() callback reported true fail — their callback gets nullptr (null) — so gate them on a flag like g_bDbReady.

Queries

virtual void Query(char* query, Source2ModSQLQueryCallbackFunc callback);
virtual void Query(const char* query, Source2ModSQLQueryCallbackFunc callback, ...);

The const char* overload is a printf format — the one you get with a string literal or .c_str(). Escape every string you insert, and write a literal % as %%. SourcePawn's Query() takes finished text: build it with Source2ModApi.Format() and Escape(text, buffer, maxlen):

std::string name = g_pConn->Escape(player->GetPlayerName());

g_pConn->Query("UPDATE players SET name = '%s', kills = kills + %d WHERE steamid = %llu",
    [](ISource2ModSQLQuery* q)
    {
        if (!q)
            return; // failed -- not connected, or rejected; the reason is in the server log

        // q->GetAffectedRows(), q->GetInsertId(), q->GetResultSet()
    },
    name.c_str(), kills, steamid);

void SavePlayer(CCSPlayerController player, int kills, const char[] steamid)
{
    char name[64], escaped[160], query[512];
    player.GetPlayerName(name, sizeof(name));
    g_Conn.Escape(name, escaped, sizeof(escaped));

    Source2ModApi.Format(query, sizeof(query), "UPDATE players SET name = '%s', kills = kills + %d WHERE steamid = %s",
        escaped, kills, steamid);

    g_Conn.Query(query, OnPlayerSaved);
}

public void OnPlayerSaved(ISource2ModSQLQuery q, any data)
{
    if (q == null)
        return; // failed -- not connected, or rejected; the reason is in the server log

    // q.GetAffectedRows(), q.GetInsertId(), q.GetResultSet()
}

The callback always runs exactly once. q is nullptr (null) when the query failed; otherwise it belongs to Source2Mod and is freed when the callback returns.

Escape() needs the live connection — call it only once connected.

Transactions

Source2ModSQLTransaction txn;
txn.queries.push_back("UPDATE wallets SET credits = credits - 100 WHERE steamid = 1");
txn.queries.push_back("UPDATE wallets SET credits = credits + 100 WHERE steamid = 2");

g_pConn->ExecuteTransaction(txn,
    [](std::vector<ISource2ModSQLQuery*> results)
    {
        // One per query, in order. Owned by Source2Mod -- do not delete them.
        unsigned int moved = results[0]->GetAffectedRows();
    },
    [](std::string error, int failedIndex)
    {
        // Already rolled back. failedIndex is -1 when no single query failed
        // (not connected, or BEGIN / COMMIT failed).
        g_Source2ModAPI->ConPrintf("Transaction failed at query %d: %s\n", failedIndex, error.c_str());
    });

void MoveCredits()
{
    // ExecuteTransaction runs a copy: the transaction can go right after.
    Source2ModSQLTransaction txn = new Source2ModSQLTransaction();
    txn.AddQuery("UPDATE wallets SET credits = credits - 100 WHERE steamid = 1");
    txn.AddQuery("UPDATE wallets SET credits = credits + 100 WHERE steamid = 2");

    g_Conn.ExecuteTransaction(txn, OnTxnSuccess, OnTxnFailure);
    delete txn;
}

public void OnTxnSuccess(ArrayList results, any data)
{
    // One per query, in order. Owned by Source2Mod -- do not delete them.
    ISource2ModSQLQuery first = results.Get(0);
    int moved = first.GetAffectedRows();
}

public void OnTxnFailure(const char[] error, int failedIndex, any data)
{
    // Already rolled back. failedIndex is -1 when no single query failed
    // (not connected, or BEGIN / COMMIT failed).
    Source2ModApi.Log("Transaction failed at query %d: %s", failedIndex, error);
}

Exactly one of the two callbacks runs. The queries in a transaction are sent as written — escape their values yourself. In SourcePawn the success callback gets the queries as an ArrayList, and ExecuteTransaction() runs a copy of the Source2ModSQLTransaction, so it can be deleted right after the call.

Reading a result set

ISource2ModSQLQuery::GetResultSet() returns nullptr (null) for statements without rows (INSERT, UPDATE). Otherwise step through the rows with FetchRow() and read the current row's columns by index:

FetchMoreResults() advances to the next result set of a multi-statement query. In SourcePawn GetString() and FieldNumToName() write into a buffer, GetInt64() fills an int value[2], and FieldNameToNum() takes the column by reference.

unsigned int colKills;
if (set->FieldNameToNum("kills", &colKills))
    while (set->FetchRow())
        total += set->GetInt(colKills);

int colKills;
if (set.FieldNameToNum("kills", colKills))
    while (set.FetchRow())
        total += set.GetInt(colKills);

Pitfalls

• Copy what you keep. Query objects, result sets and every string they return are freed once the callback returns — transaction results included. Never delete them.
• Callbacks run on the main thread, one completed operation per connection per frame. Re-resolve players inside them by SteamID or slot; they may have left while the query ran.
• Check for nullptr (null). A failed query on an ISource2ModSQLConnection still runs its callback, with nullptr instead of a query; the error text only goes to the server log. If you need the message or the error code, use a serverless handle.
• Unload. Connections and serverless handles are destroyed for you when your plugin unloads, together with every query still queued — their callbacks never run. Destroy() (delete in SourcePawn) does the same by hand; a query already on the wire finishes and its result is thrown away.

Reference

• ISource2ModSQL API reference