fhSQL

Reference for the fhSQL module, a set of functions to create and update SQLite and other databases from a Family Historian plugin, using the Microsoft COM object ADODB.Connection.

  • Version: 1.3
  • Author: Calico Pie
  • Licence: MIT (see plugin licence)
  • Dependencies: luacom

Note: This module requires UTF-8 encoding to be active. When using Microsoft or other OLEDB drivers, the 32-bit versions are required.

Usage

Start with connect or connectSQLite to open a database connection, then use select or execute on the returned object to read and write data:

local db = fhSQL.connectSQLite("mydata.sqlite")
db:execute("insert into mytable values ('George Bush', '123-4567')")

local people = db:select("select * from mytable")
for person in people:rows() do
  print(tostring(person.name) .. "\t" .. tostring(person.phone))
end

db:close()

All the functions below (other than connect/connectSQLite themselves) are called as methods on the connection object returned by connect/connectSQLite, i.e. db:execute(...), db:select(...), db:close().

Contents

Connecting

fhSQL.connect(connection_string)

Connects to any ADO-compliant database. See connectionstrings.com for connection string formats for different databases.

  • Parameters: connection_string — connection string for the database to use
  • Returns: fhSQL object to use for running SQL statements against the selected database

fhSQL.connectSQLite(sFilename)

Connects to a SQLite3 database file, creating it if it does not already exist. Implemented as a thin wrapper around connect using the SQLite3 ODBC driver.

  • Parameters: sFilename — database file name
  • Returns: fhSQL object to use for running SQL statements against the selected database
  • Note: blob objects are truncated to 255 characters when using SQLite

db:close()

Closes the database connection.

  • Parameters: none (called as db:close())
  • Returns: none

Running statements

db:execute(statement)

Executes the given SQL statement; the connection's recordset will contain the result. For select statements it's recommended to use db:select(...) instead, since it supports multiple concurrent result sets, whereas execute does not.

db:execute("insert into mytable values ('George Bush', '123-4567')")

Three special statement strings are handled as transaction control rather than being sent to the database as SQL:

  • "%BEGIN" — starts a transaction (BeginTrans)

  • "%COMMIT" — commits the current transaction (CommitTrans)

  • "%ROLLBACK" — rolls back the current transaction (RollbackTrans)

  • Parameters: statement — SQL statement to execute, or one of "%BEGIN"/"%COMMIT"/"%ROLLBACK"

  • Returns: none

db:select(statement)

Runs a select statement and returns a result set object that can be iterated with rows() or read with fetch(). Unlike execute, multiple select result sets can be open on the same connection at once.

local people = db:select("select * from test")
  • Parameters: statement — select statement to run
  • Returns: result set object (see Reading results)

Reading results

These are methods on the result set object returned by db:select(...).

resultset:rows()

Returns an iterator function for stepping through every row of the result set, for use in a for loop:

local people = db:select("select * from test")
for person in people:rows() do
  print(tostring(person.name) .. "\t" .. tostring(person.phone))
end
  • Parameters: none (called as resultset:rows())
  • Returns: iterator function, yielding one table per row (fields keyed by field name), or nil once exhausted (the underlying result set is closed automatically at that point)

resultset:fetch()

Gets a single row from the result set — use this instead of rows() when only one row is needed. Closes the result set after reading.

  • Parameters: none (called as resultset:fetch())
  • Returns: table of fields keyed by field name, or nil if there was no active connection or no rows

resultset:rowcount

Not a function — a property (alias for the underlying ADO RecordCount) giving the number of rows in the result set, set by select.