LuaSQL logo LuaSQL Database connectivity for the Lua programming language

Introduction

This documentation is based on the Kepler projects LuaSQL documentation.
LuaSQL is a simple interface from Lua to a number of database management systems. It includes a set of drivers to some popular databases (currently PostgreSQL, ODBC, MySQL, SQLite, Oracle, and ADO). LuaSQL defines a simple object-oriented API. All drivers should implement this common API, but each one is free to offer extensions.

Barracuda Application Server (BAS) offers support for one LuaSQL driver, namely; SQLite3. In addition to LuaSQL, BAS also provides drivers for MySQL and Redis. See the online MySQL and Redis tutorial for more information.

Before using the SQLite API, read the section Lua-SQLite and LSP Considerations. You may also be interested in the Mako Server's introductory LuaSQL tutorial.

LuaSQL defines one single global variable, a table called luasql.This table is used to store the initialization methods of the loaded drivers. luasql is preloaded in the global environment and to use luasql requires the driver to be initialized via luasql.sqlite() or luasql.odbc().

SQLite specific: the global table includes function quotestr. The globsal quotestr is the same function as env.quotestr.

The initialization methods are used to create an SQL environment object which is used to create a connection object. A connection object can execute SQL statements and eventually create a cursor object which is used to retrieve data.

LuaSQL is free software and uses the same license as Lua.

This version of LuaSQL for Barracuda is substantially modified from the Kepler version, mainly for SQLite support and is Copyright (C) Real Time Logic.

Error handling

LuaSQL is just an abstraction layer that communicates between Lua and a database system. Therefore errors can occur on both levels, that is, inside the database client or inside LuaSQL driver.

Errors such as malformed SQL statements, unknown table names etc. are called database errors and will be reported by the function/method returning nil followed by the error message provided by the database system. Errors such as wrong parameters, absent connection, invalid objects etc., called API errors, are usually program errors and so will raise a Lua error.

This behavior will be followed by all functions/methods described in this document unless otherwise stated.

SQL Environment Objects

An environment object is created by calling the driver's initialization function that is stored in the luasql table, indexed with the same name as the driver (odbc, sqlite, etc). For example, 

env = luasql.sqlite()

will try to create an environment object using SQLite.

local io = A Barracuda I/O created by C code and returned by ba.openio
           or a Barracuda I/O created ba.mkio

-- Converts a name to the absolute path + name
local function datasource(name)
   return io:realpath(name) -- I/O from above
end

-- Open database helper function
local function opendb(name, mode)
   local conn
   local env,err = luasql.sqlite()
   if env then
      conn, err = env:connect(datasource(name), mode == "r" and "READONLY" or "")
      if conn then
         return env,conn
      end
      env:close()
   end
   trace("Opening db failed",name,err)
   return nil,err
end


-- Close database helper function
local function closedb(env,conn)
   if env then
      if conn then
         conn:close()
      end
      env:close()
   end
end


local env,conn = opendb"mydb.sqlite"
if env then
   .
   .
end
closedb(env,conn)

Methods

env:close()
Closes the environment env. Open cursor objects should preferably be closed prior to calling this function, however, the method will attempt to close all open cursor objects if any are still open.
Returns: true in case of success; false when the object is already closed.
env:connect(sourcename[,options])
Connects to a data source specified in sourcename using the provided options. options are [,username[,password]]) for most SQL databases, for SQLite options can be one of the two strings 'READONLY' or 'NOCREATE'. READONLY opens the connection in read only mode and NOCREATE prevents creation of an SQL database. For SQLite, the sourcename is the file name of the database. The sourcename may vary according to each driver. The ODBC driver expects the name of the DSN;

