Database not updating

Solar2D General Questions/Discussion
#1

Hello guys,

I’m stuck on  updating my database, it doesn’t update. I tested my SQLite statement in a database browser it works, but when I’m emulating it, it doesn’t update anymore here my code

--Database creation local path = system.pathForFile ("playerDB.sqlite" , system.DocumentsDirectory) local db = sqlite.open(path) local sql =[[CREATE TABLE IF NOT EXISTS player\_Achievement ( id\_Num INTEGER PRIMARY KEY, player\_ID INTEGER, --foreign key coll\_2k INTEGER, coll\_3k INTEGER, coll\_5k INTEGER, lastColl INTEGER, hint1 INTEGER, hint2 INTEGER, hint3 INTEGER, hint4 INTEGER, acientFin INTEGER, middleFin INTEGER, earlyFin INTEGER, modernFin INTEGER, gameFin INTEGER, totalPoint INTEGER );]] print ("achievement DB is created") db:exec(sql) db:close()

--My SQLite statement local path = system.pathForFile ("playerDB.sqlite",system.DocumentsDirectory) local db = sqlite.open(path) local sql = "(UPDATE player\_Achievement SET coll\_2k =" .. param1 .. ", coll\_3k =" .. param2 .. ", coll\_5k =" .. param3 .. " , lastColl =" .. param4 .. " , totalPoint =" .. totalPoint .. " WHERE player\_ID =" .. playerID .. ");" print (sql) db:exec(sql) print ("successfully updated player achievement DB") db:close()

Thanks in advance,

Jam

#2

I think you have to use system.ResourceDirectory

But if you plan on modifying the database at runtime on the device you will need to copy it to the system.DocumentsDirectory first.

Cheers.

#3

Thanks for the reply develephant, Android doesn’t have any ResourceDirectory file, all of my sql statement runs perfectly except to this one. I do not know why

#4

Are you sure you need the “(” and “)” in your update statement?

#5

I just deleted that part, as you mention, but it seems do not work

#6

While there may not be a “Resources” directory, the constant still must have some use on Android.  I use it often for databases, especially when testing locally.

I rebuilt your database and when I call the update statement after changing system.DocumentsDirectory to system.ResourceDirectory, it works fine.

I used this code, and I have the playerDB.sqlite in my root directory with main.lua.

[lua]
local sqlite = require( “sqlite3” )

–My SQLite statement

local path = system.pathForFile (“playerDB.sqlite”, system.ResourceDirectory)

local db = sqlite.open(path)

local param1 = 1

local param2 = 4

local param3 = 6

local param4 = 12

local totalPoint = 2304

local playerID = 1

local sql = “UPDATE player_Achievement SET coll_2k=” … param1 … “, coll_3k=” … param2 … “, coll_5k=” … param3 … " , lastColl=" … param4 … " , totalPoint=" … totalPoint … " WHERE player_ID=" … playerID … “;”

print (sql)

db:exec(sql)

print (“successfully updated player achievement DB”)

db:close()
[/lua]

Give it a try.

Cheers.

#7

I think you have to use system.ResourceDirectory

But if you plan on modifying the database at runtime on the device you will need to copy it to the system.DocumentsDirectory first.

Cheers.

#8

Thanks for the reply develephant, Android doesn’t have any ResourceDirectory file, all of my sql statement runs perfectly except to this one. I do not know why

#9

Are you sure you need the “(” and “)” in your update statement?

#10

I just deleted that part, as you mention, but it seems do not work

#11

While there may not be a “Resources” directory, the constant still must have some use on Android.  I use it often for databases, especially when testing locally.

I rebuilt your database and when I call the update statement after changing system.DocumentsDirectory to system.ResourceDirectory, it works fine.

I used this code, and I have the playerDB.sqlite in my root directory with main.lua.

[lua]
local sqlite = require( “sqlite3” )

–My SQLite statement

local path = system.pathForFile (“playerDB.sqlite”, system.ResourceDirectory)

local db = sqlite.open(path)

local param1 = 1

local param2 = 4

local param3 = 6

local param4 = 12

local totalPoint = 2304

local playerID = 1

local sql = “UPDATE player_Achievement SET coll_2k=” … param1 … “, coll_3k=” … param2 … “, coll_5k=” … param3 … " , lastColl=" … param4 … " , totalPoint=" … totalPoint … " WHERE player_ID=" … playerID … “;”

print (sql)

db:exec(sql)

print (“successfully updated player achievement DB”)

db:close()
[/lua]

Give it a try.

Cheers.