The short version
The following fails with a syntax error:
local q = "[[UPDATE birdTable SET name='fuggel' WHERE bName='Fringilla coelebs';]]" local status = db:exec( q )
Is there something obviously wrong with this query?
The longer version
I use a (bird) database which is updated with new entries as the app is updated. Most of the lines of code below is assigned to that part. The part where it goes wrong is the last 5 lines:
require "sqlite3" print( "SQLite3 version: " .. sqlite3.version() ) local path = system.pathForFile( "bird\_data.db", system.DocumentsDirectory ) local db = sqlite3.open( path ) local tablesetup = [[CREATE TABLE IF NOT EXISTS birdTable (id INTEGER PRIMARY KEY autoincrement, name, bName, flags, isFav, picture, audio, gps);]] db:exec( tablesetup ) local staticBirdArr = { {"B1", "Parus caeruleus", 0}, {"B2", "Fringilla coelebs", 0}, {"B3", "Acrocephalus dumetorum", 0}, } -- The array holding the sorted/filtered entries local birdArr = {} -- Update SQL table with birds in staticBirdArr (skip existing) for i=1, #staticBirdArr do -- Check if name is already in database local notAlreadyInDB = true for row in db:nrows("SELECT \* FROM birdTable;") do if (row.name == staticBirdArr[i][1]) then notAlreadyInDB = false print("Found " .. row.name .. ", " .. row.bName .. ", " .. row.flags .. ", " .. row.isFav .. " in database") end end -- Name not found - add into db if (notAlreadyInDB) then local insertQuery = [[INSERT INTO birdTable VALUES (NULL, ']] .. staticBirdArr[i][1] ..[[',']] .. staticBirdArr[i][2] ..[[', ']] .. staticBirdArr[i][3] ..[[', '0', '0', '0', '0');]] local status = db:exec( insertQuery ) print("DB query: " .. insertQuery) print("status : " .. status) end end print("\nSORTED BY BINOMIAL NAMES") for row in db:nrows("SELECT \* FROM birdTable ORDER BY bName;") do -- create table at next available array index birdArr[#birdArr+1] = {row.name, row.bName, row.flags, row.isFav} print(row.name .. ", " .. row.bName .. ", " .. row.flags .. ", " .. row.isFav) end local q = "[[UPDATE birdTable SET name='fuggel' WHERE bName='Fringilla coelebs';]]" print(q) local status = db:exec( q ) print("db UPDATE status = " .. status) print(db:errmsg()) print(db:error\_message())
When I try to update a row in the db I always get an error message.
The complete log from one rund of the above codes looks like this:
SQLite3 version: 3.8.1 DB query: INSERT INTO birdTable VALUES (NULL, 'B1','Parus caeruleus', '0', '0', '0', '0', '0'); status : 0 DB query: INSERT INTO birdTable VALUES (NULL, 'B2','Fringilla coelebs', '0', '0', '0', '0', '0'); status : 0 DB query: INSERT INTO birdTable VALUES (NULL, 'B3','Acrocephalus dumetorum', '0', '0', '0', '0', '0'); status : 0 SORTED BY BINOMIAL NAMES B3, Acrocephalus dumetorum, 0, 0 B2, Fringilla coelebs, 0, 0 B1, Parus caeruleus, 0, 0 [[UPDATE birdTable SET name='fuggel' WHERE bName='Fringilla coelebs';]] db UPDATE status = 1 near "[[UPDATE birdTable SET name='fuggel' WHERE bName='Fringilla coelebs';]": syntax error near "[[UPDATE birdTable SET name='fuggel' WHERE bName='Fringilla coelebs';]": syntax error
(running the app a second time, the log looks slightly different, because then the entries already exist in the db)
But the UPDATE always fails and that is the issue. Waht am I doing wrong?