Need help with sql and amxx.

xd346 · **Author**

Hi, I want to make a plugin that will store data in a database I made using phpMyAdmin. I'm also using wamp.
I've been using this guide https://wiki.alliedmods.net/Advanced...ng_(AMX_Mod_X).

PHP Code:


			
#include <amxmodx>

#include <dbi>

#include <amxx\amxmod_compat\mysql>

#include <esf>

#include <ipci\ascend>

#include <esf_util>

#include <fun>

#include <fakemeta>

#include <fakemeta_const>

#include <hamsandwich> 









new Sql:databaseconnection

new err[255]

new errNum



public plugin_init() 

{

  register_plugin("Database Data","1.0","")

  register_clcmd("say steamid","plugintriggertest");

  RegisterHam(Ham_Killed, "player", "fwdPlayerDeath", 0);

  

  databaseconnection= dbi_connect("127.0.0.1", "root", "", "esf_data")

  if (databaseconnection < SQL_OK) 

    {

        

        errNum = dbi_error(databaseconnection, err, 254)

        server_print("error1: %s|%d", err, errNum)

    }

}



new steamid[32]

new username[40]

new powerlevel

new charactername[32]

new characterid 



public plugintriggertest(id)

{

    client_print(id,print_chat,"error1: %s|%d", err, errNum)

    get_user_name(id,username,40)

    get_user_authid( id, steamid, 32)

    powerlevel=getClientPL(id);

    characterid=getClientCLASS(id);

    getClassDATAv( characterid, cxName, charactername, 32 );

    new Result:ret = dbi_query(databaseconnection,"INSERT INTO general_information (SteamID,Username,Character_1,Powerlevel_1) VALUES ('%s','%s','%s',%i);",steamid,username,charactername,powerlevel) 

    //new Result:ret = dbi_query(databaseconnection,"INSERT INTO general_information (SteamID,Username,Character_1,Powerlevel_1) VALUES ('%s','%s','%s',%i);",steamid,username,charactername,powerlevel) 

    //client_print(id,print_chat,"INSERT INTO general_information (SteamID,Username,Character_1,Powerlevel_1) VALUES ('%s','%s','%s',%i);",steamid,username,charactername,powerlevel)

    if (ret < RESULT_NONE) 

    {

        new err[255]

        new errNum = dbi_error(databaseconnection, err, 254)

        server_print("error2: %s|%d", err, errNum)

    }

    dbi_close(databaseconnection)

}

If I run the query through the mysql console it works, but when I try running it ingame I get this error:

PHP Code:


			
L 08/26/2019 - 21:06:26: [SQLITE] Invalid DBI handle 0

L 08/26/2019 - 21:06:26: [AMXX] Displaying debug trace (plugin "DBContent.amxx")

L 08/26/2019 - 21:06:26: [AMXX] Run time error 10: native error (native "dbi_query")

L 08/26/2019 - 21:06:26: [AMXX]    [0] DBContentSQLX.sma::plugintriggertest (line 48)

I'm kinda new to amxx scripting so any help would be appreciated.

Natsheh · 08-26-2019 , 15:22 Re: Need help with sql and amxx.

That guide is outdated use sqlx include file, for more info about sqlx natives you can search for it...

xd346 · 08-26-2019 , 15:48 Re: Need help with sql and amxx.

Quote:

Originally Posted by Natsheh

That guide is outdated use sqlx include file, for more info about sqlx natives you can search for it...

Thanks for the reply, I've tried rewriting it and still get the same error.

Code:

PHP Code:


			
#include <amxmodx> 

#include <sqlx> 

#include <amxx\amxmod_compat\mysql> 

#include <esf> 

#include <ipci\ascend> 

#include <esf_util> 

#include <fun> 

#include <fakemeta> 

#include <fakemeta_const> 

#include <hamsandwich>  



new Handle:g_SqlTuple

new g_Error[512]

new ErrorCode,Handle:SqlConnection



public plugin_init()  

{ 

  register_plugin("Database Data","1.0","") 

  register_clcmd("say steamid","plugintriggertest"); 

  RegisterHam(Ham_Killed, "player", "fwdPlayerDeath", 0);

  g_SqlTuple=SQL_MakeDbTuple("127.0.0.1","root","","esf_data")

  

  SqlConnection= SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)

  if(SqlConnection == Empty_Handle)

        set_fail_state(g_Error)

} 



