dendbi and densqlite - libDBI and SQLITE3 modules

V1.01 - 23 Dec 2020

Table of Contents

▶︎
all
running...

Introduction

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.

dendbi - Database Support using LibDBI

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.

densqlite Database Support using densqlite

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.

System Library Dependencies

dendbi module depends on shared libraries installed on the host system:

whereas densqlite does not introduce additional dependencies, available for all platforms

Important note about SQLite

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.

Class DBICon (dendbi)

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() ; 
}

Class SQLiteCon (densqlite)

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 Constructor

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 Constructor

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.

SQLiteCon and DBICon Methods

connect Method

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

close Method

function close()

close the database connection / file.
Note: This was never tested. Expect issues when querying on a closed connection.

query Method

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 or null on an error
THROWS: no

Notes:

quote Method

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

likequote Method

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

Error Method

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.

LastAutoIncrement Method

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

Classes SQLiteResult and DBIResult

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!
	
}

DBIResult/SQLiteResult Constructor

Users cannot instanciate DBIResult/SQLiteResult, instances are created by DBICon/SQLiteCon.query only

DBIResult/SQLiteResult Methods

DBIResult.first_row

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

DBIResult.next_row

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

SQLiteCon.next_row

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 constants SQLITE_DONE, SQLITE_ROW or SQLITE_ERROR
THROWS: no

row Method

function row()

returns a table containing the selected row data

INPUTS: none
RETURNS: table with row data, or null 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
		}
	}
}

table Method

function table()

synonym for row, see above

INPUTS: none
RETURNS: table with row data, or null when no row is selected or available
THROWS: no

array Method

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, or null 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") ;

Error Method

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.

rowsaffected Method

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!

Full example using next_row()#

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 ;