sqlite3 how to update text

Hi,

 

I try to update the text data but it isn’t update

Here is some code:
 

 

  1. require “sqlite3”
  2. local path = system.pathForFile(“data.db”, system.DocumentsDirectory)
  3. db = sqlite3.open( path )   
  4. local tablesetup = [[CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY autoincrement, text);]]
  5. db:exec( tablesetup )
  6. local t = “Old text”
  7. local q = [[INSERT INTO data VALUES (NULL, ‘]] … t …[[’);]]
  8. db:exec( q )
  9. for row in db:nrows(“SELECT * FROM data WHERE id = 1”) do 
  10.     print(row.text)
  11. end
  12. local t = “New text”
  13. local Update = [[UPDATE data SET text =]] … t … [[WHERE id = 1]]
  14. db:exec(Update)
  15. for row in db:nrows(“SELECT * FROM data WHERE id = 1”) do
  16.     print(row.text)
  17. end

How can i do?

Have you viewed the table you created in a visual tool to confirm?

It looks like there might be an issue near “text” in the create sql. You need to provide type like: text TEXT

Did not test, but that’s my guess.

Cheers.

I didn’t test either but since your first field is auto incrementing you should not need to specify a value for it in the INSERT. So try this perhaps

[lua]local q = [[INSERT INTO data VALUES (’]] … t …[[’);]][/lua]

Your update sql is wrong, you need to put text values in quotes.

so your update should be like this

local Update = [[UPDATE data SET text = ']] .. t .. [[' WHERE id = 1;]]

Ahhh yes, the ever illusive quotes.  Good eye @thassman

Have you viewed the table you created in a visual tool to confirm?

It looks like there might be an issue near “text” in the create sql. You need to provide type like: text TEXT

Did not test, but that’s my guess.

Cheers.

I didn’t test either but since your first field is auto incrementing you should not need to specify a value for it in the INSERT. So try this perhaps

[lua]local q = [[INSERT INTO data VALUES (’]] … t …[[’);]][/lua]

Your update sql is wrong, you need to put text values in quotes.

so your update should be like this

local Update = [[UPDATE data SET text = ']] .. t .. [[' WHERE id = 1;]]

Ahhh yes, the ever illusive quotes.  Good eye @thassman