[SuperManIsOld]

Hi everyone,
I've read the wiki / link https://wiki.alliedmods.net/SQL_%28S...d_Scripting%29
and I see that we need to use SQL_TQuery to do database queries without causing server lags.

The other SQL_Query I can use to read, manipulate and write data but for the life of me I cannot understand the syntax of SQL_TQuery and how to use it to read, manipulate and write data to the database.

Anyone has any tips, helpful information, guides or tutorials on using SQL_TQuery that could explain to me how it works. I'm not a great programmer but I can manage my way around things once I understand the concept of how things work.

Thank you for your time and help.

[Impact123]

I'm not sure what exactly you don't understand, so i suggest you take a look at a plugin which uses them.
If you don't understand something that was done in it, you're free to ask me about it.

[floube]

Hope that helps you understanding threaded sql.

PHP Code:

#include <sourcemod>

new Handle:g_hDatabase;

public OnPluginStart() {
    // Connect to the database
    Database_Connect();
}

// The function to call, when you want to connect to the database
stock Database_Connect() {
    // Make sure to close the database handle, in case you try to connect more than once
    if (g_hDatabase != INVALID_HANDLE) {
        CloseHandle(g_hDatabase);
        g_hDatabase = INVALID_HANDLE;
    }

    // Only connect if the handle is invalid
    if (g_hDatabase == INVALID_HANDLE) {
        // Use KeyValues for login credentials
        new Handle:hKeyValues = CreateKeyValues("");
        KvSetString(hKeyValues, "host", "YOUR DATABASE ADDRESS HERE");
        KvSetString(hKeyValues, "database", "YOUR DATABASE NAME HERE");
        KvSetString(hKeyValues, "user", "YOUR DATABASE USER HERE");
        KvSetString(hKeyValues, "pass", "YOUR DATABASE PASSWORD HERE");

        // Connect and write errors to sError
        decl String:sError[512];
        g_hDatabase = SQL_ConnectCustom(hKeyValues, sError, sizeof(sError), true);
        
        // Close the KeyValues handle to free memory
        CloseHandle(hKeyValues);

        if (g_hDatabase == INVALID_HANDLE) {
            // An error has occured if the database handle is still invalid
            LogError("[Database] Failed to connect to the database! Error: %s", sError);
        } else {
            // Successfully connected to the database
            // Now you can use g_hDatabase to query your database
            PrintToServer("[Database] Successfully connected to the database");
        }
    }
}

// Gets called when a player connects and gets his steam id
public OnClientAuthorized(iClient, const String:sSteamId[]) {
    // Insert a player into database, as soon as he connects
    Database_InsertPlayer(iClient, sSteamId);
}

// The function to call, when you want to insert a player
stock Database_InsertPlayer(iClient, const String:sSteamId[]) {
    // The SQL query string
    decl String:sQuery[512];
    Format(sQuery, sizeof(sQuery), "INSERT INTO `player` (`steamid`, `name`) VALUES ('%s', '%N')", sSteamId, iClient);

    // Test data to pass to the callback
    new Float:fTestData = 63.21;

    // Execute the threaded query with sQuery as query string,
    // call Database_OnInsertPlayer when the database server responds
    // and pass fTestData to the callback
    SQL_TQuery(g_hDatabase, Database_OnInsertPlayer, sQuery, fTestData);
}

// This is the callback, which is called when the database server responds
public Database_OnInsertPlayer(Handle:hDriver, Handle:hResult, const String:sError[], any:iData) {
    // The database server responded with hResult and sError

    // fTestData was passed and is now saved in iData
    PrintToServer("%f", iData);

    if (hResult == INVALID_HANDLE) {
        // The result is invalid -> an error occured
        LogError("[Database] SQL-Query failed! Error: %s", sError);
    } else {
        // The result is valid -> do something with it
        PrintToServer("[Database] Added player");

        // Use SQL_FetchRow(hResult) to fetch a row
        // and then SQL_FetchInt(hResult, 0) to get the first column in the fetched row of the result
    }

    // Close the result handle to free memory (not sure if this is needed)
    if (hResult != INVALID_HANDLE) {
        CloseHandle(hResult);
        hResult = INVALID_HANDLE;
    }
} 


[SuperManIsOld]

