SQL Toolbox Help

A Glimpse of SQL Toolbox In-App Documentation

iLuaBox  ·  iLua Toolbox  ·  Socket Toolbox  ·  SQL Toolbox  ·  Vox Toolbox

1 - Toolbox Reference

The SQL Toolbox adds a set of functions to iLuaBox for creating, editing and managing databases using the Structured Query Language and the SQLite database engine. The SQL Toolbox is included with iLuaBox Pro, and is available as an In App purchase from within iLuaBox standard edition. iLuaBox expansions are available via In App purchases from the Toolbox Info screen. Select the Toolbox Info item from the Documentation menu to display purchasable items.

1.1 – Functions

luasql.sqlite3 ()

Creates and returns an environment object.

luasql._DESCRIPTION

A variable (not a function) of the 'lfs' global table that holds a string containing the description of the SQL Toolbox.

luasql._NAME

A variable (not a function) of the 'lfs' global table that holds a string containing the name of the SQL Toolbox.

luasql._VERSION

A variable (not a function) of the 'lfs' global table that holds a string containing the current version of the SQL Toolbox.

env:close ()

Closes the environment object.

Only successful if all connections pertaining to it were closed first.

Returns true in case of success and false when the object is already closed.

env:connect (sourcename [, username [,password]])

Connects to a data source specified in sourcename using username and password if they are supplied.

The sourcename is simply the database name.

Returns a connection object. A connection object contains specific attributes and parameters of a single
data source connection.

con:close ()

Closes the connection object.

Only successful if all cursors pertaining to it have been closed and the connection is still open.

Returns true in case of success and false in case of failure.

con:commit ()

Commits the current transaction.

Returns true in case of success and false when the operation could not be performed or when it is not implemented.

con:execute (statement)

Executes the given SQL statement as a string.

Returns a cursor object if there are results, or the number of rows affected by the command otherwise.
A cursor object contains methods to retrieve data resulting from an executed statement.

con:rollback ()

Rolls back the current transaction.

Returns true in case of success and false when the operation could not be performed or when it is not implemented.

con:setautocommit (boolean)

Turns on or off the "auto commit" mode.

Returns true in case of success and false when the operation could not be performed or when it is not implemented.

cur:close ()

Closes this cursor.

Returns true in case of success and false when the object is already closed.

cur:fetch ([table [, modestring]])

Retrieves the next row of results.

If fetch is called without parameters, the results will be returned directly to the caller.
If fetch is called with a table, the results will be copied into the table and the changed table will be returned.
In this case, an optional modestring parameter can be used. It is just a string indicating how the resulting table
should be constructed. The mode string can contain:

  • "n": the resulting table will have numerical indices (default)
  • "a": the resulting table will have alphanumerical indices

The numerical indices are the positions of the fields in the SELECT statement; the alphanumerical indices are the
names of the fields.

The optional table parameter is a table that should be used to store the next row. This allows the use of a unique
table for many fetches, which can improve the overall performance.

Returns data or nil if there are no more rows. Note that this method could return nil as a valid result.

cur:getcolnames ()

Returns a list (table) of column names.

cur:getcoltypes ()

Returns a list (table) of column types.

2 – Code Snippets

Below are listings of the sample files included with iLuaBox that utilize SQL Toolbox functionality.
If these files are not present, completely exit from iLuaBox and enable the 'Restore Examples' setting
from the Settings app. Then restart iLuaBox.

  • "quicktest.lua":
    the sample code that build, prints and destroys a small database. 
    -- load driver
require"luasql.sqlite3"

-- create environment object
env = assert (luasql.sqlite3())

-- connect to data source
con = assert (env:connect("luasql-test"))

-- reset our table
res = con:execute"DROP TABLE people"
res = assert (con:execute[[
  CREATE TABLE people(
    name  varchar(50),
    email varchar(50)
  )
]])

-- add a few elements
list = {
  { name="Jose das Couves", email="jose@couves.com", },
  { name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
  { name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
  res = assert (con:execute(string.format([[
    INSERT INTO people
    VALUES ('%s', '%s')]], p.name, p.email)
  ))
end

-- retrieve a cursor
cur = assert (con:execute"SELECT name, email from people")

-- print all rows
row = cur:fetch ({}, "a")	-- the rows will be indexed by field names
while row do
  print(string.format("Name: %s, E-mail: %s", row.name, row.email))
  row = cur:fetch (row, "a")	-- reusing the table of results
end

-- close everything
cur:close()
con:close()
env:close()

3 – Version History

Here is a chronology of the releases of all versions of SQL Toolbox.

3.1 – SQL Toolbox 1.4

SQL Toolbox 1.4 contains the following improvements:

  • [NEW] SQLite updated to version 3.7.11.

3.2 – SQL Toolbox 1.3

SQL Toolbox 1.3 contains the following improvements:

  • [NEW] SQLite updated to version 3.7.8.

3.3 – SQL Toolbox 1.2

SQL Toolbox 1.2 contains the following improvements:

  • [NEW] SQLite updated to version 3.7.6.3.

3.4 – SQL Toolbox 1.1

SQL Toolbox 1.1 contains the following improvements:

  • [NEW] SQLite updated to version 3.7.6.2.

3.5 – SQL Toolbox 1.0

Initial release.

4 – About SQL Toolbox

SQL Toolbox 1.4

iLuaBox, mLuaBox, eLuaBox, LuaBoxen and the iLuaBox logo are trademarks of MobileApp Systems LLC.
iLuaBox and associated software and hardware products are designed, manufactured and supported by
MobileApp Systems LLC. Visit the MobileApp Systems Web site for additional information:
www.mobileappsystems.com

SQL Toolbox contains the LuaSQL library.

Copyright © 2003-2007 The Kepler Project.

Copyright © 2010-12 MobileApp Systems LLC

Last update: 21-Mar-2012