SQLite Update statement works in simulator. Not On device

Solar2D General Questions/Discussion
#1

I am not sure why, but i am trying to use the following SQLite statement to update a record in my database.

this works just fine on all the simulators. but when i compile to my Android Device (a motorola Xoom, with daily build 655) the update statement doesnt seem to work at all.

I am new to working with databases here is what I have:

local defaultValue = [[UPDATE collection SET currentStage = ']] .. newStage .. [[' WHERE keyid = ']] .. rowCount .. [[';]]  
  
db:exec( defaultValue );

any suggestions [import]uid: 67604 topic_id: 17444 reply_id: 317444[/import]

#2

You need to make sure you have the Copy piece of code at start of App launch to copy SQLlite DB from Resource directory to Documents directory — having DB in Resource directory is Read-only on device … in order to have INSERT / UPDATE / DELETE … you need to have DB in Documents directory —

If you do not need to copy … you can have code to create table structure and data via code which will create DB in Documents directory.

Let me know if you have further questions or if this makes sense.

[import]uid: 10965 topic_id: 17444 reply_id: 66271[/import]

#3

Thanks for the insight, it at least gives me something to look into.

I don’t have a copy code in place to move my database file. however here is how I am initiating my database:

require "sqlite3"  
  
local path = system.pathForFile("mydatabase.db", system.DocumentsDirectory)  
  
db = sqlite3.open( path )

So I believe I am creating the database in the right location and dont need to copy it.

here is the code for the specific table i am creating where this doesn’t take effect:

local tablesetup = [[CREATE TABLE IF NOT EXISTS collection (keyid INTEGER PRIMARY KEY,numCollected INTEGER not null,lastCollected DATE,currentStage INTEGER not null,currentStageStartDate DATE,stage1Pic varchar(20) not null,stage1Title varchar(20) not null,stage1Desc varchar(20) not null,stage2Pic varchar(20) not null,stage2Title varchar(20) not null,stage2Desc varchar(20) not null,stage3Pic varchar(20) not null,stage3Title varchar(20) not null,stage3Desc varchar(20) not null,stage4Pic varchar(20) not null,stage4Title varchar(20) not null,stage4Family varchar(20) not null,stage4Genus varchar(20) not null,stage4Spec varchar(20) not null,stage4Desc varchar(20) not null,stage4URL varchar(20) not null,stage4Audio varchar(20) not null);]]  
  
db:exec( tablesetup )

So i am wondering if i am creating the table correctly, again it all works in the simulators…

So in the code where i do the update, newStage is a number as well as my rowCount…

[code]
local defaultValue = [[UPDATE collection SET currentStage = ‘]] … newStage … [[’ WHERE keyid = ‘]] … rowCount … [[’;]]

db:exec( defaultValue );
[/code] [import]uid: 67604 topic_id: 17444 reply_id: 66275[/import]

#4

A couple of thoughts since you’re creating TABLE and not copying DB:

1] Datatype of varchar (I do not think exists in SQLite) – it will translate to TEXT datatype anyway

http://www.sqlite.org/datatype3.html

Try this:

local tablesetup = "CREATE TABLE tableCollection (keyid INTEGER PRIMARY KEY,currentStage INTEGER NOT NULL,stage1Pic TEXT)"  
   
db:exec( tablesetup )

Small test table — I changed “collection” table name to tableCollection because sometimes on devices keywords can cause conflicts

…then you need to INSERT (this is what I think you are really missing — you cannot run an UPDATE without any data)

local defaultValue = "INSERT INTO tableCollection (keyid, currentStage) VALUES (1,1)"  
   
db:exec( defaultValue );

…then run your update and let me know —

local defaultValue = "UPDATE tableCollection SET currentStage = ".. tostring(newStage) .. " WHERE keyid = " .. tostring(rowCount)  
   
db:exec( defaultValue );

[import]uid: 10965 topic_id: 17444 reply_id: 66300[/import]

#5

Also, be mindful that when you are working with strings in SQL you need single quotes around the string. If you exclude this it will not work on the device as expected. [import]uid: 66859 topic_id: 17444 reply_id: 66592[/import]

#6

