AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   [SQL] Connection problem (https://forums.alliedmods.net/showthread.php?t=189767)

bartek93tbg 07-10-2012 16:54
[SQL] Connection problem
 
Hey
I have got a problem with SQL db.
Here is the code:

Code:
#include <amxmodx>
#include <amxmisc>
#include <sqlx>

new SQL_HOST[]        = "xxx"       
new SQL_USER[]        = "yyy"       
new SQL_PASS[]        = "zzz"         
new SQL_DB[]        = "expmod_data"       

new Handle:g_SqlTuple
new bool:IsLogged[33]

new Nick[33][32], SteamID[33][32]
new XP[33], XPALL[33], Poziom[33]

new Zycie[33], Odpornosc[33], Pancerz[33], Szybkosc[33], Grawitacja[33], Widocznosc[33], Obrazenia[33], Krytyk[33]
new Wampiryzm[33], DarmowaBron[33], Unik[33], Infekcja[33], Podpalenie[33], Kradziez[33], Dekoncentracja[33], Kolo[33]

new KillZ[33], KillH[33], KillZB[33], KillHB[33], Death[33], Infect[33], Boss[33], Survive[33], Damages[33], Maps[33], Online[33]
new g_StartTime[33], g_LastCheck[33]

public plugin_init()
{
    register_plugin("[ZP] SQL Test", "1.0", "XYZ")

    set_task(3.0, "makeTuple")
    set_task(3.5, "createTable")
}

public makeTuple()
{
    g_SqlTuple = SQL_MakeDbTuple(SQL_HOST, SQL_USER, SQL_PASS, SQL_DB)
}

public createTable()
{
    new szQuery[2048]
    new len
   
    len += format(szQuery[len], 2048,"CREATE TABLE IF NOT EXISTS expmod(")
    len += format(szQuery[len], 2048-len,"klucz VARCHAR(100), ")
    len += format(szQuery[len], 2048-len,"nick VARCHAR(100), ")
    len += format(szQuery[len], 2048-len,"xp INT(11), ")
    len += format(szQuery[len], 2048-len,"xpall INT(11), ")
    len += format(szQuery[len], 2048-len,"poziom INT(11), ")
    len += format(szQuery[len], 2048-len,"zycie INT(11), ")
    len += format(szQuery[len], 2048-len,"odpornosc INT(11), ")
    len += format(szQuery[len], 2048-len,"pancerz INT(11), ")
    len += format(szQuery[len], 2048-len,"szybkosc INT(11), ")
    len += format(szQuery[len], 2048-len,"grawitacja INT(11), ")
    len += format(szQuery[len], 2048-len,"widocznosc INT(11), ")
    len += format(szQuery[len], 2048-len,"obrazenia INT(11), ")
    len += format(szQuery[len], 2048-len,"krytyk INT(11), ")
    len += format(szQuery[len], 2048-len,"wampiryzm INT(11), ")
    len += format(szQuery[len], 2048-len,"darmowabron INT(11), ")
    len += format(szQuery[len], 2048-len,"unik INT(11), ")
    len += format(szQuery[len], 2048-len,"infekcja INT(11), ")
    len += format(szQuery[len], 2048-len,"podpalenie INT(11), ")
    len += format(szQuery[len], 2048-len,"kradziez INT(11), ")
    len += format(szQuery[len], 2048-len,"dekoncentracja INT(11), ")
    len += format(szQuery[len], 2048-len,"kolo INT(11), ")
    len += format(szQuery[len], 2048-len,"killz INT(11), ")
    len += format(szQuery[len], 2048-len,"killh INT(11), ")
    len += format(szQuery[len], 2048-len,"killzb INT(11), ")
    len += format(szQuery[len], 2048-len,"killhb INT(11), ")
    len += format(szQuery[len], 2048-len,"death INT(11), ")
    len += format(szQuery[len], 2048-len,"infect INT(11), ")
    len += format(szQuery[len], 2048-len,"boss INT(11), ")
    len += format(szQuery[len], 2048-len,"survive INT(11), ")
    len += format(szQuery[len], 2048-len,"damages INT(22), ")
    len += format(szQuery[len], 2048-len,"maps INT(11), ")
    len += format(szQuery[len], 2048-len,"online INT(11))")

    SQL_ThreadQuery(g_SqlTuple,"createTableThread", szQuery)
}

public createTableThread(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
        return set_fail_state("Could not connect to SQL database.")
    else if(FailState == TQUERY_QUERY_FAILED)
        return set_fail_state("Query failed creating table.")
   
    if(Errcode)
        return log_amx("Error on query: %s",Error)
   
    return PLUGIN_CONTINUE
}

public client_putinserver(id)
{
    IsLogged[id] = false

    get_user_name(id, Nick[id], 31)
    get_user_authid(id, SteamID[id], 31)

    XP[id] = 0
    XPALL[id] = 0
    Poziom[id] = 1
    Zycie[id] = 0
    Odpornosc[id] = 0
    Pancerz[id] = 0
    Szybkosc[id] = 0
    Grawitacja[id] = 0
    Widocznosc[id] = 0
    Obrazenia[id] = 0
    Krytyk[id] = 0
    Wampiryzm[id] = 0
    DarmowaBron[id] = 0
    Unik[id] = 0
    Infekcja[id] = 0
    Podpalenie[id] = 0
    Kradziez[id] = 0
    Dekoncentracja[id] = 0
    Kolo[id] = 0

    KillZ[id] = 0
    KillH[id] = 0
    KillZB[id] = 0
    KillHB[id] = 0
    Death[id] = 0
    Infect[id] = 0
    Boss[id] = 0
    Survive[id] = 0
    Maps[id] = 0
    Online[id] = 0

    g_StartTime[id] = get_systime()
    g_LastCheck[id] = get_systime()

    if(!is_user_bot(id) && !is_user_hltv(id))
    {   
        set_task(3.0, "Load_MySQL", id)
    }
}

public Load_MySQL(id)
{   
    new Data[1]
    Data[0] = id

    new ErrorCode
    new Error[512]
    new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, Error, 511)
           
    if(SqlConnection == Empty_Handle)
    {   
        set_fail_state(Error)
    }
   
    new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT * FROM expmod WHERE klucz='%s'", SteamID[id])
       
    if(!SQL_Execute(Query))
    {
        SQL_QueryError(Query, Error, 511)
        set_fail_state(Error)
    }

    new iRowsFound = SQL_NumRows(Query)

    if(iRowsFound < 1)
    {
        new Handle:Query = SQL_PrepareQuery(SqlConnection, "INSERT INTO expmod VALUES('%s','%s', '0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0')", SteamID[id], Nick[id])

        if(!SQL_Execute(Query))
        {
            SQL_QueryError(Query, Error, 511)
            set_fail_state(Error)
        }   
    }
    else
    {
        XP[id] = SQL_ReadResult(Query, 2)
        XPALL[id] = SQL_ReadResult(Query, 3)
        Poziom[id] = SQL_ReadResult(Query, 4)
        Zycie[id] = SQL_ReadResult(Query, 5)
        Odpornosc[id] = SQL_ReadResult(Query, 6)
        Pancerz[id] = SQL_ReadResult(Query, 7)
        Szybkosc[id] = SQL_ReadResult(Query, 8)
        Grawitacja[id] = SQL_ReadResult(Query, 9)
        Widocznosc[id] = SQL_ReadResult(Query, 10)
        Obrazenia[id] = SQL_ReadResult(Query, 11)
        Krytyk[id] = SQL_ReadResult(Query, 12)
        Wampiryzm[id] = SQL_ReadResult(Query, 13)
        DarmowaBron[id] = SQL_ReadResult(Query, 14)
        Unik[id] = SQL_ReadResult(Query, 15)
        Infekcja[id] = SQL_ReadResult(Query, 16)
        Podpalenie[id] = SQL_ReadResult(Query, 17)
        Kradziez[id] = SQL_ReadResult(Query, 18)
        Dekoncentracja[id] = SQL_ReadResult(Query, 19)
        Kolo[id] = SQL_ReadResult(Query, 20)
        KillZ[id] = SQL_ReadResult(Query, 21)
        KillH[id] = SQL_ReadResult(Query, 22)
        KillZB[id] = SQL_ReadResult(Query, 23)
        KillHB[id] = SQL_ReadResult(Query, 24)
        Death[id] = SQL_ReadResult(Query, 25)
        Infect[id] = SQL_ReadResult(Query, 26)
        Boss[id] = SQL_ReadResult(Query, 27)
        Survive[id] = SQL_ReadResult(Query, 28)
        Damages[id] = SQL_ReadResult(Query, 29)
        Maps[id] = SQL_ReadResult(Query, 30)
        Online[id] = SQL_ReadResult(Query, 31)
    }

    SQL_FreeHandle(Query)
    SQL_FreeHandle(SqlConnection)

    IsLogged[id] = true
}

