[HELP] MySQL - Query Datas and send it to the Client

[=KoG=Rouche]

Hi all,

I'm working on my userpanel and i try to send the datas from DB to my client when they logged in.

I would like to ask you if my script is well done.

Serverside

  
rootElement = getRootElement() 
  
---- MYSQL Connection ---- 
-- Create an connection object for the Database 
function connectDB() 
    local con = dbConnect("mysql","dbname=DBNAME;host=IP", "USERNAME", "PASSWORD", "share=1") 
    if (con) then 
        outputDebugString("Connection OK !") 
    else 
        outputDebugString("Connection to Database failled !") 
    end 
    return con 
end 
  
function readPlayerDatas(account) -- Account must be a input variable from clientside 
    connect = connectDB() 
    -- Retrieve Player table datas from DB 
    local queryPlayer = dbQuery(connect, "SELECT * FROM Player WHERE account = ?", account) 
    local resultPlayer = dbPoll(queryPlayer, -1) 
    local playerData = resultPlayer[1] 
    -- Retrieve Events table datas from DB 
    local queryEvents = dbQuery(connect, "SELECT * FROM Events WHERE toPlayer = ?", account) 
    local resultEvents = dbPoll(queryEvents, -1) 
    local EventsData = resultEvents[1] 
    -- Retrieve DM datas from DB 
    local queryDM = dbQuery(connect, "SELECT * FROM DM WHERE toPlayer = ?", account) 
    local resultDM = dbPoll(queryDM, -1) 
    local DMData = resultDM[1] 
    -- Retrieve OLD datas from DB 
    local queryOLD = dbQuery(connect, "SELECT * FROM OLD WHERE toPlayer = ?", account) 
    local resultOLD = dbPoll(queryOLD, -1) 
    local OLDData = resultOLD[1] 
    -- Trigger an event clientside and send the previous datas with in 
    triggerClientEvent(thePlayer,"onReplyFromReadDatas",rootElement,playerData,EventsData,DMData,OLDData) 
end 
addEvent("onPlayerReadDatas",true) 
addEventHandler("onPlayerReadDatas",rootElement,readPlayerDatas) 
  

Clientside

  
rootElement = getRootElement() 
  
-- trigger the readPlayerDatas serverside 
function callReadDatas() 
    -- TEST Variable (Later it'll come from login panel) 
    account = "rouche"  
    triggerServerEvent("onPlayerReadDatas",localPlayer,account) 
end 
-- When the player logged in call the function callReadDatas 
addEventHandler("onPlayerLogin",rootElement,callReadDatas) 
  
-- Recceive the data from serverside 
function receiveDatasFromDB(playerDatas) 
    -- Set the local vars that'll be edited while playing (I'll later save all these changes to database) 
    local mail = playerData.mail 
    -- Display Test 
    outputDebugString(mail) 
end 
-- Trigggered by serverside to call receiveDatasFromDB 
addEvent("onReplyFromReadDatas",true) 
addEventHandler("onReplyFromReadDatas",rootElement,receiveDatasFromDB)  
  

Thanks

[JR10]

First of all, rootElement is already defined globally as root so you can just use root instead of rootElement. You're connecting to the database every time you request data which is not optimum, you should connect once at the start of the resource and use the connection variable throughout the scripts.

Also, you're using dbPoll with -1 and that will freeze the whole thing until it receives the results, a better way would be to use callbacks, as they will not cause freezing and will be called immediately after the results are ready.

onPlayerLogin event is server-side only.

[=KoG=Rouche]

Here is some modifications with your advices.

Where can i find information about Callback ? I know them in C++ but not with Lua. Thanks

Serverside

  
---- MYSQL Connection ---- 
-- Create an connection object for the Database 
function connectDB() 
    ... 
end 
  
-- Call the function to create a Connection Object 
function initVar() 
    connect = connectDB() 
end 
addEventHandler("onResourceStart",root,initVar) 
  
