Problem with SQLite3 - updating database.

Solar2D Lua Language
#1

Hello guys, thank you in advance for reading this.
I am learning how to use databases for my game, and I am really stucked with this issue.

I made a file and there is a variable called str, which its value I can store in the content of my database (row 9, column 3)

I created 4 buttons.
2 buttons are for changing the value of the variable str.
the third button is for updating specific content of the database.

and the fourth button is for printing the value of said content. (not the variable)

require "sqlite3" --Open data.db. If the file doesn't exist it will be created local path = system.pathForFile("data.db", system.DocumentsDirectory) db = sqlite3.open( path ) ----MAIN VARIABLE str = "I am str" ----FIRST BUTTON---- local button = display.newImage ('images/boton.png') function button:touch( event ) if (event.phase == "began") then str = "chaos" print ("str = " ..str) end end button:addEventListener("touch", button) ----SECOND BUTTON---- local button2 = display.newImage ('images/boton.png') button2.x = 200 function button2:touch( event ) if (event.phase == "began") then str = "hi" print ("str = " ..str) end end button2:addEventListener("touch", button2) --Handle the applicationExit event to close the db local function onSystemEvent( event ) if( event.type == "applicationExit" ) then db:close() end end local insertMore = false --Setup the table if it doesn't exist local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]] print(tablesetup) db:exec( tablesetup ) local tablesave = [[UPDATE test SET content2=']]..str ..[[' WHERE id = 9]] ----SAVE BUTTON---- updates de db local save = display.newImage('images/boton.png') save.x = 100; save.y = 100 function save:touch (event) if (event.phase == "began") then db:exec( tablesave ) print ("str saved as:" ..str) end end save:addEventListener("touch", save) ----PRINTER BUTTON---- prints the current value of content2 where id == 9. local printer = display.newImage('images/boton.png') printer.x = 180; printer.y = 100 function printer:touch (event) if (event.phase == "began") then for row in db:nrows("SELECT \* FROM test WHERE id = 9") do print (row.content2) end end end printer:addEventListener("touch", printer) for row in db:nrows("SELECT \* FROM test WHERE id = 9") do print (row.content2) end --print the sqlite version to the terminal print( "version " .. sqlite3.version() ) --setup the system listener to catch applicationExit Runtime:addEventListener( "system", onSystemEvent )

My problem is that the database will be updated only with the original value of str, but no the ones I assign from the app by using the buttons.

If I change the value of str directly from the code, then it prints the old value when it first runs, and after I pressed the save button, the value of str changes to the one I put manually into the code.

I would really appreciate that someone with more experience than me could guide me on what I am doing wrong.
Thank you very much and I look forward to your answers.

Best Regards,

Lucas.

#2

Where are you inserting records?  I don’t see how you can find id = 9 when you never add a record with an id of 9

#3

Hello Rob! Thank you for your prompt reply. Yes. I inserted many rows on the data base table. I then removed them from my code so I wouldnt insert new rows every time I reloaded the simulator.

#4

Hi i think this is whats wrong. You are setting the tablesave when the app is started. Since the code will run from top down the variable is set before the function to save is called. 

Try this instead:

----SAVE BUTTON---- updates de db local save = display.newImage('images/boton.png') save.x = 100; save.y = 100 function save:touch (event) if (event.phase == "began") then local tablesave = [[UPDATE test SET content2=']]..str ..[[' WHERE id = 9]] db:exec( tablesave ) print ("str saved as:" ..str) end end save:addEventListener("touch", save)
#5

Hello Erik!

Well, that solved everything. Thank you very much!!

However, I still don’t understand what is the difference of creating the query outside the function…
I thought it only had to be created before I call the exec() method.

Well, I will study more to be as good as you!  B) 

Greetings and Best Regards from Chile.

Lucas Peralta.

#6

Hey Erik, Now I understand! :smiley:
When I create the query “local tablesave” I am assigning the current value to the query just once.
If i put it inside the touch, method, I the query has the value of “str” at the time I pressed the button.

Thank you once again! :smiley:

Lucas Peralta

#7

Where are you inserting records?  I don’t see how you can find id = 9 when you never add a record with an id of 9

#8

Hello Rob! Thank you for your prompt reply. Yes. I inserted many rows on the data base table. I then removed them from my code so I wouldnt insert new rows every time I reloaded the simulator.

#9

Hi i think this is whats wrong. You are setting the tablesave when the app is started. Since the code will run from top down the variable is set before the function to save is called. 

Try this instead:

----SAVE BUTTON---- updates de db local save = display.newImage('images/boton.png') save.x = 100; save.y = 100 function save:touch (event) if (event.phase == "began") then local tablesave = [[UPDATE test SET content2=']]..str ..[[' WHERE id = 9]] db:exec( tablesave ) print ("str saved as:" ..str) end end save:addEventListener("touch", save)
#10

Hello Erik!

Well, that solved everything. Thank you very much!!

However, I still don’t understand what is the difference of creating the query outside the function…
I thought it only had to be created before I call the exec() method.

Well, I will study more to be as good as you!  B) 

Greetings and Best Regards from Chile.

Lucas Peralta.

#11

Hey Erik, Now I understand! :smiley:
When I create the query “local tablesave” I am assigning the current value to the query just once.
If i put it inside the touch, method, I the query has the value of “str” at the time I pressed the button.

Thank you once again! :smiley:

Lucas Peralta