9

Corona App Development – Developing with SQLite Database

Posted by Dr. Burton on February 16, 2011 in Android, Corona, iPad, iPhone, Mobile |

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”]

Tags: , , , , ,

9 Comments

  • David says:

    hi,

    Greta article, is possible to use bigger db(200mb), with no memory problems?

    And is possible to make fullsearch?

    David.

  • Dr. Burton says:

    As long as you are only pulling a small piece of that 200mb database, it shouldn’t be a memory problem. You would want to have a good index to speedup the search.
    I’m not sure what you mean by full search. Could you provide more details?

  • Charlie says:

    Do you have the source code availablefor download?

  • Scott Orheim says:

    Thanks for the examples. I have been working on a simple program that uses an sqlite db file. Where would I place the already populated database? I copied it into the Documents folder of the sandbox which works fine in the Corona Simulator but not at all when built for my Android device. The database is copied into the /data/data/myprogram/Documents but with zero bytes.

  • shabbo says:

    can it be made to display more records with scrolling?

  • Dr. Burton says:

    Yes. I have a demonstration of loading a lot more records with scrolling in Chapter 13 of my book. Gilbert of Ansca Mobile has a nice tutorial on a library he made that includes scrolling here.

  • Seng Keat says:

    No source code sir for this tut?

    Keat

  • Juanjo says:

    Good morning,

    I have read your book and trying to make an implementation of the SQL…

    But something wierd is happening… Is working perfect in my mobile but on my Windows Simulator works fine the first time I run the app (first time copy the file from resources to Documents following your code) and the second time is reading fine from the database but is not saving…

    Is quite strange because is working fine in my mobile and in the Simulator works perfect until y execute it for the second time…

    Please, can you bring some light on this?

    Thanks a lot.

    Regards,

    Juanjo

  • […] Learn how to develop software for Android OS devices. In this demonstration, Dr. Burton shows how to develop a Corona app that reads data from a SQLite database. You can view the complete tutorial at http://www.burtonsmediagroup.com/blog… […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Copyright © 2010-2019 Burtons Media Group All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from BuyNowShop.com.