=head1 NAME

luasql - database interface (LuaSQL)

=head1 OVERVIEW

LuaSQL is a simple interface from Lua to a DBMS. It enables a Lua program to:

=over 4

=item

Connect to ODBC, ADO, Oracle, MySQL, SQLite, JDBC, and PostgreSQL databases;

=item

Execute arbitrary SQL statements;

=item

Retrieve results in a row-by-row cursor fashion.

=back

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, JDBC, MySQL, SQLite, Oracle,
and ADO; Interbase and Sybase are in our plans).  LuaSQL defines a
simple object-oriented API.  All drivers should implement this common
API, but each one is free to offer extensions.

LuaSQL defines one single global variable, a table called C<luasql>.
This table is used to store the initialization methods of the loaded
drivers.  These methods are used to create an L</environment object>
which is used to create a L</connection object>.
A connection object can execute SQL statements and eventually create a
L</cursor object> which is used to retrieve data.

=head1 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.

=head2 Basic use

  -- load driver
  require "luasql.postgres"
  -- create environment object
  env = assert (luasql.postgres())
  -- 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

=head2 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:
 
  require "luasql.mysql"
  env = assert (luasql.mysql())
  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).

=head1 DOWNLOAD

Source code for LuaSQL can be downloaded from the LuaForge
(L<http://luaforge.net/project/showfiles.php?group_id=12>) page.  If
you are using LuaBinaries
(L<http://luaforge.net/projects/luabinaries>) Release 2, a Windows
executable version of LuaSQL can be found at the same LuaForge page.

=head1 COMPILING

LuaSQL is distributed as a set of C source files: a pair of common
source and header files (C<luasql.h> and C<luasql.c>); and one source
file for each driver.  Each driver should be compiled with the
luasql.c file to generate a library.  This library can be linked to
the application or dynamically loaded.  The initialization function is
C<luaopen_luasql>I<drivername> and it is a Lua C C<open-library>
(L<http://www.lua.org/manual/5.1/manual.html#pdf-require>) compatible
function.

=head1 INSTALLATION

All LuaSQL drivers follow the package model
(L<http://www.keplerproject.org/compat>) for Lua 5.1 and therefore
should be "installed". Refer to the Compat-5.1 configuration
(L<http://www.keplerproject.org/compat/manual.html#configuration>)
section to learn how to install the compiled binary.  The compiled
binary should be copied to a directory named C<luasql> in your
LUA_CPATH.

Windows users can use the compiled versions of LuaSQL available at
LuaForge (L<http://luaforge.net/projects/luasql/files>)

In order to use LuaSQL with JDBC, make sure that:

=over 4

=item

Lua is running with LuaJava (L<http://www.keplerproject.org/luajava/>)

=item

The LuaSQL jar is in the Java Virtual Machine classpath

=item

The JDBC driver of the desired database is also in the virtual machine
classpath

=back

In order to use LuaSQL with ADO, make sure that Lua is running with
LuaCOM 1.3 (L<http://www.tecgraf.puc-rio.br/~rcerq/luacom>).

=head1 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 I<database errors> and will be reported by the function/method
returning C<nil> followed by the error message provided by the
database system.  Errors such as wrong parameters, absent connection,
invalid objects etc., called I<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.

=head1 DRIVERS

A LuaSQL driver allows the use of the LuaSQL API with a database
management system that corresponds to the driver. To use a driver you
have to load it in the C<luasql> table. The example below

  require "luasql.odbc"

loads the ODBC driver in the C<luasql> table. Note that you can have
more than one driver loaded at the same time doing something like:

  require "luasql.odbc"
  require "luasql.oci8"

This example also shows that the driver name not always correspond to
the Database name, but to the driver name in the file system. Since it
refers to the OCI8 API, the Oracle driver has the name C<oci8> instead
of C<oracle>.

Some drivers, such as the MySQL, have libraries for a number of
database versions that use the same file name (C<mysql>). In this case
it is not possible to load more than one version of the MySQL driver
in the C<luasql> table.

=head1 Environment Object

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

  env = luasql.odbc()

will try to create an environment object using the ODBC driver.  The
only exception is the JDBC driver, which needs to know which internal
driver to use. Therefore, when creating an environment, the driver
class name must be passed as the first parameter to the C<luasql.jdbc>
function. For example:

  env = luasql.jdbc ("com.mysql.jdbc.Driver")

=head2 C<env:close>

  env:close()

Closes the environment C<env>.  Only successful if all connections
pertaining to it were closed first.

Returns: C<true> in case of success; C<false> when the object is
already closed.

=head2 C<env:connect>

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

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

The C<sourcename> may vary according to each driver.  Some use a
simple database name, like PostgreSQL, MySQL and SQLite; the ODBC
driver expects the name of the DSN; the Oracle driver expects the
service name; the JDBC driver expects a string like
C<"jdbc:&lt;database system&gt;://&lt;database name&gt;">, which is
specific for each driver.

See also: L</PostgreSQL Extensions>, and L</MySQL Extensions>
extensions.

Returns: a L</connection object>.

=head1 Connection Object

A connection object contains specific attributes and parameters of a
single data source connection.  A connection object is created by
calling the C<environment:connect (L</env_connect>)> method.

=head2 C<conn:close>

  conn:close()

Closes the connection C<conn>.  Only successful if all cursors
pertaining to it have been closed and the connection is still open.

Returns: C<true> in case of success and C<false> in case of
failure.

=head2 C<conn:commit>

  conn:commit()

Commits the current transaction.
This feature might not work on database systems that do not implement
transactions.

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

=head2 C<conn:execute>

  conn:execute(statement)

Executes the given SQL C<statement>.

Returns: a L</cursor object> if there are results, or the number of
rows affected by the command otherwise.

=head2 C<conn:rollback>

  conn:rollback()

Rolls back the current transaction.
This feature might not work on database systems that do not implement
transactions.

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

=head2 C<conn:setautocommit>

  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: C<true> in case of success and C<false> when
the operation could not be performed or when it is not implemented.

=head1 Cursor Object

A cursor object contains methods to retrieve data resulting from an
executed statement. A cursor object is created by using the
C<connection:execute> function.  See also L</PostgreSQL Extensions>
and L</Oracle Extensions>.

=head2 C<cur:close>

  cur:close()

Closes this cursor.

Returns: C<true> in case of success and C<false> when
the object is already closed.

=head2 C<cur:fetch>	
	
  cur:fetch([table[,modestring]])

Retrieves the next row of results.

If C<fetch> is called without parameters,
the results will be returned directly to the caller.
If C<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 C<modestring> parameter can be used.
It is just a string indicating how the resulting table should be constructed.
The mode string can contain:

=over 4

=item C<"n">

the resulting table will have numerical indices (default)

=item C<"a">

the resulting table will have alphanumerical indices

=back

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

The optional C<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 PostgreSQL and MySQL drivers return all values as
strings while the ODBC and Oracle drivers convert them to Lua types.

Returns: data, as above, or C<nil> if there are no more rows.  Note
that this method could return C<nil> as a valid result.

=head2 C<cur:getcolnames>

  cur:getcolnames()

Returns: a list (table) of column names.

=head2 C<cur:getcoltypes>

  cur:getcoltypes()

Returns: a list (table) of column types.

=head1 PostgreSQL Extensions

Besides the basic functionality provided by all drivers,
the Postgres driver also offers these extra features:

=head2 C<env:connect>

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

In the PostgreSQL driver, this method adds two optional parameters
that indicate the hostname and port to connect.
Also, the first parameter can contain all connection information,
as stated in the documentation for C<PQconnectdb> function
in the PostgreSQL manual
(e.g. C<environment:connect("dbname=&lt;>I<name>C<&gt; user=&lt;>I<username>C<&gt;")>)

See also: L</environment object>

Returns: a L</connection object>

=head2 C<cur:numrows>

  cur:numrows()

See also: L</cursor object>

Returns: the number of rows in the query result.

=head1 MySQL Extensions

Besides the basic functionality provided by all drivers,
the MySQL driver also offers these extra features:

=head2 C<env:connect>

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

In the MySQL driver, this method adds two optional parameters
that indicate the hostname and port to connect.
See also: L</environment object>

Returns: a L</connection object>

=head2 C<cur:numrows>

  cur:numrows()

See also: L</cursor object>

Returns: the number of rows in the query result.

=head1 Notes

This driver is compatible with versions 4.0, 4.1 and 5.0 of the
MySQL API. Only from version 4.1 MySQL provides support for transactions by using
BDB or INNODB tables.
Therefore, with version 4.0 or without one of these types of tables, the
methods C<commit>, C<rollback> and
C<setautocommit> will not work.

If you are using LuaSQL 2.0, C<cur:numrows()>
is available only in version 2.0.2 or later.

=head1 Oracle Extensions

Besides the basic functionality provided by all drivers,
the Oracle driver also offers this extra feature:

=head2 C<cur:numrows>

  cur:numrows()

See also: L</cursor object>

Returns: the number of rows in the query result.

=head1 VERSION

LuaSQL version 2.0.2 (for Lua 5.0) is now available for download.

The PostgreSQL driver has been tested on Windows, Linux and MacOS X
and is compatible with PostgreSQL 7.x and 8.x.

The ODBC driver has been tested on Windows (SQLServer and Microsoft
Access drivers).

The MySQL driver has been tested on Windows and Linux and is
compatible with versions 4.0, 4.1 and 5.0.

The Oracle driver has been tested on Windows and is compatible with
OCI 8 API.

The SQLite driver has been tested on Windows and Linux and is
compatible with versions 2.x.

The JDBC driver has been tested on Windows with LuaJava 1.0 and JDK
1.4 (MySQL driver).

The ADO driver has been tested on Windows with LuaCOM 1.3 (Microsoft
Access driver).

=head1 CREDITS

=head2 LuaSQL 2.0

Version 2.0 was redesigned by Roberto Ierusalimschy, AndrE<eacute>
Carregal and TomE<aacute>s Guisasola as part of the
Kepler Project (L<http://www.keplerproject.org>).  The
implementation is compatible with Lua 5.0 and was coded by
TomE<aacute>s Guisasola, Eduardo QuintE<atilde>o, Thiago Ponte, Fabio
Mascarenhas and Danilo Tuler, with many priceless contributions from
Michael Roth, Tiago Dionizio, Pedro Maia and Leonardo Godinho.

=head2 LuaSQL 1.0

LuaSQL was originally designed by Pedro Miller Rabinovitch and Roberto
Ierusalimschy.  The first implementation was compatible with Lua 4.0a.
Many modifications were made but not distributed by Diego Nehab
(ODBC),

LuaSQL development was sponsored by FE<aacute>brica Digital
(L<http://www.fabricadigital.com.br>), FINEP and CNPq.

=head1 CONTACT

For more information please contact us
(info-NO-SPAM-THANKS@keplerproject.org).  Comments are welcome!

You can also reach other Kepler developers and users on the Kepler
Project mailing list (L<http://luaforge.net/mail/?group_id=104>).

=head1 LICENSE

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

LuaSQL is free software:
it can be used for both academic and commercial purposes at absolutely no cost.
There are no royalties or GNU-like "copyleft" restrictions.
LuaSQL qualifies as
Open Source (L<http://www.opensource.org/docs/definition.html>)
software.
Its licenses are compatible with
GPL (L<http://www.gnu.org/licenses/gpl.html>).
LuaSQL is not in the public domain and the
Kepler Project (L<http://www.keplerproject.org>)
keep its copyright.
The legal details are below.</p>

The spirit of the license is that
you are free to use LuaSQL for any purpose at no cost without having to ask us.
The only requirement is that if you do use LuaSQL,
then you should give us credit by including the appropriate copyright notice
somewhere in your product or its documentation.

The LuaSQL library is designed and implemented by the
Kepler Project (L<http://www.keplerproject.org>) team.
The implementation is not derived from licensed software.

~~~~~

Copyright E<copy> 2003-2006 The Kepler Project.

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.  IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.

~~~~~