SQL (SourceMod Scripting)

This article is an introduction to using SourceMod's SQL features. It is not an introduction or tutorial about SQL or any specific SQL implementation.

SourceMod's SQL layer is formally called DBI, or the Database Interface. The interface is a generic abstraction of common SQL functions. To connect to a specific database implementation (such as MySQL, or sqLite), a SourceMod DBI "driver" must be loaded. Currently, there is only a driver for MySQL.

SourceMod automatically detects and loads drivers on demand (if they exist, of course). All database natives are in scripting/include/dbi.inc. The C++ API is in public/IDBDriver.h.

Connecting

There are two ways to connect to a database. The first is through named configurations. Named configurations are preset configurations listed in configs/databases.cfg. SourceMod specifies that if SQL is being used, there should always be one configuration named "default."

An example of such a configuration looks like:

	"default"
	{
		"host"				"localhost"
		"database"			"sourcemod"
		"user"				"root"
		"pass"				""
		//"timeout"			"0"
		//"port"			"0"
	}

Connections based on named configurations can be instantiated with either SQL_Connect or SQL_DefConnect.

The other option is to use SQL_ConnectEx and manually specify all connection parameters.

Example of a typical connection:

new String:error[255]
new Handle:db = SQL_DefConnect(error, sizeof(error))
 
if (db == INVALID_HANDLE)
{
	PrintToServer("Could not connect: %s", error)
} else {
	CloseHandle(db)
}

Queries

No Results

The simplest queries are ones which do not return results -- for example, CREATE, DROP, UPDATE, INSERT, and DELETE. For such queries it is recommended to use SQL_FastQuery(). The name does not imply that the query will be faster, but rather, it is faster to write code using this function. For example, given that db is a valid database Handle:

if (!SQL_FastQuery(db, "UPDATE stats SET players = players + 1"))
{
	new String:error[255]
	SQL_GetError(db, error, sizeof(error))
	PrintToServer("Failed to query (error: %s)", error)
}

Results

If a query returns a result set and the results must be processed, you must use SQL_Query(). Unlike SQL_FastQuery(), this function returns a Handle which must be closed.

An example of a query which will produce results is:

Handle:query = SQL_Query(db, "SELECT userid FROM vb_user WHERE username = 'BAILOPAN'")
if (query == INVALID_HANDLE)
{
	new String:error[255]
	SQL_GetError(db, error, sizeof(error))
	PrintToServer("Failed to query (error: %s)", error)
} else {
	/* Process results here!
	 */
 
	/* Free the Handle */
	CloseHandle(query)
}

Prepared Statements

Prepared statements are another method of querying. The idea behind a prepared statement is that you construct a query "template" once, and re-use it as many times as needed. Prepared statements have the following benefits:

• A good SQL implementation will be able to cache the query better if it is a prepared statement.
• You don't have to rebuild the entire query string every execution.
• You don't have to allocate a new query structure on every execution.
• Input is "always" secure (more on this later).

A prepared statement has "markers" for inputs. For example, let's consider a function that takes in a database Handle and a name, and retrieves some info from a table:

GetSomeInfo(Handle:db, const String:name[])
{
	new Handle:hQuery
	new String:query[100]
 
	/* Create enough space to make sure our string is quoted properly  */
	new buffer_len = strlen(name) * 2 + 1
	new String:new_name[buffer_len]
 
	/* Ask the SQL driver to make sure our string is safely quoted */
	SQL_QuoteString(db, name, new_name, buffer_len)
 
	/* Build the query */
	Format(query, sizeof(query), "SELECT userid FROM vb_user WHERE username = '%s'", new_name)
 
	/* Execute the query */
	if ((hQuery = SQL_Query(query)) == INVALID_HANDLE)
	{
		return 0
	}
 
	/* Get some info here
	 */
 
	CloseHandle(hQuery)
}

Observe a version with prepared statements:

new Handle:hUserStmt = INVALID_HANDLE
GetSomeInfo(Handle:db, const String:name[])
{
	/* Check if we haven't already created the statement */
	if (hUserStmt == INVALID_HANDLE)
	{
		new String:error[255]
		hUserStmt = SQL_PrepareQuery(hUserStmt, "SELECT userid FROM vb_user WHERE username = ?", error)
		if (hUserStmt == INVALID_HANDLE)
		{
			return 0
		}
	}
 
	SQL_BindParamString(hUserStmt, 0, name)
	if (!SQL_Execute(hUserStmt))
	{
		return 0
	}
 
	/**
	 * Get some info here
	 */
}

The important differences:

• The input string (name) did not need to be backticked (quoted). The SQL engine automatically performs all type safety and insertion checks.
• There was no need for quotation marks around the parameter marker, ?, even though it accepted a string.
• We only needed to create the statement Handle once; after that it can live for the lifetime of the database connection.

Processing Results

Processing results is done in the same manner for both normal queries and prepared statements. The important functions are:

• SQL_GetRowCount() - Returns the number of rows.
• SQL_FetchRow() - Fetches the next row if one is available.
• SQL_Fetch[Int|String|Float]() - Fetches data from a field in the current row.

Let's consider a sample table that looks like this:

CREATE TABLE users (
	name VARCHAR(64) NOT NULL PRIMARY KEY,
	age INT UNSIGNED NOT NULL
	);

The following example has code that will print out all users matching a certain age. There is an example for both prepared statements and normal queries.

PrintResults(Handle:query)
{
	/* Even if we have just one row, you must call SQL_FetchRow() first */
	new String:name[65]
	while (SQL_FetchRow(query))
	{
		SQL_FetchString(query, 0, name, sizeof(name))
		PrintToServer("Name \"%s\" was found.", name)
	}
}
 
bool:GetByAge_Query(Handle:db, age)
{
	new String:query[100]
	Format(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age)
 
	new Handle:hQuery = SQL_Query(db, query)
	if (hQuery == INVALID_HANDLE)
	{
		return false
	}
 
	PrintResults(hQuery)
 
	CloseHandle(hQuery)
 
	return true
}
 
new Handle:hAgeStmt = INVALID_HANDLE
bool:GetByAge_Statement(Handle:db, age)
{
	if (hAgeSmt == INVALID_HANDLE)
	{
		new String:error[255]
		if ((hAgeStmt = SQL_PrepareQuery(db, 
			"SELECT name FROM users WHERE age = ?", 
			error, 
			sizeof(error))) 
		     == INVALID_HANDLE)
		{
			return false
		}
	}
 
	SQL_BindParamInt(hAgeStmt, 0, age)
	if (!SQL_Execute(hAgeStmt))
	{
		return false
	}
 
	PrintResults(hAgeStmt)
 
	return true
}

Note that these examples did not close the statement Handles. These examples assume a global database instance that is only closed when the plugin is unloaded. For plugins which maintain temporary database connections, prepared statement Handles must be freed or else the database connection will never be closed.