Help with Saving SQLITE stats

[Gabriel1375]

Hello, i've got an backup database script that the user Nexus made on my previous topic, that save the player stats on a SQLITE database, when player say /savestats it gets some internal.db data from the player like: name, rank, etc... and save it to a new database, but i have 2 problems...

 

My script:

local dbConnection = dbConnect("sqlite", "backupexprank.db")


function saveStat(sourcePlayer)
	local account = getPlayerAccount(sourcePlayer)
	local nomeconta = getAccountName(account)
	local patente = getAccountData(account, "password")
	local experiencia = getAccountData(account, "ip") 
	dbExec(dbConnection, "INSERT INTO accounts VALUES(?, ?, ?)", nomeconta, patente, experiencia)
end
addCommandHandler("savestats", saveStat)

 

My problems:

1 - It isn't getting the IP and Password, it shows this in the new backup database:

 

2 - When the player say /savestats more than one time, it saves the player state more than 1x, i need some way to check if the player is already on database, and if it is, then just update, not insert... Problem in this screenshot:

 

Thanks !!!

 

[Ayush Rathore]

1 hour ago, Gabriel1375 said:

Hello, i've got an backup database script that the user Nexus made on my previous topic, that save the player stats on a SQLITE database, when player say /savestats it gets some internal.db data from the player like: name, rank, etc... and save it to a new database, but i have 2 problems...

 

My script:

local dbConnection = dbConnect("sqlite", "backupexprank.db")


function saveStat(sourcePlayer)
	local account = getPlayerAccount(sourcePlayer)
	local nomeconta = getAccountName(account)
	local patente = getAccountData(account, "password")
	local experiencia = getAccountData(account, "ip") 
	dbExec(dbConnection, "INSERT INTO accounts VALUES(?, ?, ?)", nomeconta, patente, experiencia)
end
addCommandHandler("savestats", saveStat)

 

My problems:

1 - It isn't getting the IP and Password, it shows this in the new backup database:

 

2 - When the player say /savestats more than one time, it saves the player state more than 1x, i need some way to check if the player is already on database, and if it is, then just update, not insert... Problem in this screenshot:

 

Thanks !!!

 

use this

local dbConnection = dbConnect("sqlite", "backupexprank.db")
local qh = dbQuery( dbConnection,"CREATE TABLE IF NOT EXISTS accounts (nomeconta STRING,patente STRING,experiencia STRING)")
dbFree( qh )
    


function saveStat(sourcePlayer)
    local account = getPlayerAccount(sourcePlayer)
    local nomeconta = getAccountName(account)
    local patente = getAccountData(account, "password")
    local experiencia = getAccountData(account, "ip") 
    local qh = dbQuery( dbConnection, "SELECT * FROM accounts where nomeconta=?",nomeconta)
    local res = dbPoll(qh,-1) 
    dbFree( qh )
    if #res > 0 then
    dbExec( dbConnection, "UPDATE accounts SET experiencia=? where nomeconta=? ", experiencia,nomeconta )
    dbExec( dbConnection, "UPDATE accounts SET patente=? where nomeconta=? ", patente,nomeconta )
    else
    dbExec(dbConnection, "INSERT INTO accounts VALUES(?, ?, ?)", nomeconta, patente, experiencia)
    end
end
addCommandHandler("savestats", saveStat)

Be sure to add in account data  ip and password  enjoy

Edited March 19, 2017 by Ayush Rathore

[Gordon_G]

Hey, instead of just copy and past the code, try to learn the SQL :

