[Help] Use mysql instead Get & Set accountdata

SinaAmp · February 3, 2022

hello guys

i working on hunger and thirst system. i can save and get data with get and setaccountdata but when i try to change the saving way to mysql i got errors

here is my script:

Spoiler

addEventHandler ("onPlayerLogin",root,function(_,Acc)
local MainHunger2 = getAccountData (Acc,"AmpHunger")  --i want to change this
local MainThirst2 = getAccountData (Acc,"AmpThirst")
        if MainHunger2 == false then
        setElementData (source,"hunger",100)
        else
        setElementData (source,"hunger",MainHunger2)
        end
            if MainThirst2 == false then 
            setElementData (source,"Thirst",100)
            else
            setElementData (source,"Thirst",MainThirst2)
            end 
end)

function saveAccountData ( account )
    if isGuestAccount ( account ) then
        return false
    end
    Player = getAccountPlayer (account)
Hungers = getElementData (Player,"hunger")
Thirsts = getElementData (Player,"Thirst")
setAccountData (account,"AmpHunger",Hungers)
setAccountData (account,"AmpThirst",Thirsts)
end

addEventHandler ( 'onPlayerQuit', root, function ( )
    local acc = getPlayerAccount ( source )
    saveAccountData ( acc )
end )


addEventHandler( "onResourceStart", getResourceRootElement( getThisResource() ), function( )
 for _, plr in pairs( getElementsByType( "player" ) ) do 
 local Acc = getPlayerAccount (plr)
    if not isGuestAccount (Acc) then
    local MainHunger = getAccountData (Acc,"AmpHunger") -- and this
    local MainThirst = getAccountData (Acc,"AmpThirst")
        if MainHunger == false then
        setElementData (plr,"hunger",100)
        else
        setElementData (plr,"hunger",FomeAcc)
        end
            if MainThirst == false then
            setElementData (plr,"thirst",100) 
            else
            setElementData (plr,"thirst",SedeAcc)
            end 
    end    
 end
 end)
 
 
 addEventHandler ("onResourceStop",getResourceRootElement( getThisResource()),function()
for index,players in ipairs(getElementsByType("player")) do 
 local acc = getPlayerAccount ( players )
 saveAccountData ( acc )
end
end)

also i have clinet side script but that not needed

Edited February 3, 2022 by SinaAmp

βurak · February 4, 2022

You can use the previous examples for this, add 2 more rows to the database and get the information with select queries. You can use the update command to change it.

SinaAmp · February 4, 2022

i edited the code but i got this two errors:

1- attempt to index field '?' (a nil value)

2- Bad argument @ 'getAccountName' [Expected account at argument 1, got nil]

please review my code @Burak5312

Spoiler

addEventHandler ("onPlayerLogin",root,function(_,Acc)
    local AccName = getAccountName(Acc)
    AccData = dbPoll(dbQuery(db,"SELECT Hunger AND Thirst FROM stats WHERE Account=?", AccName), -1)
            if AccData[1].Hunger == false then
            setElementData (source,"hunger",100)
            else
            setElementData (source,"hunger",AccData[1].Hunger)
            end
                if AccData[2].Thirst == false then --error here
                setElementData (source,"Thirst",100)
                else
                setElementData (source,"Thirst",AccData[2].Thirst)
                end 
    end)
    
    function saveAccountData (account )
        if isGuestAccount ( account ) then
            return false
        end
        Player = getAccountPlayer (account)
        local AccName = getAccountName(player) -- and error here
    Hungers = getElementData (Player,"hunger")
    Thirsts = getElementData (Player,"Thirst")
    dbExec(db,"UPDATE stats SET Hunger=? AND Thirst=? WHERE Account=?",Hungers, Thirsts, AccName)
    end
    
    addEventHandler ( 'onPlayerQuit', root, function ( )
        local acc = getPlayerAccount ( source )
        saveAccountData ( acc )
    end )
    
    
    addEventHandler( "onResourceStart", getResourceRootElement( getThisResource() ), function( )
     for _, plr in pairs( getElementsByType( "player" ) ) do 
     local Acc = getPlayerAccount (plr)
        if not isGuestAccount (Acc) then
            AccData = dbPoll(dbQuery(db,"SELECT Hunger AND Thirst FROM stats WHERE Account=?", AccName), -1)
            if AccData[1].Hunger == false then
            setElementData (plr,"hunger",100)
            else
            setElementData (plr,"hunger",AccData[1].Hunger)
            end
                if AccData[2].Thirst == false then
                setElementData (plr,"thirst",100) 
                else
                setElementData (plr,"thirst",AccData[2].Thirst)
                end 
        end    
     end
     end)
     
     
     addEventHandler ("onResourceStop",getResourceRootElement( getThisResource()),function()
    for index,players in ipairs(getElementsByType("player")) do 
     local acc = getPlayerAccount ( players )
     saveAccountData ( acc )
    end
    end)

