SQLite in Lua - Henningstone/HMod GitHub Wiki

Contents

Global Namespace sql

Open

sql.Open(Filename)

Returns a new instance of the sqlite database handler that can be used to operate on the database with given filename (explained below).

Flush

sql.Flush(dbInstance)

Executes all currently queued queries synchronously (i.e. blocks until no queries are left). dbInstance is what sql.Open returns.

Clear

sql.Clear(dbInstance)

Synchonously discards all queries on the query queue. dbInstance is what sql.Open returns.

SQLite database instance

To create a new instance one would use

myDB = sql.Open("myDB")

In the following section, 'myDB' will be used as a example variable and must be replaced accordingly.

Execute

myDB:Execute(Statement [, Callback])

Queues a new sql statement for execution on the database, using the sqlite dialect. Callback is an optional function to be called for each row returned by the query and is used to receive data from it. Please note that this function only queues a statement, in order to execute it, you need to call Work or Flush. The callback function must accept 3 arguments: function(Row, Statement, Index). Row represents one of the rows returned by the query and is usually what you are interested in. It is explained in further detail below. Statement is the original statement string that resulted in this query. Index is the index of the current row.

Work

myDB:Work()

Synchronously executes the next batch of queries in a transaction (batch size is 250). Returns the number of queries left in the queue after the operation completed. This will fire off given callback functions. Without calling this function or Flush, your statement will not be executed.

GetDatabasePath

myDB:GetDatabasePath()

Returns the path to the database file used by this database instance.

Flush

myDB:Flush()

Like sql.Flush(myDB).

Clear

myDB:Clear()

Like sql.Clear(myDB).

Retrieving SQLite query results

This is the first argument to a given callback function for an executed statement.

myDB:Execute("SELECT * FROM my_table WHERE some_column = 'some_data', function(Row, Statement, Index)
    -- the `Row` argument in here is what is used in the following section.
end)

As stated in the section above, the callback function is called for each row returned by the query and Index tells you the number of the current row. To get data from the row you must operate on the Row object as explained below.

GetColumnCount

Row:GetColumnCount()

Returns the number of columns this row has. Important if you want to iterate through all the columns instead of addressing them by name (see below). Technical explanation: see sqlite3_column_count.

GetName

Row:GetName(ColumnID)

Returns the name if the column with given index or nil if the index is invalid. Technical explanation: see sqlite3_column_name.

GetType

Row:GetType(ColumnID)

Returns the type of the column with given index. The returned value is a number defined by sqlite. Assignments as follows:

  • INTEGER => 1
  • FLOAT => 2
  • TEXT => 3
  • BLOB => 4
  • NULL => 5 Note: According to the sqlite docs, "the value returned is only meaningful if no type conversions have occurred. After a type conversion, the value returned is undefined".

GetID

Row:GetID(ColumnName)

Returns the index of the column with the given name, or -1 if there is no column with such a name.

Reading data from the row

The following methods can be used to read data from a column of the current row. You must use the correct function for the type of data this column holds. The base functions (listed below) accept the column index to read data from, there is also a version of each of them with an appended N (e.g. GetInt and GetIntN) which accept the column name instead of its index.

Row:GetInt
Row:GetFloat
Row:GetText
Row:GetStr
Row:GetBlob
Row:GetSize