public client_disconnect(id)
{
    new current_time = get_systime()

    new diff = current_time - g_StartTime[id]
    Online[id] += current_time - g_LastCheck[id]

    if(diff > 1200)
        Maps[id]++

    if(IsLogged[id])
        Save_MySQL(id)
}

public Save_MySQL(id)
{               
    new szQuery[2048]
    new len = 0
       
    len += format(szQuery[len], 2048, "UPDATE expmod SET ")
    len += format(szQuery[len], 2048-len,"nick = '%s', ", Nick[id])
    len += format(szQuery[len], 2048-len,"xp = '%i', ", XP[id])
    len += format(szQuery[len], 2048-len,"xpall = '%i', ", XPALL[id])
    len += format(szQuery[len], 2048-len,"poziom = '%i', ", Poziom[id])
    len += format(szQuery[len], 2048-len,"zycie = '%i', ", Zycie[id])
    len += format(szQuery[len], 2048-len,"odpornosc = '%i', ", Odpornosc[id])
    len += format(szQuery[len], 2048-len,"pancerz = '%i', ", Pancerz[id])
    len += format(szQuery[len], 2048-len,"szybkosc = '%i', ", Szybkosc[id])
    len += format(szQuery[len], 2048-len,"grawitacja = '%i', ", Grawitacja[id])
    len += format(szQuery[len], 2048-len,"widocznosc = '%i', ", Widocznosc[id])
    len += format(szQuery[len], 2048-len,"obrazenia = '%i', ", Obrazenia[id])
    len += format(szQuery[len], 2048-len,"krytyk = '%i', ", Krytyk[id])
    len += format(szQuery[len], 2048-len,"wampiryzm = '%i', ", Wampiryzm[id])
    len += format(szQuery[len], 2048-len,"darmowabron = '%i', ", DarmowaBron[id])
    len += format(szQuery[len], 2048-len,"unik = '%i', ", Unik[id])
    len += format(szQuery[len], 2048-len,"infekcja = '%i', ", Infekcja[id])
    len += format(szQuery[len], 2048-len,"podpalenie = '%i', ", Podpalenie[id])
    len += format(szQuery[len], 2048-len,"kradziez = '%i', ", Kradziez[id])
    len += format(szQuery[len], 2048-len,"dekoncentracja = '%i', ", Dekoncentracja[id])
    len += format(szQuery[len], 2048-len,"kolo = '%i', ", Kolo[id])
    len += format(szQuery[len], 2048-len,"killz = '%i', ", KillZ[id])
    len += format(szQuery[len], 2048-len,"killh = '%i', ", KillH[id])
    len += format(szQuery[len], 2048-len,"killzb = '%i', ", KillZB[id])
    len += format(szQuery[len], 2048-len,"killhb = '%i', ", KillHB[id])
    len += format(szQuery[len], 2048-len,"death = '%i', ", Death[id])
    len += format(szQuery[len], 2048-len,"infect = '%i', ", Infect[id])
    len += format(szQuery[len], 2048-len,"boss = '%i', ", Boss[id])
    len += format(szQuery[len], 2048-len,"survive = '%i', ", Survive[id])
    len += format(szQuery[len], 2048-len,"damages = '%i', ", Damages[id])
    len += format(szQuery[len], 2048-len,"maps = '%i', ", Maps[id])
    len += format(szQuery[len], 2048-len,"online = '%i' ", Online[id])
    len += format(szQuery[len], 2048-len,"WHERE klucz = '%s'", SteamID[id])

    SQL_ThreadQuery(g_SqlTuple,"updateTableThread", szQuery)
}