function readPlayerDatas(account) -- Account must be a input variable from clientside 
    -- Retrieve Player table datas from DB 
    local queryPlayer = dbQuery(connect, "SELECT * FROM Player WHERE account = ?", account) 
    local resultPlayer = dbPoll(queryPlayer, -1) 
    local playerData = resultPlayer[1] 
    -- Retrieve Events table datas from DB 
    local queryEvents = dbQuery(connect, "SELECT * FROM Events WHERE toPlayer = ?", account) 
    local resultEvents = dbPoll(queryEvents, -1) 
    local EventsData = resultEvents[1] 
    -- Retrieve DM datas from DB 
    local queryDM = dbQuery(connect, "SELECT * FROM DM WHERE toPlayer = ?", account) 
    local resultDM = dbPoll(queryDM, -1) 
    local DMData = resultDM[1] 
    -- Retrieve OLD datas from DB 
    local queryOLD = dbQuery(connect, "SELECT * FROM OLD WHERE toPlayer = ?", account) 
    local resultOLD = dbPoll(queryOLD, -1) 
    local OLDData = resultOLD[1] 
    -- Trigger an event clientside and send the previous datas with in 
    triggerClientEvent(thePlayer,"onReplyFromReadDatas",root,playerData,EventsData,DMData,OLDData) 
end 
addEvent("onPlayerReadDatas",true) 
addEventHandler("onPlayerReadDatas",root,readPlayerDatas) 
  
  
  

Clienside

  
-- trigger the readPlayerDatas serverside 
function callReadDatas() 
    -- TEST Variable (Later it'll come from login panel) 
    account = "rouche"  
    triggerServerEvent("onPlayerReadDatas",localPlayer,account) 
end 
-- When the player logged in call the function callReadDatas 
addEventHandler("onPlayerLogin",root,callReadDatas) 
  
-- Recceive the data from serverside 
function receiveDatasFromDB(playerDatas) 
    -- Set the local vars that'll be edited while playing (I'll later save all these changes to database) 
    local mail = playerData.mail 
    -- Display Test 
    outputDebugString(mail) 
end 
-- Trigggered by serverside to call receiveDatasFromDB 
addEvent("onReplyFromReadDatas",true) 
addEventHandler("onReplyFromReadDatas",root,receiveDatasFromDB)  
  

[JR10]

https://wiki.multitheftauto.com/wiki/DbQuery

You will find examples with callbacks. You're still using onPlayerLogin client-side which is a server-side event only.

[=KoG=Rouche]

Now it must be ok ?

Any other advices ?

Is it important to use dbFree ?

Serverside

  
---- MYSQL Connection ---- 
-- Create an connection object for the Database 
function connectDB() 
    .... 
end 
  
-- Call the function to create a Connection Object 
function initVar() 
    connect = connectDB() 
end 
addEventHandler("onResourceStart",root,initVar) 
  
-- When a player login it calls the readPlayerDatas function 
function initPlayer() 
    account = "Blabla" -- Test Variable ! ##### 
    readPlayerDatas() 
end 
addEventHandler("onPlayerLogin",root,init) 
  
function readPlayerDatas(account) -- Account must be a input variable from clientside 
    -- Retrieve Player table datas from DB 
    local queryPlayer = dbQuery(myCallBackPlayer,connect, "SELECT * FROM Player WHERE account = ?", account) 
    function myCallBackPlayer(queryPlayer) 
        local resultPlayer = dbPoll(queryPlayer,0) 
        playerData = resultPlayer[1] 
    end 
    -- Retrieve Events table datas from DB 
    local queryEvents = dbQuery(myCallBackEvents,connect, "SELECT * FROM Events WHERE toPlayer = ?", account) 
    function myCallBackEvents(queryEvents) 
        local resultEvents = dbPoll(queryEvents,0) 
        EventsData = resultEvents[1] 
    end 
    -- Retrieve DM datas from DB 
    local queryDM = dbQuery(myCallBackDM,connect, "SELECT * FROM DM WHERE toPlayer = ?", account) 
    function myCallBackDM(queryDM) 
        local resultDM = dbPoll(queryDM,0) 
        DMData = resultDM[1] 
    end 
    -- Retrieve OLD datas from DB 
    local queryOLD = dbQuery(myCallBackOLD,connect, "SELECT * FROM OLD WHERE toPlayer = ?", account) 
    function myCallBackOLD(queryOLD) 
        local resultOLD = dbPoll(queryOLD,0) 
        OLDData = resultOLD[1] 
    end 
    -- Trigger an event clientside and send the previous datas with in 
    triggerClientEvent(thePlayer,"onReplyFromReadDatas",root,playerData,EventsData,DMData,OLDData) 
