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