V1.01 - 23 Dec 2020
Both dens have a very, very similar interface and hence are described in this combined document.
Unless expressly indicated this documentation refers to both modules.
Basically both differ in constructors and external library dependencies only, and dendbi having an additional method selectdatabase
densqlite instances do not provide. The most important difference is the return value of next_row
and first_row
on result instances. Unfortunately this makes wrapping a requirement for compatitibility.
To access Databases like MySQL, PostgreSQL or SQLite using libDBI and
Not available for Windows
Note access to any SQLite3 database file is thread safe, but accessing a single database from several threads involves locking issues and is not recommended.
To access SQLite databases without libDBI dependencies. Note access to any database file is thread safe, but accessing a single database from several threads involves locking issues and is not recommended.
dendbi module depends on shared libraries installed on the host system:
whereas densqlite does not introduce additional dependencies, available for all platforms
It is not recommended to use this den to access the same SQLite databases using dendbi and densqlite simultaneously. Choose either one to avoid locking issues.
The DBICon class represents a database connection
Virtual prototype:
class DBICon { constructor(drivername,hostorpath,username,password,dbnameorfile[,port[,IANAEncoding]); function connect(); function close(); function query(sql); //blocking call function quote(string) ; function likequote(string); function selectdatabase(db); function Error(); function LastAutoIncrement() ; }
The SQLiteCon class represents a database "connection" (that is, the interface to access a database file)
When an instance is constructed for a non-existing database file, the specified database file will be automatically created (empty, no tables or setups).
The constructor will open the file for access in all cases, no call to connect
required (unless you called close
)
Virtual prototype:
class SQLiteCon { constructor(databasefile); function connect() ; function close() ; function query(sql) ; function quote(string); function likequote(string); function Error() ; function LastAutoIncrement(); }
DBICon(driver,hostorpath,user,password,dborfile[,port,[IANA]])
where
driver
string, name of the DBD driver to use (e.g. "mysql", "sqlite" or "postgre")
hostorpath
string. hostname or file path to the database (depending on driver)
user
string. username (depends on driver)
password
string. user password (depends on driver)
dborfile
string. database name or database file
port
integer, optional. TCP/UDP port of database server
IANA
string, optional. IANA string defining the character encoding the connection should use
The constructor will perform a connect() automatically to avoid a bug in some mysqlclient libraries causing a hard crash when accessing before connecting.
SQLiteCon(databasefile)
where
databasefile
string, absolute or relative path to the database file
If the specified database file does not exist a new, empty database file will be created.
The constructor will perform a connect() automatically.
function connect()
connects to the database specified in constructor (SQLite: opens the database file for access)
Note the constructors will perform this action already, you will need this only in case you used close
function close()
close the database connection / file.
Note: This was never tested. Expect issues when querying on a closed connection.
function query(sql)
perform a sql query. This is a blocking call and will return when the query result is available or any error occurs.
INPUTS:
sql
string, an sql query string
RETURNS: a result instance (DBIResult or SQLiteResult instance) on success ornull
on an error
THROWS: no
Notes:
Error()
(See below)Error()
strings reflect the error.function quote(string)
quotes a string to make sure it will not break an SQL statement when used as string parameter in a query
INPUTS:
string
string to quote
RETURNS: string, quoted (and enclosed in delimiters)
THROWS: no
function likequote(string)
quotes a string to use as a like statement, literalizing characters like %
INPUTS:
string
string to quote
RETURNS: quoted string
THROWS: no
This method will add the sql keyword for setting quoting characters
function Error()
returns tha last error
INPUTS: none
RETURNS: string, human readable error report of last error
THROWS: no
returns an empty string ("") when there is no last error available.
Note this Error message gets updated by queries, too.
function LastAutoIncrement()
returns the autoincrement value of the last operation affecting any increment counter (like an INSERT)
Note on 32bit gonuts this may be a 64 bit value in string representation
INPUTS: none
RETURNS integer or string representation of a number, whatever fits the number best.
THROWS: no
see add64
,eq64
ff. to process the return value
A class holding the result of a DBICon query
Virtual representation:
Class DBIResult { function first_row() ; //switch to first row, not available for SQLiteResult function next_row() ; //switch to next row, different return values depending on instance function column(col) ; //return a column of current row function row() ; //return current row as table function table() ; //synonym for row() function array() ; //return an array of all rows function Error() ; //different error strings depending on instance! function rowsaffected() ; // return number of rows affected by query. Not available for SQLiteResult! }
Users cannot instanciate DBIResult/SQLiteResult, instances are created by DBICon/SQLiteCon.query
only
function first_row()
not available for SQLiteResult.
Moves the internal pointer to the first row of a result, if any. Subsequent calls to row
, table
or column
(and even array
) will reference the selected row.
INPUTS: none
RETURNS:true
when there is a first row,false
when there is none
THROWS: no
Note that for both SQLiteResults and DBIResults it is not required as a head for iterating result rows. However, when a DBIResult instance is returned by query, there is no row selected. You need to either call first_row or next_row FIRST, before accessing row content using row
, table
or column
function next_row()
DBIResult only. For SQLiteResult see below
Moves an internal pointer to the next row of a resultset, if any. Subsequent calls to row
, table
or column
(and even array
) will reference the selected row.
INPUTS: none
RETURNS:true
when there is a row selected,false
when there is no row left to select
THROWS: no
function next_row()
SQLiteResult only. For DBIResult see above
Moves an internal pointer to the next row of a resultset, if any. Subsequent calls to row
, table
or column
(and even array
) will reference the selected row.
INPUTS: none
RETURNS: integer value, being one of the constantsSQLITE_DONE
,SQLITE_ROW
orSQLITE_ERROR
THROWS: no
function row()
returns a table containing the selected row data
INPUTS: none
RETURNS: table with row data, ornull
when no row is selected or an SQL error occured on the query
THROWS: no
Example (shortened, db being the database connection)
local result=db.query("SELECT * from `mytable` WHERE 1") ; local haverow=typeof db==DBICon?true:SQLITE_ROW ; if (result) { while (result.next_row()==haverow) { local rowtable=result.row() ; if (rowtable) foreach (column,value in rowtable) { //do something. column holds the column name, value the column value } } }
function table()
synonym for row
, see above
INPUTS: none
RETURNS: table with row data, ornull
when no row is selected or available
THROWS: no
function array()
returns an array of row tables (see row
above) beginning with the current selected row up to the last row available.
INPUTS: none
RETURNS: array of row data tables, ornull
when an SQL error occured on the query
THROWS: no
Example (shortened, db being the database connection)
local result=db.query("SELECT * from `mytable` WHERE 1") ; if (result) { result=result.array() ; if (result) { foreach (row in result) { foreach(column, value in row) { //do something. column holds the column name, value the column value } } } //no else! above array() call may still return null on errors relating to the query if (!result) print(db.Error()+"\n") ;
function Error()
returns any error that may have occured
INPUTS: none
RETURNS: string, human readable error report of last error
THROWS: no
returns an empty string ("") when there was no error.
Unlike the Error() method of the connection instance, the Error() method of a result will not be updated by subsequent queries and hence always relates to this very result and its query.
function rowsaffected()
returns the number of rows affected by the query which returned the result
INPUTS: none
RETURNS: integer, number of affected rows (or null)
THROWS: no
returns the number of rows affected by the query, or returns null in case of an internalt error or "empty" result. NOTE: This function is only available for DBIResult, and missing with SQLiteResult!
if (!import("densqlite")) return false; local db=SQLiteCon("mydatabase.sqlite"); local quoted=db.quote("somestringvalue"); local result=db.query("SELECT * FROM `mytable` WHERE `somecolumn`="+quoted); if ((!result || result.Error()!="") { print("SQL Error:"+result.Error()); } else { local i=0 ; while (result.next_row()==SQLITE_ROW) { local fullrow=result.row(); print("Row "+ ++i +":\n") ; foreach (column,value in fullrow) { print("\tcolumn "+column+": "+value+"\n") ; } } } return true ;