end 
addEvent("onPlayerReadDatas",true) 
addEventHandler("onPlayerReadDatas",root,readPlayerDatas) 
  

Clientside

  
-- Recceive the data from serverside 
function receiveDatasFromDB(playerDatas) 
    -- Set the local vars that'll be edited while playing (I'll later save all these changes to database) 
    local mail = playerData.mail 
    -- Display Test 
    outputDebugString(mail) 
    -- ... 
end 
-- Trigggered by serverside to call receiveDatasFromDB 
addEvent("onReplyFromReadDatas",true) 
addEventHandler("onReplyFromReadDatas",root,receiveDatasFromDB)  
  

[JR10]

You're not sending the account in readPlayerDatas. You're also unnecessarily using global variables.

Here:

  
  
---- MYSQL Connection ---- 
-- Create an connection object for the Database 
function connectDB() 
    .... 
end 
  
-- Call the function to create a Connection Object 
function initVar() 
    connect = connectDB() 
end 
addEventHandler("onResourceStart",root,initVar) 
  
-- When a player login it calls the readPlayerDatas function 
function init(_, account) 
    account = "Blabla" -- Test Variable ! ##### 
    readPlayerDatas(account) 
end 
addEventHandler("onPlayerLogin",root,init) 
  
function readPlayerDatas(account) -- Account must be a input variable from clientside 
    -- Retrieve Player table datas from DB 
    local playerData, EventsData, DMData, OLDData 
    local queryPlayer = dbQuery(myCallBackPlayer,connect, "SELECT * FROM Player WHERE account = ?", account) 
    function myCallBackPlayer(queryPlayer) 
        local resultPlayer = dbPoll(queryPlayer,0) 
        playerData = resultPlayer[1] 
    end 
    -- Retrieve Events table datas from DB 
    local queryEvents = dbQuery(myCallBackEvents,connect, "SELECT * FROM Events WHERE toPlayer = ?", account) 
    function myCallBackEvents(queryEvents) 
        local resultEvents = dbPoll(queryEvents,0) 
        EventsData = resultEvents[1] 
    end 
    -- Retrieve DM datas from DB 
    local queryDM = dbQuery(myCallBackDM,connect, "SELECT * FROM DM WHERE toPlayer = ?", account) 
    function myCallBackDM(queryDM) 
        local resultDM = dbPoll(queryDM,0) 
        DMData = resultDM[1] 
    end 
    -- Retrieve OLD datas from DB 
    local queryOLD = dbQuery(myCallBackOLD,connect, "SELECT * FROM OLD WHERE toPlayer = ?", account) 
    function myCallBackOLD(queryOLD) 
        local resultOLD = dbPoll(queryOLD,0) 
        OLDData = resultOLD[1] 
    end 
    -- Trigger an event clientside and send the previous datas with in 
    triggerClientEvent(thePlayer,"onReplyFromReadDatas",root,playerData,EventsData,DMData,OLDData) 
end 
addEvent("onPlayerReadDatas",true) 
addEventHandler("onPlayerReadDatas",root,readPlayerDatas) 
  
  

[=KoG=Rouche]

Here is the edited code.

I don't know if i can add the "local" state for variables "playerData", "EventsData", "DMData" and "OLDData" because i've to use them out of the function with the triggerClientEvent action.

The account 'll come from a loginPanel which is not yet implemented here.

  
---- MYSQL Connection ---- 
-- Create an connection object for the Database 
function connectDB() 
    .... 
end 
  
