SQL UPDATE crashes scene

Director Class
#1

In one of my scene files I have the following code:

function new()  
 db:exec( 'update user set highscore=1 where id=1' )  
 for row in db:nrows("SELECT \* FROM user") do  
 print('id: '..row.id)  
 print('highscore: '..row.highscore)  
 end  
  
 initVars()  
  
 return localGroup  
  
end

This causes the scene to “crash” and main.lua to be reloaded. What’s interesting is that if I take out the SQL UPDATE statement, the scene runs fine.

Is this a bug with director.lua or am I doing something wrong?

Thanks
[import]uid: 23583 topic_id: 12188 reply_id: 312188[/import]

#2

I should add that even though the scene “crashes” the table does get updated correctly. So the SQL statements are working but for some reason are causing the app to crash. The console provides no additional info other than displaying the Corona simulator startup text which indicates that the app crashed and restarted. [import]uid: 23583 topic_id: 12188 reply_id: 44317[/import]

#3

What happens in initVars() and where is db initialized? [import]uid: 58455 topic_id: 12188 reply_id: 44370[/import]

#4

If you comment out initVars() does it work properly? [import]uid: 31262 topic_id: 12188 reply_id: 44397[/import]

#5

Commenting out initVars() doesn’t fix the problem.

I initialize the db in main.lua with this code:

require("sqlite3")  
  
db = sqlite3.open( system.pathForFile("data.db", system.ResourceDirectory) )

[import]uid: 23583 topic_id: 12188 reply_id: 44409[/import]

#6

FIGURED IT OUT!!!

I had copied my data.db file to my project directory for deployment and testing on my iPhone. In the Corona Simulator -> Preferences there is an option to “Relaunch Simulator when project is modified?”. This setting is set to “Always” be default. Since a SQL UPDATE modifies the data.db file, this caused the simulator to restart itself. Changing this setting to “Never” resolved the problem! [import]uid: 23583 topic_id: 12188 reply_id: 44481[/import]

#7

I put my database in the documents folder. This would probably get around your issue. I find the automatic relaunch very handy since you always have to relaunch anyways. [import]uid: 31262 topic_id: 12188 reply_id: 44492[/import]

#8

@davidchiu

Thanks for writing up the solution! I just moved from the Windows Sim to Mac Sim, and was trying to figure this exact thing out! I was able to narrow it down to the UPDATE clause, and actually came in here to find if it was a bug! :stuck_out_tongue:

Great to know I can just turn off a setting and make my game work again. :slight_smile: [import]uid: 42145 topic_id: 12188 reply_id: 48728[/import]

#9

Having some problem to UPDATE a database I felt on your post.
Hoping it’s still active.

Any idea why this code runs fine except it does not change my database :

local function updateBaseWith(lettertochange)  
 local sql = "SELECT \* FROM database"   
 for row in db:nrows(sql) do  
 local str, match = string.gsub(row.blueletters, lettertochange, "", 1)  
 -- remove a letter from bluletters then add a letter in redletters  
 if match \> 0 then  
 row.blueletters = str  
 row.redletters = row.redletters..lettertochange  
 db:exec( "UPDATE database SET blueletters=str, redletters=row.redletters WHERE id=row.id" )  
 -- print() here confirms that row.blueletters and row.redletters have changed  
 -- but database is not changed ?...?  
 end   
 end  
end  
updateBaseWith("a")

Thx. [import]uid: 9328 topic_id: 12188 reply_id: 60498[/import]

#10 
db:exec( "UPDATE database SET blueletters=str, redletters=row.redletters WHERE id=row.id" )

Above is your current update function… but try the below instead, in my version its using your strings instead of just entering the text “str” and “row.redletters” which is how you had it.

local update1 = "UPDATE database SET blueletters = '"..str.."', redletters = '"..row.redletters.."' WHERE id=row.id"  
db:exec( update1 )

[import]uid: 69826 topic_id: 12188 reply_id: 60595[/import]

#11

Thx for the answer.
I tried your code but with no more success. No terminal error, the db just doesn’t change … [import]uid: 9328 topic_id: 12188 reply_id: 60603[/import]

#12

I’m not sure you can run a statement against the database while you are still looping through a results list, especially where you are trying to update the same table you are reading from.
For testing purposes, just do a static UPDATE statement with known values instead of inside a loop. Then, you can make sure your database is being updated.
For this specific scenario. I would run my select, and push all my data to a lua table. Then, I would loop through that table, doing the update statements
[import]uid: 42145 topic_id: 12188 reply_id: 60655[/import]

#13

Thx for the tip. I’ll try to do this way then come back for feedback. [import]uid: 9328 topic_id: 12188 reply_id: 60784[/import]

#14

Super ! Thx jammydodger and thytoeung, I did it :slight_smile:
With the following code ( just for guys who would encounter the same questions - caution, may need optimization and real coder touch :slight_smile:

[code]
local function updateBaseWith(lettertochange)
local results,k = {},1

– SELECT from database AND PUSH TABLE
local sql = “SELECT id, blueletters, redletters FROM database”
for row in db:nrows(sql) do
local str, match = string.gsub(row.blueletters, lettertochange, “”, 1)
– remove a letter from bluletters then add a letter in redletters
if match > 0 then
results[k] = {id = row.id, blue = str, red = row.redletters…lettertochange}
k = k+1
end
end

– UPDATE database
for i=1,#results do
local update = “UPDATE database SET blueletters=’”…results[i].blue…"’, redletters=’"…results[i].red"’ WHERE id=’"…results[i].id…"’"
db:exec( update )
end

–
results,k = nil,nil --not sure it’s needed

end
updateBaseWith(“a”)
[/code] [import]uid: 9328 topic_id: 12188 reply_id: 60923[/import]

#15

Glad to hear! And thanks for posting back - always helps people to see solutions. [import]uid: 42145 topic_id: 12188 reply_id: 60938[/import]

#16

awesome, glad you got it working :slight_smile:

SQL can be a pain at times, so this should help a fair few people out! [import]uid: 69826 topic_id: 12188 reply_id: 61062[/import]

#17

Thanks for this code feed back. I’m ok to work with this code to save and retrieve data from db on simulator.

KC [import]uid: 94613 topic_id: 12188 reply_id: 65346[/import]

#18

Hummm… I’m back with sql :

I tried to test my app and no savings occur on the device build (it was ok on the simulator).

So I tried to change ResourceDirectory with DocumentsDirectory, but the simulator just doesn’t see my tables any longer …

Could you help ? [import]uid: 9328 topic_id: 12188 reply_id: 87103[/import]