Originally Posted by Impact123 I'm not sure what exactly you don't understand, so i suggest you take a look at a plugin which uses them. If you don't understand something that was done in it, you're free to ask me about it.

Thank you, I went through it and it makes more sense to me now then it did before

Originally Posted by floube Hope that helps you understanding threaded sql. PHP Code: #include <sourcemod> new Handle:g_hDatabase; public OnPluginStart() {     // Connect to the database     Database_Connect(); } // The function to call, when you want to connect to the database stock Database_Connect() {     // Make sure to close the database handle, in case you try to connect more than once     if (g_hDatabase != INVALID_HANDLE) {         CloseHandle(g_hDatabase);         g_hDatabase = INVALID_HANDLE;     }     // Only connect if the handle is invalid     if (g_hDatabase == INVALID_HANDLE) {         // Use KeyValues for login credentials         new Handle:hKeyValues = CreateKeyValues("");         KvSetString(hKeyValues, "host", "YOUR DATABASE ADDRESS HERE");         KvSetString(hKeyValues, "database", "YOUR DATABASE NAME HERE");         KvSetString(hKeyValues, "user", "YOUR DATABASE USER HERE");         KvSetString(hKeyValues, "pass", "YOUR DATABASE PASSWORD HERE");         // Connect and write errors to sError         decl String:sError[512];         g_hDatabase = SQL_ConnectCustom(hKeyValues, sError, sizeof(sError), true);                  // Close the KeyValues handle to free memory         CloseHandle(hKeyValues);         if (g_hDatabase == INVALID_HANDLE) {             // An error has occured if the database handle is still invalid             LogError("[Database] Failed to connect to the database! Error: %s", sError);         } else {             // Successfully connected to the database             // Now you can use g_hDatabase to query your database             PrintToServer("[Database] Successfully connected to the database");         }     } } // Gets called when a player connects and gets his steam id public OnClientAuthorized(iClient, const String:sSteamId[]) {     // Insert a player into database, as soon as he connects     Database_InsertPlayer(iClient, sSteamId); } // The function to call, when you want to insert a player stock Database_InsertPlayer(iClient, const String:sSteamId[]) {     // The SQL query string     decl String:sQuery[512];     Format(sQuery, sizeof(sQuery), "INSERT INTO `player` (`steamid`, `name`) VALUES ('%s', '%N')", sSteamId, iClient);     // Test data to pass to the callback     new Float:fTestData = 63.21;     // Execute the threaded query with sQuery as query string,     // call Database_OnInsertPlayer when the database server responds     // and pass fTestData to the callback     SQL_TQuery(g_hDatabase, Database_OnInsertPlayer, sQuery, fTestData); } // This is the callback, which is called when the database server responds public Database_OnInsertPlayer(Handle:hDriver, Handle:hResult, const String:sError[], any:iData) {     // The database server responded with hResult and sError     // fTestData was passed and is now saved in iData     PrintToServer("%f", iData);     if (hResult == INVALID_HANDLE) {         // The result is invalid -> an error occured         LogError("[Database] SQL-Query failed! Error: %s", sError);     } else {         // The result is valid -> do something with it         PrintToServer("[Database] Added player");         // Use SQL_FetchRow(hResult) to fetch a row         // and then SQL_FetchInt(hResult, 0) to get the first column in the fetched row of the result     }     // Close the result handle to free memory (not sure if this is needed)     if (hResult != INVALID_HANDLE) {         CloseHandle(hResult);         hResult = INVALID_HANDLE;     } }

And thank you very much as well, I understand threaded sql queries a lot better now.

I'm going to start writing code to test and practice threaded sql queries now

If I have anymore questions, I will ask

[yusufali]

Originally Posted by SuperManIsOld Thank you, I went through it and it makes more sense to me now then it did before And thank you very much as well, I understand threaded sql queries a lot better now. I'm going to start writing code to test and practice threaded sql queries now If I have anymore questions, I will ask

I always view T_Queries similar to how node.js handles stuff, in an event loop. Sure they are not the same languages and under the hood maybe completely different, but i belive the concept is more or less the same.

[JoB2C]

As you can imagine, they run in a different thread than the game, so they don't slow it down.

This is asynchronous, so the query is not necessarily executed immediately and you can't process its result directly after calling the function. Instead, you pass it a callback function that will be called when the result is available, and this result is passed as a parameter to that callback function.