Returns: a connection object.
SQLite extensions
env.version()
returns the version number string of SQLite that is being used. 
> print(env.version())
3.5.1
env.memory()
returns two numbers, namely; the current memory in bytes that is in use by SQLite and the memory highwater mark.
env.quotestr(string)
quotes the provided string so that it may be used as an SQL string. Single quotes (') are added to the string. 
> print("INSERT INTO table VALUES(" .. env.quotestr([[It's a happy day!]]) .. ")")
	would produce
INSERT INTO table1 VALUES('It''s a happy day!')

See also luasql.quotestr.

Connection Objects

A connection object contains specific attributes and parameters of a single data source connection. A connection object is created by calling the environment:connect method.

SQLite specific

The methods setautocommit, commit, and rollback on the connection object take an optional "transaction type" argument. The transaction type is set to DEFERRED if not specified.

SQLite has three different transaction types that start transactions in different locking states. Transactions can be started as DEFERRED, IMMEDIATE, or EXCLUSIVE. A transaction's type is specified in the BEGIN command:

BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] TRANSACTION;

A deferred transaction does not acquire any locks until it has to. Thus with a deferred transaction, the BEGIN statement itself does nothing - it starts in the unlocked state. This is the default. If you simply issue a BEGIN, then your transaction is DEFERRED, and therefore sitting in the unlocked state.

Why do you need to know this? Because if you don't know what you are doing, you can end up in a deadlock. If you disable auto commit and are using a database that other connections are also writing to, both you and they should use BEGIN IMMEDIATE or BEGIN EXCLUSIVE to initiate transactions. See the SQLite BEGIN TRANSACTION command for more information.

Example:

local env = luasql.sqlite()
local conn = env:connect(myDbName)
conn:setbusytimeout(1000) -- Wait max 1 sec on BUSY.
local ok,err=conn:setautocommit("IMMEDIATE")
if ok then
   .
   .
   conn:commit() -- Commit and switch to "DEFERRED" mode
elsif err == "BUSY" then
  print"DB busy"
end

Methods

conn:close()
Closes the connection conn. Only successful if all cursors and BLOBS pertaining to it have been closed and the connection is still open.
Returns: true in case of success and false in case of failure.
conn:commit(["DEFERRED" | "IMMEDIATE" | "EXCLUSIVE"])
Commits the current transaction. This feature might not work on database systems that do not implement transactions.
SQLite specific: Commits and starts a new transaction type.
. Returns: true in case of success and false when the operation could not be performed or when it is not implemented.
conn:execute(statement)
Executes the given SQL statement.


Returns: a cursor object if there are results, or the number of rows affected by the command otherwise.
conn:rollback(["DEFERRED" | "IMMEDIATE" | "EXCLUSIVE"])
Rolls back the current transaction. This feature might not work on database systems that do not implement transactions.
SQLite specific:Rolls back and starts a new transaction type.
. Returns: true in case of success and false when the operation could not be performed or when it is not implemented.
conn:setautocommit([boolean] [,"DEFERRED" | "IMMEDIATE" | "EXCLUSIVE"])
Turns on or off the "auto commit" mode. This feature might not work on database systems that do not implement transactions. On database systems that do not have the concept of "auto commit mode", but do implement transactions, this mechanism is implemented by the driver.
SQLite specific parameters:
  1. The transaction type can be optionally set. Default is DEFERRED.

Returns: true in case of success and false when the operation could not be performed or when it is not implemented.
ODBC extensions
conn:tables()
returns an array of names of all tables in the database.
SQLite extensions
conn:lastid()
returns the ROWID of the last inserted row.
conn:setbusytimeout(millisecs)
sets the busy timeout for the connection. This is the time that SQLite will wait for a lock to be freed. The default value is 500ms.
conn:tables()
returns an array of names of all tables in the database.
conn:prepare(statement)
Returns: a cursor object The cursor object must be used in stmt:execute() before the cursor can be used to fetch data or execute an update. The normal sequence is prepare, then bind, then execute.
conn:openblob(table, column, rowid [,update])
Returns: a SQLite BLOB object If update is true then the BLOB object may be updated, if omitted or false then the BLOB object is read only.
conn:zeroblob(table, column, rowid, size)
Sets the a BLOB object to the specified size. The BLOB contains only zero bytes.

Cursor Objects

A cursor object contains methods to retrieve data resulting from an executed statement. A cursor object is created by using the connection:execute function or connection:prepare function.

Methods

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.
There is no guarantee about the types of the results: they may or may not be converted to adequate Lua types by the driver. In the current implementation, the SQLite driver converts returned values them to Lua strings,
the ODBC driver converts returned values them to Lua types.
Returns: data, as above, 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.
SQLite extensions
cur:bind({ {type,value},... })
cur:bind()
This method binds Lua values to the SQL statment or returns the number of SQL parameters if called without any arguments.
In the SQL strings input to cur:prepare(), one or more literals can be replace by a parameter in one of these forms:
  • ?
  • ?NNN
In the parameter forms shown above NNN is an integer literal. The values of these parameters are set using cur:bind().
The table entries in the supplied table correspond to the index of the arguments in the SQL statment. That is, the table index corresponds to the index of the parameter to be set. The first parameter has an index of 1.
When the same named parameter is used more than once, second and subsequent occurrences have the same index as the first occurrence. The index for "?NNN" parametes is the value of NNN. The NNN value must be between 1 and 999.
The type parameter may be one of the following values:
  • "BLOB"
  • "FLOAT"
  • "INTEGER"
  • "NULL"
  • "TEXT"
For FLOAT and INTEGER the value may be a Lua number or a string that is covertable to a number via tonumber().
For NULL the value is irrelevant.
For TEXT the value is a Lua string or a value that is convertable via Lua tostring().
For BLOB the value is a Lua string. Alternatively, the BLOB value may be a number, in which case the BLOB is initialized with the provided number of zero bytes. This is a convenient method to resize a BLOB.
Returns: the same cursor object
Examples: 
-- simple prepare/execute example
> cur = conn:prepare"INSERT INTO table (f1) VALUES(1)"
> cur:execute() -- executes the prepared statement,
-- note that execute may be called again execute() does not destroy the
-- cursor (unlike conn:execute())

-- simple prepare/bin/execute example
> cur = conn:prepare"INSERT INTO table (f1) VALUES(?)"
> cur:bind{{"INTEGER",1}}
> cur:execute() -- executes the prepared statement,

BLOB Objects

A blob object contains methods to read and write BLOBS. A BLOB object is created by using the connection:openblob function.

Methods

blob:close()
Closes this blob.
#blob
Returns: the length in bytes of the blob.
blob:read(size,offset)
Reads incrementally a blob object.
Size and offset are integers describing the amount of data to read and where to start reading it. The offset is zero based. The size of the blob can be established with the '#' operator. blob:read(#blob,0) would read the blob in one operation.
Returns: a string containg the requested data.
blob:write(string, offset)
Writes the string at the offset into the blob. This function cannot change the size of a blob. If you wish to change the size of a blob use either SQL or the connection:zeroblob function.
Returns: the number of bytes written.

Examples

Here is an example of the basic use of the library. After that, another example shows how to create an iterator over the result of a SELECT query.

Basic use

-- create environment object
env = assert (luasql.sqlite())
-- 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, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
  print(string.format("Name: %s, E-mail: %s", row.name, row.email))
  -- reusing the table of results
  row = cur:fetch (row, "a")
end
-- close everything
cur:close()
con:close()
env:close()

And the output of this script should be:

Name: Jose das Couves, E-mail: jose@couves.com
Name: Manoel Joaquim, E-mail: manoel.joaquim@cafundo.com
Name: Maria das Dores, E-mail: maria@dores.com

Iterator use

It may be useful to offer an iterator for the resulting rows:

function rows (connection, sql_statement)
  local cursor = assert (connection:execute (sql_statement))
  return function ()
    return cursor:fetch()
  end
end

Here is how the iterator is used:

env = assert (luasql.sqlite())
con = assert (env:connect"my_db")
for id, name, address in rows (con, "select * from contacts") do
  print (string.format ("%s: %s", name, address))
end

The above implementation relies on the garbage collector to close the cursor. It could be improved to give better error messages (including the SQL statement) or to explicitly close the cursor (by checking whether there are no more rows).

xlua Usage

SQLite can be used with the extended Lua interpreter (xlua).