I’ve been fighting SQLite in Corona a lot lately.
So far I’ve determined that if I create and write to a database then immediately read it back in (like the demo in the samples) it works fine. But if the file has been closed and reopened, or I try to do a straight open of a database, then a nil is returned, no matter which field you try to display.
The interesting this is that it is reading the rows (I can tell by the number of nil displayed in terminal), but not returning the fields values.
Can anyone share their code of a working read of a previously created db or is this a bug?
Here is a sample code showing my problem (based off of the sqlite sample code):
– Demonstration of working with SQLite and Corona for iOS & Android devices
– by Brian G. Burton, Ed.D. http://www.BurtonsMediaGroup.com/blog
– include sqlite so that we can work with it…
require “sqlite3”
– open the database (if it doesn’t exist, it will be created)
local path = system.pathForFile(“data.sqlite”, system.ResourceDirectory) --find the db in the same folder as main.lua
db = sqlite3.open( path )
– handle the applicationExit event to close the db
local function onSystemEvent( event )
if( event.type == “applicationExit”) then
db:close()
end
end
–Setup the table if it doesn’t exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]
print(tablesetup)
db:exec( tablesetup )
–Add rows with a auto index in ‘id’. You don’t need to specify a set of values because we’re populating all of them
local testvalue = {}
testvalue[1] = ‘Hello’
testvalue[2] = ‘World’
testvalue[3] = ‘Lua’
local tablefill =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[1]…[[’,’]]…testvalue[2]…[[’);]]
local tablefill2 =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[2]…[[’,’]]…testvalue[1]…[[’);]]
local tablefill3 =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[1]…[[’,’]]…testvalue[3]…[[’);]]
db:exec( tablefill )
db:exec( tablefill2 )
db:exec( tablefill3 )
db:close()
print(“write completed. DB closed”)
– reopen database for read
db = sqlite3.open( path )
print ("version "…sqlite3.version())
print ("db path "…path)
local output
local sql = “SELECT * FROM test”
for a in db:nrows(sql) do
output = a[content]
print(output)
end
– setup the system listener to catch applicationExit
Runtime:addEventListener ( “system”, onSystemEvent)
[import]uid: 6551 topic_id: 6681 reply_id: 306681[/import]