sqlite3.* (database)

Type Library
Revision Current Public Release (2014.2393)
Keywords sqlite3, database, sqlite
See also Database Access in Corona with SQLite
Official luasqlite Documentation

Overview

Corona includes support for SQLite databases on all platforms. This is based on the built-in sqlite support on the iPhone, and a compiled version of SQLite on Android (this increases the size of Android binaries by 300K).

SQLite is available in all versions of Android, iPhone, and iPad, as well as in the Corona Simulator.

For a tutorial on how to use SQLite in Corona, please see the Database Access in Corona with SQLite tutorial.

The Lua API is provided by luasqlite 3 v0.7. The documentation for luasqlite3 can be viewed at luasqlite.luaforge.net/lsqlite3.html. This documentation also includes test and sample code.

Also see SQLite Language Reference.

Gotchas

When providing a file path to open(), make sure to use system.pathForFile(). Providing just a plain file name such as my.db will not work consistently across the simulator and devices, especially on Android.

Quick Start

sqlite3.open( path )

Opens the SQLite file. Note that the path should be the full path to the database, not just the file name to avoid errors.

sqlite3.version()

Returns the version of SQLite in use.

file:exec( SQL_Command )

Executes a SQL command in the database. Typically used to create tables, insert, update, append or retrieve data from a database.

file:nrows( SQL_Command )

Returns successive rows from the SQL statement.

file:close()

Close the database.

Examples

Create Table
local sqlite3 = require "sqlite3"
local db = sqlite3.open_memory()

db:exec[[
  CREATE TABLE test (id INTEGER PRIMARY KEY, content);
  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]

print( "version " .. sqlite3.version() )

for row in db:nrows("SELECT * FROM test") do
  local t = display.newText(row.content, 20, 30 * row.id, null, 16)
  t:setFillColor( 1, 0, 1 )
end
Open File-Based Database
--Include sqlite
local sqlite3 = require "sqlite3"

--Open data.db.  If the file doesn't exist it will be created
local path = system.pathForFile("data.db", system.DocumentsDirectory)
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

--Setup the table if it doesn't exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]
print(tablesetup)
db:exec( tablesetup )

--Add rows with a auto index in 'id'. You don't need to specify a set of values because we're populating all of them
local testvalue = {}
testvalue[1] = 'Hello'
testvalue[2] = 'World'
testvalue[3] = 'Lua'
local tablefill =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[2]..[['); ]]
local tablefill2 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[2]..[[',']]..testvalue[1]..[['); ]]
local tablefill3 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[3]..[['); ]]
db:exec( tablefill )
db:exec( tablefill2 )
db:exec( tablefill3 )

--print the sqlite version to the terminal
print( "version " .. sqlite3.version() )

--print all the table contents
for row in db:nrows("SELECT * FROM test") do
    local text = row.content.." "..row.content2
    local t = display.newText(text, 20, 30 * row.id, null, 16)
    t:setFillColor( 1, 0, 1 )
end

--setup the system listener to catch applicationExit
Runtime:addEventListener( "system", onSystemEvent )