Help with Threaded SQL

crazydog · **Author**

I have this function

Code:

public Action:UpdateTimes(Handle:timer){
    
    new enabled = GetConVarInt(sm_ptt_enabled)
    if (enabled == 1){
        if (!rowExists){
            return Plugin_Stop
        }else{                  
            new String:steamid[32], String:clientName[64], String:getTime[128], String:getStored[128], String:findActive[128], String:query[128], bool:exists, tracking, String:error[128]
            new Handle:db = SQL_Connect("default", true, error, sizeof(error));
            for (new i = 1; i < MaxClients; i++){
                if (IsClientInGame(i)){
                    new client = i
                    if (IsFakeClient(client)){
                        return Plugin_Continue
                    }
                    GetClientName(client, clientName, sizeof(clientName))
                    GetClientAuthString(client, steamid, sizeof(steamid))
                    new Float:TimePlayed = GetClientTime(client)
                    new TotalTime, TimeStored           
                    if (db == INVALID_HANDLE){
                        LogError("[Player Time Tracker] ERROR: Could not connect to SQL database! (%s)", error);
                        CloseHandle(db)
                        return Plugin_Continue
                    }
                    
                    Format(getTime, sizeof(getTime), "SELECT TimePlayed FROM TimeTracker WHERE steamid = '%s'", steamid)
                    Format(getStored, sizeof(getStored), "SELECT StoredTime FROM TimeTracker WHERE steamid = '%s'", steamid)
                    Format(findActive, sizeof(findActive), "SELECT tracking FROM TimeTracker WHERE steamid = '%s'", steamid)
                    new Handle:hQuery;
                    if ((hQuery = SQL_Query(db, getTime)) == INVALID_HANDLE){
                        SQL_GetError(db, error, sizeof(error))
                        LogError("Failed to query (first) (error: %s)", error)
                        CloseHandle(db)
                        return Plugin_Continue
                    }
                    else
                    {
                        new Handle:hQuery2;
                        if ((hQuery2 = SQL_Query(db, getStored)) == INVALID_HANDLE){
                            CloseHandle(db)
                            return Plugin_Continue
                        }else{
                            exists = SQL_FetchRow(hQuery)
                            if (exists == true){
                                new Handle:activeQuery = SQL_Query(db, findActive)
                                if (activeQuery == INVALID_HANDLE){
                                    SQL_GetError(db, error, sizeof(error))
                                    LogError("Failed to query (second) (error: %s)", error)
                                    CloseHandle(db)
                                    return Plugin_Continue
                                }else{
                                    SQL_FetchRow(activeQuery)
                                    SQL_FetchRow(hQuery2)
                                    TotalTime = SQL_FetchInt(hQuery, 0)
                                    TimeStored = SQL_FetchInt(hQuery2, 0)
                                    tracking = SQL_FetchInt(activeQuery, 0)
                                    CloseHandle(hQuery);
                                }
                            }
                        }
                    }
                    if (exists == true){
                        if (tracking == 1){
                            new iTimePlayed = RoundToFloor(TimePlayed)
                            TotalTime = TotalTime+iTimePlayed-TimeStored
                            Format(query, sizeof(query), "UPDATE TimeTracker SET TimePlayed='%i', StoredTime='%i' WHERE steamid ='%s'", TotalTime, iTimePlayed, steamid);
                            
                            new Handle:hQuery2;
                            if ((hQuery2 = SQL_Query(db, query)) == INVALID_HANDLE){
                                LogError("[Player Time Tracker] ERROR: Problem with the SQL query! (%s)", query);
                                return Plugin_Continue
                            }
                            else
                            {
                                CloseHandle(hQuery2);
                            }
                        }
                        CloseHandle(db);
                    }
                }
            }
        }
        return Plugin_Continue;
    }
    return Plugin_Continue;
}

That is executed often based on a timer.

I was trying to get it set up using threaded SQL connections so that it wouldn't make the server pause every time it runs, but I just got very lost.

I'm guessing it's easier if I started with threaded SQL from the start, because I'm having a lot of trouble converting this over...can anyone guide me to where I should be going with it?

Note: Please ignore my bad coding conventions...I'm still getting my Pawn-legs.

MoggieX · 07-21-2009 , 10:14 Re: Help with Threaded SQL

heya see the code for http://forums.alliedmods.net/showthread.php?p=754041

Matt

crazydog · 07-21-2009 , 13:02 Re: Help with Threaded SQL

Blah. I fixed this. Added "any:client" to the last parameter of the callbacks.

