Raised This Month: $12 Target: $400
 3% 

Solved MySQL and SQLITE


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
iceeedr
Veteran Member
Join Date: Apr 2017
Location: Brazil
Old 07-05-2021 , 00:30   MySQL and SQLITE
Reply With Quote #1

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(PLUGINVERSIONAUTHOR)

        
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(FailStateHandle:QueryError[], ErrcodeData[], DataSize)
{
        if(
FailState == TQUERY_CONNECT_FAILED)
                
log_amx("Load - Could not connect to SQL database.  [%d] %s"ErrcodeError)
        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(FailStateHandle:QueryError[], ErrcodeData[], DataSize)
{
        if(
FailState == TQUERY_CONNECT_FAILED)
                
log_amx("Load - Could not connect to SQL database.  [%d] %s"ErrcodeError)
        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".
__________________


Quote:
Originally Posted by fysiks View Post
Please stop trying to help. You appear to just be posting random stuff. Wait until you actually understand more about AMX Mod X and how the game works.
https://iceeedr.com.br/

Last edited by iceeedr; 07-05-2021 at 19:50.
iceeedr is offline
Send a message via Skype™ to iceeedr
Natsheh
Veteran Member
Join Date: Sep 2012
Old 07-05-2021 , 08:48   Re: MySQL and SQLITE
Reply With Quote #2

Try creating the tuples first before initiating any threads and use set_affinity after creaing the tuples.
__________________
@Jailbreak Main Mod v2.7.0 100%
@User Tag Prefix 100% done !
@Mystery Box 100% done !
@VIP System 100% done !


Last edited by Natsheh; 07-05-2021 at 08:50.
Natsheh is offline
Send a message via MSN to Natsheh Send a message via Skype™ to Natsheh
iceeedr
Veteran Member
Join Date: Apr 2017
Location: Brazil
Old 07-05-2021 , 09:12   Re: MySQL and SQLITE
Reply With Quote #3

Quote:
Originally Posted by Natsheh View Post
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(PLUGINVERSIONAUTHOR)

        
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(FailStateHandle:QueryError[], ErrcodeData[], DataSize)
{
        if(
FailState == TQUERY_CONNECT_FAILED)
                
log_amx("Load - Could not connect to SQL database.  [%d] %s"ErrcodeError)
        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(FailStateHandle:QueryError[], ErrcodeData[], DataSize)
{
        if(
FailState == TQUERY_CONNECT_FAILED)
                
log_amx("Load - Could not connect to SQL database.  [%d] %s"ErrcodeError)
        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)
__________________


Quote:
Originally Posted by fysiks View Post
Please stop trying to help. You appear to just be posting random stuff. Wait until you actually understand more about AMX Mod X and how the game works.
https://iceeedr.com.br/

Last edited by iceeedr; 07-05-2021 at 09:25.
iceeedr is offline
Send a message via Skype™ to iceeedr
Natsheh
Veteran Member
Join Date: Sep 2012
Old 07-05-2021 , 09:52   Re: MySQL and SQLITE
Reply With Quote #4

This script should help you.

https://github.com/alliedmodders/amx...e/sqlxtest.sma
__________________
@Jailbreak Main Mod v2.7.0 100%
@User Tag Prefix 100% done !
@Mystery Box 100% done !
@VIP System 100% done !

Natsheh is offline
Send a message via MSN to Natsheh Send a message via Skype™ to Natsheh
iceeedr
Veteran Member
Join Date: Apr 2017
Location: Brazil
Old 07-05-2021 , 11:02   Re: MySQL and SQLITE
Reply With Quote #5

Quote:
Originally Posted by Natsheh View Post
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(PLUGINVERSIONAUTHOR)

        
set_task(2.0"InitMYSQLQueries")
}

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

        
mysql formatex(wanted_typecharsmax(wanted_type), "mysql") : formatex(wanted_typecharsmax(wanted_type), "sqlite")

        
SQL_GetAffinity(affinitycharsmax(affinity))

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

        if (!
equal(wanted_typeaffinity))
        {
                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(affinity11)
                
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"QueryNumget_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"QueryNumget_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"QueryNumget_gametime())

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

public 
InitialMySQL_Handler(FailStateHandle:QueryError[], ErrcodeData[], DataSizeFloat: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"ErrcodeError)
        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

__________________


Quote:
Originally Posted by fysiks View Post
Please stop trying to help. You appear to just be posting random stuff. Wait until you actually understand more about AMX Mod X and how the game works.
https://iceeedr.com.br/

Last edited by iceeedr; 07-05-2021 at 12:03.
iceeedr is offline
Send a message via Skype™ to iceeedr
Old 07-05-2021, 13:09
Natsheh
This message has been deleted by Natsheh. Reason: nvm
Natsheh
Veteran Member
Join Date: Sep 2012
Old 07-05-2021 , 13:53   Re: MySQL and SQLITE
Reply With Quote #6

here give this a try and show the outputs
Spoiler
__________________
@Jailbreak Main Mod v2.7.0 100%
@User Tag Prefix 100% done !
@Mystery Box 100% done !
@VIP System 100% done !


Last edited by Natsheh; 07-05-2021 at 14:26.
Natsheh is offline
Send a message via MSN to Natsheh Send a message via Skype™ to Natsheh
iceeedr
Veteran Member
Join Date: Apr 2017
Location: Brazil
Old 07-05-2021 , 14:12   Re: MySQL and SQLITE
Reply With Quote #7

Quote:
Originally Posted by Natsheh View Post
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_HandleQueryNum;
new 
QueryError[512]

public 
plugin_init()
{
        
register_plugin(PLUGINVERSIONAUTHOR)

        
set_task(2.0"InitMYSQLQueries")
}

public 
InitMYSQLQueries()
{
        
PrepareTuple();

        
DoBasicInfo("mysql");

        new 
szQuery[128]
        new 
szData[3];

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

        
DoBasicInfo("sqlite");

        
formatexszQuerycharsmax(szQuery), CREATE2)
        
server_print("Adding to %i queue at: %f", ++QueryNumget_gametime())
        
szData[0] = QueryNum;
        
SQL_ThreadQuery(g_iTuple,"InitialMySQL_Handler"szQueryszDatasizeof(szData))

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

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

    
SQL_GetAffinity(affinitycharsmax(affinity))

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

    if (!
equal(wanted_typeaffinity))
    {
        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(affinity11)
        
PrepareTuple();
        
server_print("Verification: %s"affinity)
    }
}

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

public 
InitialMySQL_Handler(FailStateHandle:QueryError[], ErrcodeData[], DataSizeFloat: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"ErrcodeError)
        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.")

__________________


Quote:
Originally Posted by fysiks View Post
Please stop trying to help. You appear to just be posting random stuff. Wait until you actually understand more about AMX Mod X and how the game works.
https://iceeedr.com.br/

Last edited by iceeedr; 07-05-2021 at 14:15.
iceeedr is offline
Send a message via Skype™ to iceeedr
Natsheh
Veteran Member
Join Date: Sep 2012
Old 07-05-2021 , 14:28   Re: MySQL and SQLITE
Reply With Quote #8

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.
__________________
@Jailbreak Main Mod v2.7.0 100%
@User Tag Prefix 100% done !
@Mystery Box 100% done !
@VIP System 100% done !

Natsheh is offline
Send a message via MSN to Natsheh Send a message via Skype™ to Natsheh
iceeedr
Veteran Member
Join Date: Apr 2017
Location: Brazil
Old 07-05-2021 , 14:37   Re: MySQL and SQLITE
Reply With Quote #9

Quote:
Originally Posted by Natsheh View Post
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.
__________________


Quote:
Originally Posted by fysiks View Post
Please stop trying to help. You appear to just be posting random stuff. Wait until you actually understand more about AMX Mod X and how the game works.
https://iceeedr.com.br/
iceeedr is offline
Send a message via Skype™ to iceeedr
Natsheh
Veteran Member
Join Date: Sep 2012
Old 07-05-2021 , 14:52   Re: MySQL and SQLITE
Reply With Quote #10

Quote:
Originally Posted by iceeedr View Post
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
__________________
@Jailbreak Main Mod v2.7.0 100%
@User Tag Prefix 100% done !
@Mystery Box 100% done !
@VIP System 100% done !


Last edited by Natsheh; 07-05-2021 at 19:00.
Natsheh is offline
Send a message via MSN to Natsheh Send a message via Skype™ to Natsheh
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 18:55.


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