LuaSQLite 3 is a thin wrapper around the public domain SQLite3 database engine.

The lsqlite3 module supports the creation and manipulation of SQLite3 databases. The exported functions are called with prefix sqlite3. However, most sqlite3 functions are called via an object-oriented interface to either database or SQL statement objects.

This documentation does not attempt to describe how SQLite3 itself works, it just describes the Lua binding and the available functions.

For more information about the SQL features supported by SQLite3 and details about the syntax of SQL statements and queries, please see the SQLite3 documentation http://www.sqlite.org/.

Using some of the advanced features (how to use callbacks, for instance) will require some familiarity with the SQLite3 API.

General technique

• Open the database with either sqlite3.open or sqlite3.open_memory
  
  
• Execute "action" SQL statements with db:exec
  
  
• To query data from the database:
  
  
  
  
  • Prepare an SQL statement with db:prepare
    
  • Get column names with stmt:get_names or similar
    
  • Step through each row with stmt:step
    
  • Get column values with stmt:get_values or similar
    
  • After all rows are extracted, finalize the statement with stmt:finalize
    
  
  
  
• Close the database with db:close
  
  
  
  

  OK: 0          ERROR: 1       INTERNAL: 2    PERM: 3        ABORT: 4
  BUSY: 5        LOCKED: 6      NOMEM: 7       READONLY: 8    INTERRUPT: 9
  IOERR: 10      CORRUPT: 11    NOTFOUND: 12   FULL: 13       CANTOPEN: 14
  PROTOCOL: 15   EMPTY: 16      SCHEMA: 17     TOOBIG: 18     CONSTRAINT: 19
  MISMATCH: 20   MISUSE: 21     NOLFS: 22      FORMAT: 24     RANGE: 25
  NOTADB: 26     ROW: 100       DONE: 101

Lua functions

context:aggregate_count - Returns the number of calls to the aggregate step function.
context:get_aggregate_data - Returns the user-definable data field for callback functions.
context:result - This function sets the result of a callback function to res.
context:result_blob - This function sets the result of a callback function to the binary string in blob.
context:result_error - This function sets the result of a callback function to the error value in err.
context:result_int - This function sets the result of a callback function to the integer value in number
context:result_null - This function sets the result of a callback function to nil.
context:result_number - This function sets the result of a callback function to the value number.
context:result_text - This function sets the result of a callback function to the string in str.
context:set_aggregate_data - Set the user-definable data field for callback functions to udata.
context:user_data - Returns the userdata parameter given in the call to install the callback function.
db:busy_handler - Sets or removes a busy handler for a database
db:busy_timeout - Sets a busy handler
db:changes - Returns number of changes by the most recent SQL statement
db:close - Closes a database
db:close_vm - Finalizes all statements that have not been explicitly finalized
db:create_aggregate - Creates an aggregate callback function
db:create_collation - Creates a collation callback
db:create_function - Creates a callback function
db:errcode - Returns the most recent result code
db:errmsg - Returns the most recent error message
db:exec - Executes SQL statements
db:interrupt - Interrupts any pending operation
db:isopen - Tests if the database is open
db:last_insert_rowid - Returns the key of the most recent insert into the database
db:nrows - Creates an iterator that returns a table of rows from a SELECT - keyed by name
db:prepare - Compiles an SQL statement
db:progress_handler - Installs a progress callback function
db:rows - Creates an iterator that returns a table of rows from a SELECT - numerically keyed
db:total_changes - Returns total number of changes
db:trace - Installs debug trace callback
db:urows - Creates an iterator that returns the rows from a SELECT
sqlite3.complete - Checks for a complete SQL statement
sqlite3.open - Opens or creates a database
sqlite3.open_memory - Opens a database in memory
sqlite3.version - Returns the SQLite version
stmt:bind - Binds a value to a parameter
stmt:bind_blob - Binds a blob to a parameter
stmt:bind_names - Binds values in a table to parameters
stmt:bind_parameter_count - Returns the largest parameter index
stmt:bind_parameter_name - Returns the name of the n'th paremeter
stmt:bind_values - Binds values to parameters
stmt:columns - Returns number of columns in the result set
stmt:finalize - Frees a prepared statement
stmt:get_name - Returns the name of a column in a result set
stmt:get_named_types - Returns a table of names and types for the columns in a result set
stmt:get_named_values - Returns a table of names and values for the columns in a result set
stmt:get_names - Returns a table of the names of all columns in a result set
stmt:get_type - Returns the type of a column in a result set
stmt:get_types - Returns a table of the types of all columns in a result set
stmt:get_unames - Returns a list of names of the columns in a result set
stmt:get_utypes - Returns a list of types of the columns in a result set
stmt:get_uvalues - Returns a list of values of the columns in a result set
stmt:get_value - Returns the value of a column in a result set
stmt:get_values - Returns a table of the values of all columns in a result set
stmt:isopen - Checks if the statement is open
stmt:nrows - Creates an iterator that returns a table of rows from a SELECT - keyed by name
stmt:reset - Resets an SQL statement, so it can be executed again
stmt:rows - Creates an iterator that returns a table of rows from a SELECT - numerically keyed
stmt:step - Executes a previously-prepared SQL statement
stmt:urows - Creates an iterator that returns the rows from a SELECT

Topics

Database (SQLite)
Lua script extensions
Lua syntax
Scripting
Scripting callbacks - plugins

(Help topic: general=lua_sqlite3)