Mkay...I think I have it all right.

These lines are throwing off:

"Error 100: Function Prototypes do not match"

Code:

SQL_TQuery(db, GetTheTime, getTime)
SQL_TQuery(db, GetStored, getStored)
SQL_TQuery(db, FindActive, findActive)
SQL_TQuery(db, updateTime, query)

db is defined as:

Code:

new Handle:db = SQL_Connect("default", true, error, sizeof(error));

each callback is basically the same. Here's one of them:

Code:

public GetTheTime(Handle:owner, Handle:hQuery, const String:error[]){
    if (hQuery != INVALID_HANDLE){
        if(SQL_GetRowCount(hQuery) > 0){
            SQL_FetchRow(hQuery)
            TotalTime = SQL_FetchInt(hQuery, 0)
        }
    }
    CloseHandle(hQuery)
}

and the query strings are just strings...what prototypes are being mismatched?

bl4nk · 07-21-2009 , 13:24 Re: Help with Threaded SQL

You're still missing the "any:data" part at the end of the callback header.

crazydog · 07-21-2009 , 13:43 Re: Help with Threaded SQL

Yeah, I got that.

New problem, this time with race conditions.

Code:

public Action:UpdateTimes(Handle:timer){
    
    new enabled = GetConVarInt(sm_ptt_enabled)
    if (enabled == 1){
        if (!rowExists){
            return Plugin_Stop
        }else{                  
            new String:steamid[32], String:clientName[64], String:getTime[128], String:getStored[128], String:findActive[128], String:query[128]
            for (new i = 1; i < MaxClients; i++){
                if (IsClientInGame(i)){
                    new client = i
                    if (IsFakeClient(client)){
                        return Plugin_Continue
                    }
                    GetClientName(client, clientName, sizeof(clientName))
                    GetClientAuthString(client, steamid, sizeof(steamid))
                    new Float:TimePlayed = GetClientTime(client)        
                    
                    Format(getTime, sizeof(getTime), "SELECT TimePlayed FROM TimeTracker WHERE steamid = '%s'", steamid)
                    Format(getStored, sizeof(getStored), "SELECT StoredTime FROM TimeTracker WHERE steamid = '%s'", steamid)
                    Format(findActive, sizeof(findActive), "SELECT tracking FROM TimeTracker WHERE steamid = '%s'", steamid)
                    SQL_TQuery(hDatabase, GetTheTime, getTime)
                    SQL_TQuery(hDatabase, GetStored, getStored)
                    SQL_TQuery(hDatabase, FindActive, findActive)
                    
                    if (tracking == 1){
                        new iTimePlayed = RoundToFloor(TimePlayed)
                        TotalTime = TotalTime+iTimePlayed-TimeStored
                        Format(query, sizeof(query), "UPDATE TimeTracker SET TimePlayed='%i', StoredTime='%i' WHERE steamid ='%s'", TotalTime, iTimePlayed, steamid);
                        SQL_TQuery(hDatabase, updateTime, query)
                        LogError("Query: %s", query)
                    }
                }
            }
        }
    }
    return Plugin_Continue;
}

public GetTheTime(Handle:owner, Handle:hQuery, const String:error[], any:client){
    if (hQuery != INVALID_HANDLE){
        if(SQL_GetRowCount(hQuery) > 0){
            SQL_FetchRow(hQuery)
            TotalTime = SQL_FetchInt(hQuery, 0)
        }
    }
    CloseHandle(hQuery)
}

public GetStored(Handle:owner, Handle:hQuery, const String:error[], any:client){
    if (hQuery != INVALID_HANDLE){
        if(SQL_GetRowCount(hQuery) > 0){
            SQL_FetchRow(hQuery)
            TimeStored = SQL_FetchInt(hQuery, 0)
        }
    }
    CloseHandle(hQuery)
}

public FindActive(Handle:owner, Handle:hQuery, const String:error[], any:client){
    if (hQuery != INVALID_HANDLE){
        if(SQL_GetRowCount(hQuery) > 0){
            SQL_FetchRow(hQuery)
            tracking = SQL_FetchInt(hQuery, 0)
        }
    }
    CloseHandle(hQuery)
}

public updateTime(Handle:owner, Handle:hQuery, const String:error[], any:client){
    if (hQuery != INVALID_HANDLE){
    }
    CloseHandle(hQuery)
}

