interface_dbi

DESCRIPTION

The dbi interface is designed for creating access to different SQL databases. It presents a generic api to open, query, and change databases.

THE DBI INTERFACE

objectName open database ?arg ...?

Opens the given 'database'. Extra arguments can (must) be given, depending on the backend used. database is an identifier of the database. What this is exactly, can depend on the implementation.

arg .. usually consists of one or more of the following optional parameters:

-user username

connect to the database as username instead of connecting as the current user.

-password password

provide a password if required

-role role

connect using the given role

-host host

connect to a database on a different host

objectName exec ?options? sql ?param ...?

Executes arbitrary SQL commands in the currently open database. when a select query has been processed, and the -usefetch option is not used, the command returns a list of lists: each element of this list contains the selected values of one record (in a list). Otherwise nothing is returned

sql contains the commands to be executed. When sql contains placeholders (indicated by ?), extra parameters must be given (param, that will be used to fill in the gaps. If an sql error occurs, all changes made in the current transaction will be rolled back. If no explicit has been explicitly started by the user using "objectName begin", this means all changes made in the current exec.

The following options can be present:

-flat

if present, the result will be returned as a flat list instead of a list of lists. Each element in the list will contain the value of one column. eg. If a query returns 2 rows of each 2 elements, a list of 4 elements is returned

-usefetch

if present, the command does not return the resultset of a select query as a list. The fetch method will be used to get the results instead.

-nullvalue nullval

nullval will be returned for each NULL value in the result instead of an empty string

example:

db exec {select "value" from "test_table" where "id" = ?} 100

objectName fetch ?options? ?option? ?line? ?field?

Fetches one line of data of the result of a previous query. This will only work after the exec method has been invoked with the -usefetch option. Normally, the next line in the result will be fetched. The optional arguments can be used to fetch a specific line and/or field. by specifying option, other information about the result may be obtained.

When calling exec again, the results are cleared, and cannot be fetched any longer using the fetch method. If you want to use the exec method while keeping the resultset available, use a clone (see further) of the dbi object.

If present, line is an an integer >= 0 indicating which line of the result to fetch. If line is "current", the current line will be fetched. Not all databases allow backward positioning in a result set, so not all objects that support the dbi interface will be able to fetch lines earlier than the current line. field is also an integer >= 0 indicating the field of the result to fetch.

If option is present, information about the result is returned instead of the result data itself. It can have any of the following values:

pos

return the current position in the result. The first line is position 0

fields

return the fields present in the resultset

clear

clear the current resultset, do not return anything

isnull

returns 1 if the result contains a NULL value at the given line and field

lines

returns the number of lines in the result. This function cannot always be supported by a dbi object

The following options can be present:

-nullvalue nullval

nullval will be returned for each NULL value in the result instead of an empty string

examples:

db fetch

db fetch isnull current 1

objectName tables

Returns a list with tables present in the current database

objectName fields tableName

Returns a list with the fields present in the tableName

objectName close

Closes the current database connection

objectName begin

Starts a transaction

objectName commit

Commits the current transaction

objectName rollback

Does a rollback for the current transaction

objectName destroy

Destroys the dbi object

objectName serial option args ...

Creates or manages a serial field

objectName serial add table field ?value?

Converts the integer field field in the given table into a serial field (using generators, triggers, ...) and optionally initialise it with value

objectName serial delete table field

Returns the given serial field in the given table into a normal integer field

objectName serial set table field ?value?

Returns the current value for given serial field. If the argument value is present, the given serial field will be set to this value first.

objectName serial next table field

Increments the current value of the given serial field by 1, and return the new value

objectName info option args ...

Returns information about the database, database tables, etc. Which information is returned depends on option. Some options require further arguments. The options and information available can depend on the database and on the implementation of the interface. eg. some databases do not support roles, so you cannot get information about them. Some options that should be avaiable are:

objectName info user

Returns the current user for the database connection

objectName info systemtables

Returns the systemtables. These are not included in the list returned by "objectName tables".

objectName info views

Returns a list of all views in the current database

objectName info access accesstype user ?table?

Returns information about the accessibility of tables, .... accesstype determines the type of access that is queried and can be one of:

select

insert

delete

update

reference

If the table argument is not given, the command returns a list of all tables user has access to; If the table argument is not provided, a list of all fields in the given table user has access to is returned

objectName info table table

Returns information about the table table. This information is returned in the form of a list containing pairs of elements (a map). The first element in each pair is a keyword and the second element of each pair is the value for that keyword. Possible pairs are:

fields

list containing all fields in the table

type,field

type of field field

length,field

size of field field

notnull,field

1 if field field has a not null constraint, otherwise not present

primary,field

present if field field is a primary key, may contain the index

unique,field

present if field field has a unique constraint, may contain the index

foreign,field

list with table and field referenced by the field field. Is only present if the field field has a foreign key constraint

constraint,constraint

'constraint' is a constraint defined for the table, may contain the index enforcingg the constraint as a value

objectName info domains

Returns a list of all domains in the current database

objectName info domain

Returns the definition of the domain provided as an argument

objectName info roles

Returns all roles in the database. If the optional argument username is provided, only roles accessible by the given user are returned

objectName supports ?keyword?

Returns 1 if the object supports the feature indicated by keyword, and 0 if it does not. If keyword is not provided, the keywords for all supported features are returned. Following keywords can be available:

lines

support for "objectName fetch lines"

backfetch

support for going back when fetching results

columnperm

support for working with permissions per column

blobparams

support for blobs throug parameters

sharedtransactions

a clone shares the same transaction with its parent

transactions

support for transactions

roles

support for roles

domains

support for domains

objectName clone ?name?

Creates a clone of the dbi object. You will be able invoke the clone using the command name. If not provided, a name will be created and returned as a result. A clone is an object very similar to the dbi object it spawned from. It supports the same methods, and has the same connection parameters (user, etc.) A clone is dependend on its parent: If the parent is destroyed, or its connection closed, the clone will be destroyed. Closing a clone will also destroy it. A clone can be eg. be created for using exec while keeping open a resultset for fetching data line by line, or to compare different resultsets using fetch. Creating a clone has several advantages to creating a new connection:

• The user of the interface does not have to know the parameters of the connection to temporarily spawn a clone
• If the backend database api allows it, a clone actually shares the connection with its parent, making the creation of a clone a lot faster then creating a new connection.

If possible, a clone also shares transactions with its parent. Since this is not always possible; You can check if it actually does this using "objectName supports sharedtransactions".

objectName clones

Returns a list of clones spawned from the dbi object. This command will give an error when invoked from a clone.

objectName parent

Returns the parent dbi object that spawned this clone. A dbi object that is not a clone, will return its own name.

SEE ALSO

interface interface_dbi_admin

KEYWORDS

• interface
• database
• sql
• dbi

Peter De Rijk

hosted at