AlliedModders
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   Solved MySQL and SQLITE (https://forums.alliedmods.net/showthread.php?t=333345)

iceeedr 07-05-2021 00:30
MySQL and SQLITE
 
Here I go again, I've read somewhere that I don't remember that using SQL_ThreadQuery in a plugin that uses Mysql and SQLITE at the same time would be problematic (and in fact it is), would there be any possibility of using it now in 2021?

Below I leave a small example of error.

PHP Code:
#include <amxmodx>
#include <sqlx>
#pragma defclasslib sqlite sqlite

#define PLUGIN  "New Plug-In"
#define VERSION "1.0"
#define AUTHOR  "Author"

#define CREATE "CREATE TABLE IF NOT EXISTS example (steamid varchar(32), name varchar(32));"
#define CREATE2 "CREATE TABLE IF NOT EXISTS example (steamid TEXT, name TEXT);"

new Handle:g_MySQL
new Handle:g_MySQLITE
new QueryError[512]

public plugin_init()
{
        register_plugin(PLUGIN, VERSION, AUTHOR)

        set_task(1.0, "InitQueries")
}

public InitQueries()
{
        SQL_SetAffinity("mysql")

        g_MySQL = SQL_MakeDbTuple(Host,User,Pass,Db)

        new szMysqlQuery[150]
        formatex
        (
                szMysqlQuery,
                charsmax(szMysqlQuery),
                CREATE
        )

        SQL_ThreadQuery(g_MySQL,"InitialMySQL_Handler",szMysqlQuery)

        SQL_SetAffinity("sqlite")

        g_MySQLITE = SQL_MakeDbTuple("", "", "", "SQLITE")

        new szQuery[150]

        formatex
        (
                szQuery,
                charsmax(szQuery),
                CREATE2
        )

        SQL_ThreadQuery(g_MySQLITE,"InitialSQLITE_Handler", szQuery)
}

public InitialSQLITE_Handler(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
        if(FailState == TQUERY_CONNECT_FAILED)
                log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
        else if(FailState == TQUERY_QUERY_FAILED)
        {
                SQL_GetQueryString(Query,QueryError,charsmax(QueryError))
                log_amx("Could not execute query ^"%s^"",QueryError)
        }

        if(!SQL_NumRows(Query))
        {
                server_print("No Results.")
        }
        else
                server_print("Created.")

        return PLUGIN_HANDLED
}

public InitialMySQL_Handler(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
        if(FailState == TQUERY_CONNECT_FAILED)
                log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
        else if(FailState == TQUERY_QUERY_FAILED)
        {
                SQL_GetQueryString(Query,QueryError,charsmax(QueryError))
                log_amx("Could not execute query ^"%s^"",QueryError)
        }

        if(!SQL_NumRows(Query))
        {
                server_print("No Results.")
        }
        else
                server_print("Created.")

        return PLUGIN_HANDLED
} 
Code:
L 07/05/2021 - 01:13:37: [SQLITE] Invalid query handle: 2
L 07/05/2021 - 01:13:37: [AMXX] Displaying debug trace (plugin "error.amxx", version "1.0")
L 07/05/2021 - 01:13:37: [AMXX] Run time error 10: native error (native "SQL_NumResults")
L 07/05/2021 - 01:13:37: [AMXX]    [0] error.sma::InitialMySQL_Handler (line 86)
As you can see, I set the driver to mysql, and ran the threadquery, right after I set the driver to sqlite to do the same with the other query, and as we can see the first query was sent to the handler with the sqlite driver.

Then they will tell me "put a 1 second task between them", yes it works, but there are more functions that can occur in the middle of the game that there is no way to control and set tasks, so I'm looking for a "solution".

Natsheh 07-05-2021 08:48
Re: MySQL and SQLITE
 
Try creating the tuples first before initiating any threads and use set_affinity after creaing the tuples.

iceeedr 07-05-2021 09:12
Re: MySQL and SQLITE
 
Quote:
Originally Posted by Natsheh (Post 2751927)
Try creating the tuples first before initiating any threads and use set_affinity after creaing the tuples.
Same...

Edit: I found where I read the threadqueries issue (in the include of course...), but I really couldn't understand this explanation.

Code:
@note Intuitive programmers will note that this causes problems for
 *      threaded queries. You will have to either force your script to work
 *      under one affinity, or to pack the affinity type into the query data,
 *      check it against the current, then set the new affinity if necessary.
 *      Then, restore the old one for safety.
PHP Code:
#include <amxmodx>
#include <sqlx>
#pragma defclasslib sqlite sqlite

#define PLUGIN  "New Plug-In"
#define VERSION "1.0"
#define AUTHOR  "Author"

#define CREATE "CREATE TABLE IF NOT EXISTS example (steamid varchar(32), name varchar(32));"
#define CREATE2 "CREATE TABLE IF NOT EXISTS example (steamid TEXT, name TEXT);"
#define CREATE3 "CREATE TABLE IF NOT EXISTS stats (steamid TEXT, name TEXT);"

#define SQLITEFILE "SQLITE"
#define SQLITEFILESTATS "SQLITESTATS"

new Handle:g_MySQL
new Handle:g_MySQLITE
new Handle:g_MySQLITESTATS
new QueryError[512]

public plugin_init()
{
        register_plugin(PLUGIN, VERSION, AUTHOR)

        g_MySQL = SQL_MakeDbTuple(Host,User,Pass,Db)

        g_MySQLITE = SQL_MakeDbTuple("", "", "", SQLITEFILE)

        g_MySQLITESTATS = SQL_MakeDbTuple("", "", "", SQLITEFILESTATS)

        set_task(1.0, "InitQueries")
}

public InitQueries()
{
        SQL_SetAffinity("mysql")

        new szQuery[3][150]
        formatex
        (
                szQuery[0],
                charsmax(szQuery[]),
                CREATE
        )
        SQL_ThreadQuery(g_MySQL,"InitialMySQL_Handler",szQuery[0])

        SQL_SetAffinity("sqlite")

        formatex
        (
                szQuery[1],
                charsmax(szQuery[]),
                CREATE2
        )
        SQL_ThreadQuery(g_MySQLITE,"InitialSQLITE_Handler", szQuery[1])

        formatex
        (
                szQuery[2],
                charsmax(szQuery[]),
                CREATE3
        )
        SQL_ThreadQuery(g_MySQLITESTATS,"InitialSQLITE_Handler", szQuery[2])
}

public InitialSQLITE_Handler(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
        if(FailState == TQUERY_CONNECT_FAILED)
                log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
        else if(FailState == TQUERY_QUERY_FAILED)
        {
                SQL_GetQueryString(Query,QueryError,charsmax(QueryError))
                log_amx("Could not execute query ^"%s^"",QueryError)
        }

        if(!SQL_NumRows(Query))
        {
                server_print("No Results.")
        }
        else
                server_print("Created.")

        return PLUGIN_HANDLED
}

public InitialMySQL_Handler(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
        if(FailState == TQUERY_CONNECT_FAILED)
                log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
        else if(FailState == TQUERY_QUERY_FAILED)
        {
                SQL_GetQueryString(Query,QueryError,charsmax(QueryError))
                log_amx("Could not execute query ^"%s^"",QueryError)
        }

        if(!SQL_NumRows(Query))
        {
                server_print("No Results.")
        }
        else
                server_print("Created.")

        return PLUGIN_HANDLED
} 
Code:
L 07/05/2021 - 10:10:33: Start of error session.
L 07/05/2021 - 10:10:33: Info (map "de_dust2") (file "addons/amxmodx/logs/error_20210705.log")
L 07/05/2021 - 10:10:33: [SQLITE] Invalid info tuple handle: 2
L 07/05/2021 - 10:10:33: [AMXX] Displaying debug trace (plugin "error.amxx", version "1.0")
L 07/05/2021 - 10:10:33: [AMXX] Run time error 10: native error (native "SQL_ThreadQuery")
L 07/05/2021 - 10:10:33: [AMXX]    [0] error.sma::InitQueries (line 56)
L 07/05/2021 - 10:10:34: [SQLITE] Invalid query handle: 4
L 07/05/2021 - 10:10:34: [AMXX] Displaying debug trace (plugin "error.amxx", version "1.0")
L 07/05/2021 - 10:10:34: [AMXX] Run time error 10: native error (native "SQL_NumResults")
L 07/05/2021 - 10:10:34: [AMXX]    [0] error.sma::InitialMySQL_Handler (line 97)

Natsheh 07-05-2021 09:52
Re: MySQL and SQLITE
 
This script should help you.

https://github.com/alliedmodders/amx...e/sqlxtest.sma

iceeedr 07-05-2021 11:02
Re: MySQL and SQLITE
 
Quote:
Originally Posted by Natsheh (Post 2751931)
PHP Code:
#include <amxmodx>
#include <sqlx>
#pragma defclasslib sqlite sqlite

#define PLUGIN  "New Plug-In"
#define VERSION "1.0"
#define AUTHOR  "Author"

#define CREATE "CREATE TABLE IF NOT EXISTS example (steamid varchar(32), name varchar(32));"
#define CREATE2 "CREATE TABLE IF NOT EXISTS example (steamid TEXT, name TEXT);"
#define CREATE3 "CREATE TABLE IF NOT EXISTS stats (steamid TEXT, name TEXT);"

#define SQLITEFILE "SQLITE"
#define SQLITEFILESTATS "SQLITESTATS"

new Handle:g_MySQL
new Handle:g_MySQLITE
new Handle:g_MySQLITESTATS
new QueryError[512]
new QueryNum

public plugin_init()
{
        register_plugin(PLUGIN, VERSION, AUTHOR)

        set_task(2.0, "InitMYSQLQueries")
}

DoBasicInfo(bool:mysql)
{
        new affinity[12]
        new wanted_type[12]

        mysql ? formatex(wanted_type, charsmax(wanted_type), "mysql") : formatex(wanted_type, charsmax(wanted_type), "sqlite")

        SQL_GetAffinity(affinity, charsmax(affinity))

        server_print("SQLX Affinity: %s", affinity)
        server_print("SQLX Wanted: %s", wanted_type)

        if (!equal(wanted_type, affinity))
        {
                if(equal(affinity, "mysql"))
                {
                        SQL_FreeHandle(g_MySQL)
                        g_MySQL = Empty_Handle
                }
                new res = SQL_SetAffinity(wanted_type)
                server_print("Setting affinity from %s to %s: %s",
                        affinity,
                        wanted_type,
                        res ? "Success" : "Failed")
                SQL_GetAffinity(affinity, 11)
                server_print("Verification: %s", affinity)
        }
}

public InitMYSQLQueries()
{
        DoBasicInfo(true)

        g_MySQL = SQL_MakeDbTuple(Host,User,Pass,Db)

        new szQuery[3][150]
        new szData[3][3]
        formatex
        (
                szQuery[0],
                charsmax(szQuery[]),
                CREATE
        )
        QueryNum++
        szData[0][0] = QueryNum
        server_print("Adding to %i queue at: %f", QueryNum, get_gametime())

        SQL_ThreadQuery(g_MySQL,"InitialMySQL_Handler",szQuery[0], szData[0], sizeof(szData))

        DoBasicInfo(false)

        g_MySQLITE = SQL_MakeDbTuple("", "", "", SQLITEFILE)

        g_MySQLITESTATS = SQL_MakeDbTuple("", "", "", SQLITEFILESTATS)

        formatex
        (
                szQuery[1],
                charsmax(szQuery[]),
                CREATE2
        )
        QueryNum++
        szData[1][0] = QueryNum
        server_print("Adding to %i queue at: %f", QueryNum, get_gametime())

        SQL_ThreadQuery(g_MySQLITE,"InitialMySQL_Handler", szQuery[1], szData[1], sizeof(szData))

        formatex
        (
                szQuery[2],
                charsmax(szQuery[]),
                CREATE3
        )
        QueryNum++
        szData[2][0] = QueryNum
        server_print("Adding to %i queue at: %f", QueryNum, get_gametime())

        SQL_ThreadQuery(g_MySQLITESTATS,"InitialMySQL_Handler", szQuery[2], szData[2], sizeof(szData))
}

public InitialMySQL_Handler(FailState, Handle:Query, Error[], Errcode, Data[], DataSize, Float:queuetime)
{
        server_print(" --> Resolved query %d, took %f seconds", Data[0], queuetime)

        if(FailState == TQUERY_CONNECT_FAILED)
                log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
        else if(FailState == TQUERY_QUERY_FAILED)
        {
                SQL_GetQueryString(Query,QueryError,charsmax(QueryError))
                log_amx("Could not execute query ^"%s^"",QueryError)
        }

        if(!SQL_NumRows(Query))
        {
                server_print("No Results.")
        }
        else
                server_print("Created.")

        return PLUGIN_HANDLED
} 
https://i.imgur.com/eYoaxCd.png

Natsheh 07-05-2021 13:53
Re: MySQL and SQLITE
 
here give this a try and show the outputs
Spoiler

iceeedr 07-05-2021 14:12
Re: MySQL and SQLITE
 
Quote:
Originally Posted by Natsheh (Post 2751941)
here give this a try and show the outputs
Made some minor edits to proceed with the compilation.

PHP Code:
#include <amxmodx>
#include <sqlx>
#pragma defclasslib sqlite sqlite

#define PLUGIN  "New Plug-In"
#define VERSION "1.0"
#define AUTHOR  "Author"

#define CREATE "CREATE TABLE IF NOT EXISTS example (steamid varchar(32), name varchar(32));"
#define CREATE2 "CREATE TABLE IF NOT EXISTS example (steamid TEXT, name TEXT);"
#define CREATE3 "CREATE TABLE IF NOT EXISTS stats (steamid TEXT, name TEXT);"

#define SQLITEFILE "SQLITE"
#define SQLITEFILESTATS "SQLITESTATS"


#define HOST "localhost"
#define USER "user"
#define PASS "PASSWORD"
#define DB_INFO "DB INFO"

new Handle:g_iTuple = Empty_Handle, QueryNum;
new QueryError[512]

public plugin_init()
{
        register_plugin(PLUGIN, VERSION, AUTHOR)

        set_task(2.0, "InitMYSQLQueries")
}

public InitMYSQLQueries()
{
        PrepareTuple();

        DoBasicInfo("mysql");

        new szQuery[128]
        new szData[3];

        formatex(szQuery, charsmax(szQuery), CREATE);
        server_print("Adding to %i queue at: %f", ++QueryNum, get_gametime())
        szData[0] = QueryNum;
        SQL_ThreadQuery(g_iTuple,"InitialMySQL_Handler",szQuery, szData, sizeof(szData))

        DoBasicInfo("sqlite");

        formatex( szQuery, charsmax(szQuery), CREATE2)
        server_print("Adding to %i queue at: %f", ++QueryNum, get_gametime())
        szData[0] = QueryNum;
        SQL_ThreadQuery(g_iTuple,"InitialMySQL_Handler", szQuery, szData, sizeof(szData))

        formatex(szQuery, charsmax(szQuery), CREATE3)
        server_print("Adding to %i queue at: %f", ++QueryNum, get_gametime())
        szData[0] = QueryNum;
        SQL_ThreadQuery(g_iTuple,"InitialMySQL_Handler", szQuery, szData, sizeof(szData))
}

DoBasicInfo(const wanted_type[]="mysql")
{
    new affinity[12];

    SQL_GetAffinity(affinity, charsmax(affinity))

    server_print("SQLX Affinity: %s", affinity)
    server_print("SQLX Wanted: %s", wanted_type)

    if (!equal(wanted_type, affinity))
    {
        if(g_iTuple != Empty_Handle)
        {
            SQL_FreeHandle(g_iTuple)
            g_iTuple = Empty_Handle
        }

        new res = SQL_SetAffinity(wanted_type)
        server_print("Setting affinity from %s to %s: %s",
                        affinity,
                        wanted_type,
                        res ? "Success" : "Failed")
        SQL_GetAffinity(affinity, 11)
        PrepareTuple();
        server_print("Verification: %s", affinity)
    }
}

PrepareTuple()
{
    g_iTuple = SQL_MakeDbTuple(HOST,USER,PASS,DB_INFO);
}

public InitialMySQL_Handler(FailState, Handle:Query, Error[], Errcode, Data[], DataSize, Float:queuetime)
{
        server_print(" --> Resolved query %d, took %f seconds", Data[0], queuetime)

        if(FailState == TQUERY_CONNECT_FAILED)
                log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
        else if(FailState == TQUERY_QUERY_FAILED)
        {
                SQL_GetQueryString(Query,QueryError,charsmax(QueryError))
                log_amx("Could not execute query ^"%s^"",QueryError)
        }

        if(!SQL_NumRows(Query))
        {
                server_print("No Results.")
        }
        else
                server_print("Created.")
} 
https://i.imgur.com/mDmdUI1.png

Natsheh 07-05-2021 14:28
Re: MySQL and SQLITE
 
code edited removed "SQL_GetQueryString(Query,QueryError,charsmax (QueryError))" from the code, since having it inside threader function callback doesn't make alot of sense to me.

test again and see if the error still occurs.

iceeedr 07-05-2021 14:37
Re: MySQL and SQLITE
 
Quote:
Originally Posted by Natsheh (Post 2751943)
code edited removed "SQL_GetQueryString(Query,QueryError,charsmax (QueryError))" from the code, since having it inside threader function callback doesn't make alot of sense to me.

test again and see if the error still occurs.
Removed, but the problem persists, honestly I'm out of ideas.

Natsheh 07-05-2021 14:52
Re: MySQL and SQLITE
 
Quote:
Originally Posted by iceeedr (Post 2751944)
Removed, but the problem persists, honestly I'm out of ideas.
okay after a small reading period i found out that you need to do this inside the threader callback

Spoiler


All times are GMT -4. The time now is 06:04.
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.