# Io Reference

Databases / SQLite3 / SQLite3

SQLite provides a embedded simple and fast (2x faster than PostgreSQL or MySQL) SQL database. See http://www.hwaci.com/sw/sqlite/ for details. It's SQL command set is described at http://www.hwaci.com/sw/sqlite/lang.html. SQLite was written by Dr. Richard Hipp who offers consulting services for custom modifications and support of SQLite. Example:

    db := SQLite clone
    db setPath("myDatabase.sqlite")
    db open
    db exec("CREATE TABLE Dbm (key, value)")
    db exec("CREATE INDEX DbmIndex ON Dbm (key)")
    db exec("INSERT INTO Dbm ('key', 'value') VALUES ('a', '123')")
    db exec("INSERT INTO Dbm ('key', 'value') VALUES ('a', 'efg')")
    rows := db exec("SELECT key, value FROM Dbm WHERE key='a'")
    db exec("DELETE FROM Dbm WHERE key='a'")
    rows := db exec("SELECT key, value FROM Dbm WHERE key='a'")
    db close

changes

Returns the number of rows that were changed by the most recent SQL statement. Or Nil if the database is closed.
close

Closes the database if it is open. Returns self. If the database is open when the open is garbage collected, it will be automatically closed.
columnNamesOfTable(tableName)

Returns a list containing the names of all columns in the specified table.
debugOff

Turns off debugging.
debugOn

Turns on debugging.
error

Results a string containing the current error. If there is no error, Nil is returned.
escapeString(aString)

Returns a translated version of aString by making two copies of every single-quote (') character. This has the effect of escaping the end-of-string meaning of single-quote within a string literal.
exec(aString)

Opens the database if it is not already open and executes aString as an sql command. Results a List of Map objects or Nil if there was an error. Each map holds the contents of a row. The key/value pairs of the maps being column name/column value pairs for a row.
isOpen

Returns true if the database is open, false otherwise.
lastInsertRowId

Returns the number with the row id of the last row inserted.
open(optionalPathString)

Opens the database. If there is an optionalPathString argument, the path is set to it's value before opening the database. If path is "" or ":memory:" a database will be created in-memory, otherwise the file specified by path is opened. Returns self or Nil upon failure. If the databse is locked, "yield" will be called until it is accessable or timeoutSeconds has expired. ""
path

Returns the path to the database file.
setPath

Sets the path to the database file. Returns self.
setTimeoutSeconds(aNumber)

Sets the open timeout to aNumber. If aNumber is 0, an open call will never timeout. Returns self.
tableNames

Returns a list containing the names of all tables in the database.
timeoutSeconds

Returns the number of seconds to wait before timing out an open call. If the number is 0, an open call will never timeout.
version

Results a string the version of SQLite being used.
viewNames

Returns a list containing the names of all views in the database.