SQLite db returning nil

Solar2D General Questions/Discussion
#1

I’m trying to return data from my db (created using Firefox SQLite Manger) but only receive a nil value.
I have successfully compile and run the sample app, but when I apply the same code to my db I get:

Runtime error
bad argument #1 to ‘_newText’ (string expected, got nil)
stack traceback:
[C]: ?
[C]: in function ‘_newText’
?: in function ‘newText’
/Users/bgb07a/Desktop/Corona SQLite/main.lua:23: in main chunk
Runtime error: bad argument #1 to ‘_newText’ (string expected, got nil)
stack traceback:
[C]: ?
[C]: in function ‘_newText’
?: in function ‘newText’
/Users/bgb07a/Desktop/Corona SQLite/main.lua:23: in main chunk

in the code:
for row in db:nrows(“SELECT * FROM zipcode”) do
local text = row.content
local t = display.newText(text, 20, 120, native.systemFont, 16)
t:setTextColor(255,0,255)
end

I’ve tried several different databases (all created with SQL Manager) and different sql calls, but always get ‘nil’.

Does anyone have any advise? If I put a counter in my for loop, it does read each of the records, but always returns nil.

Thank you for any help/advise!
Brian [import]uid: 6551 topic_id: 6576 reply_id: 306576[/import]

#2

Hi Brian,

I was getting the same error message and it looks like you were following the SQLITE example provided here. I couldn’t make much sense of what it was doing so I posted in the forums and J.A. Whye gave me a really helpful response and provided me with some really useful code. Find my original thread here:

http://developer.anscamobile.com/forum/2011/02/11/director-class-sqlite

This really helped me wrap my head around how to query a database. The sample provided from Corona throws values into an array and gets confusing. [import]uid: 31262 topic_id: 6576 reply_id: 22964[/import]

#3

Thank you aaaron,
Your link helped, the code is no longer throwing an error, but I’m still getting a nil from the db. I’ve tried 4 different dbs and all return the same result.

We’re making progress, just not there yet! [import]uid: 6551 topic_id: 6576 reply_id: 22984[/import]

#4

Do you have any code that you can post? Perhaps some of the others can help you as I’m pretty new at this myself. Maybe it’s the location of your database. I had to change the code to look at system.DocumentsDirectory instead as that is where my database was located.

--Include sqlite require "sqlite3" local path = system.pathForFile("agm.sqlite", system.DocumentsDirectory) db = sqlite3.open( path ) [import]uid: 31262 topic_id: 6576 reply_id: 22989[/import]

#5

Thank you for the suggestion aaaron… should expect people to read my mind!

I’m to the point now where I don’t receive any errors, but still getting a nil response no matter which database or field I try to read:

– 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(“zipcodes.sqlite”, system.ResourceDirectory) --find the database 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

print ("version "…sqlite3.version())
print ("db path "…path)
local output
local sql = “SELECT * FROM zipcode limit 1”
for a in db:nrows(sql) do
output = a[state]
print(col_4)
end

– setup the system listener to catch applicationExit
Runtime:addEventListener ( “system”, onSystemEvent)
[import]uid: 6551 topic_id: 6576 reply_id: 22990[/import]

#6

Found the solution:

I was treating the data I was receiving like an array instead of just straight data.

Change the
output = a[state]
to
output = a.state

and it works! [import]uid: 6551 topic_id: 6576 reply_id: 23309[/import]

#7

Just because I’m so new at this too, can you clarify something for me? Is state in your code the name of a database column? i.e. zipcode.state? [import]uid: 31262 topic_id: 6576 reply_id: 23316[/import]

#8

Yes, I have zip, city, and state as columns.

I have created a short tutorial on youtube if your interested. It is uploading as I type this!

http://www.youtube.com/drbburton [import]uid: 6551 topic_id: 6576 reply_id: 23331[/import]