[mikes3ds]

I need a solid and simple example of a SQLX saving system for amxx...

I know there are some plugins that use SQLX but I do not know what one will be best to use.


Note: What the SQL system I will be coding to save: The total time a person is on that server.


Old post of trying to code it befor.
http://forums.alliedmods.net/showthread.php?t=62618&

Thank you!

[|PJ| Shorty]

this is (basicly) the way i did this (mysql server > v4.0 required):

PHP Code:

#include <amxmodx>
#include <amxmisc>
#include <sqlx>
new Handle:dbc;
new g_Cache[1024];
new g_authid[33][36];
public plugin_init() {
 register_plugin("time_2_sqlx", "1.0", "author");
 set_task(1.0,"sql_init")
}
public sql_init() {
 new host[64], username[32], password[32], dbname[32];
 //no pcvar, only called once at plugin start
 get_cvar_string("amx_sql_host",host,64);
 get_cvar_string("amx_sql_user",username,32);
 get_cvar_string("amx_sql_pass",password,32);
 get_cvar_string("amx_sql_db",dbname,32);
 
 SQL_SetAffinity("mysql");
 dbc = SQL_MakeDbTuple(host,username,password,dbname);
 
 formatex(g_Cache,1023,"CREATE TABLE IF NOT EXISTS `contime` ( \
    `authid` VARCHAR(36) NOT NULL,`name` VARCHAR(33) NOT NULL, \
    `time_all` INT NOT NULL, `time_last` TIMESTAMP NOT NULL \
    , PRIMARY KEY(`authid`))");
 SQL_ThreadQuery(dbc,"TableHandle",g_Cache);
}
public plugin_end()
{
 SQL_FreeHandle(dbc);
 
 return PLUGIN_HANDLED;
}
public client_authorized(id) {
 if(is_user_bot(id) || is_user_hltv(id)) return PLUGIN_CONTINUE;
 
 get_user_authid(id,g_authid[id],35);
 return PLUGIN_CONTINUE;
}
public client_putinserver(id) {
 if(is_user_bot(id) || is_user_hltv(id)) return PLUGIN_CONTINUE;
 
 new name[65];
 get_user_name(id,name,64);
 replace_all(name,64,"'","''") //avoiding sql errors with ' in name
 
 formatex(g_Cache,1023,"INSERT INTO `contime` (`authid`,`name`,`time_all`) \
  VALUES ('%s','%s',0) ON DUPLICATE KEY UPDATE name='%s' ; ", \
  g_authid[id],name,name);
 
 SQL_ThreadQuery(dbc,"QueryHandle",g_Cache);
 
 return PLUGIN_CONTINUE;
}
public client_disconnect(id) {
 new plr_time=get_user_time(id,1); //1=without connection time
 new plr_name[65];
 get_user_name(id,plr_name,64);
 replace_all(plr_name,64,"'","''"); //avoiding sql errors with ' in name
 
 formatex(g_Cache,1023,"UPDATE `contime` SET time_all=time_all+%i,name='%s' \
    WHERE authid='%s' ; ",plr_time,plr_name,g_authid[id]);
 
 SQL_ThreadQuery(dbc,"QueryHandle",g_Cache);
 
 return PLUGIN_CONTINUE;
}
public QueryHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
 // lots of error checking
 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("SQL Query failed.");
 if(Errcode)
  return log_amx("SQL Error on query: %s",Error);
 
 return PLUGIN_CONTINUE;
} 


table format:

authid = steamid
name = name (what else ^^), updated on connect / disconnect
time_all = connection time ever (seconds), updated on disconnect
time_last = last connect/disconnect on server (timestamp), updated on connect / disconnect

[mikes3ds]

Thank you Shorty!!!!

I will try it tonight...Mucho Love!!!




Going to add this code to a couple mods/plugins I have been working on! My sql saving system loses data...hope this fix's it!

[mikes3ds]

I would like to be able to view the ALLTIME in the server, and able to change it, would this be right?

PHP Code:

#include <amxmodx>
#include <amxmisc>
#include <sqlx>
#define Keys (1<<0)|(1<<1)|(1<<2)|(1<<3)|(1<<4) // Keys: 12345


