Db Class

The ADO objects give you highly flexible access to the data in the database. However, this flexibility comes at a price -- the ADO objects are rather complicated to use.

To help you, the Db object wraps up the ADO objects. You can use the Carapace methods for high-level control, and yet still access the ADO objects underneath if you need finer control for specific tasks.

Db Creation

Script defining Db object: adodb.cpl

The create function can be used to create a Db object eg.

    (create Db)

Once created, this module is tailored using its properties and methods.

Db Properties

A Db object supports the following properties:

property name property type description

source String name of the ODBC data source which identifies the desired database

user String user name -- for authorising access to the database

password String password -- for authorising access to the database; if this starts with * the password is assumed to have been encrypted.

db Dispatch the ADODB.Connection dispatch interface onto the database -- only valid following a successful call to the connect method

log Log log for recording database activity -- if this log is set, then the database activity will be recorded if the db level is set on the Log.

suspendTransactions Object (readOnly) flag to control the use of transactions by the Db class -- if this value is the empty List () then the transaction methods beginTransaction, rollback and commit work as normal. Any other value for this property and these methods silently do nothing. This value is set using the suspend method

property name	property type	description
`source`	String	name of the ODBC data source which identifies the desired database
`user`	String	user name -- for authorising access to the database
`password`	String	password -- for authorising access to the database; if this starts with `*` the password is assumed to have been encrypted.
`db`	Dispatch	the ADODB.Connection dispatch interface onto the database -- only valid following a successful call to the connect method
`log`	Log	log for recording database activity -- if this log is set, then the database activity will be recorded if the `db` level is set on the Log.
`suspendTransactions`	Object	(readOnly) flag to control the use of transactions by the `Db` class -- if this value is the empty List `()` then the transaction methods beginTransaction, rollback and commit work as normal. Any other value for this property and these methods silently do nothing. This value is set using the `suspend` method

Db Methods

The Db object supports the following methods:

method name description

dataSource configure an ODBC data source -- this method can create, modify or delete a data source

connect connect to the database identified by the source property

sql perform the supplied SQL command returning any results as a List where each entry of the returned list is itself a list of data representing the selected row

sqlAux perform the supplied SQL command which takes parameters, returning any results as a List where each entry of the returned list is itself a list of data representing the selected row

recordSet return a record set generated from the supplied SQL command

beginTransaction begin a transaction on the database

commit commit a transaction on the database

rollback rollback a transaction on the database

suspend set the suspendTransactions flag and return its original value

disconnect close the current connection to the database

isDeadlock tests if the supplied Error represents a deadlock within the database

ddl perform the supplied DDL (Data Definition Language) command returning any results as a List where each entry of the returned list is itself a list of data representing the selected row -- if required, the DDL statement is converted to a vendor-specific form before evaluation

encrypt encrypt a password -- this encrypts a password so that it can be stored in the registry without allowing others to determine the password value -- the resultant string starts with *

decrypt decrypt an encrypted password -- if the string does not start with * then the password is not changed

method name	description
`dataSource`	configure an ODBC data source -- this method can create, modify or delete a data source
`connect`	connect to the database identified by the `source` property
`sql`	perform the supplied SQL command returning any results as a `List` where each entry of the returned list is itself a list of data representing the selected row
`sqlAux`	perform the supplied SQL command which takes parameters, returning any results as a `List` where each entry of the returned list is itself a list of data representing the selected row
`recordSet`	return a record set generated from the supplied SQL command
`beginTransaction`	begin a transaction on the database
`commit`	commit a transaction on the database
`rollback`	rollback a transaction on the database
`suspend`	set the `suspendTransactions` flag and return its original value
`disconnect`	close the current connection to the database
`isDeadlock`	tests if the supplied `Error` represents a deadlock within the database
`ddl`	perform the supplied DDL (Data Definition Language) command returning any results as a `List` where each entry of the returned list is itself a list of data representing the selected row -- if required, the DDL statement is converted to a vendor-specific form before evaluation
`encrypt`	encrypt a password -- this encrypts a password so that it can be stored in the registry without allowing others to determine the password value -- the resultant string starts with `*`
`decrypt`	decrypt an encrypted password -- if the string does not start with `*` then the password is not changed