I learnt with this (you'll speedly understand) : https://www.w3schools.com/sql/sql_syntax.asp

good luck 

Edited March 19, 2017 by Gordon_G

[NeXuS™]

The IP and password is not working, because in your SQL Table the two column's type is set to integer, not to text.

[Gabriel1375]

Thanks for all the reply....

But it still getting value 0 on IP and Password, doesn't it have to connect to the internal.db before getting IP and Password from the database ? Ty

[NeXuS™]

5 hours ago, NeXuS™ said:

The IP and password is not working, because in your SQL Table the two column's type is set to integer, not to text.

Set your type in your SQL Table to text.

[Gabriel1375]

40 minutes ago, NeXuS™ said:

Set your type in your SQL Table to text.

I've tried to do that, i'm getting the same problem, its still setting IP and Password to 0 on the new database..

[NeXuS™]

Try sending out the datas to the chat?

[Gabriel1375]

I've tried to output the data in the chat, but nothing happens, the database has been created by the script, and the same results, 0 and 0 on the IP and password field...

But no results in the chat...

The script is at this:

local dbConnection = dbConnect("sqlite", "backupexprank.db")
local qh = dbQuery( dbConnection,"CREATE TABLE IF NOT EXISTS accounts (nomeconta text,patente text,experiencia text)")
dbFree( qh )
    


function saveStat(sourcePlayer)
    local account = getPlayerAccount(sourcePlayer)
    local nomeconta = getAccountName(account)
    local patente = getAccountData(account, "password")
    local experiencia = getAccountData(account, "ip") 
    local qh = dbQuery( dbConnection, "SELECT * FROM accounts where nomeconta=?",nomeconta)
    local res = dbPoll(qh,-1) 
    dbFree( qh )
    if #res > 0 then
    dbExec( dbConnection, "UPDATE accounts SET experiencia=? where nomeconta=? ", experiencia,nomeconta )
    dbExec( dbConnection, "UPDATE accounts SET patente=? where nomeconta=? ", patente,nomeconta )
	outputChatBox ( "Saved account " .. nomeconta .. " with the password " .. patente .. " with the ip" .. experiencia .. " to our database")
    else
    dbExec(dbConnection, "INSERT INTO accounts VALUES(?, ?, ?)", nomeconta, patente, experiencia)
	outputChatBox ( "Saved account " .. nomeconta .. " with the password " .. patente .. " with the ip" .. experiencia .. " to our database")
    end
end
addCommandHandler("savestats", saveStat)

 

On the MTA server console it says:

ERROR: GZ_Backup\rankexp.lua:18: attempt to concatenate local 'experiencia' (a boolean value)

 

Line 18:

    outputChatBox ( "Saved account " .. nomeconta .. " with the password " .. patente .. " with the ip" .. experiencia .. " to our database")

 

Edited March 19, 2017 by Gabriel1375

[NeXuS™]

Replace

local experiencia = getAccountData(account, "ip")

with

local experiencia = getPlayerIP(sourcePlayer)

 

[Gabriel1375]

5 minutes ago, NeXuS™ said:

Replace

local experiencia = getAccountData(account, "ip")

with

local experiencia = getPlayerIP(sourcePlayer)

 

 

I've done that, but its giving error on "patente", that is the player password (the same error of the "experiencia")

Edited March 19, 2017 by Gabriel1375

[NeXuS™]

Did you save the player's password into an account data?

[Gabriel1375]

Yeah, the players password is stored at the internal.db, i think is this happening because it isn't connecting to the internal.db to get the infos

[NeXuS™]

Why do you have to save the players password?

[Gabriel1375]

I don't, its just for testing, as i need to do this script for my friend server, that i'm staff, and i'm testing in my local server, and i dont have the rank system, so i tried to save other informations of the internal.db, so if this work i just change the name of the tables to give it to my friend

[NeXuS™]

Then use random numbers, and output them into the chat. (math.random(10000))

[Gabriel1375]

Now it worked fine:

 

 

Why do you think that isn't working to get the player informations ? Because i think when i send it to the owner of the server i'm staff, with the elements name that he gave me (rank, experience), i dont think it's going to work, as getting info from internal.db doesn't worked yet...

[NeXuS™]

You are not getting data from "internal.db" at the moment. You are saving data into internal.db. You had no data in your "password" account data, so it was false, and if you set a integer an invalid value, it'll be 0.

[Gabriel1375]

Ah okay...

But how can i get the data from internal.db and save it to another .db file ?

Because the server has so much players that we have to reset, but we want to save the rank and exp from players that give the /savestats command, and the rank/exp is stored at the internal.db

[NeXuS™]

You can just fileCopy the file, so it'll be doubled with the exact same datas in it.

[Gabriel1375]

But i don't want to save all the players, because it will still be lagging, the internal.db has 50mb of data, i wan't to save just the exp/rank from players that are online and give /savestats, then we reset the database, and after this we make another script like: /getstats, to retrieve the saved stats and get back to the player account...

[NeXuS™]

You'll have this "internal.db", and you'll have only those datas which have been saved via /savestats, and you'll have to write an another function to retrieve those datas from the database with /getstats

[Gabriel1375]

Can you please help me ? I'm lost e.e

So with this function i will save every row/data from internal.db of the player that said /savestats ?

And how can i do this ? Ty !

[NeXuS™]

Whenever you type in /savestats on your server, it saves/updates the data of the player into the backup.db

[Ayush Rathore]

15 hours ago, Gordon_G said:

Hey, instead of just copy and past the code, try to learn the SQL :

I learnt with this (you'll speedly understand) : https://www.w3schools.com/sql/sql_syntax.asp

good luck 

Hi have you said this to me or the post owner☺ 

Edited March 20, 2017 by Ayush Rathore