thanks for your insight, I finally figured out my problem… though i don’t understand WHY its a problem.

here is the functions i used that FAILED to update on the device, but worked in the simulator:

 function applyUpdateToRow(tempKey,tempNewStage)  
 -- tableUpdate = [[UPDATE collection SET currentStage = ']]..newStage..[[' WHERE keyid = ']]..tempRow.keyid..[[';]]  
 local tempSQLString = "UPDATE collection SET currentStage = '"..tempNewStage.."' WHERE keyid = '"..tempKey.."'"  
 db:exec(tempSQLString);  
   
 -- tableUpdate = [[UPDATE collection SET currentStageStartDate = ']].. os.time( t ) ..[[' WHERE keyid = ']] .. tempRow.keyid .. [[';]]  
 local tempSQLString = "UPDATE collection SET currentStageStartDate = '"..os.time( t ).."' WHERE keyid = '"..tempKey.."'"  
 db:exec(tempSQLString);  
  
 end  
  
 function updateCollectionRow()  
  
 for tempRow in db:nrows("SELECT \* FROM collection WHERE numCollected \> 0 AND currentStage \< 4") do  
  
 local timeCompare = (os.time( t ) - tempRow.currentStageStartDate)  
 newStage = tempRow.currentStage +1  
  
 --this Update fails on the Device, works great in the Simulator  
 applyUpdateToRow(tempRow.keyid,newStage)  
  
 end   
  
  
 --this UPDATE WORKS but its outside the database "for/do" select loop  
 local defaultValue = [[UPDATE collection SET currentStage = '3' WHERE keyid='1';]]  
 db:exec( defaultValue );  
 local defaultValue = [[UPDATE collection SET currentStageStartDate = ']].. os.time( t ) ..[[' WHERE keyid='1';]]  
 db:exec( defaultValue );   
  
 end

Now what i eventually figured out with doing a straight update with hard coded values instead of variables is that… the update would not work INSIDE the “For/Do” database select loop.

So i did a bunch of dirty BS and dumped everything to a variable and did all my updates outside of the Select “For/Do” loop and… it works just fine.

here is the code that works on the Device and in the Simulator:

 function updateCollectionRow()  
  
 dataRow = {}  
 dataRowCount = 0  
  
 for tempRow in db:nrows("SELECT \* FROM collection WHERE numCollected \> 0 AND currentStage \< 4") do  
 dataRowCount = dataRowCount +1  
 dataRow[dataRowCount] = tempRow -- Save everything to an array to get out of this "for/do" loop  
 end   
  
 for x = 1, dataRowCount do -- do the update in a loop outside of the "for/do" database select loop  
 -- NOW THIS UPDATE WORKS on the DEVICE as well as in the simulator....   
 applyUpdateToRow(dataRow[x])  
 print ("Updating Row: ",x)  
 end  
  
 -- this one still works  
 local defaultValue = [[UPDATE collection SET currentStage = '3' WHERE keyid='1';]]  
 db:exec( defaultValue );  
 local defaultValue = [[UPDATE collection SET currentStageStartDate = ']].. os.time( t ) ..[[' WHERE keyid='1';]]  
 db:exec( defaultValue );   
  
 end  
  
 function applyUpdateToRow(tempDataRow)  
 newStage = tempDataRow.currentStage +1  
  
 -- tableUpdate = [[UPDATE collection SET currentStage = ']]..newStage..[[' WHERE keyid = ']]..tempRow.keyid..[[';]]  
 local tempSQLString = "UPDATE collection SET currentStage = '"..newStage.."' WHERE keyid = '"..tempDataRow.keyid.."'"  
 db:exec(tempSQLString);  
   
 -- tableUpdate = [[UPDATE collection SET currentStageStartDate = ']].. os.time( t ) ..[[' WHERE keyid = ']] .. tempRow.keyid .. [[';]]  
 local tempSQLString = "UPDATE collection SET currentStageStartDate = '"..os.time( t ).."' WHERE keyid = '"..tempDataRow.keyid.."'"  
 db:exec(tempSQLString);  
  
 end

Anyone have any insight as to why this is a problem the initial way i was trying it?? [import]uid: 67604 topic_id: 17444 reply_id: 66684[/import]