`dataSource`

Configure an ODBC data source -- this method can create, modify or delete a data source. Both user- or system- data sources can be configured. An Error is raised if, for example, the driver is unknown or the command fails in any way.

The available commands are:

add
configure
remove
addSystem
configureSystem
removeSystem

The driver argument must match one configured on your system e.g.

Oracle ODBC Driver
Microsoft Access Driver (*.mdb)
Pervasive ODBC Engine Interface

The attributes argument is either a String or a List of Strings and depend both on the supplied command and driver values.

Arguments:

command String

driver String

attributes Object

(optional) windowHandle Integer

Return type: the empty List () interface onto

For example, there are two stages to create a Microsoft Access data source:

    # create the .mdb file
    (db.dataSource "add" 
                   "Microsoft Access Driver (*.mdb)"
                   "CREATE_DB=c:\\temp\\demo.mdb"
    )

    # create the data source
    (db.dataSource "add"
                   "Microsoft Access Driver (*.mdb)"
                   (list "DSN=demo"
                         "DBQ=c:\\temp\\demo.mdb"
                   )
    )

Note that if a path contains spaces it must be enclosed with double quotes.

To remove this data source:

    # create the .mdb file
    (db.dataSource "remove"
                   "Microsoft Access Driver (*.mdb")
                   "DSN=demo"
    )

`connect`

Connect to the database identified by the source property.

Arguments: none

Return type: returns the empty List () -- an error is raised if the connection failed.

`sql`

Perform the supplied SQL command returning any results as a List where each entry of the returned list is itself a list of data representing the selected row.

For example, suppose we had a table called Users which held the following information:

name age

Harry 5

Rachel 3

name	age
Harry	5
Rachel	3

If db is our Db object, then:

    (db.sql "select name, age from Users")

returns the following:

    ( ( "Harry"  5 )
      ( "Rachel" 3 )
    )

Arguments:

stmt String

Return type: returns the List of results.

`sqlAux`

Perform the supplied SQL command which takes parameters, returning any results as a List where each entry of the returned list is itself a list of data representing the selected row.

Using the above example, the following command:

    (db.sqlAux "select * from users where name = ?"
               (list "Harry")
    )

returns just the one row:

    ( ( "Harry"  5 ) )

Arguments:

stmt String

parameters List

Return type: returns the List of results.

`recordSet`

Return a record set generated from the supplied SQL command. The arguments for this function are exactly like those for the sqlAux method.

Arguments:

stmt String

parameters List

Return type: Dispatch interface onto an ADODB.RecordSet object.

`beginTransaction`

Begin a transaction on the database.

Arguments: none

Return type: returns the empty List ()

`commit`

Commit a transaction on the database.

Arguments: none

Return type: returns the empty List ()

`rollback`

Rollback a transaction on the database.

Arguments: none

Return type: returns the empty List ()

`suspend`

Set the suspendTransactions flag and return its original value.

Arguments:

flag Object

Return type: Object

`isDeadlock`

Tests if the supplied Error represents a deadlock within the database.

Arguments:

err Error

Return type: returns the empty List if this is not a deadlock error.

`ddl`

Perform the supplied DDL (Data Definition Language) command returning any results as a List where each entry of the returned list is itself a list of data representing the selected row -- if required, the DDL statement is converted to a vendor-specific form before evaluation.

Arguments:

stmt String

... optional arguments of any type

Return type: returns the List of results.

`encrypt`

Encrypt a password -- this encrypts a password so that it can be stored in the registry without allowing others to determine the password value -- the resultant string starts with *.

Arguments:

plaintext String

Return type: returns the String

`decrypt`

Decrypt an encrypted password -- if the string does not start with * then the password is not changed.

Arguments:

encodedText String

Return type: returns the String

Contents Index Current topic: databases Related topics: objects, functions

command	`String`
driver	`String`
attributes	`Object`
(optional) windowHandle	`Integer`