-- Call the function to create a Connection Object 
function initVar() 
    connect = connectDB() 
end 
addEventHandler("onResourceStart",root,initVar) 
  
-- When a player login it calls the readPlayerDatas function 
function initPlayer() 
    local account = "Blabla" -- Test Variable ! ##### Later it'll be send by the Login Panel ##### 
    readPlayerDatas(account) 
end 
addEventHandler("onPlayerLogin",root,init) 
  
function readPlayerDatas(account) -- Account must be a input variable from clientside 
    -- Retrieve Player table datas from DB 
    local queryPlayer = dbQuery(myCallBackPlayer,connect, "SELECT * FROM Player WHERE account = ?", account) 
    function myCallBackPlayer(queryPlayer) 
        local resultPlayer = dbPoll(queryPlayer,0) 
        playerData = resultPlayer[1] 
    end 
    -- Retrieve Events table datas from DB 
    local queryEvents = dbQuery(myCallBackEvents,connect, "SELECT * FROM Events WHERE toPlayer = ?", account) 
    function myCallBackEvents(queryEvents) 
        local resultEvents = dbPoll(queryEvents,0) 
        EventsData = resultEvents[1] 
    end 
    -- Retrieve DM datas from DB 
    local queryDM = dbQuery(myCallBackDM,connect, "SELECT * FROM DM WHERE toPlayer = ?", account) 
    function myCallBackDM(queryDM) 
        local resultDM = dbPoll(queryDM,0) 
        DMData = resultDM[1] 
    end 
    -- Retrieve OLD datas from DB 
    local queryOLD = dbQuery(myCallBackOLD,connect, "SELECT * FROM OLD WHERE toPlayer = ?", account) 
    function myCallBackOLD(queryOLD) 
        local resultOLD = dbPoll(queryOLD,0) 
        OLDData = resultOLD[1] 
    end 
    -- Trigger an event clientside and send the previous datas with in 
    triggerClientEvent(thePlayer,"onReplyFromReadDatas",root,playerData,EventsData,DMData,OLDData) 
end 
addEvent("onPlayerReadDatas",true) 
addEventHandler("onPlayerReadDatas",root,readPlayerDatas) 
  

Thank you,

[JR10]

I already posted a fixed code for you, use that. Also, you have init as the function handler for onPlayerLogin while it should be initPlayer.

[=KoG=Rouche]

Ok thanks.

What is the meaning of the underscore in this function ?

-- When a player login it calls the readPlayerDatas function 
function init(_, account) 
    account = "Blabla" -- Test Variable ! ##### The Account variable 'll come later with a loginPanel ##### 
    readPlayerDatas(account) 
end 
addEventHandler("onPlayerLogin",root,init) 

[Woovie]

It has no meaning, it was just put there because the variable isn't going to be used.

[=KoG=Rouche]

I've an error when the test player "blabla" login.

[16:17:33] WARNING: [gamemodes]/[race]/[addons]/kog-readdb/server.lua:29: Bad argument @ 'dbQuery' [Expecte 
d db-connection at argument 1, got nil] 
[16:17:33] WARNING: [gamemodes]/[race]/[addons]/kog-readdb/server.lua:35: Bad argument @ 'dbQuery' [Expecte 
d db-connection at argument 1, got nil] 
[16:17:33] WARNING: [gamemodes]/[race]/[addons]/kog-readdb/server.lua:41: Bad argument @ 'dbQuery' [Expecte 
d db-connection at argument 1, got nil] 
[16:17:33] WARNING: [gamemodes]/[race]/[addons]/kog-readdb/server.lua:47: Bad argument @ 'dbQuery' [Expecte 
d db-connection at argument 1, got nil] 

It seems that the "connect" object is not set.

[JR10]

Are you sure dbConnect is being used and the variable is being assigned correctly? You should check for the variable to make sure the connection succeeded. Post the part where you run dbConnect.

[=KoG=Rouche]