i commented the error line

βurak · February 4, 2022

can you try this

addEventHandler ("onPlayerLogin",root,function(_,Acc)
    local AccName = getAccountName(Acc)
    local AccData = dbPoll(dbQuery(db,"SELECT Hunger,Thirst FROM stats WHERE Account=? LIMIT 1", AccName), -1)
        if(AccData) then
           if(#AccData > 0) then

              for _,row in ipairs(AccData) do
                 setElementData (source,"hunger",row["Hunger"])
                 setElementData(source, "Thirst", row["Thirst"])
                 break
              end

           end
        end
    end)
    
    function loadAccountData(player)
    local playerAccount = getPlayerAccount(player) 
    if(playerAccount) then
       if(isGuestAccount(playerAccount)) then return end
    end    
    local AccName = getAccountName(playerAccount)
    local AccData = dbPoll(dbQuery(db,"SELECT Hunger,Thirst FROM stats WHERE Account=? LIMIT 1", AccName), -1)
        if(AccData) then
           if(#AccData > 0) then

            for _,row in ipairs(AccData) do
                setElementData (player,"hunger",row["Hunger"])
                setElementData(player, "Thirst", row["Thirst"])
                break
            end

        end
    end

    function saveAccountData (player)
    local playerAccount = getPlayerAccount(player) 
    if(playerAccount) then
       if(isGuestAccount(playerAccount)) then return end
    end
    local AccName = getAccountName(playerAccount)

    local Hungers = getElementData (player,"hunger")
    local Thirsts = getElementData (player,"Thirst")
    dbExec(db,"UPDATE stats SET Hunger=?, Thirst=? WHERE Account=?",Hungers, Thirsts, AccName)
    end
    
    addEventHandler ( 'onPlayerQuit', root, function ( )
        saveAccountData(source)
    end )
    
    
    addEventHandler( "onResourceStart", resourceRoot, function( )
     for _, plr in pairs( getElementsByType("player")) do
           loadAccountData(plr)
        end
     end)
     
     
     addEventHandler ("onResourceStop", resourceRoot, function()
    for index,players in ipairs(getElementsByType("player")) do 
       saveAccountData(players)
    end
    end)

Edited February 4, 2022 by Burak5312

SinaAmp · February 4, 2022

i got this error:

sourceS.lua:59: Bad argument @ 'getAccountName' [Expected account at argument 1, got nil]

βurak · February 4, 2022

edited code

SinaAmp · February 4, 2022

i got expected end at line 87 for line 37 after this error added that missing end and i got another error dbexec failed

and i got another error in my client side code attemt to compare nil with number

my client code:

function checkHunger()
	local hunger = tonumber(getElementData(localPlayer, "hunger"))
	if hunger and hunger > 0 then
		setElementData(localPlayer, "hunger", hunger - 1)
	end
	if hunger <= 20 then
		outputChatBox("You need to feed as fast as possible.", 255, 0, 0)
		playSoundFrontEnd(40)
	end
	if hunger == 0 then	
		if not isPedDead(localPlayer) then
			local hp = getElementHealth(localPlayer)
			setElementHealth(localPlayer, hp - 30)
		end
	end
end
setTimer(checkHunger, 60000, 0) -- configure a time

function onClientPlayerSpawn()
	setElementData(source, "hunger", 100)
end
addEventHandler("onClientPlayerSpawn", root, onClientPlayerSpawn)

function checkThirst()
	local Thirst = tonumber(getElementData(localPlayer, "Thirst"))
	if Thirst and Thirst > 0 then
		setElementData(localPlayer, "Thirst", Thirst - 1)
	end
	if Thirst <= 20 then
		outputChatBox("You need to drink something as fast as possible.", 255, 0, 0)
		playSoundFrontEnd(40)
	end
	if Thirst == 0 then	
		if not isPedDead(localPlayer) then
			local hp = getElementHealth(localPlayer)
			setElementHealth(localPlayer, hp - 30)
		end
	end
end
setTimer(checkThirst, 60000, 0) -- configure a time

function onClientPlayerSpawn()
	setElementData(source, "Thirst", 100)
end
addEventHandler("onClientPlayerSpawn", root, onClientPlayerSpawn)

βurak · February 4, 2022

can you try this again 
what's the problem on the client side?

addEventHandler ("onPlayerLogin",root,function(_,Acc)
    local AccName = getAccountName(Acc)
    local AccData = dbPoll(dbQuery(db,"SELECT Hunger,Thirst FROM stats WHERE Account=? LIMIT 1", AccName), -1)
        if(AccData) then
           if(#AccData > 0) then

              for _,row in ipairs(AccData) do
                 setElementData (source,"hunger",row["Hunger"])
                 setElementData(source, "Thirst", row["Thirst"])
                 break
              end

           end
        end
    end)
    
    function loadAccountData(player)
    local playerAccount = getPlayerAccount(player) 
    if(playerAccount) then
       if(isGuestAccount(playerAccount)) then return end
    end    
    local AccName = getAccountName(playerAccount)
    local AccData = dbPoll(dbQuery(db,"SELECT Hunger,Thirst FROM stats WHERE Account=? LIMIT 1", AccName), -1)
        if(AccData) then
           if(#AccData > 0) then

              for _,row in ipairs(AccData) do
                 setElementData (player,"hunger",row["Hunger"])
                 setElementData(player, "Thirst", row["Thirst"])
                 break
               end
               
            end
        end
    end

    function saveAccountData (player)
    local playerAccount = getPlayerAccount(player) 
    if(playerAccount) then
       if(isGuestAccount(playerAccount)) then return end
    end
    local AccName = getAccountName(playerAccount)

    local Hungers = getElementData (player,"hunger")
    local Thirsts = getElementData (player,"Thirst")
    dbExec(db,"UPDATE stats SET Hunger=?, Thirst=? WHERE Account=?",Hungers, Thirsts, AccName)
    end
    
    addEventHandler ( 'onPlayerQuit', root, function ( )
        saveAccountData(source)
    end )
    
    
    addEventHandler( "onResourceStart", resourceRoot, function( )
     for _, plr in pairs( getElementsByType("player")) do
           loadAccountData(plr)
        end
     end)
     
     
     addEventHandler ("onResourceStop", resourceRoot, function()
    for index,players in ipairs(getElementsByType("player")) do 
       saveAccountData(players)
    end
    end)

Edited February 4, 2022 by Burak5312

SinaAmp · February 4, 2022

every thing works fine till i quit the server i got dbExec failed; (1205) Lock wait timeout exceeded; try restarting transaction

my thirst works fine but Hunger is still stuck at 100

SinaAmp · February 4, 2022

@IIYAMA @The_GTA

i think it's impossible to complete this metode with set and get elemnt

isn'n better to use one variable instead of using get,set element?

βurak · February 4, 2022

I'm not sure but can you remove LIMIT 1 for hunger?

local AccData = dbPoll(dbQuery(db,"SELECT Hunger,Thirst FROM stats WHERE Account=? LIMIT 1", AccName), -1)

local AccData = dbPoll(dbQuery(db,"SELECT Hunger,Thirst FROM stats WHERE Account=?", AccName), -1)

SinaAmp · February 5, 2022

after disconnecting from server i got these errors i think it's for wrog argument for onplayerquit:

line 55: Bad argument @ 'getPlayerAccount' [Expected element at argument 1, got string 'Quit']
line 59: Bad argument @ 'getAccountName' [Expected account at argument 1, got boolean]
line 60: Bad argument @ 'getElementData' [Expected element at argument 1, got string 'Quit']
line 61: Bad argument @ 'getElementData' [Expected element at argument 1, got string 'Quit']

these top issues fixed

bu i still got this error after quit from server:

dbExec failed; (1205) Lock wait timeout exceeded; try restarting transaction

Edited February 5, 2022 by SinaAmp

βurak · February 5, 2022

I don't know this error maybe you can ask someone else

Edited February 5, 2022 by Burak5312

SinaAmp · February 5, 2022

oh i found whats wrong

i had too many update requests after onplayerquit in another resources

after i stoped another resources with db update in their code my problem was solved

but i need to use db update in my another resources

thank you bro

maybe @IIYAMA can help

SinaAmp · February 5, 2022

i solved the problem with using callback for must of them but some of them when i use callback get attempt to call global ' ' nil value

like this code:

Spoiler

function getpaccount (_,account)
	local AccName = getAccountName(account)
	local Setpname = setPlayerName(source,tostring(AccName))
	local AccData = dbQuery(AccDatacallback(), db,"SELECT * FROM stats WHERE Account=? LIMIT 1", AccName)
	function AccDatacallback(AccData)
		local result = dbPoll( AccData, 0 )
	end
	if(AccData) then 
       if(#AccData > 0) then return end
	end
	local ID = getFreeID()
	local SetData = dbExec(db,"INSERT INTO stats (ID,Account) VALUES (?, ?)",ID, AccName)
end
addEventHandler("onPlayerLogin",root, getpaccount)

βurak · February 5, 2022

Try changing 0 to -1 for dbPoll

function getpaccount (_,account)
	local AccName = getAccountName(account)
	local Setpname = setPlayerName(source,tostring(AccName))
	local AccData = dbQuery(AccDatacallback(), db,"SELECT * FROM stats WHERE Account=? LIMIT 1", AccName)
	function AccDatacallback(AccData)
		local result = dbPoll( AccData, -1 ) --this
	end
	if(AccData) then 
       if(#AccData > 0) then return end
	end
	local ID = getFreeID()
	local SetData = dbExec(db,"INSERT INTO stats (ID,Account) VALUES (?, ?)",ID, AccName)
end
addEventHandler("onPlayerLogin",root, getpaccount)

Edited February 5, 2022 by Burak5312

SinaAmp · February 5, 2022

nothing changed still got that error

my issue is server freeze but i cant understand whats going on in dbpool

IIYAMA · February 6, 2022

53 minutes ago, SinaAmp said:

nothing changed still got that error

my issue is server freeze but i cant understand whats going on in dbpool

I updated Burak his example. But I have not refactored the variable names.

function AccDatacallback(AccData, player, account, AccName)
  local result = dbPoll( AccData, 0 )

  if AccData and #AccData > 0 then return end

  local ID = getFreeID()
  local SetData = dbExec(db,"INSERT INTO stats (ID,Account) VALUES (?, ?)", ID, AccName)
end

function getpaccount (_,account)
	local AccName = getAccountName(account)
	setPlayerName(source,tostring(AccName))

	dbQuery(AccDatacallback, {source, account, AccName}, db, "SELECT * FROM stats WHERE Account=? LIMIT 1", AccName)

end
addEventHandler("onPlayerLogin",root, getpaccount)

The function was called instead of provided as function.

local AccData = dbQuery(AccDatacallback(), db,"SELECT * FROM stats WHERE Account=? LIMIT 1", AccName)

This is now moved inside of the call back function. Since the data is not immediately available.

if(AccData) then 
       if(#AccData > 0) then return end
	end
	local ID = getFreeID()
	local SetData = dbExec(db,"INSERT INTO stats (ID,Account) VALUES (?, ?)",ID, AccName)

SinaAmp · February 6, 2022

For this line:

if AccData and #AccData > 0 then return end

i got this error:

attempt to get length of local 'AccData' (a userdata value)

βurak · February 6, 2022

change with result

if result and #result > 0 then return end

SinaAmp · February 6, 2022

thank you guys problem solved

SinaAmp · February 6, 2022

sorry @IIYAMA but i still got dbExec failed; (1205) Lock wait timeout exceeded; try restarting transaction

i had 15 dbexec in my resources. 3 of them must work after player logout

when i decrease them (dbexec's) by stop one of the resources. after that i hadn't this issue but when 3 of them work's together i got that error

aslo i added the callback for all of my dbquerys

maybe that's for timing issue in my hunger and thirst system

Spoiler

function saveAccountData (player)
      local playerAccount = getPlayerAccount(player) 
      if(playerAccount) then
         if(isGuestAccount(playerAccount)) then return end
      end
      local AccName = getAccountName(playerAccount)
  
      local Hungers = getElementData (player,"Hunger")
      local Thirsts = getElementData (player,"Thirst")
      dbExec(db,"UPDATE stats SET Hunger=?, Thirst=? WHERE Account=?",Hungers, Thirsts, AccName)
      end
      
      addEventHandler ( 'onPlayerQuit', root, function ( )
          saveAccountData(source)
      end )

i think in this code dbexec doesn't get data from Hungers and thirsts variables

βurak · February 6, 2022

14 minutes ago, SinaAmp said:

i think in this code dbexec doesn't get data from Hungers and thirsts variables

you can debug it with outputDebugString to find out

outputDebugString(Hungers)
outputDebugString(Thirsts)

SinaAmp · February 6, 2022

i got my hunger and thirst number

IIYAMA · February 6, 2022

3 hours ago, SinaAmp said:

sorry @IIYAMA but i still got dbExec failed; (1205) Lock wait timeout exceeded; try restarting transaction

You can increase the lock wait timeout:

https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded-on-mysql

There are multiple reasons why this error can occur, so maybe that will not be enough.

Also consider to index your most used columns, like Account:

https://www.w3resource.com/mysql/creating-table-advance/create-index.php

This will speed up your search queries.

But de-increase the speed of removal queries. (which you probably only use when removing accounts)

[Help] Use mysql instead Get & Set accountdata

Recommended Posts

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members