sqlite UPDATE query

[Mr.Loki]

When i try updating the database it does not work when using the WHERE clause...

I get no errors.

Here's all the code

Spoiler

local db = dbConnect("sqlite","playerData/accounts.db")
db:exec( "CREATE TABLE IF NOT EXISTS playerData (account TEXT, lastpos_x INT, lastpos_y INT, lastpos_z INT, money INT, health INT, hunger INT, water INT, exp INT, kills INT, deaths INT )")

PDT={}

function registerPlayerToDB( p )
	db:exec( "INSERT INTO playerData(account , lastpos_x, lastpos_y, lastpos_z, money, health, hunger, water, exp, kills, deaths ) VALUES(?,?,?,?,?,?,?,?,?,?,?)", p.account.name, p.position.x, p.position.y, p.position.z, p:getMoney(),p.health,100,100,0,0,0)
	PDT[p.account.name] = {["money"] = 0,["health"] = 100,["hunger"] = 100,["water"] = 100,["exp"] = 0,["kills"] = 0,["deaths"] = 0 }
end

function loadPlayerDataFromDB( player )
	local data = dbQuery(db, "SELECT * FROM playerData"):poll(-1)
	outputDebugString( inspect(PDT) )	
end
addCommandHandler( "db", loadPlayerDataFromDB )

addEventHandler( "onResourceStart", resourceRoot, function( )
	local data = dbQuery(db, "SELECT * FROM playerData"):poll(-1)
	for i=1,#data do
		local d = data[i]
		PDT[d.account] = {["money"] = d.money,["health"] = d.health,["hunger"] = d.hunger,["water"] = d.water,["exp"] = d.exp,["kills"] = d.kills,["deaths"] = d.deaths }
		--outputConsole( inspect(PDT) )
	end
end )

function saveDataToDB( plr )
	if plr then
		for acc,data in pairs(PDT) do
			if acc == source.account.name then
				local pos = Account(acc).player.position
				db:exec( "UPDATE playerData SET lastpos_x=?, lastpos_y=?, lastpos_z=?, money=?, health=?, hunger=?, water=?, exp=?, kills=?, deaths=? WHERE account=?",pos.x,pos.y,pos.z,data.health,data.hunger,data.water,data.exp,data.kills,data.deaths,tostring(acc))
				PDT[acc]=nil
				iprint(inspect(PDT))
			end
		end
	else
		for acc,data in pairs(PDT) do
			local p = Account(acc).player
			local pos = p.position
			iprint(acc)
			local update = db:exec( "UPDATE playerData SET lastpos_x=?,lastpos_y=?,lastpos_z=?,money=?,health=?,hunger=?,water=?,exp=?,kills=?,deaths=? WHERE account=?",pos.x,pos.y,pos.z,data.health,data.hunger,data.water,data.exp,data.kills,data.deaths,tostring(acc))
			iprint("DB updated.",data.health,data.hunger,data.water,data.exp,data.kills,data.deaths,acc)
		end
	end
end

addEventHandler( "onResourceStop", resourceRoot, function ( ) saveDataToDB() end )

local r = math.random

function updateTable( ) --for testing purposes
	for acc,data in pairs(PDT) do
		if Account(acc).player then
		local p = Account(acc).player
			PDT[p.account.name] = {["money"] = p:getMoney(),["health"] = p.health,["hunger"] = r(100),["water"] = r(100),["exp"] = r(100),["kills"] = r(100),["deaths"] = r(100) }
		end
	end
	saveDataToDB()
end
setTimer( updateTable, 3000, 0 )

 

 

Edited February 9, 2017 by loki2143

[Mr.Loki]

Anyone?

[3aGl3]

I haven't really worked with SQLite as I think it's not really more than an excel sheet compared to MySQL but as SQL is usually the same...

There are only two small things I can see, first you never really use the plr variable but instead the source variable, second I always embed column and table names in ``, e.g.

UPDATE `characters` SET `pos_y`='1' WHERE (`character_id`='2')

Also I use Navicat Lite to double check my queries for function, might help you.

[Mr.Loki]

I tried them but nothing worked still not updating.

[3aGl3]

So, I tried the querying a SQLite database and can't seem to find any mistake here...
For ease of use I did however use Navicat and not MTA, all queries worked just fine.

However the positiondata should in theory be REAL and not INTEGER...SQLite did not complain and even saved the reals in the integer fields.
Maybe MTA is picky there...

[pa3ck]

MTA is just using embedded SQL, there shouldn't be any difference between plain SQL and MTA SQL. The only thing I can think about is that you don't have any matches in the WHERE, maybe wrong id / username? Debug your code with outputChatBoxes to make sure the query actually runs and also output the values you are checking in the WHERE clause. If you are still unable to solve it, send us the layout of your table and the outputChatBox from the WHERE clause values. 

Edited February 10, 2017 by pa3ck

[Mr.Loki]

There is an iprint in the code above and it was printing the correct results.

But i solved it, thanks.