Error in External MySQL database config - bad key

[jacksop]

Hey!
So I have connected my server to an external mysql database using the mysqloo9 module and have populated it with information such as player steamids.
I also want to log the ULX rank/user group on this database to help keep track, however I have run into an issue with the code.

Here is the code:

Code: [Select]

function DBULXRank (ply, currentulxrank)
local query2 = DB_RP:query( "SELECT ulx_rank FROM playerinformation WHERE steam_id = '" .. ply:SteamID() .. "';") -- checking current data

query2.onSuccess = function()

if (#query2:getData() != ply:GetUserGroup()) then -- if the data does not equal the in-game usergroup
local query = DB_RP:query("UPDATE playerinformation SET ulx_rank = '".. currentulxrank .."' WHERE steam_id = '" .. ply:SteamID() .. "';") --update the database column called ulx_rank
query.onSuccess = function()
MsgC( Color (0, 0, 255), "[SQL] The new player's rank " .. currentulxrank .. " was logged! \n")
end
query.onError = function(db, err)
MsgC( Color (255, 0, 0), "[SQL] (Add Player) - Error: ", err)
end
query:start()
end
end
query2:start()
end

hook.Add("PlayerInitialSpawn", "UpdateULXrole", function(ply)
DBULXRank(ply:GetUserGroup())
end)

What I want this function to do is when the player spawns in/connects, i want to check the database if the ULX rank is the correct one that is in-game, if not then update it.

The error is:

attempt to index a string value with bad key ('SteamID' is not part of the string library)

How can I fix this? Let me know if you need more information.
Please keep in mind that I am new to lua so if theres an obvious problem please let me know how to solve it!
Thanks in advance

[jacksop]

Based on some testing I did, the cause of this problem is when I try to retrieve and concatenate ply:SteamID.
I am assuming the error: "attempt to index a string value with bad key" means I cannot concatenate this value as a string but rather as a variable.
So i changed my query line to this:

Code: [Select]

local query2 = DB_RP:query( "SELECT ulx_rank FROM playerinformation WHERE steam_id = '" .. ply:SteamID() "';")         -- no dots after ply:SteamID()

Doing so gives me this error instead: attempt to call a string value

So I guess my ultimate question here is.. how does lua respond to what value the ply:SteamID() is? is a treated as a variable? string?
Am I on the right track here or just going off on pointless tangents? 

[Timmy]

Code: [Select]

attempt to index a string value with bad key
The error is a little obscure. It means that you are trying to call a nonexistent method on a string.

Take a look at the example below.

Code: [Select]

local nickname = "Timmy"
nickname:SteamID() -- Error: attempt to index a string value with bad key ('SteamID' is not part of the string library)
The :SteamID() only works on Players, not strings. Your ply is actually a string.

Have a look at the hook that calls DBULXRank. Make sure you give it all the necessary arguments.

[jacksop]

Hey thanks for the reply!

Ive slightly edited the hook to what I want it and changed the concatenation for the string in the function.
The hook is now this:

Code: [Select]

hook.Add("PlayerDisconnected", "UpdateULXrole", function(ply)
DBULXRank(ply, ply:GetUserGroup())
end)

The bad key error seems to be fixed! However, now i get this error:

Code: [Select]

lua/autorun/server/sv_database.lua:100: Tried to use a NULL entity!
stack traceback:
        [C]: at 0x647e9220
        [C]: in function 'SteamID'
        lua/autorun/server/sv_database.lua:100: in function <lua/autorun/server/sv_database.lua:97>
        [C]: in function 'fn'
        addons/ulib-v2_63/lua/ulib/shared/hook.lua:109: in function <addons/ulib-v2_63/lua/ulib/shared/hook.lua:92>

FYI - line 100 is the local query = DB_RP:query(UPDATE ....) line so it looks like we have progressed...

Seems like a generic error? Why is this one happening?? Thanks for your help 

[Timmy]

Lua has a built-in garbage collector. It frees up memory by purging data that that is no longer needed.

Data associated with a disconnected player will get purged soon after they disconnect. The Player object will become NULL.

Player can be accessed inside PlayerDisconnected hook

Code: [Select]

hook.Add("PlayerDisconnected", "Instant", function(ply)
    print("Goodbye " .. ply:Nick())
end)
But not if we introduce a 1 sec. delay

Code: [Select]

hook.Add("PlayerDisconnected", "DelayedBroken", function(ply)
    timer.Simple(1, function()
        print("Goodbye " .. ply:Nick()) -- Tried to use a NULL entity!
    end)
end)
Whether we wait for a 1 sec. timer or for the answer to a database query doesn't matter. The Player object will be gone.

Unless the data is stored in variables before the delay

Code: [Select]

hook.Add("PlayerDisconnected", "Delayed", function(ply)
    local nick = ply:Nick()
    timer.Simple(1, function()
        print("Goodbye " .. nick)
    end)
end)
Store the data you need in variables instead. You can still access those after a delay.

Can't I just store the entire Player object in a variable?
Variables can store numbers, strings, booleans and nil values. You can not store objects in variables, only references to those objects.

Variables can only store references to objects

Code: [Select]

hook.Add("PlayerDisconnected", "StorePlayer", function(ply)
    local dude = ply
    timer.Simple(1, function()
        print("Goodbye " .. dude:Nick()) -- Tried to use a NULL entity!
    end)
end)

[jacksop]

WOW! Thank you so much for the in-depth explanation. IT ALL MAKES SENSE NOW!

The server ran the function successfully. It all looks good!!

Thank you!


EDIT: 1 last thing... This function updates the data in the table every time a player disconnects. This means the if statement isn't working.

Code: [Select]

if (#query2:getData() != currentulxrank) then

After trying to print #query2:getData() using the following code I think I have nailed it down to the fact the function does not return a value other than nil.

Code: [Select]


local query2 = DB_SWRPDB:query( "SELECT ulx_role FROM player_information WHERE steam_id = '" .. plysteamid .. "';")
print(#query2:getData())


Am I handling retrieving the contents of a table wrong? or is there something wrong with the function?

Here is the explanation of the function using FredyH's MYSQLOO manual (https://github.com/FredyH/MySQLOO)

Code: [Select]

Query:getData()
-- Returns [Table]
-- Gets the data the query returned from the server
-- Format: { row1, row2, row3, ... }
-- Row format: { field_name = field_value } or {first_field, second_field, ...} if OPTION_NUMERIC_FIELDS is enabled


This function would come in handy. Any ideas?

[Timmy]

The query looks fine.

That if-statement looks odd.

There's a # in front of query2. The length operator (#) is used to get the length of a table or string.

Note that the GetData function returns a table of result rows. It takes a few more steps to access the rank.

Try to debug the select query a little further. Perhaps you could try something like this (untested)?

Code: [Select]

query2.onSuccess = function()
    local results = query2:getData()
    local result = results[1] -- Get first result

    if result then
        PrintTable(result)

        if result.ulx_rank ~= currentulxrank then
            print("TODO: Update user rank from " .. result.ulx_rank .. " to " .. currentulxrank)
        else
            print("No update required")
        end
    else
        print("No results for this SteamID")
    end
end

[jacksop]

Oh ok thanks for clearing that up! Realised I used the wrong negation operator for lua!

After some playing around with the code you gave me I believe its working now!! THANKS!

I have another one of these functions but I don't understand why it isn't working. They are basically identical despite the job parameters. Just wanna keep track of this too.

Code: [Select]

function DBSaveJobID (currentteam, steamid, nickname)
local query1 = DB_RP:query( "SELECT job_id FROM player_information WHERE steam_id = '" .. steamid .. "';")

query1.onSuccess = function()
local results = query1:getData()
local value = results[1]
if (value.job_id ~= currentteam) then
local query2 = DB_RP:query("UPDATE player_information SET job_id = '".. currentteam .."' WHERE steam_id = '".. steamid.."';")
query2.onSuccess = function()
MsgC( Color (0, 0, 255), "[SQL] The players job was logged \n")
end
query2.onError = function(db, err)
MsgC( Color (255, 0, 0), "[SQL] (Job Update) - Error: ", err)
end
query2:start()
end
end
query1:start()
end

Thanks so much for helping this far...

[Timmy]

Garry made some modifications to the Lua engine so you can actually use != to negate in GLua. It's not valid in regular Lua though.

I don't see any obvious mistakes in the last snippet you posted.

If the UPDATE query does not run, keep track of the value in currentteam. Does it update after you switch jobs? Perhaps you are confusing teams and jobs?

There will also be a Lua error if query1 returns no results. Make sure value is not nil before accessing job_id. Something like this will work:
if (value and value.job_id ~= currentteam) then

[jacksop]

Ahhh right. That's handy.

I've tested what you said and changed the if statement to include the if value and value.job_id.
What happens is the query2 runs regardless of the if statement. The way I know this is everytime a player disconnects, the console prints:

[SQL] The players job was logged

...and the text for the UpdateULXRank function doesn't - therefore that if statement works but the save job if statement does not.

The function does replace the value in the database table under job_id to the correct team/job (I think job and team are related somehow...) - if I change jobs before I disconnect from the server, the number in the job_id column in the database changes. Also, if I print value.job_id it is the correct number that I am expecting.

This is really confusing because both functions should act the same.... weird... 

[Timmy]

Oh, I bet MySQLOO returns the ID as a string... Cast it to a number before comparing.

if (value and tonumber(value.job_id) ~= currentteam) then

If you ever need to debug something like this, you can get the data type of a value with type().

print(type(my_variable))

[jacksop]

Awesome. I believe that worked! Nice work thinking of that. (I probs would never have)

Thanks for your help through all this. Would have been stuck for countless hours otherwise. I couldn't find any other forum post similar to mine so I started this one so thank you.
Funny how far we got from the actual problem that started this thread.

Thank you again!