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 offers support for two drivers, namely; ODBC and SQLite3.

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().

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 5.1.

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.

Methods

env:close()

Closes the environment env. Only successful if all connections pertaining to it were closed first.
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!')

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.

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(): Commits the current transaction. This feature might not work on database systems that do not implement transactions.
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(): Rolls back the current transaction. This feature might not work on database systems that do not implement transactions.
Returns: true in case of success and false when the operation could not be performed or when it is not implemented.
conn:setautocommit(boolean): 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.
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 od the last inserted row.
conn:setbusytimeout(secs): sets the busy timeout for the connection. This number may be decimal e.g. 2.516 means 2516 millseconds. By default there is no busy timeout. This the time that SQLite will wait for a lock to be freed.
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},... })

This method binds Lua values to the SQL statment.
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,

cur:execute()

Executes the previously prepared SQL statement.
Returns: same as conn:execute()

cur:unbind()

Clears all bindings previously created with cur:bind().
Returns: boolean true or an error

cur:mexec(statements [."a"|"n"])

Executes the SQL statements. statements can contain many SQL statements separated with ';'. This is a one step SQL execution. The default option is numerical indices, "a" generates field name indices.
Returns: true and a table containing any result sets (like fetch())

ret,t1=conn:mexec([[
BEGIN;
CREATE TABLE testxx1(a INTEGER PRIMARY KEY,b TEXT, c INTEGER);
insert into testxx1 (a, b, c) values (1, "XX", 2);
insert into testxx1 (a, b, c) values (2, "AA", 3);
select * from testxx1;
select * from testxx1;
drop table testxx1;
ROLLBACK;
BEGIN;]],"n")

for k,v in next,t1 do
  print(k,v)
  for i,w in next,v do
    print("\t",i,w)
  end
end

produces:

1	table: 00CE1FC0
		1	1
		2	XX
		3	2
2	table: 00CE2190
		1	2
		2	AA
		3	3
3	table: 00CD01C0
		1	1
		2	XX
		3	2
4	table: 00CD0160
		1	2
		2	AA
		3	3


print(conn:mexec([[drop table testxx1;]]))
print(conn:mexec([[
CREATE TABLE testxx1(a INTEGER PRIMARY KEY,b TEXT, c INTEGER);
drop table testxx1;
ROLLBACK;
]]))

produces:

nil	ERROR	no such table: testxx1
true

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).

blua Usage

SQLite can be used with standalone Barracuda Lua interpreter (blua) by issuing a require"luasql.sqlite" prior to making any sqlite calls.
Here is how it is used:

require"luasql.sqlite"
env = assert (luasql.sqlite())
...
...

     or

blua -l luasql.sqlite