SQLite Opening/creating database

Solar2D General Questions/Discussion
1

Hi, just installed Beta 3 and I am trying to create my first database (sample shows only memory creation). unfortunately the code below is not working. First I create the database manually (using Firefox SQLite Manager) and tried to read data - didn’t work. After that, I tried only to use the open command (which suppose to create a new database if the named one does not exist), but it didn’t create anything. Any ideas why? thanks!

require “sqlite3”
local db = sqlite3.open(‘test.sqlite’)

for row in db:nrows(“SELECT * FROM temples”) do
local t = display.newText(row.tName, 20, 30*row.id, null, 16)
t:setTextColor(255,0,255)
end

db:close() – close

Alex [import]uid: 4883 topic_id: 1005 reply_id: 301005[/import]

2

Looks to me like it can’t find your database. Try system.pathForFile().

Here’s a disk based test case that does work:

(in terminal)

sqlite3 test.sqlite3 "create table t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double);"  
sqlite3 test.sqlite3 "insert into t1 (data,num) values ('This is sample data',3);"  
sqlite3 test.sqlite3 "insert into t1 (data,num) values ('More sample data',6);"  
sqlite3 test.sqlite3 "insert into t1 (data,num) values ('Still more sample data',9);"  
sqlite3 test.sqlite3 "select \* from t1"

And the Corona code:

require "sqlite3"  
  
local path = system.pathForFile( "test.sqlite3", system.ResourceDirectory )  
  
local db = sqlite3.open(path)  
  
print( "version " .. sqlite3.version() )  
  
local i = 2  
  
for row in db:nrows("SELECT \* FROM t1") do  
 local t = display.newText(row.data, 20, 30 \* i, null, 16)  
 t:setTextColor(255,0,255)  
 i = i + 1  
end

[import]uid: 54 topic_id: 1005 reply_id: 2440[/import]

3

I have a SQLite database fully populated that I want to use in my app. Where do I put that file so Corona SDK will pick it up?

I’ve done multiple searches in the forum, looked through the docs and API more than once, and can’t find the official answer. Nothing I’ve tried works.

Any hints?

Thanks.

Jay
[import]uid: 9440 topic_id: 1005 reply_id: 22085[/import]

4

Here’s a good sample:
http://developer.anscamobile.com/content/data-storage

If you already have your SQLite db you can place it in the same folder as your main.lua file so it’s easy to find.

For example:
[lua]local path = system.pathForFile(“data.db”, system.ResourceDirectory)
db = sqlite3.open( path )

–more code below…[/lua]

It’s very important that the correct path is defined for the SQLite db.

The example code just above your post and the sample in the link will help you to connect and query the database.

There is a good set of starting points available at:
http://luasqlite.luaforge.net/lsqlite3.html

I keep that open when I do my work.

[import]uid: 8045 topic_id: 1005 reply_id: 22090[/import]

5

Thank you!

The sample here on the Ansca site uses system.DocumentsDirectory which is what I was doing instead of system.ResourceDirectory that you show.

That was the missing piece I needed to even get this thing off the launching pad. I think I’ll go leave a comment on the SQLite page because this little bit of info shouldn’t be so hard to find. :slight_smile:

Jay
[import]uid: 9440 topic_id: 1005 reply_id: 22097[/import]