Database problem

[xeon17]

Hello there,

I'm trying to create some kind of save system using SQLite database but it doesn't save the data of players.

The database is successfully created but it doesn't update the rows with newest player data. Also , it output in chatbox that the data is saved. About debugscript , there aren't any errors or warrings.

Here is my code:

Database.getInstance():execute( "CREATE TABLE IF NOT EXISTS Accounts (Username VARCHAR(200),Serial VARCHAR(50),IP VARCHAR(20),Money INT,Health INT,Armour INT,x VARCHAR(20),y VARCHAR(20),z VARCHAR(20),Rotation INT,Skin INT,Interior INT,Dimension INT,Team VARCHAR(20),Weapons TEXT,Stats TEXT)") 
  
local WeaponStats = {  
    ['9mm'] = 0, ['silenced'] = 0, ['deagle'] = 0, ['shotgun'] = 0, ['combat_shotgun'] = 0,  
    ['micro_smg'] = 0, ['mp5'] = 0, ['ak47'] = 0, ['m4'] = 0, ['tec-9'] = 0, ['sniper_rifle'] = 0 } 
     
     
  
     
function saveAccount ()  
    for i,player in ipairs (getElementsByType('player')) do 
    if (not isGuestAccount(getPlayerAccount (player))) then 
    local account = player.account:getName() 
    local x, y, z = player:getPosition() 
    local money, health = player.money,player.health 
    local armor, skin = player.armor,player.model 
    local int, dim  = player.interior,player.dimension 
    local rotation = player.rotation 
    local ip = player.ip 
    local serial = player.serial 
    local team = "Nezaposleni" 
    local Stats = toJSON(WeaponStats) 
    local weapons = {} 
     
    if ( player.team) then 
     team = player.team:getName() 
     end 
      
    for i=1,12 do 
      weapons[i] = { player:getWeapon(weaponSlot), player:getTotalAmmo(weaponSlot)} 
  end 
local oruzije = toJSON ( weapons ) 
Database.getInstance():execute('UPDATE Accounts SET Username=?,Serial=?,IP=?,Money=?,Health=?,Armour=?,x=?,y=?,z=?,Skin=?,Interior=?,Rotation=?,Dimension=?,Team=?,Weapons=?,Stats=?',account,serial,ip,money,health,armor,x,y,z,skin,int,rotation,dim,team,oruzije,Stats) 
   outputChatBox('Core: Data successfully saved',player,0,255,0) 
   end 
 end     
end 
addEventHandler('onResourceStart',resourceRoot,saveAccount) 

Note, i'm still a begginer in SQLite database so please if i did something wrong , expain everything well for me so in future i won't make the same mistake.

Thank you.

[xeon17]

I've solved the problem by using INSERT INTO instead of UPDATE with the help of two friends on Skype.

[0xCiBeR]

Yeah you could have used REPLACE INTO.

[xeon17]

Yeah you could have used REPLACE INTO.

Thanks for the tip and happy birthday .:CiBeR:.

the save system is almost finished but i keep geting a error in the line where i update the data. I don't know what's wrong here.

Database.getInstance():execute('UPDATE Accounts SET Name=?,Serial=?,IP=?,Email=?,Money=?,Health=?,Armour=?,PlayerPositionX=?,PlayerPositionY=?,PlayerPositionZ=?,Skin=?,Interior=?,Rotation=?,Dimension=?,Team=?,Weapons=?,Stats=?,WHERE Username=?',name,serial,ip,email,money,health,armor,x,y,z,skin,int,rotation,dim,team,oruzije,Stats,account) 

[xeon17]

Does someone know what's wrong? I still need help.

[Anubhav]

  
Database.getInstance():execute('UPDATE Accounts SET Name=?,Serial=?,IP=?,Email=?,Money=?,Health=?,Armour=?,PlayerPositionX=?,PlayerPositionY=?,PlayerPositionZ=?,Skin=?,Interior=?,Rotation=?,Dimension=?,Team=?,Weapons=?,Stats=? WHERE Username=?',name,serial,ip,email,money,health,armor,x,y,z,skin,int,rotation,dim,team,oruzije,Stats,account) 
  

Try this, you had a comma after Stats, while it isn't needed!

[xeon17]

Anubhav Thanks for your help , it worked awesome. Also i would like to know why after i removed the comma it worked so i can prevent similiar problems in the future.

It saves the data without problem when the resource stop and when the player quit but i got another problem , it doesn't load the data and it doesn't spawn the player when he login.

I made a function for easier debugging accountExist and yes it output the name of my Username&Account in debug but the rest doesn't work for any reason.

