Sqlite3

Supported platforms:
Available since: Gideros 2012.2.1

Description

Gideros runtime supports public domain SQLite3 database engine for iOS, Android and Desktop platforms. <syntaxhighlight lang="lua"> require "lsqlite3" </source>

For more information and detailed documentation, please visit [1](http://lua.sqlite.org)

SQLite3 on Android platforms

Currently, LuaSQLite3 plugin cannot open databases stored in APK files. Therefore, database file should be copied from resource directory to documents directory.

To copy a file, this function can be used: <syntaxhighlight lang="lua"> local function copy(src, dst) local srcf = io.open(src, "r") local dstf = io.open(dst, "wb") local size = 2^13 -- good buffer size (8K) while true do local block = srcf:read(size) if not block then break end dstf:write(block) end srcf:close() dstf:close() end </source>

Also it is better to check if the database file is previously copied or not. To check if a file can be found on the underlying file system, this function can be used: <syntaxhighlight lang="lua"> local function exists(file) local f = io.open(file, "rb") if f == nil then return false end f:close() return true end </source>

By using these two function, you can copy the database file from resource to documents directory before using it: <syntaxhighlight lang="lua"> if not exists("|D|database.db") then copy("database.db", "|D|database.db") end </source>

SQLite3 on on WinRT need to add an extension to Visual Studio

Gideros now supports sqlite and to make this work on Windows Phone, it is necessary to add an extension to Visual Studio. Even if you don't use sqlite, you will need to add this extension or Windows Phone projects exported by Gideros will not compile. It's easy to do as explained next. Of course, you only need to add the extension once.

Open Visual Studio and go to TOOLS > Extensions and Updates. Select the Online tab on the left and type sqlite in the search bar at the top right. Select SQLite for Windows 8.1 or/and Windows Phone 8.1 and select install. You will need to restart VS for the extension to take effect.

Example

<syntaxhighlight lang="lua"> local sqlite3 = require "lsqlite3" print(sqlite3.version()) local db = sqlite3.open("db/mydb2.gdb") -- path to your db

db:exec[=[ CREATE TABLE numbers(num1,num2,text); ]=]

db:exec[=[ INSERT INTO numbers VALUES(1,101,"a"); INSERT INTO numbers VALUES(2,202,"b"); INSERT INTO numbers VALUES(3,303,"c"); ]=] for num1,num2,text in db:urows('SELECT * FROM numbers') do print(num1,num2,text) end

db:close() </source>

Complete Example with SQLite3 binding by Atilim

<syntaxhighlight lang="lua"> require "lsqlite3"

-- open the database local db = sqlite3.open("|D|db.sqlite3")

-- check if 'numbers' table exists local exists = false db:exec("SELECT name FROM sqlite_master WHERE type='table' AND name='numbers'", function() exists = true end)

-- if not, create it and put some values if not exists then local sql = [[ CREATE TABLE numbers(num,str); INSERT INTO numbers VALUES(1,'ABC'); INSERT INTO numbers VALUES(2,'DEF'); INSERT INTO numbers VALUES(3,'UVW'); INSERT INTO numbers VALUES(4,'XYZ'); ]]

db:exec(sql) end

-- create a prepared statement local stmt = db:prepare("SELECT * FROM numbers WHERE num=? AND str=?") stmt:bind(1, 1) -- bind 1st value as 1 stmt:bind(2, "ABC") -- bind 2nd value as "ABC"

-- execute and print the result for num,str in stmt:urows() do print(num, str) end

-- close the database db:close() </source>

Methods

Events Constants

---

• Main Page
• Lua API
• Main API
• Plugins