public DBConnect(Handle:owner, Handle:hndl, const String:error[], any:data){
    if (hndl == INVALID_HANDLE){
        LogError("Error! %s", error)
        return
    }
    
    hDatabase = hndl
}

The variables TotalTime, TimePlayed, and tracking are changing to different values before the update query is run.

bl4nk · 07-21-2009 , 15:44 Re: Help with Threaded SQL

You're going to have to move them to the SQL callbacks. SQL queries take more time than it takes to run through the rest of the function.

crazydog · 07-21-2009 , 23:41 Re: Help with Threaded SQL

Alright...here's what I have now

Code:

public Action:UpdateTimes(Handle:timer){
    tracking = 0
    new enabled = GetConVarInt(sm_ptt_enabled)
    if (enabled == 1){
        if (!rowExists){
            return Plugin_Stop
        }else{                  
            new String:steamid[32], String:clientName[64]
            for (new i = 1; i < MaxClients; i++){
                if (IsClientInGame(i)){
                    new client = i
                    if (IsFakeClient(client)){
                        return Plugin_Continue
                    }
                    GetClientName(client, clientName, sizeof(clientName))
                    GetClientAuthString(client, steamid, sizeof(steamid))
                    
                    Format(getTimeT, sizeof(getTimeT), "SELECT TimePlayed FROM TimeTracker WHERE steamid = '%s'", steamid)
                    SQL_TQuery(hDatabase, GetTheTime, getTimeT, client)
                }
            }
        }
    }
    return Plugin_Continue;
}

public GetTheTime(Handle:owner, Handle:hQuery, const String:error[], any:client){
    if (hQuery != INVALID_HANDLE){
        if(SQL_GetRowCount(hQuery) > 0){
            SQL_FetchRow(hQuery)
            TotalTime = SQL_FetchInt(hQuery, 0)
            new String:steamid[32]
            GetClientAuthString(client, steamid, sizeof(steamid))
            Format(getStoredT, sizeof(getStoredT), "SELECT StoredTime FROM TimeTracker WHERE steamid = '%s'", steamid)
            SQL_TQuery(hDatabase, GetStored, getStoredT, client)
            LogError("client is %L, TotalTime is %i", client, TotalTime)
            LogError("Next query is %s", getStoredT)
        }
    }
    CloseHandle(hQuery)
}

public GetStored(Handle:owner, Handle:hQuery, const String:error[], any:client){
    if (hQuery != INVALID_HANDLE){
        if(SQL_GetRowCount(hQuery) > 0){
            SQL_FetchRow(hQuery)
            TimeStored = SQL_FetchInt(hQuery, 0)
            new String:steamid[32]
            GetClientAuthString(client, steamid, sizeof(steamid))
            Format(findActiveT, sizeof(findActiveT), "SELECT tracking FROM TimeTracker WHERE steamid = '%s'", steamid)
            SQL_TQuery(hDatabase, FindActive, findActiveT, client)
            LogError("client is %L, TimeStored is %i", client, TimeStored)
            LogError("Next query is %s", findActiveT)
        }
    }
    CloseHandle(hQuery)
}

public FindActive(Handle:owner, Handle:hQuery, const String:error[], any:client){
    if (hQuery != INVALID_HANDLE){
        if(SQL_GetRowCount(hQuery) > 0){
            SQL_FetchRow(hQuery)
            tracking = SQL_FetchInt(hQuery, 0)
        }
        if (tracking == 1){
            new String:query[128], String:steamid[32]
            GetClientAuthString(client, steamid, sizeof(steamid))
            TimePlayed = GetClientTime(client)
            new iTimePlayed = RoundToFloor(TimePlayed)
            TotalTime = TotalTime+iTimePlayed-TimeStored
            Format(query, sizeof(query), "UPDATE TimeTracker SET TimePlayed='%i', StoredTime='%i' WHERE steamid ='%s'", TotalTime, iTimePlayed, steamid);
            SQL_TQuery(hDatabase, updateTime, query)
            LogError("client is %L, tracking is %i", client, tracking)
        }       
    }
    CloseHandle(hQuery)
}

public updateTime(Handle:owner, Handle:hQuery, const String:error[], any:client){
    if (hQuery != INVALID_HANDLE){
    }
    CloseHandle(hQuery)
}

public DBConnect(Handle:owner, Handle:hndl, const String:error[], any:data){
    if (hndl == INVALID_HANDLE){
        LogError("Error! %s", error)
        return
    }
    
    hDatabase = hndl
}

It works fine when there's only one client, but as soon as there's multiple, the variables start intermixing. What should I do?