The need to be able to access and modify data in any kind of application is critical. As databases are the primary means of storing large quantity of data, any software that is lacking this critical component will quickly be found wanting. As far as skills, being able to use and work with databases effectively in side an app is generally consider what separates the intermediate developer from an advanced/skilled developer.

Fortunately, Corona has this capability, and soon you can join the ranks of skilled developers!

For this project I loaded a SQLite database with zip code data downloaded from maxmind.com. I used the SQLite Manager plugin for Firefox to manage the import and cleaning up of the data for my needs.

The code is fairly straight forward:

First, you will need to import the sqlite3 framework and set the path to your database file and open the associated file.

In this example, the database (zip.sqlite) is located in the same folder as my main.lua file.
[sourcecode gutter=false]

4 –include sqlite
5 require “sqlite3”
6
7 –open database and set path to the same folder as our main.lua file
8 local path = system.pathForFile(“zip.sqlite”, system.ResourceDirectory)
9 db = sqlite3.open( path )

Next, I setup some code to handle an applicationExit event, so that the database will be properly closed should the user hit the home button.

11 — handle the applicationExit event to close the db
12 local function onSystemEvent( event )
13 if( event.type == “applicationExit”) then
14 db:close()
15 end
16 end

I’ve included a couple of print statements to show the current version and path being used to help with troubleshooting:
18 print (“version “..sqlite3.version())
19 print (“db path “..path)
20

Next, I use a select statement, limiting the rows found to 10, and display the content of those rows to my device:

21 local count =0
22 local output
23 local sql = “SELECT * FROM zipcode LIMIT 10″
24 for row in db:nrows(sql) do
25 count = count +1
26 local text = row.city..”, “..row.state..” “..row.zip
27 local t = display.newText(text, 20, 30 +(20 * count), native.systemFont, 14)
28 t:setTextColor(255,255,255)
29 end

and finally, I setup the system event listener for that close event that I handled earlier.

30
31 — system listener for applicationExit
32 Runtime:addEventListener (“system”, onSystemEvent)
[/sourcecode]
You can see the full tutorial right here:

[tubepress video=”vN-5m-23zgY”]