Database Access using ODBC

Introduction

Dolphin offers an Database Interface module for accessing databases through the Microsoft ODBC driver layer.

Unzip Database.ZIP into your Dolphin directory. The ZIP contains the Database Package (Database.PAC) and the Database Overview (Database.htm). Use the Package Browser to load Database.PAC into your system.

Connecting to a database

The DBConnection class defines the functionality of a database connection. One instance of DBConnection should be created for every database connection.

To connect to a datasource:

1. create a new instance of DBConnection
2. set the datasource name with #dsn:
3. set the username with #uid:
4. set the password with #pwd:
5. finally, use #connect to make the connection

Example

C := (DBConnection new)
	dsn: 'watcom32'; uid: 'dba'; pwd: 'sql';
	connect.

Alternatively, the connection details can be entered by the user at runtime using the #open message. This allows the user to select from a list of available datasources.

Example

C := DBConnection new open.

Running SQL statements

DBConnection defines a number of methods for executing SQL statements.

exec: aSqlString

• use this for statements that do not generate a result table
• answers a DBStatement that has executed the statement

Example

S := C exec: 'delete from employee where empnum=123'

query: aSqlString

• use this for statements that generate a result table
• answers a DBResultSet which can be used to browse the results of the query
• a DBResultSet responds to #first, #next etc by answering a DBRow representing the appropriate row

Example

R := C query: 'select * from employee'
R first
	"=> a DBRow(60432 'GORDON' 'G.L.' nil nil nil 'PROF' '2267' )"
R first at: #Empname
	"=> 'GORDON'"

prepare: aSqlString

• use this to prepare a statement containing the '?' place holder characters
• after preparing the statement, the attributes of the '?' place holders must be defined by sending #paramCols: with an array of DBColAttrs's.
• answers a DBParameterizedStatement that has been prepared
• generally prepared once and executed many times with varying parameters

Example

P := C prepare: 'insert into employee values (?, ?, ?, ?, ?, ?, ?, ?)'.
P paramCols: R describeCols.

P values: #(11111 'TOM' 'N' nil nil nil 'PROF' '2267' ).
P exec.
P values: #(22222 'JERRY' 'N' nil nil nil 'PROF' '2267' ).
P exec.

Transactions

DBConnection provides transaction control with the following methods.

• #beginRWTxn begin a read/write transaction
• #beginTxn begin a readonly transaction
• #commitTxn commit an outstanding transaction
• #releaseTxn rollback an outstanding transaction

Example

C beginRWTxn. "begin a transaction"
C exec: 'delete from employee where empnum=123'.
C releaseTxn. "transaction is rolled back"

Error handling

Error handling is achieved through the exception mechanism. The default outcome of an error is a walkback indicating an unhandled exception. Exceptions can be handled by enclosing the code in a block and using the on:do: method.

Example

[ R := C query: 'select xxx from employee' ]
        on: DBError do: [ :e | Transcript show: e details errors first msg; cr ]
	"=> [WATCOM][ODBC Driver]: column 'xxx' not found"

Finalization

Unreferenced connections and statements are automatically cleaned up during garbage collection. For example, evaluating the following expressions will (in the case of a Watcom database) cause the Watcom engine to appear and then disappear a few seconds later.

Example

C := (DBConnection new)
        dsn: 'watcom32'; uid: 'dba'; pwd: 'sql';
        connect.
C := nil.

Re-instatement

DBConnections and DBResultSets which exist when an image is saved will be re-instated when the image is loaded.

Catalog functions

System catalog information (such as tables, columns, indices etc) can be accessed through instances of DBConnection.

Example

C tables.
	"=> an OrderedCollection('Assignment' 'Class' 'Course' 'Employee' ... "

C columns: 'Employee'.
	"=> an OrderedCollection(
	a DBColAttr(1,Empnum,SQLInteger,4)
	a DBColAttr(2,Empname,SQLVarChar,20)... "