new Handle:dbc;
new g_Cache[1024];
new g_authid[33][36];
new timeinserver[33]


public plugin_init() {
    register_plugin("SQL SAVING SYSTEM", "3.0", "");
    set_task(1.0,"sql_init")
    register_menucmd(register_menuid(""), Keys, "Pressed")
    register_concmd("betatest","Show")
    
    register_cvar("amx_test_sql_host","");
    register_cvar("amx_test_sql_user","");
    register_cvar("amx_test_sql_psw","");
    register_cvar("amx_test_sql_db","");
    
    
}
public sql_init() {
    new host[64], username[32], password[32], dbname[32];
    //no pcvar, only called once at plugin start
    

    get_cvar_string("amx_test_sql_host",host,64);
    get_cvar_string("amx_test_sql_user",username,32);
    get_cvar_string("amx_test_sql_psw",password,32);
    get_cvar_string("amx_test_sql_db",dbname,32);
    
    SQL_SetAffinity("mysql");
    dbc = SQL_MakeDbTuple(host,username,password,dbname);
    
    formatex(g_Cache,1023,"CREATE TABLE IF NOT EXISTS `contime` ( \
    `authid` VARCHAR(36) NOT NULL,`name` VARCHAR(33) NOT NULL, \
    `time_all` INT NOT NULL, `time_last` TIMESTAMP NOT NULL \
    , PRIMARY KEY(`authid`))");
    SQL_ThreadQuery(dbc,"TableHandle",g_Cache);
}

public TableHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    // lots of error checking
    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("SQL Table Query failed.");
    
    if(Errcode)
        return log_amx("SQL Error on table query: %s",Error);
    
    return PLUGIN_CONTINUE;
}

public plugin_end()
{
    SQL_FreeHandle(dbc);
    
    return PLUGIN_HANDLED;
}

public client_authorized(id) {
    if(is_user_bot(id) || is_user_hltv(id)) return PLUGIN_CONTINUE;
    
    get_user_authid(id,g_authid[id],35);
    return PLUGIN_CONTINUE;
}
public client_putinserver(id) {
    if(is_user_bot(id) || is_user_hltv(id)) return PLUGIN_CONTINUE;
    
    /*new name[65];
    get_user_name(id,name,64);
    replace_all(name,64,"'","''") //avoiding sql errors with ' in name
    
    formatex(g_Cache,1023,"INSERT INTO `contime` (`authid`,`name`,`time_all`) \ VALUES ('%s','%s',0) ON DUPLICATE KEY UPDATE name='%s' ; ", g_authid[id],name,name);
    SQL_ThreadQuery(dbc,"QueryHandle",g_Cache);*/
    
    formatex(g_Cache,511,"SELECT time_all FROM contime WHERE authid='%s'",g_authid[id])
    
    
    new index[3];
    formatex(index,2,"%d",id)
    SQL_ThreadQuery(dbc,"SelectHandle",g_Cache,index,3)
    
    
    
    return PLUGIN_CONTINUE;
}



public client_disconnect(id) {
    new plr_time=timeinserver[id]+get_user_time(id,1); //1=without connection time
    new plr_name[65];
    get_user_name(id,plr_name,64);
    replace_all(plr_name,64,"'","''"); //avoiding sql errors with ' in name
    
    formatex(g_Cache,1023,"UPDATE `contime` SET time_all=%i,name='%s' \
    WHERE authid='%s' ; ",plr_time,plr_name,g_authid[id]);
    
    SQL_ThreadQuery(dbc,"QueryHandle",g_Cache);
    
    return PLUGIN_CONTINUE;
}
public QueryHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    // lots of error checking
    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);     
    if(Errcode)
        return log_amx("SQL Error on query: %s",Error);
    
    return PLUGIN_CONTINUE;
}  

public SelectHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize) 
{     
    //Check for errors and then process loading from table queries
    new id=str_to_num(Data)
    
    if(FailState)     
    {         
        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);                 
        return 0;     
    }
    
    if (!SQL_MoreResults(Query)) // No more results - User not found, create them a blank entry in the table. and zero their variables
    {
        new name[65];
        get_user_name(id,name,64);
        replace_all(name,64,"'","''") //avoiding sql errors with ' in name
        log_amx("SQL HERE");
        formatex(g_Cache,1023,"INSERT INTO `contime` (`authid`,`name`,`time_all`) \
        VALUES ('%s','%s',0) ON DUPLICATE KEY UPDATE name='%s' ; ", 
        g_authid[id],name,name)
        SQL_ThreadQuery(dbc,"QueryHandle",g_Cache);
        return PLUGIN_CONTINUE;
    }
    
    //Player must have been found. Loop through and load the columns into the global vars
    
    timeinserver[id] = SQL_ReadResult(Query,0)  
    client_print(id, print_chat, "Load Done")
    
    return PLUGIN_CONTINUE 
}




public Show(id) {
    show_menu(id, Keys, "\rTEST SQL  CREDIT SYSTEM^n\y1. Add a test Credit^n2. Delete a test Credit^n3. Save test Credits^n4. Load test Credits^n5. CLOSE and View credits^n", -1, "") // Display menu
}

public Pressed(id, key) {
    /* Menu:
    * TEST SQL  CREDIT SYSTEM
    * 1. Add a test Credit
    * 2. Delete a test Credit
    * 3. Save test Credits
    * 4. Load test Credits
    * 5. Reset test  Credits
    */

    switch (key) {
        case 0: { // 1
            timeinserver[id] = timeinserver[id] + 300
            client_print(id, print_console, "Added test 1 credit ")
            show_the_credits(id)
            Show(id)
            return PLUGIN_CONTINUE;
        }
        case 1: { // 2
            client_print(id, print_console, "Deleted test 1 credit ")
            timeinserver[id] = timeinserver[id] - 300
            show_the_credits(id)
            Show(id)
            return PLUGIN_CONTINUE;
        }
        case 2: { // 3
                client_print(id, print_chat, "Save Start")
                new plr_time=timeinserver[id]+get_user_time(id,1); //1=without connection time
                new plr_name[65];
                get_user_name(id,plr_name,64);
                replace_all(plr_name,64,"'","''"); //avoiding sql errors with ' in name
                
                formatex(g_Cache,1023,"UPDATE `contime` SET time_all=%i,name='%s' \
                WHERE authid='%s' ; ",plr_time,plr_name,g_authid[id]);
                
                SQL_ThreadQuery(dbc,"QueryHandle",g_Cache);
                client_print(id, print_chat, "Save Done")
                
                show_the_credits(id)
                client_print(id, print_chat, "SAVE End Credits %i Total Time %i",(get_user_time(id,1) + timeinserver[id])/300,timeinserver[id])
                Show(id)
                return PLUGIN_CONTINUE;
        }
        case 3: { // 4
                client_print(id, print_chat, "Load Start")
                formatex(g_Cache,511,"SELECT time_all FROM contime WHERE authid='%s'",g_authid[id])
            
                new index[3];
                formatex(index,2,"%d",id)
                SQL_ThreadQuery(dbc,"SelectHandle",g_Cache,index,3)
                client_print(id, print_chat, "Load End Credits %i Total Time %i",(get_user_time(id,1) + timeinserver[id])/300,timeinserver[id])
                show_the_credits(id)
                Show(id)
                return PLUGIN_CONTINUE;
        }
        case 4: { // 5
            show_the_credits(id)
            return PLUGIN_CONTINUE;
        }
    }
    return PLUGIN_CONTINUE;
}

public show_the_credits(id){
    set_hudmessage(255, 0, 0, 0.30, 0.0, 0, 6.0, 12.0)
    show_hudmessage(id, "Credits %i Total Time %i",(get_user_time(id,1) + timeinserver[id])/300,timeinserver[id])
    
} 


[mikes3ds]

The new code, I have coded seems to be working fine....

However I am getting this error...

L 05/01/2008 - 23:39:54: [MySQL] Invalid handle: 0
L 05/01/2008 - 23:39:54: [AMXX] Displaying debug trace (plugin "archtest.amxx")
L 05/01/2008 - 23:39:54: [AMXX] Run time error 10: native error (native "SQL_FreeHandle")
L 05/01/2008 - 23:39:54: [AMXX]    [0] archtest.sma::plugin_end (line 64)

[|PJ| Shorty]

if you use amxx 1.8.0, update to the latest nightly build