public updateTableThread(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
        return set_fail_state("Could not connect to SQL database.")
    else if(FailState == TQUERY_QUERY_FAILED)
        return set_fail_state("Query failed saving table.")
   
    if(Errcode)
        return log_amx("Error on query: %s",Error)
   
    return PLUGIN_CONTINUE
}

public plugin_end()
{
    if(g_SqlTuple)
        SQL_FreeHandle(g_SqlTuple)
}
Code:
L 07/10/2012 - 22:01:47: Start of error session.
L 07/10/2012 - 22:01:47: Info (map "zm_ice_attack2") (file "addons/amxmodx/logs/error_20120710.log")
L 07/10/2012 - 22:01:47: [AMXX] Plugin ("sql_test.amxx") is setting itself as failed.
L 07/10/2012 - 22:01:47: [AMXX] Plugin says: Could not connect to SQL database.
L 07/10/2012 - 22:01:47: [AMXX] Run time error 1 (plugin "sql_test.amxx") - forced exit
It works well on my test server (LAN), but when I put it on real server it just does not work and gives me the error I posted above. It happens right after mapchange.
I am sure that it is not customization (host, user, password) fault, otherwise it would not work on any server.

Have you guys any ideas why does it happen?
Modules are fine.

Any help will be appreciated.

Y060N 07-10-2012 16:56
Re: [SQL] Connection problem
 
It's because of this line:

if(FailState == TQUERY_CONNECT_FAILED)
return set_fail_state("Could not connect to SQL database.")

It a problem with the login information or permissions of file.

bartek93tbg 07-10-2012 17:16
Re: [SQL] Connection problem
 
I know which line causes the error :P

By the way I have an idea. Could somebody test the plugin on any server (using your own database)? So i would know if there is something wrong with my server or it is just plugins code fault.

wickedd 07-10-2012 18:00
Re: [SQL] Connection problem
 
I tested it for you and it did connect for me.

jimaway 07-10-2012 18:20
Re: [SQL] Connection problem
 
probably mysql permission problem, not amxx related

hornet 07-10-2012 22:03
Re: [SQL] Connection problem
 
This line wasn't reached:
Code:
log_amx("Error on query: %s",Error)

Make it reachable and then show the error it gave - that'll be your reason.


All times are GMT -4. The time now is 07:06.

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