Trouble Inserting, Updating and Deleting SQLlite Database

[gduff]

I can query data in an SQLlite database using the corona SDK simulator but can not insert or delete data. I am running the latest public release of the Corona SDK. Here is the code. It compiles fine but the insert command simply will not work. The database name is mydatabase.sqlite and the table I am querying, trying to insert, update and delete is called projects with fields: id, name, category and rating.     It auto indexes so you don’t need to insert the primary key.  Here is the code for the main.lua that I am currently working on:

    local sqlite3 = require “sqlite3”

    local function setUpDatabase(dbName)
    
    local path = system.pathForFile( dbName, system.DocumentsDirectory )
    local file = io.open( path, “r” )
 
    if ( file == nil ) then
        – copy the database file if doesn’t exist
        local pathSource     = system.pathForFile( dbName, system.ResourceDirectory )
        local fileSource     = io.open( pathSource, “r” )
        local contentsSource = fileSource:read( “*a” )
                
        local pathDest = system.pathForFile( dbName, system.DocumentsDirectory )
        local fileDest = io.open( pathDest, “w” )
        fileDest:write( contentsSource )
            
        io.close( fileSource )
        io.close( fileDest )
    end

    local gameDB = system.pathForFile(dbName, system.DocumentsDirectory)
    local dbNew = sqlite3.open( gameDB )

    return dbNew

    end

    function loadData()
        local sql = “select * from projects”
        local projects = {}
    
        for a in db:nrows(sql) do
            projects[#projects+1] =
            {
            id = a.id,
            name = a.name,
            category = a.category,
            rating = a.rating
            }
        end
    
        return projects
    end
    
    function insertData(n, c, r)
        local sql = “insert into projects (name, category, rating) values (’” … n … “’, '” … c … "’, " … r … “)”
       db:exec(sql)
    end

    function deleteData(id)
        local sql = "delete from projects where id = " … tostring(id)
        db:exec(sql)
    end

    function updateData(id, col, v)
        local sql = “update projects set " … col … " = '” … v … "’ where id = " … tostring(id)
        db:exec(sql)
    end

    db = setUpDatabase(“mydatabase.sqlite”)
    insertData(“John”,“Human”, 2)

    deleteData(3)
    updateData(4, “name”, “Ralph”)
    --updateData(2, “category”, “Dog”)
    --updateData(5, “rating”, 4)

    local data = loadData()

    for x = 1, #data do
        print (data[x].id, data[x].name, data[x].category, data[x].rating)
    end
 

[paulscottrobson]

What does ‘it does not work’ mean ? Does it return an error, does it apparently ‘work’ but nothing happens ?

A good approach when you have this sort of bug - somewhere - is to try the sample code provided by Corona, and work it outwards towards your code.

[gduff]

Hi Paul,

Thanks for making me focus. No compile issues.   What happens is that it returns the existing rows of the database in the output screen but it will not insert data.

I keep my project files under a directory “c:\cp\projectName”.   When I run the simulator it copies the mydatabase.sql file to the Corona sandbox to a directory like “C:\Users\gduff\AppData\Roaming\Corona Labs\Corona Simulator\Sandbox\Resources-F54E6EE80E7FB9CB23B76A5842B636D3”.

If I run the app once and then make a change to the mysqlplus.sql database that is contained in the c:\cp\projectName folder, close the simulator, open the simulator and runn the app again, the changes are not reflected in the “C:\Users\gduff\AppData\Roaming\Corona Labs\Corona Simulator\Sandbox\Resources-F54E6EE80E7FB9CB23B76A5842B636D3” directory when I check it with the sqlLite manager.

It almost seems as if the Corona SDK is not point to the correct database or some other pointer/location/connectivity issue.  I have tried this on 2 computers and am getting the same results.  Tried on both XP and Windows 7.    Since running the app again does not copy or does not overwrite the sandbox, perhaps the simulator is not able to connect to the database in the resource folder to make an insert, even though it does appear to read (the/some) database which has the records. 

Thanks for your help.  I provided the database structure and can provide the config and build files if that will help.  They are very generic.  I am using the latest public build as I am a newcomer to Corona and just really learning the basics.

Guy

[J_A_Whye]

Guy,

If I run the app once and then make a change to the mysqlplus.sql database that is contained in the c:\cp\projectName folder, close the simulator, open the simulator and runn the app again, the changes are not reflected in the “C:\Users\gduff\AppData\Roaming\Corona Labs\Corona Simulator\Sandbox\Resources-F54E6EE80E7FB9CB23B76A5842B636D3” directory when I check it with the sqlLite manager.

That’s because the setUpDatabase() function you’re using only copies the database file if it doesn’t exist. So when you make changes to your “master” copy of the database it doesn’t get copied down into the project sandbox when the code is run the next time. If you want to make sure that gets copied, either delete that file each time (you can find where it lives in the Simulator – File > Show Project Sandbox and then go into the Documents folder) or change the code like this:

 if ( file == nil ) or true then

…that will make sure the database is copied every time. You’ll want to remove the “or true” part before building for device, however.

As far as the inserts not working, I tried your code and found the same thing – so I changed the insert function by adding these lines after the db:exec(sql) call:

if db:errcode() then print(db:errcode(), db:errmsg()) end

I wanted to see if there was an error in SQLite that wasn’t being passed through to Corona SDK. Sure enough, I got back this:

8    attempt to write a readonly database

What?!

Since the database I’d been using was created on a Mac I decided to create a brand new one on my Windows machine (since that’s where I’m testing) but now when I test I’m getting an error message that says, “database disk image is malformed.”

For my next test I’m going to create the database by code and see what happens – that’s not necessarily a valid workaround if you have a lot of initial data in your app, but it may point to the actual fix.

 Jay

[alexf]

Out of the bucket, this usually happens when some other process is accessing the database( or if the actual db file is readonly or locked ). Does the readonly error happen in the simulator, or the actual device? Also, any chance the db is in a folder that’s shared ( maybe via virtual machine and mac )?

alex

[gduff]

Hi Jay,

I created a database from scratch 2 ways.  One, I created it using the demo version of SQLite Manager.  After that I created the database using the Firefox SQLite Plugin.   No change.  When you say create the database in code, do you mean you’ll execute table create commands in the App?  If you do, I’d be grateful if you’d post the code here.  The sandbox folders seem to be permanently read only.  You can change them in properties but they always change back.  The database itself, from a OS/file standpoint is not read-only.  

Thanks,

Guy

[gduff]

Hi Alex,

As far as I can tell, no other process is accessing the database.  I am doing this on vanilla XP and Win7 based computers and not with Remote Access.  I never leave the SQL Manager open while I am running the app.

Have not built the app for a device yet.  I’ll need to send the output to the screen first in order to test it.  I think I’ll go ahead and do that.  None the less, even if it works in the build, its important to resolve the problem in the simulator because as I use similiar code and databases in the future, I’ll be doing plenty of inserts, updates and deletes and need to test them in the simulator a lot.

You can test it easily if you have a chance and want to.  I’d upload the sqlLite database I created but the forum policy won’t allow the upload it seems.   Its only 4 fields and an auto generated primary key.

Thanks,

Guy

[J_A_Whye]

Guy, here’s the code that creates the database in code:

local function setUpDatabase(dbName) local tableMake = "CREATE TABLE IF NOT EXISTS projects (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,name text,category text,rating integer)" local gameDB = system.pathForFile(dbName, system.DocumentsDirectory) local dbNew = sqlite3.open( gameDB ) dbNew:exec(tableMake) return dbNew end

We’re setting gameDB to the location of the database file (don’t know if it exists or not at this point) and then setting dbNew to the actual database – if sqlite3.open() can’t find the database we’re referencing with gameDB it will create that file for us. Finally, we do a db:exec() call passing in the tableMake variable which contains the SQL needed to create the table. And if you look at that SQL statement, it only creates the table if it doesn’t already exist.

When I use that code the sample works fine and the record is inserted each time I run it. But I still want to find out why an already created database file won’t work. I’m going to try making one with something other than SQLiteManager to see if that makes a difference.

 Jay

[J_A_Whye]

I think it just doesn’t work in the Windows simulator with a pre-existing SQLite database. I do believe the code will work if you build to device, but that doesn’t help with the main testing phase of a project.

I’ve just submitted it as a bug (case 32557).

Until that’s fixed I guess the workaround is to create the database by code inside the app. That’s often not a problem, but could be a killer if your game or app ships with a database full of data.

 Jay

[gduff]

Hello Jay,

Thanks for everything, including submitting the bug case and having such a great course on developing business apps in Corona SDK hosted at Udemy Academy as for me, its the best one on the subject of business apps I’ve found and worth every penny I paid.    I’ve gone cross eyed trying to see where I’m going wrong.  Had about 30 hours into the previous problem that turns out to be a bug on the Windows version.  Now about 10 hours into this, I’m calling uncle.

I’m having some trouble getting the code to create the database in code to work as well.  Here is the code I am using:

    require “sqlite3”  
    
    --setup global vars
    db = nil
        
    local function setUpDatabase(dbName)

        local tableMake = “CREATE TABLE IF NOT EXISTS projects (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,name text,category text,rating integer)”

        local gameDB = system.pathForFile(dbName, system.DocumentsDirectory)
        local dbNew = sqlite3.open( gameDB )
        dbNew:exec(tableMake)
          return dbNew
    end
        
    function loadData()
        local sql = “select * from projects”
        local projects = {}
    
        for a in db:nrows(sql) do
            projects[#projects+1] =
            {
            id = a.id,
            name = a.name,
            category = a.category,
            rating = a.rating
            }
        end
    
        return projects
    end
    
    function insertData(n, c, r)
        local sql = “insert into projects (name, category, rating) values (’” … n … “’, '” … c … "’, " … r … “)”
       db:exec(sql)
    end

    function deleteData(id)
        local sql = "delete from projects where id = " … tostring(id)
        db:exec(sql)
    end

    function updateData(id, col, v)
        local sql = “update projects set " … col … " = '” … v … "’ where id = " … tostring(id)
        db:exec(sql)
    end

    setUpDatabase(“data.sqlite”)
    
    insertData(“John”,“Human”, 8)

   – deleteData(3)
    --updateData(4, “name”, “Ralph”)
    --updateData(2, “category”, “Dog”)
    --updateData(5, “rating”, 4)

    local data = loadData()

    for x = 1, #data do
        print (data[x].id, data[x].name, data[x].category, data[x].rating)
    end
 

The error messages I’m getting are below:

c:\projectSQLite\main.lua:35: attempt to index global ‘db’ (a nil value)
stack traceback:
        c:\projectSQLite\main.lua:35: in function
 ‘insertData’
        c:\projectSQLite\main.lua:50: in main chunk

I also tried doing it the way you showed in the Business Apps Using Corona SDK tutorial like this:

    require “sqlite3”  – when creating database in code

    --setup global vars
    db = nil  – used only when creating the database in code
        
    local function setUpDatabase(dbName)
    
        local path = system.pathForFile( dbname, system.DocumentsDirectory )
        db = sqlite3.open( path )
    
        local tablesetup = “CREATE TABLE IF NOT EXISTS projects (id integer primary key autoincrement, name text, category text, rating integer);”
        db:exec( tablesetup )
    end
        
    function loadData()
        local sql = “select * from projects”
        local projects = {}
    
        for a in db:nrows(sql) do
            projects[#projects+1] =
            {
            id = a.id,
            name = a.name,
            category = a.category,
            rating = a.rating
            }
        end
    
        return projects
    end
    
    function insertData(n, c, r)
        local sql = “insert into projects (name, category, rating) values (’” … n … “’, '” … c … "’, " … r … “)”
       db:exec(sql)
    end

    function deleteData(id)
        local sql = "delete from projects where id = " … tostring(id)
        db:exec(sql)
    end

    function updateData(id, col, v)
        local sql = “update projects set " … col … " = '” … v … "’ where id = " … tostring(id)
        db:exec(sql)
    end

    setUpDatabase(“data.sqlite”)  – used only when creating the database in code
    
    insertData(“John”,“Human”, 8)

   – deleteData(3)
    --updateData(4, “name”, “Ralph”)
    --updateData(2, “category”, “Dog”)
    --updateData(5, “rating”, 4)

    local data = loadData()

    for x = 1, #data do
        print (data[x].id, data[x].name, data[x].category, data[x].rating)
    end

The error messages are pretty much the same:

Runtime error
c:\projectSQLite\main.lua:12: attempt to index global ‘db’ (a nil value)

stack traceback:
       c:\projectSQLite\main.lua:12: in function  ‘setUpDatabase’
       c:\projectSQLite\main.lua:47: in main chunk

[J_A_Whye]

In that first example you showed you’re passing back a value from the setUpDatabase() function, which means this line:

setUpDatabase("data.sqlite")

Should instead be like this:

db = setUpDatabase("data.sqlite")

Without that the db variable is never getting set so when you try to use it bad things happen.

Jay

[paulscottrobson]

What does ‘it does not work’ mean ? Does it return an error, does it apparently ‘work’ but nothing happens ?

A good approach when you have this sort of bug - somewhere - is to try the sample code provided by Corona, and work it outwards towards your code.

[gduff]

Hi Paul,

Thanks for making me focus. No compile issues.   What happens is that it returns the existing rows of the database in the output screen but it will not insert data.

I keep my project files under a directory “c:\cp\projectName”.   When I run the simulator it copies the mydatabase.sql file to the Corona sandbox to a directory like “C:\Users\gduff\AppData\Roaming\Corona Labs\Corona Simulator\Sandbox\Resources-F54E6EE80E7FB9CB23B76A5842B636D3”.

If I run the app once and then make a change to the mysqlplus.sql database that is contained in the c:\cp\projectName folder, close the simulator, open the simulator and runn the app again, the changes are not reflected in the “C:\Users\gduff\AppData\Roaming\Corona Labs\Corona Simulator\Sandbox\Resources-F54E6EE80E7FB9CB23B76A5842B636D3” directory when I check it with the sqlLite manager.

It almost seems as if the Corona SDK is not point to the correct database or some other pointer/location/connectivity issue.  I have tried this on 2 computers and am getting the same results.  Tried on both XP and Windows 7.    Since running the app again does not copy or does not overwrite the sandbox, perhaps the simulator is not able to connect to the database in the resource folder to make an insert, even though it does appear to read (the/some) database which has the records. 

Thanks for your help.  I provided the database structure and can provide the config and build files if that will help.  They are very generic.  I am using the latest public build as I am a newcomer to Corona and just really learning the basics.

Guy

[J_A_Whye]

Guy,

If I run the app once and then make a change to the mysqlplus.sql database that is contained in the c:\cp\projectName folder, close the simulator, open the simulator and runn the app again, the changes are not reflected in the “C:\Users\gduff\AppData\Roaming\Corona Labs\Corona Simulator\Sandbox\Resources-F54E6EE80E7FB9CB23B76A5842B636D3” directory when I check it with the sqlLite manager.

That’s because the setUpDatabase() function you’re using only copies the database file if it doesn’t exist. So when you make changes to your “master” copy of the database it doesn’t get copied down into the project sandbox when the code is run the next time. If you want to make sure that gets copied, either delete that file each time (you can find where it lives in the Simulator – File > Show Project Sandbox and then go into the Documents folder) or change the code like this:

 if ( file == nil ) or true then

…that will make sure the database is copied every time. You’ll want to remove the “or true” part before building for device, however.

As far as the inserts not working, I tried your code and found the same thing – so I changed the insert function by adding these lines after the db:exec(sql) call:

if db:errcode() then print(db:errcode(), db:errmsg()) end

I wanted to see if there was an error in SQLite that wasn’t being passed through to Corona SDK. Sure enough, I got back this:

8    attempt to write a readonly database

What?!

Since the database I’d been using was created on a Mac I decided to create a brand new one on my Windows machine (since that’s where I’m testing) but now when I test I’m getting an error message that says, “database disk image is malformed.”

For my next test I’m going to create the database by code and see what happens – that’s not necessarily a valid workaround if you have a lot of initial data in your app, but it may point to the actual fix.

 Jay

[alexf]

Out of the bucket, this usually happens when some other process is accessing the database( or if the actual db file is readonly or locked ). Does the readonly error happen in the simulator, or the actual device? Also, any chance the db is in a folder that’s shared ( maybe via virtual machine and mac )?

alex

[gduff]

Hi Jay,

I created a database from scratch 2 ways.  One, I created it using the demo version of SQLite Manager.  After that I created the database using the Firefox SQLite Plugin.   No change.  When you say create the database in code, do you mean you’ll execute table create commands in the App?  If you do, I’d be grateful if you’d post the code here.  The sandbox folders seem to be permanently read only.  You can change them in properties but they always change back.  The database itself, from a OS/file standpoint is not read-only.  

Thanks,

Guy

[gduff]

Hi Alex,

As far as I can tell, no other process is accessing the database.  I am doing this on vanilla XP and Win7 based computers and not with Remote Access.  I never leave the SQL Manager open while I am running the app.

Have not built the app for a device yet.  I’ll need to send the output to the screen first in order to test it.  I think I’ll go ahead and do that.  None the less, even if it works in the build, its important to resolve the problem in the simulator because as I use similiar code and databases in the future, I’ll be doing plenty of inserts, updates and deletes and need to test them in the simulator a lot.

You can test it easily if you have a chance and want to.  I’d upload the sqlLite database I created but the forum policy won’t allow the upload it seems.   Its only 4 fields and an auto generated primary key.

Thanks,

Guy

[J_A_Whye]

Guy, here’s the code that creates the database in code:

local function setUpDatabase(dbName) local tableMake = "CREATE TABLE IF NOT EXISTS projects (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,name text,category text,rating integer)" local gameDB = system.pathForFile(dbName, system.DocumentsDirectory) local dbNew = sqlite3.open( gameDB ) dbNew:exec(tableMake) return dbNew end

We’re setting gameDB to the location of the database file (don’t know if it exists or not at this point) and then setting dbNew to the actual database – if sqlite3.open() can’t find the database we’re referencing with gameDB it will create that file for us. Finally, we do a db:exec() call passing in the tableMake variable which contains the SQL needed to create the table. And if you look at that SQL statement, it only creates the table if it doesn’t already exist.

When I use that code the sample works fine and the record is inserted each time I run it. But I still want to find out why an already created database file won’t work. I’m going to try making one with something other than SQLiteManager to see if that makes a difference.

 Jay

[J_A_Whye]

I think it just doesn’t work in the Windows simulator with a pre-existing SQLite database. I do believe the code will work if you build to device, but that doesn’t help with the main testing phase of a project.

I’ve just submitted it as a bug (case 32557).

Until that’s fixed I guess the workaround is to create the database by code inside the app. That’s often not a problem, but could be a killer if your game or app ships with a database full of data.

 Jay

[gduff]

Hello Jay,

Thanks for everything, including submitting the bug case and having such a great course on developing business apps in Corona SDK hosted at Udemy Academy as for me, its the best one on the subject of business apps I’ve found and worth every penny I paid.    I’ve gone cross eyed trying to see where I’m going wrong.  Had about 30 hours into the previous problem that turns out to be a bug on the Windows version.  Now about 10 hours into this, I’m calling uncle.

I’m having some trouble getting the code to create the database in code to work as well.  Here is the code I am using:

    require “sqlite3”  
    
    --setup global vars
    db = nil
        
    local function setUpDatabase(dbName)

        local tableMake = “CREATE TABLE IF NOT EXISTS projects (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,name text,category text,rating integer)”

        local gameDB = system.pathForFile(dbName, system.DocumentsDirectory)
        local dbNew = sqlite3.open( gameDB )
        dbNew:exec(tableMake)
          return dbNew
    end
        
    function loadData()
        local sql = “select * from projects”
        local projects = {}
    
        for a in db:nrows(sql) do
            projects[#projects+1] =
            {
            id = a.id,
            name = a.name,
            category = a.category,
            rating = a.rating
            }
        end
    
        return projects
    end
    
    function insertData(n, c, r)
        local sql = “insert into projects (name, category, rating) values (’” … n … “’, '” … c … "’, " … r … “)”
       db:exec(sql)
    end

    function deleteData(id)
        local sql = "delete from projects where id = " … tostring(id)
        db:exec(sql)
    end

    function updateData(id, col, v)
        local sql = “update projects set " … col … " = '” … v … "’ where id = " … tostring(id)
        db:exec(sql)
    end

    setUpDatabase(“data.sqlite”)
    
    insertData(“John”,“Human”, 8)

   – deleteData(3)
    --updateData(4, “name”, “Ralph”)
    --updateData(2, “category”, “Dog”)
    --updateData(5, “rating”, 4)

    local data = loadData()

    for x = 1, #data do
        print (data[x].id, data[x].name, data[x].category, data[x].rating)
    end
 

The error messages I’m getting are below:

c:\projectSQLite\main.lua:35: attempt to index global ‘db’ (a nil value)
stack traceback:
        c:\projectSQLite\main.lua:35: in function
 ‘insertData’
        c:\projectSQLite\main.lua:50: in main chunk

I also tried doing it the way you showed in the Business Apps Using Corona SDK tutorial like this:

    require “sqlite3”  – when creating database in code

    --setup global vars
    db = nil  – used only when creating the database in code
        
    local function setUpDatabase(dbName)
    
        local path = system.pathForFile( dbname, system.DocumentsDirectory )
        db = sqlite3.open( path )
    
        local tablesetup = “CREATE TABLE IF NOT EXISTS projects (id integer primary key autoincrement, name text, category text, rating integer);”
        db:exec( tablesetup )
    end
        
    function loadData()
        local sql = “select * from projects”
        local projects = {}
    
        for a in db:nrows(sql) do
            projects[#projects+1] =
            {
            id = a.id,
            name = a.name,
            category = a.category,
            rating = a.rating
            }
        end
    
        return projects
    end
    
    function insertData(n, c, r)
        local sql = “insert into projects (name, category, rating) values (’” … n … “’, '” … c … "’, " … r … “)”
       db:exec(sql)
    end

    function deleteData(id)
        local sql = "delete from projects where id = " … tostring(id)
        db:exec(sql)
    end

    function updateData(id, col, v)
        local sql = “update projects set " … col … " = '” … v … "’ where id = " … tostring(id)
        db:exec(sql)
    end

    setUpDatabase(“data.sqlite”)  – used only when creating the database in code
    
    insertData(“John”,“Human”, 8)

   – deleteData(3)
    --updateData(4, “name”, “Ralph”)
    --updateData(2, “category”, “Dog”)
    --updateData(5, “rating”, 4)

    local data = loadData()

    for x = 1, #data do
        print (data[x].id, data[x].name, data[x].category, data[x].rating)
    end

The error messages are pretty much the same:

Runtime error
c:\projectSQLite\main.lua:12: attempt to index global ‘db’ (a nil value)

stack traceback:
       c:\projectSQLite\main.lua:12: in function  ‘setUpDatabase’
       c:\projectSQLite\main.lua:47: in main chunk