new steamid[32] 

new username[40] 

new powerlevel 

new charactername[32] 

new characterid  



public plugintriggertest(id) 

{ 

    get_user_name(id,username,40) 

    get_user_authid( id, steamid, 32) 

    powerlevel=getClientPL(id); 

    characterid=getClientCLASS(id); 

    getClassDATAv( characterid, cxName, charactername, 32 ); 

    

    new Handle:Query=SQL_PrepareQuery(SqlConnection,"INSERT INTO general_information (SteamID,Username,Character_1,Powerlevel_1) VALUES ('%s','%s','%s',%i);",steamid,username,charactername,powerlevel)

    

    if(!SQL_Execute(Query))

    {

        SQL_QueryError(Query,g_Error,511)

        set_fail_state(g_Error)

    }

    

    SQL_FreeHandle(Query)

}

Error:

PHP Code:


			
L 08/26/2019 - 21:45:54: [SQLITE] Invalid database handle: 0

L 08/26/2019 - 21:45:54: [AMXX] Displaying debug trace (plugin "DBContent.amxx")

L 08/26/2019 - 21:45:54: [AMXX] Run time error 10: native error (native "SQL_PrepareQuery")

L 08/26/2019 - 21:45:54: [AMXX]    [0] DBContentSQLX.sma::plugintriggertest (line 42)

Natsheh · *Last edited by Natsheh; 08-26-2019 at 16:01.*

What's the Affinity type you are using ?

Also why do you need this include amxx\amxmod_compat\mysql

Can you check if SqlConnection is valid in plugintriggertest

xd346 · *Last edited by xd346; 08-26-2019 at 16:11.*

This is my first time using sql with amxx so I didn't know if i needed to include it or not.

What do you mean by Affinity type, data type stored in the database?
SteamID, Username and Character_1 are varchar while Powerlevel is int.

Quote:

if(SqlConnection == Empty_Handle)
set_fail_state(g_Error)

After adding this to plugintriggertest i get this error:

Quote:

L 08/26/2019 - 22:10

2: [AMXX] Plugin ("DBContent.amxx") is setting itself as failed.
L 08/26/2019 - 22:10

2: [AMXX] Plugin says:
L 08/26/2019 - 22:10

2: [AMXX] Displaying debug trace (plugin "DBContent.amxx")
L 08/26/2019 - 22:10

2: [AMXX] Run time error 1: forced exit
L 08/26/2019 - 22:10

2: [AMXX] [0] DBContentSQLX.sma::plugintriggertest (line 36)

Natsheh · *Last edited by Natsheh; 08-26-2019 at 16:57.*

Are you sure you have connected to the database?

xd346 · 08-27-2019 , 04:33 Re: Need help with sql and amxx.

Quote:

Originally Posted by Natsheh

Are you sure you have connected to the database?

How can I check? I'm using the correct hostname,username,password and database, wamp is running.

After following the guide Airkish left I get this error:

PHP Code:


			
L 08/27/2019 - 10:23:43: [MySQL] Invalid info tuple handle: 0

L 08/27/2019 - 10:23:43: [AMXX] Displaying debug trace (plugin "DBContentSQLX.amxx")

L 08/27/2019 - 10:23:43: [AMXX] Run time error 10: native error (native "SQL_ThreadQuery")

L 08/27/2019 - 10:23:43: [AMXX]    [0] DBContentSQLX.sma::Load_MySql (line 59)

L 08/27/2019 - 10:23:43: [AMXX]    [1] DBContentSQLX.sma::client_putinserver (line 116)

lines 50-60:

PHP Code:


			
public Load_MySql(id)

{

    new szSteamId[32], szTemp[512]

    get_user_authid(id, szSteamId, 32)

    

    new Data[1]

    Data[0] = id



    format(szTemp,512,"SELECT * FROM test WHERE (test.steamid = '%s')", szSteamId)

    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)

}

lines 114-117:

PHP Code:


			
public client_putinserver(id)

{

    Load_MySql(id)

}

Airkish · 08-27-2019 , 06:32 Re: Need help with sql and amxx.

Quote:

Originally Posted by xd346

How can I check? I'm using the correct hostname,username,password and database, wamp is running.

After following the guide Airkish left I get this error:

PHP Code:


			
L 08/27/2019 - 10:23:43: [MySQL] Invalid info tuple handle: 0

