Sqlite3: failing to update db after upgrade from 2100 to 2189

Getting Started Newbie Questions
#1

Good evening,

I’m working on an app that’s heavily dependent on sqlite3. Since I upgraded to build 2189, the Corona simulator on Windows is displaying strange behavior. When I first launch the app, and a new database is copied to the system.documentsdirectory, sql (specifically an UPDATE) works fine. However when I relaunch the simulator, sqlite UPDATE no longer does anything. However, Select still functions.

It’s almost as if the database has become read-only. I’ve already added the “wb” and “rb” arguments, so that can’t be it. Also, I can manually open the sqlite database in a manager and update that way.

When I run it on my mac, it also works fine.

Has anyone else encountered this?

#2

same… any solution?

#3

Nope, nothing yet. Not even an acknowledgement.

#4

Can you put together a test case including your config.lua and build.settings and file a bug report please?

Thanks

Rob

#5

I have some test updating a database sqlite

  1. local file
  2. local sqlite3 = require “sqlite3”
  3. local db
  4. local level = 100
  5. local diff = “hello”
  6. local path=system.pathForFile(“data.db”, system.DocumentsDirectory)
  7. file = io.open( path )
  8. if( file == nil )then
  9.     db=sqlite3.open(path)
  10.     local create_info = [[CREATE TABLE IF NOT EXISTS player (id INTEGER PRIMARY KEY,level,diff);]]
  11.     db:exec (create_info)
  12.     print(create_info)
  13.     
  14.     local insert_Data =[[INSERT INTO player VALUES (NULL,]] … level…[[,’]]…diff…[[’);]]
  15.     db:exec(insert_Data)
  16.     print(insert_Data)
  17. else
  18.     db = sqlite3.open(path)
  19.     
  20.     for row in db:nrows(“SELECT * FROM player WHERE id = 1”) do
  21.         print(row.level,row.diff)
  22.     end
  23. end
  24. timer.performWithDelay(2000,function ()
  25.     
  26.     level = 1123
  27.     diff = “hello_world_2”
  28.     pointupdate = [[UPDATE player SET level =]]…level…[[,diff=’]]…diff…[[’ WHERE rowid =1;]]
  29.     db:exec(pointupdate)
  30.     print(pointupdate)
  31.     timer.performWithDelay(2000,function ()
  32.         for row in db:nrows(“SELECT * FROM player WHERE id = 1”) do
  33.             print(row.level,row.diff)
  34.         end
  35.     end,1)
  36.     
  37. end,1)
     

in this case 1st open in the app works and update maybe cause of the 1st making of the db, but after reloading in and change the value of “hello_world_2” to “hello_world_3” it wont update any more, 1st update is working but after next update will not work.

all config and build are default and no changes

#6

seems the file = io.open( path ) will make the db no update i dont really know why, but if i remove file = io.open( path ) and just update the existing db it will work. so my problem now is how will i can create db in 1st open of the app

#7

I understand why.  You are opening the file twice.  You don’t need to do io.open() to open the database.  The sqlite3.open() takes care of that for you.  The only time you would want to open the database using io.open() is if you’re copying the database from system.ResourceDirectory to system.DocumentsDirectory so that it can be a writable database.

Rob

#8

thanks rob, from past months I’ve beed using io.open for creating and insterting data in db, so during 1st open of the app it will check db then create and insert data. any alternative way during the 1st open the app ?

#9

You can use the io.open() method to detect if the file exists, just close it before you call db.open().

Rob

#10

same… any solution?

#11

Nope, nothing yet. Not even an acknowledgement.

#12

Can you put together a test case including your config.lua and build.settings and file a bug report please?

Thanks

Rob

#13

I have some test updating a database sqlite

  1. local file
  2. local sqlite3 = require “sqlite3”
  3. local db
  4. local level = 100
  5. local diff = “hello”
  6. local path=system.pathForFile(“data.db”, system.DocumentsDirectory)
  7. file = io.open( path )
  8. if( file == nil )then
  9.     db=sqlite3.open(path)
  10.     local create_info = [[CREATE TABLE IF NOT EXISTS player (id INTEGER PRIMARY KEY,level,diff);]]
  11.     db:exec (create_info)
  12.     print(create_info)
  13.     
  14.     local insert_Data =[[INSERT INTO player VALUES (NULL,]] … level…[[,’]]…diff…[[’);]]
  15.     db:exec(insert_Data)
  16.     print(insert_Data)
  17. else
  18.     db = sqlite3.open(path)
  19.     
  20.     for row in db:nrows(“SELECT * FROM player WHERE id = 1”) do
  21.         print(row.level,row.diff)
  22.     end
  23. end
  24. timer.performWithDelay(2000,function ()
  25.     
  26.     level = 1123
  27.     diff = “hello_world_2”
  28.     pointupdate = [[UPDATE player SET level =]]…level…[[,diff=’]]…diff…[[’ WHERE rowid =1;]]
  29.     db:exec(pointupdate)
  30.     print(pointupdate)
  31.     timer.performWithDelay(2000,function ()
  32.         for row in db:nrows(“SELECT * FROM player WHERE id = 1”) do
  33.             print(row.level,row.diff)
  34.         end
  35.     end,1)
  36.     
  37. end,1)
     

in this case 1st open in the app works and update maybe cause of the 1st making of the db, but after reloading in and change the value of “hello_world_2” to “hello_world_3” it wont update any more, 1st update is working but after next update will not work.

all config and build are default and no changes

#14

seems the file = io.open( path ) will make the db no update i dont really know why, but if i remove file = io.open( path ) and just update the existing db it will work. so my problem now is how will i can create db in 1st open of the app

#15

I understand why.  You are opening the file twice.  You don’t need to do io.open() to open the database.  The sqlite3.open() takes care of that for you.  The only time you would want to open the database using io.open() is if you’re copying the database from system.ResourceDirectory to system.DocumentsDirectory so that it can be a writable database.

Rob

#16

thanks rob, from past months I’ve beed using io.open for creating and insterting data in db, so during 1st open of the app it will check db then create and insert data. any alternative way during the 1st open the app ?

#17

You can use the io.open() method to detect if the file exists, just close it before you call db.open().

Rob