Accessing an SQLite table on the phone

Betlic · February 23, 2015, 12:07am

This is the partial code from the sample from Hardware–>SQLite. Once a table called ‘test’ is created, is this code all that is needed to access the table for the first time on a phone? In the simulator it runs fine, but on the phone for the first time I get an error, “Runtime error, no such table found.” The table exists and has already been created, Ive looked in the Sandbox and I see it.

require “sqlite3”
local path = system.pathForFile(“data.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

local function onSystemEvent( event )
        if( event.type == “applicationExit” ) then
            db:close()
        end
end

for row in db:nrows(“SELECT * FROM test”) do
local text = row.content…" "…row.content2
local t = display.newText(text, 20, 120 + (20 * row.id), native.systemFont, 16)
t:setFillColor(1,0,1)
end

Runtime:addEventListener( “system”, onSystemEvent )

Do I need to have db:exec in here at some point?

Thanks in advance.

doubleslashdesign · February 23, 2015, 2:44am

No you should not need to use db:exec - only because you are using the for loop for row in db:nrows(“SELECT * FROM test”) do

lets try a little debugging.

Runtime error, no such table found - could mean it is not looking in the correct place than where it was created.

ResourceDirectory -vs- DocumentsDirectory…

check the Error code after your open statement

local dbErrorCode = 0

local path = system.pathForFile(“data.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

dbErrorCode = db:errcode()

if not db:isopen() then

db = nil

print("DBopen Failed ->> ")

end

     if dbErrorCode ~= 0 then
            print("dbErrorCode is ->> ")
            print("db:errcode() = " … dbErrorCode)
        else
            print(“DBOpen Success ->> YES <<-”)
        end

some error codes and info

– error codes and meanings This is lsqlite3 subversion 6, also known as devel-0.6’’.
– http://luasqlite.luaforge.net/lsqlite3.html#numerical error and result codes
–        OK: 0          ERROR: 1       INTERNAL: 2    PERM: 3        ABORT: 4
–        BUSY: 5        LOCKED: 6      NOMEM: 7       READONLY: 8    INTERRUPT: 9
–        IOERR: 10      CORRUPT: 11    NOTFOUND: 12   FULL: 13       CANTOPEN: 14
–        PROTOCOL: 15   EMPTY: 16      SCHEMA: 17     TOOBIG: 18     CONSTRAINT: 19
–        MISMATCH: 20   MISUSE: 21     NOLFS: 22      FORMAT: 24     RANGE: 25
–        NOTADB: 26     ROW: 100       DONE: 101
–        db:errcode()
–        db:error_code()
–        db:errmsg()
–        db:error_message()
– system.ResourceDirectory – returns constant to the App Bundle folder
– system.DocumentsDirectory – returns constant to /Documents folder
– system.TemporaryDirectory – returns constant to /tmp folder

Good luck

larry

Betlic · February 23, 2015, 7:59pm

I get a DBOpen Success ->> YES <<- in the console in the simulator.

I just put in these lines and tested on the phone,

local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]
db:exec( tablesetup )

I get no error on the phone, but no table data. Should I be setting up the loop differently?

Betlic · February 23, 2015, 8:46pm

So I tested out copying the database to the Project folder, renaming system.DocumentsDirectory to system.ResourceDirectory, and this functions fine on the phone.

I understand ResourceDirectory should be read only, which my database is, but what about the future? For some reason the database is not being included in DocumentsDirectory.

doubleslashdesign · February 24, 2015, 1:15am

Actually I have seen this issue in the past. As I recall I copied the file just like you did. But I did not have the issue on the actual device.

So i think when you deploy you will be ok.

If you had an issue i would be surprised.

Larry

Betlic · February 25, 2015, 8:06pm

Thats right. I found out that, at least with the two phones I tested with, that if you build an app with a pre built SQL table in DocumentsDirectory, the app cannot find it and returns an error, ‘no such table.’ So, the table needs to be copied to ResourceDirectory before building, and if it needs to be writable, should be copied when the app first runs to DocumentsDirectory.

doubleslashdesign · February 23, 2015, 2:44am