L 08/27/2019 - 10:23:43: [AMXX] Displaying debug trace (plugin "DBContentSQLX.amxx")

L 08/27/2019 - 10:23:43: [AMXX] Run time error 10: native error (native "SQL_ThreadQuery")

L 08/27/2019 - 10:23:43: [AMXX]    [0] DBContentSQLX.sma::Load_MySql (line 59)

L 08/27/2019 - 10:23:43: [AMXX]    [1] DBContentSQLX.sma::client_putinserver (line 116)

lines 50-60:

PHP Code:


			
public Load_MySql(id)

{

    new szSteamId[32], szTemp[512]

    get_user_authid(id, szSteamId, 32)

    

    new Data[1]

    Data[0] = id



    format(szTemp,512,"SELECT * FROM test WHERE (test.steamid = '%s')", szSteamId)

    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)

}

lines 114-117:

PHP Code:


			
public client_putinserver(id)

{

    Load_MySql(id)

}

show me whole code

xd346 · 08-27-2019 , 06:36 Re: Need help with sql and amxx.

Here you go

PHP Code:


			
#include <amxmodx> 

#include <sqlx>

#include <ipci\ascend> 

#include <esf_util> 

#include <fun> 

#include <fakemeta> 

#include <fakemeta_const> 

#include <hamsandwich>  



new Handle:g_SqlTuple

new g_Error[512]

new iExp[32]



new Host[]     = "hostname"

new User[]    = "root"

new Pass[]     = ""

new Db[]     = "esf_data"



public plugin_init()  

{ 

  register_plugin("Database Data","1.0","") 

  register_clcmd("say steamid","plugintriggertest"); 

  RegisterHam(Ham_Killed, "player", "fwdPlayerDeath", 0);

  

  set_task(1.0, "MySql_Init")

} 



public MySql_Init()

{

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

    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,512)

    if(SqlConnection == Empty_Handle)

        set_fail_state(g_Error)

       

    new Handle:Queries

    Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS test (steamid varchar(32),pl INT(12))")



    if(!SQL_Execute(Queries))

    {

        SQL_QueryError(Queries,g_Error,512)

        set_fail_state(g_Error)

    }

    

    SQL_FreeHandle(Queries)



    SQL_FreeHandle(SqlConnection)   

}



public Load_MySql(id)

{

    new szSteamId[32], szTemp[512]

    get_user_authid(id, szSteamId, 32)

    

    new Data[1]

    Data[0] = id



    format(szTemp,512,"SELECT * FROM test WHERE (tutorial.steamid = '%s')", szSteamId)

    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)

}





public register_client(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)

    {

        log_amx("Load Query failed. [%d] %s", Errcode, Error)

    }



    new id

    id = Data[0]

    

    if(SQL_NumResults(Query) < 1) 

    {

        new szSteamId[32]

        get_user_authid(id, szSteamId, 32)

        if (equal(szSteamId,"ID_PENDING"))

            return PLUGIN_HANDLED

            

        new szTemp[512]



        format(szTemp,512,"INSERT INTO test ( steamid , pl)VALUES ('%s','0');",szSteamId)

        SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)

    } 

    else 

    {

        // if there are results found

        iExp[id]         = SQL_ReadResult(Query, 1)

    }

    

    return PLUGIN_HANDLED

}



public Save_MySql(id)

{

    new szSteamId[32], szName[32], szTemp[512]

    get_user_authid(id, szSteamId, 32)



    format(szTemp,512,"UPDATE test SET pl = '%i' WHERE tutorial.steamid = '%s';",iExp[id], szSteamId)

    SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)

}



public IgnoreHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)

{

    SQL_FreeHandle(Query)

    

    return PLUGIN_HANDLED

}



public client_putinserver(id)

{

    Load_MySql(id)

}



public client_disconnect(id)

{

    Save_MySql(id)

}



//ignore this part

new steamid[32] 

new username[40] 

new powerlevel 

new charactername[32] 

new characterid  



public plugintriggertest(id) 

{ 

    get_user_name(id,username,40) 

    get_user_authid( id, steamid, 32)

    getClassDATAv( characterid, cxName, charactername, 32 ); 

}

Airkish · 08-27-2019 , 03:24 Re: Need help with sql and amxx.

Here's simple mysql example, just follow it: https://forums.alliedmods.net/showthread.php?t=132686