It was working before 0.o

  
function connectDB() 
    local con = dbConnect("mysql","dbname=DBNAME;host=IP", "USERNAME", "PASSWORD", "share=1") 
    if (con) then 
        outputDebugString("Connection OK !") 
    else 
        outputDebugString("Connection to Database failled !") 
    end 
    return con 
end 
  
-- Call the function to create a Connection Object 
function initVar() 
    connect = connectDB() 
end 
addEventHandler("onResourceStart",root,initVar) 
  

[JR10]

Try this:

  
  
---- MYSQL Connection ---- 
-- Create an connection object for the Database 
function connectDB() 
    .... 
end 
  
-- Call the function to create a Connection Object 
function initVar() 
    connect = connectDB() 
end 
addEventHandler("onResourceStart",root,initVar) 
  
-- When a player login it calls the readPlayerDatas function 
function init(_, account) 
    account = "Blabla" -- Test Variable ! ##### 
    readPlayerDatas(account) 
end 
addEventHandler("onPlayerLogin",root,init) 
  
function readPlayerDatas(account) -- Account must be a input variable from clientside 
    -- Retrieve Player table datas from DB 
    local playerData, EventsData, DMData, OLDData 
    local function myCallBackPlayer(queryPlayer) 
        local resultPlayer = dbPoll(queryPlayer,0) 
        playerData = resultPlayer[1] 
    end 
    local queryPlayer = dbQuery(myCallBackPlayer,connect, "SELECT * FROM Player WHERE account = ?", account) 
  
    -- Retrieve Events table datas from DB 
    local function myCallBackEvents(queryEvents) 
        local resultEvents = dbPoll(queryEvents,0) 
        EventsData = resultEvents[1] 
    end 
    local queryEvents = dbQuery(myCallBackEvents,connect, "SELECT * FROM Events WHERE toPlayer = ?", account) 
    -- Retrieve DM datas from DB 
local function myCallBackDM(queryDM) 
        local resultDM = dbPoll(queryDM,0) 
        DMData = resultDM[1] 
    end 
    local queryDM = dbQuery(myCallBackDM,connect, "SELECT * FROM DM WHERE toPlayer = ?", account) 
    -- Retrieve OLD datas from DB 
local function myCallBackOLD(queryOLD) 
        local resultOLD = dbPoll(queryOLD,0) 
        OLDData = resultOLD[1] 
    end 
    local queryOLD = dbQuery(myCallBackOLD,connect, "SELECT * FROM OLD WHERE toPlayer = ?", account) 
    -- Trigger an event clientside and send the previous datas with in 
    triggerClientEvent(thePlayer,"onReplyFromReadDatas",root,playerData,EventsData,DMData,OLDData) 
end 
addEvent("onPlayerReadDatas",true) 
addEventHandler("onPlayerReadDatas",root,readPlayerDatas) 
  
  

[=KoG=Rouche]

Just change this

  
triggerClientEvent(thePlayer,"onReplyFromReadDatas",root,playerData,EventsData,DMData,OLDData) 
  

to this

  
triggerClientEvent("onReplyFromReadDatas",root,playerData,EventsData,DMData,OLDData) 
  

and it works now

I'll now try to make it work with the "onPlayerLogin" event. Get the account, create DB record if not yet done and read datas or read datas if already created and send it all to Clientside.

Juste one question.

What is the best way to manage datas and DB ?

I mean ...

- Must i trigger a serverside query to database each time a player interact with a data ? (winCash, winMap, lostRoll, LostBet, got an achievement, ...). I think it can easily overload the DB.

- Must i use the function updateDatasToDB (to write clientside datas to DB) and readDatasFromDB (query datas from DB and send it clientside) in a laps of time (Every 20 seconds for example). In this case clientside variables are updated for all interactions from theplayer (winCash, winMap, lostRoll, LostBet, got an achievement, ...).

- Must i use updateDatasToDB (to write current clientside datas to DB) every 20 seconds and readDatasFromDB (query datas from DB and send it clientside) each time the player open the userpanel ? In this case clientside variables are updated for all interactions from theplayer (winCash, winMap, lostRoll, LostBet, got an achievement, ...).