SQL (SourceMod Scripting) - Revision history

Bacardi: /* External Links */ update link

2022-09-25T18:16:40Z

‎External Links: update link

Peace-Maker: Mention Postgres driver

2021-06-06T14:57:27Z

Mention Postgres driver

Joinedsenses: Update methodmap, highlighting, and formatting

2020-03-30T01:04:44Z

Update methodmap, highlighting, and formatting

Show changes

MisakaSora at 08:54, 1 July 2019

2019-07-01T08:54:01Z

Sim242 at 20:26, 1 July 2017

2017-07-01T20:26:11Z

Shavit: Update example code to transitional syntax

2016-07-26T02:52:13Z

Update example code to transitional syntax

Xerox8521: /* Operations */

2016-02-10T04:10:35Z

‎Operations

Xerox8521: /* Processing Results */ Updated to Transitional Syntax

2016-02-10T04:02:01Z

‎Processing Results: Updated to Transitional Syntax

Xerox8521: /* Prepared Statements */ Updated to Transitional Syntax

2016-02-10T03:53:26Z

‎Prepared Statements: Updated to Transitional Syntax

Xerox8521: /* Results */ Updated to Transitional Syntax

2016-02-10T03:48:16Z

‎Results: Updated to Transitional Syntax

@@ Line 354: / Line 354: @@
 ==External Links==
 *[http://www.sqlite.org SQLite Homepage]
-*[http://sqlitebrowser.sourceforge.net/ SQLite Browser]
+*[https://sqlitebrowser.org/ DB Browser for SQLite]

@@ Line 2: / Line 2: @@
 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 '''D'''ata'''b'''ase '''I'''nterface.  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 are drivers for MySQL and SQLite
+SourceMod's SQL layer is formally called ''DBI'', or the '''D'''ata'''b'''ase '''I'''nterface.  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 are drivers for MySQL, PostgreSQL (pgsql), and SQLite.
 SourceMod automatically detects and loads drivers on demand (if they exist, of course).  All database natives are in <tt>scripting/include/dbi.inc</tt>.  The C++ API is in <tt>public/IDBDriver.h</tt>.

← Older revision		Revision as of 08:54, 1 July 2019
Line 1:		Line 1:
		+	{{Languages\|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.		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.

@@ Line 249: / Line 249: @@
          else
+         {
-		hDatabase = hndl;
+		hDatabase = db;
+	}
 }</pawn>

@@ Line 238: / Line 238: @@
 void StartSQL()
+{
-	SQL_TConnect(GotDatabase);
+	Database.Connect(GotDatabase);
+}
@@ Line 257: / Line 257: @@
 Query operations use the following callback parameters:
-*<tt>owner</tt>: A Handle to the database used to query.  The Handle is not the same as the Handle originally passed; however, it will test positively with <tt>SQL_IsSameConnection</tt>.  The Handle can be cloned but it cannot be closed (it is closed automatically).  It may be <tt>INVALID_HANDLE</tt> in the case of a serious error (for example, the driver being unloaded).
+*<tt>owner</tt>: A Handle to the database used to query.  The Handle is not the same as the Handle originally passed; however, it will test positively with <tt>SQL_IsSameConnection</tt>.  The Handle can be cloned but it cannot be closed (it is closed automatically).  It may be <tt>null</tt> in the case of a serious error (for example, the driver being unloaded).
-*<tt>hndl</tt>: A Handle to the query.  It can be cloned, but not closed (it is closed automatically).  It may be <tt>INVALID_HANDLE</tt> if there was an error.
+*<tt>hndl</tt>: A Handle to the query.  It can be cloned, but not closed (it is closed automatically).  It may be <tt>null</tt> if there was an error.
 *<tt>error</tt>: Error string, if any.
 *<tt>data</tt>: Optional user-defined data passed in through <tt>SQL_TQuery()</tt>.
 Example, continuing from above:
-<pawn>CheckSteamID(userid, const String:auth[])
+<pawn>void CheckSteamID(int userid, const char[] auth)
+{
-	decl String:query[255];
+	char query[255];
-	Format(query, sizeof(query), "SELECT userid FROM users WHERE steamid = '%s'", auth);
+	FormatEx(query, sizeof(query), "SELECT userid FROM users WHERE steamid = '%s'", auth);
-	SQL_TQuery(hdatabase, T_CheckSteamID, query, userid)
+	hdatabase.Query(T_CheckSteamID, query, userid);
+}
-public T_CheckSteamID(Handle:owner, Handle:hndl, const String:error[], any:data)
+public void T_CheckSteamID(Database db, DBResultSet results, const char[] error, any data)
+{
-	new client;
+	int client = 0;
 	/* Make sure the client didn't disconnect while the thread was running */
@@ Line 280: / Line 280: @@
+	}
-	if (hndl == INVALID_HANDLE)
+	if (results == null)
+	{
 		LogError("Query failed! %s", error);
 		KickClient(client, "Authorization failed");
-	} else if (!SQL_GetRowCount(hndl)) {
+	} else if (results.RowCount == 0) {
 		KickClient(client, "You are not a member");
+	}
@@ Line 302: / Line 302: @@
 Example:
-<pawn>bool:GetByAge_Query(Handle:db, age)
+<pawn>bool GetByAge_Query(Database db, int age)
+{
-	new String:query[100]
+	char query[100];
-	Format(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age)
+	FormatEx(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age);
 	SQL_LockDatabase(db);
-	new Handle:hQuery = SQL_Query(db, query)
+	DBResultSet hQuery = SQL_Query(db, query);
-	if (hQuery == INVALID_HANDLE)
+	if (hQuery == null)
+	{
 		SQL_UnlockDatabase(db);
-		return false
+		return false;
+	}
 	SQL_UnlockDatabase(db);
-	PrintResults(hQuery)
+	PrintResults(hQuery);
-	CloseHandle(hQuery)
+	delete hQuery;
-	return true
+	return true;
 }</pawn>

@@ Line 211: / Line 211: @@
 ==Operations==
-All threaded operations use the same callback for result notification: <tt>SQLTCallback</tt>.  The parameters are:
+All threaded operations (except connecting) use the same callback for result notification: <tt>SQLQueryCallback</tt>.  The parameters are:
-*<tt>owner</tt> - The "owner" of the operation.  For a query, this is a database.  For a database, this is a driver.  If the owner was not found or was invalidated, <tt>INVALID_HANDLE</tt> is passed.
+*<tt>db</tt> - The cloned database handle.  If the db handle was not found or was invalidated, <tt>null</tt> is passed.
-*<tt>hndl</tt> - The object being requested.  The value is defined by the SQL operation.
+*<tt>results</tt> - Result object, or null on failure.
 *<tt>error</tt> - An error string.
 *<tt>data</tt> - Custom data that can be passed via some SQL operations.
@@ Line 225: / Line 225: @@
 ===Connecting===
 It is not necessary to make a threaded connection in order to make threaded queries.  However, creating a threaded connection will not pause the game server if a connection cannot be immediately established.
 The following parameters are used for the threaded connection callback:
-*<tt>owner</tt>: A Handle to the driver, or <tt>INVALID_HANDLE</tt> if it could not be found.
+*<tt>db</tt>: Handle to the database connection, or <tt>null</tt> if it could not be found.
 *<tt>error</tt>: The error string, if any.
 *<tt>data</tt>: Unused (0)
 Example:
-<pawn>new Handle:hDatabase = INVALID_HANDLE;
+<pawn>Database hDatabase = null;
-StartSQL()
+void StartSQL()
+{
 	SQL_TConnect(GotDatabase);
+}
-public GotDatabase(Handle:owner, Handle:hndl, const String:error[], any:data)
+public void GotDatabase(Database db, const char[] error, any data)
+{
-	if (hndl == INVALID_HANDLE)
+	if (db == null)
+	{
 		LogError("Database failure: %s", error);
 	} else {
 		hDatabase = hndl;
+	}

@@ Line 147: / Line 147: @@
 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.
-<pawn>PrintResults(Handle:query)
+<pawn>void PrintResults(Handle query)
+{
 	/* Even if we have just one row, you must call SQL_FetchRow() first */
-	new String:name[65]
+	char name[MAX_NAME_LENGTH];
 	while (SQL_FetchRow(query))
+	{
-		SQL_FetchString(query, 0, name, sizeof(name))
+		SQL_FetchString(query, 0, name, sizeof(name));
-		PrintToServer("Name \"%s\" was found.", name)
+		PrintToServer("Name \"%s\" was found.", name);
+	}
+}
-bool:GetByAge_Query(Handle:db, age)
+bool GetByAge_Query(Database db, int age)
+{
-	new String:query[100]
+	char query[100];
-	Format(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age)
+	Format(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age);
-	new Handle:hQuery = SQL_Query(db, query)
+	DBResultSet hQuery = SQL_Query(db, query);
-	if (hQuery == INVALID_HANDLE)
+	if (hQuery == null)
+	{
-		return false
+		return false;
+	}
-	PrintResults(hQuery)
+	PrintResults(hQuery);
-	CloseHandle(hQuery)
+	delete hQuery;
-	return true
+	return true;
+}
-new Handle:hAgeStmt = INVALID_HANDLE
+DBStatement hAgeStmt = null;
-bool:GetByAge_Statement(Handle:db, age)
+bool GetByAge_Statement(Database db, int age)
+{
-	if (hAgeSmt == INVALID_HANDLE)
+	if (hAgeSmt == null)
+	{
-		new String:error[255]
+		char error[255];
 		if ((hAgeStmt = SQL_PrepareQuery(db,
 			"SELECT name FROM users WHERE age = ?",
 			error,
 			sizeof(error)))
-		     == INVALID_HANDLE)
+		     == null)
+		{
-			return false
+			return false;
+		}
+	}
-	SQL_BindParamInt(hAgeStmt, 0, age)
+	SQL_BindParamInt(hAgeStmt, 0, age);
 	if (!SQL_Execute(hAgeStmt))
+	{
-		return false
+		return false;
+	}
-	PrintResults(hAgeStmt)
+	PrintResults(hAgeStmt);
-	return true
+	return true;
 }</pawn>
 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.
 =Threading=

@@ Line 76: / Line 76: @@
 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:
-<pawn>GetSomeInfo(Handle:db, const String:name[])
+<pawn>int GetSomeInfo(Database db, const char[] name)
+{
-	new Handle:hQuery
+	DBResultSet hQuery;
-	new String:query[100]
+	char query[100];
 	/* Create enough space to make sure our string is quoted properly  */
-	new buffer_len = strlen(name) * 2 + 1
+	int buffer_len = strlen(name) * 2 + 1;
-	new String:new_name[buffer_len]
+	char[] new_name = new char[buffer_len];
 	/* Ask the SQL driver to make sure our string is safely quoted */
-	SQL_EscapeString(db, name, new_name, buffer_len)
+	SQL_EscapeString(db, name, new_name, buffer_len);
 	/* Build the query */
-	Format(query, sizeof(query), "SELECT userid FROM vb_user WHERE username = '%s'", new_name)
+	Format(query, sizeof(query), "SELECT userid FROM vb_user WHERE username = '%s'", new_name);
 	/* Execute the query */
-	if ((hQuery = SQL_Query(query)) == INVALID_HANDLE)
+	if ((hQuery = SQL_Query(query)) == null)
+	{
-		return 0
+		return 0;
+	}
@@ Line 100: / Line 100: @@
 	 */
-	CloseHandle(hQuery)
+	delete hQuery;
 }</pawn>
 Observe a version with prepared statements:
-<pawn>new Handle:hUserStmt = INVALID_HANDLE;
+<pawn>DBStatement hUserStmt = null;
-GetSomeInfo(Handle:db, const String:name[])
+int GetSomeInfo(Database db, const char[] name)
+{
 	/* Check if we haven't already created the statement */
-	if (hUserStmt == INVALID_HANDLE)
+	if (hUserStmt == null)
+	{
-		new String:error[255]
+		char error[255];
 		hUserStmt = SQL_PrepareQuery(db, "SELECT userid FROM vb_user WHERE username = ?", error, sizeof(error));
-		if (hUserStmt == INVALID_HANDLE)
+		if (hUserStmt == null)
+		{
 			return 0;

@@ Line 52: / Line 52: @@
 An example of a query which will produce results is:
-<pawn>new Handle:query = SQL_Query(db, "SELECT userid FROM vb_user WHERE username = 'BAILOPAN'")
+<pawn>DBResultSet query = SQL_Query(db, "SELECT userid FROM vb_user WHERE username = 'BAILOPAN'");
-if (query == INVALID_HANDLE)
+if (query == null)
+{
 	/* Process results here!
 	 */
 	/* Free the Handle */
-	CloseHandle(query)
+	delete query;
 }</pawn>