sqlite | Burtons Media Group

Last semester I had a group of students using Corona SDK who were using a database to list local establishments. They needed a way to easily display the list of locations in the SQLite database to the application.While I had discussed the possibility of reading from a database and loading a picker-wheel in the textbooks Learning Mobile Application & Game Development and Beginning Mobile App Development with Corona, these students were relatively new to app development and needed a little more guidance on the process. So, without further background, here is how to load a picker-wheel from a local SQLite database in Corona SDK. To get started we will need to load the required plugins for sqlite and the picker-wheel widget tool in Corona. We will also make sure that the database is in the documents folder of the mobile device and not the resource folder. *Note: I have created a database that lists all cities in the USA by zip code. I will be using it for this demonstration. require("sqlite3") local widget = require "widget" -- Does the database exist in the documents directory (allows updating and persistence) local path = system.pathForFile("zip.sqlite", system.DocumentsDirectory ) file = io.open( path, "r" ) if( file == nil )then -- Doesn't Already Exist, So Copy it In From Resource Directory pathSource = system.pathForFile( "zip.sqlite", system.ResourceDirectory ) fileSource = io.open( pathSource, "rb" ) contentsSource = fileSource:read( "*a" ) --Write Destination File in Documents Directory pathDest = system.pathForFile( "zip.sqlite", system.DocumentsDirectory ) fileDest = io.open( pathDest, "wb" ) fileDest:write( contentsSource ) -- Done io.close( fileSource ) io.close( fileDest ) end -- One Way or Another The Database File Exists Now -- So Open Database Connection 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 A quick note on the copy method. When you are working on a Windows system for development, you MUST use the "rb" (read binary) and "wb" (write binary) methods for reading and writing a database. This forces Corona to copy the file in the binary format. On a Mac, you can do a regular "r" and "w". For greatest compatibility, we recommend always using rb and wb to avoid premature greying and extensive head-smashing-into-desk. Time to load the data from the database. To begin, create a table to store the data loaded from the database (click here to see my tutorial on tables). Next, create the SQL statement to load records from the database. I have limited the SQL statement to the first 20 records for simplicity (there are 1000's of records). After reading each row of data and creating a variable to store the city & state captured from the database, we add the text to the data table that will be used by our picker-wheel. local data = {}local sql = "SELECT * FROM zipcode LIMIT 20"for row in db:nrows(sql) do local text = row.city..", "..row.state table.insert(data, text)end Time to create the picker-wheel. We are going to keep it simple; just one column of data that contains the city and state information.The picker-wheel will be 258 pixels from the top of the screen (for no real reason, just looked good on my test app), with a font size of 18 and a default color of black. The currently selected city & state will be red. --Create storage for picker wheel local columnData = { {align = "left", startIndex = 3, labels=data } } -- create the picker widget with columnData local picker = widget.newPickerWheel({ top=258, fontSize = 18, fontColor={0,0,0}, fontColorSelected = { 1, 0, 0 }, columnColor = { 0.8, 0.8, 0.8 }, columns = columnData, }) Just three more things to do: add a button to return the selected data to the console, setup the function to print the selected value, and finally, add a event listener to close the database if the user abruptly closes the app. local function showValues( event ) -- Retrieve the current values from the picker local values = picker:getValues() -- print the selected values print( "Column Information: " .. values[1].value ) end local getValuesButton = widget.newButton { left = 10, top = 150, width = 298, height = 56, id = "getValues", label = "Values", onRelease = showValues, } -- system listener for applicationExit Runtime:addEventListener ("system", onSystemEvent) And that is how you load a picker-wheel from a local SQLite database. This tutorial has been added to the Corona textbooks available on our website.FYI, the students did a great job on their project! If you would like the video tutorial of working with a Picker wheel and SQLite, checkout the link below! https://youtu.be/egZmKwH_xxo