<Solved>update sqllite

Getting Started Newbie Questions
#1

Dear everyone,
I’m new here. I got some problem with my update command with sqllite.

I got the code from this site and modify a little bit.

here is all my code and I find for insert is not a problem and I use the insert to build a db with 4 entry. Later I try to use the update to amend one of the record. It fail and I find the db file have not modified.
What I’m doing wrong? It seems to be little bit different from all sample on the web. To using lua/corona with sql what other code I need to add to make it work.

Thanks for all your help.

KC-bonvivid

local saveValue = function( stageName, scoreValue )

require “sqlite3”

local sv = tostring(scoreValue)
–Open data.db. If the file doesn’t exist it will be created
local path = system.pathForFile(“data21.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

–Handle the applicationExit event to close the db
local function onSystemEvent( event )
if( event.type == “applicationExit” ) then
db:close()
end
end

–Setup the table if it doesn’t exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id, content, content1);]]
print(tablesetup)
db:exec( tablesetup )

local scoreValue = {}
scoreValue[1] = tostring(stageName)
scoreValue[2] = sv

–local tablefill =[[INSERT INTO test VALUES ( 4, ‘stage001’,’]]…scoreValue[2]…[[’);]]
–db:exec( tablefill )

local tableupdate = " UPDATE test SET id = 1 WHERE content1 = "…scoreValue[2];
print(tableupdate)
db:exec( tableupdate )

end
[import]uid: 94613 topic_id: 17012 reply_id: 317012[/import]

#2

Ha Ha!

Let me answer my own question, I finally find out the answer by myself. Here is the correct code to update a field in sql.

This is first time I’m using sql and sql with corona. Why I can’t update the field because I mess up with the [new value] and [condition]. After I swap both parameter position I can update my field.

Here I find it should be in w3c:

UPDATE “table_name”
SET “column_1” = [new value]
WHERE {condition}

Wrong one:
local tableupdate = " UPDATE test SET id = 1 WHERE content1 = "…scoreValue[2];

Corrected one:
local tableupdate = " UPDATE test SET content1 = ‘"…scoreValue[2]…"’ WHERE id = ‘1’;"

here is my code, feel free to use it.
+++++++++++++++++++++++++++++++++++++++
local saveValue = function( stageName, scoreValue )

require “sqlite3”

local sv = tostring(scoreValue)
–Open data.db. If the file doesn’t exist it will be created
local path = system.pathForFile(“data21.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

–Handle the applicationExit event to close the db
local function onSystemEvent( event )
if( event.type == “applicationExit” ) then
db:close()
end
end

–Setup the table if it doesn’t exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id, content, content1);]]
print(tablesetup)
db:exec( tablesetup )

local scoreValue = {}
scoreValue[1] = tostring(stageName)
scoreValue[2] = sv

–local tablefill =[[INSERT INTO test VALUES ( 4, ‘stage001’,’]]…scoreValue[2]…[[’);]]
–db:exec( tablefill )

local tableupdate = " UPDATE test SET content1 = ‘"…scoreValue[2]…"’ WHERE id = ‘1’;"
print(tableupdate)
db:exec( tableupdate )

end [import]uid: 94613 topic_id: 17012 reply_id: 63982[/import]