Here is the part of my code where i load the data and spawn the player.

           function loadPlayerData() 
                if (not isGuestAccount(getPlayerAccount (source))) then 
                local account = source.account:getName() 
                local data = doesAccountExists (account) 
                outputDebugString('Core: Checking does the account exists') -- it output this 
                if (data and type (data) == 'table' ) then 
                outputDebugString('Core: Checking: true') -- it doesn't output this 
                for i, v in ipairs  (data) do 
                if (v['Username'] == account) then 
                outputDebugString('Core: Loading Data') -- it doesn't output this 
                local money =       tonumber ( v['Money']) or 0 
                local armor =       tonumber (v['Armour']) or 0 
                local health =      tonumber (v['Health']) or 0 
                local x =           tonumber (v['PlayerPositionX']) or 0 
                local y =           tonumber (v['PlayerPositionY']) or 0 
                local z =           tonumber (v['PlayerPositionZ']) or 3 
                local rotation =    tonumber (v['Rotation']) or 90.0 
                local skin =        tonumber (v['Skin']) or 0 
                local interior =    tonumber (v['Interior']) or 0 
                local dimension =   tonumber (v['Dimension']) or 0 
                local team =        tostring (v['Team']) or "Nezaposleni" 
                local weapons =     fromJSON (v['Weapons'])  
                local weapstats =   fromJSON (tostring(v['Stats'])) 
                source:spawn(x,y,z,rotation,skin,interior,dimension) 
                source:setMoney(money) 
                source:setArmor(armor) 
                source:setHealth(health) 
                outputDebugString('Core: Data successfully loaded') -- it doesn't output this 
             end 
          end 
       end 
    end 
end 
addEventHandler('onPlayerLogin',root,loadPlayerData) 
  
  
  
  
-- Utills 
  
function accountExist(acc) 
    outputDebugString(acc .. "exists!") 
end 
  
function doesAccountExists ( acc ) 
 if ( acc ) then 
     local check = function(result, acc) 
      if(#result > 0 ) then 
       accountExist(acc) 
   end 
  end 
  Database.getInstance():query(check, {acc},"SELECT * FROM Accounts WHERE Username='"..acc.."' LIMIT 1") 
 end 
 return nil 
end 

[TAPL]

Line 4 .. line 41 to 55, my brain stop working...

[xeon17]

Line 4 .. line 41 to 55, my brain stop working...

Sorry, but i don't understand you?

[Mr_Moose]

Those lines seems correct but has a little confusing syntax, use "exec" (as in dbExec) instead of "query" on line 52, when updating that way. The same issue has been described in other topics recently, not for object orientated resources but the same rules applies to them.

[xeon17]

I'm not trying to update , and as i know i need to use query to get the result, but it could be wrong as i'm begginer at sqlite.I'm trying to load it and spawn the player with these data.

I can post some more information

Edited June 15, 2015 by Guest

[TAPL]

The function doesAccountExists not returning anything other than nil...

[xeon17]

The function doesAccountExists not returning anything other than nil...

What a stupid mistake, i didn't tested that...

Thanks.

[Lawliet]

The problem with INSERT INTO / REPLACE INTO is that it inserts all data you'd like to save each time the saving functions gets executed. Basically, this means that you will get several entries of player A having different (or the same) values. If you wish to save the data correctly, you must use UPDATE. What you can do, however, is that when the player logs in for the first time (i.e registers an account), you execute (using exec) a query which INSERTs all needed info into the table. The reason UPDATE won't work is because there is nothing to update, but if the data gets inserted into the database upon registering the player, it can update no problem.

This also means that you will have to write your own login panel (or get one from the resources page on MTA's website and customize it to your needs). If you wish to write your own, use getAccount to see if the account exists, and if does not, use addAccount. If the account exists, simply spawn the player.

Also, this line in your first post:

  
addEventHandler('onResourceStart',resourceRoot,saveAccount) 
  

You are trying to save the data when the resource starts - shouldn't it be other way around?

[xeon17]

The problem with INSERT INTO / REPLACE INTO is that it inserts all data you'd like to save each time the saving functions gets executed. Basically, this means that you will get several entries of player A having different (or the same) values. If you wish to save the data correctly, you must use UPDATE. What you can do, however, is that when the player logs in for the first time (i.e registers an account), you execute (using exec) a query which INSERTs all needed info into the table. The reason UPDATE won't work is because there is nothing to update, but if the data gets inserted into the database upon registering the player, it can update no problem.

This also means that you will have to write your own login panel (or get one from the resources page on MTA's website and customize it to your needs). If you wish to write your own, use getAccount to see if the account exists, and if does not, use addAccount. If the account exists, simply spawn the player.

Also, this line in your first post:

  
addEventHandler('onResourceStart',resourceRoot,saveAccount) 
  

You are trying to save the data when the resource starts - shouldn't it be other way around?

I've already made my own Login Panel , and yes i insert the player data into the database on registration so there is no problem. Using onResourceStart was a mistake which i've fixed already , i wanted to use onResourceStop but i wrote it wrong for some reason.

I'm already working on a new and better looking code of the save system but i have some problems if i don't solve them i'll post again here.

Thanks Lawliet