Error trying to load data from SQLite database

Other
#1

I’m trying to load data into a table from a sqlite table using the following:

local myData = {} local int = 1 --Go through my database selecting all the subcategory name local dbPath = system.pathForFile("EMS\_DB.sqlite", system.DocumentsDirectory) local db = sqlite3.open( dbPath) for row in db:nrows("SELECT \* FROM Pro\_SubCategory") do myData[int].name = row.Sub\_Cat\_Name myData[int].phone = row.Sub\_CatID int = int+1 end db:close()

When I try to run it, I get the following error from the simulator:

No such table Pro_SubCategory

The table does exist in the database and the database is in the project, so I’m not sure if there’s something else I’m missing.  Does the database need to be somewhere specific?

#2

The database file needs to be in the main folder (same as where main.lua is located).

#3

Have you transfered the database file into documents directory before this code executes. (if you have then ignore).

I’m no expert but the code i use … system.ResourceDirectory accesses the db file but if you use system.documentsDirectory, you first must transfer/create the local db.

T.

#4

I ended up moving it to the Resource Directory, but I found a piece of code to move it to the documents directory, so I figured it would work - but apparently not:

 local path = system.pathForFile("EMS\_DB.sqlite", system.DocumentsDirectory ) file = io.open( path, "r" ) if( file == nil )then -- Doesn't Already Exist, So Copy it In From Resource Directory pathSource = system.pathForFile( "EMS\_DB.sqlite", system.ResourceDirectory ) fileSource = io.open( pathSource, "rb" ) contentsSource = fileSource:read( "\*a" ) --Write Destination File in Documents Directory pathDest = system.pathForFile( "EMS\_DB.sqlite", system.DocumentsDirectory ) fileDest = io.open( pathDest, "wb" ) fileDest:write( contentsSource ) -- Done io.close( fileSource ) io.close( fileDest ) end
#5

Here’s my code. 

Hope it works for you.

local path = system.pathForFile( "myDb.db", system.ResourceDirectory ) -- For normal use/operation local db = sqlite3.open( path ) -- Creates the city table local cityTableName = "table\_city" local cityTableColumnList = { state = "state", printable\_name = "printable\_name", numcode = "numcode" } function self:createCityTable() local cityTableSetup = [[CREATE TABLE IF NOT EXISTS]] .. cityTableName .. [[( id INTEGER PRIMARY KEY autoincrement,]] .. cityTableColumnList.state .. [[VARCHAR(80) NOT NULL,]] .. cityTableColumnList.printable\_name .. [[VARCHAR(80) NOT NULL,]] .. cityTableColumnList.numcode .. [[SMALLINT );]] local result = db:exec( cityTableSetup ) return result end function self:getCityList(args) if db:isopen() == false then return false end self:createCityTable() local query local returnData = {} -- print("getCityList" , args.data) if args == nil or args.data == nil then query = "SELECT " .. cityTableColumnList.printable\_name .. " , " .. cityTableColumnList.state .. " , " .. cityTableColumnList.numcode .. " FROM " .. cityTableName .. " ORDER BY " .. cityTableColumnList.printable\_name else query = "SELECT " .. cityTableColumnList.printable\_name .. " , " .. cityTableColumnList.state .. " , " .. cityTableColumnList.numcode .. " FROM " .. cityTableName .. " WHERE " .. cityTableColumnList.numcode .. "='" .. args.data .. "'" .. " ORDER BY " .. cityTableColumnList.printable\_name end for row in db:nrows(query) do -- create table at next available array index local tmpTable = { row.printable\_name, row.state, row.numcode } table.insert(returnData, tmpTable) end return returnData end
#6

Thanks!  Apparently, it was trying to access the file from the documents directory instead of the resource directory that was the issue.

Can you access the documents directory from the emulator?

#7

The database file needs to be in the main folder (same as where main.lua is located).

#8

Have you transfered the database file into documents directory before this code executes. (if you have then ignore).

I’m no expert but the code i use … system.ResourceDirectory accesses the db file but if you use system.documentsDirectory, you first must transfer/create the local db.

T.

#9

I ended up moving it to the Resource Directory, but I found a piece of code to move it to the documents directory, so I figured it would work - but apparently not:

 local path = system.pathForFile("EMS\_DB.sqlite", system.DocumentsDirectory ) file = io.open( path, "r" ) if( file == nil )then -- Doesn't Already Exist, So Copy it In From Resource Directory pathSource = system.pathForFile( "EMS\_DB.sqlite", system.ResourceDirectory ) fileSource = io.open( pathSource, "rb" ) contentsSource = fileSource:read( "\*a" ) --Write Destination File in Documents Directory pathDest = system.pathForFile( "EMS\_DB.sqlite", system.DocumentsDirectory ) fileDest = io.open( pathDest, "wb" ) fileDest:write( contentsSource ) -- Done io.close( fileSource ) io.close( fileDest ) end
#10

Here’s my code. 

Hope it works for you.

local path = system.pathForFile( "myDb.db", system.ResourceDirectory ) -- For normal use/operation local db = sqlite3.open( path ) -- Creates the city table local cityTableName = "table\_city" local cityTableColumnList = { state = "state", printable\_name = "printable\_name", numcode = "numcode" } function self:createCityTable() local cityTableSetup = [[CREATE TABLE IF NOT EXISTS]] .. cityTableName .. [[( id INTEGER PRIMARY KEY autoincrement,]] .. cityTableColumnList.state .. [[VARCHAR(80) NOT NULL,]] .. cityTableColumnList.printable\_name .. [[VARCHAR(80) NOT NULL,]] .. cityTableColumnList.numcode .. [[SMALLINT );]] local result = db:exec( cityTableSetup ) return result end function self:getCityList(args) if db:isopen() == false then return false end self:createCityTable() local query local returnData = {} -- print("getCityList" , args.data) if args == nil or args.data == nil then query = "SELECT " .. cityTableColumnList.printable\_name .. " , " .. cityTableColumnList.state .. " , " .. cityTableColumnList.numcode .. " FROM " .. cityTableName .. " ORDER BY " .. cityTableColumnList.printable\_name else query = "SELECT " .. cityTableColumnList.printable\_name .. " , " .. cityTableColumnList.state .. " , " .. cityTableColumnList.numcode .. " FROM " .. cityTableName .. " WHERE " .. cityTableColumnList.numcode .. "='" .. args.data .. "'" .. " ORDER BY " .. cityTableColumnList.printable\_name end for row in db:nrows(query) do -- create table at next available array index local tmpTable = { row.printable\_name, row.state, row.numcode } table.insert(returnData, tmpTable) end return returnData end
#11

Thanks!  Apparently, it was trying to access the file from the documents directory instead of the resource directory that was the issue.

Can you access the documents directory from the emulator?