Introduction

The Empress Tcl/Tk interface is a Tcl/Tk extension which allows Tcl/Tk applications to access the Empress RDBMS and manipulate information held in Empress databases.

The interface provides commands to connect to databases, perform SQL operations (such as inserting, deleting, updating records) on tables, use cursors to fetch records in tables, handle transactions, etc.

Some database administration can also be performed since SQL statements such as create table, create index, create range, alter table, drop table, drop index, grant, revoke, etc. can be executed using the interface.

The Empress Tcl/Tk interface is not meant to be a replacement for Embedded/Dynamic SQL for C programs. It aims to provide database access and functionality to Tcl/Tk, allowing quick and simple database applications to be created using Tcl/Tk scripts. As such, it becomes a useful tool for prototyping applications.

This document assumes that the reader is already familiar with the Tcl/Tk language, and SQL concepts. It is written for the freeware version of the Empress Tcl/Tk interface, which (as its name implies) is available free of charge. The freeware interface is designed to be used with Empress RDBMS V6.8, and Tcl v7.4 or v7.5 or higher.

The "etsql" Command

The Empress Tcl/Tk interface adds only one command to the Tcl language: etsql. It takes one or more arguments, and the first argument specifies the database operation to perform.

Syntax

   etsql | connect dbname [username] [password]    |
         | disconnect [dbh]                        |
         | commit dbh                              |
         | rollback dbh                            |
         | autocommit dbh | on  |                  |
         |                | off |                  |
         | prepare dbh statement                   |
         | execute sth                             |
         | titles sth                              |
         | types sth                               |
         | fetch sth                               |
         | finish sth                              |
         | destroy sth                             |
         | delete sth tabname                      |
         | update sth tabname attrib_list val_list |
         | quote string                            |
         | autoquote | on  |                       |
         |           | off |                       |
         | version                                 |

Note that commands in Tcl are case-sensitive.

The etsql command returns values as follows:

It passes back a result which is either a string or a list
It passes back one of these return codes (which can be trapped using the Tcl catch command)
- TCL_OK (0)
- TCL_ERROR (1)
On failure, it puts an error message in the Tcl variable errorInfo (in interactive Tcl shell, this message is sent to stderr)
On failure, it sets Tcl variable errorCode (by default, this variable is set to NONE) to the following:
ETSQL Code ErrorMessage

On success, the etsql command will return TCL_OK and pass back a result if appropriate. On failure, it will return TCL_ERROR and pass back an error message, and sets global variable errorCode. If the error was detected by the SQL processor, then the Code in errorCode will contain the value of SQLCODE.

For more details regarding Empress Embedded and Dynamic SQL, as well as the possible meanings of SQLCODE, please refer to these Empress manuals:

B1 - Empress Host Language: Command Language Interface
B5 - Empress Host Language: Dynamic SQL

Using the Interface

Connect to Database

The first step in using the interface to access Empress databases is to use the etsql command to connect to a database:

    % etsql connect my_database
    dbh1

After a successful connection, a database session is started, and a database handle (dbh1 in the example above) is returned to Tcl. In a Tcl script, this database handle would most likely be assigned to a variable:

    % set dbhandle [etsql connect my_database]

To terminate a database session, disconnect from the database:

    % etsql disconnect $dbhandle
    SUCCESS

Executing SQL Commands

During a database session, SQL operations can be performed on the database. SQL statements need to be prepared before they are executed.

    % etsql prepare $dbhandle "insert into customer_table (id, name) \
                                 values (1095, 'Joe Bloggs')"
    sth1

When a SQL statement is prepared, a statement handle is returned to Tcl. To execute the SQL statement:

    % etsql execute sth1
    SUCCESS

For most SQL operations, etsql execute simply returns SUCCESS on success. For update or delete operations, etsql execute returns the number of records affected.

    % etsql prepare $dbhandle "update customer_table set rep = 55003 \
                                 where id between 1000 and 2000"
    sth2
    % etsql execute sth2
    12

Retrieving Records

If the SQL statement is a SELECT, then the

etsql
execute

command implicitly opens a SQL cursor. The etsql titles command will return a list of selected item names in a cursor:

    % set sthandle [etsql prepare $dbhandle "select * from customer_table"]
    % etsql execute $sthandle
    SUCCESS
    % etsql titles $sthandle
    id name address rep

The etsql fetch command is used to fetch the next record in a cursor. It returns a list of values obtained from the record obtained.

    % etsql fetch $sthandle
    1012 {Mary Lamb} {901 North Street, Oxford} 55002

The etsql fetch command will return an empty string if there is no more record to fetch. Hence, in a Tcl script, a loop similar to the following could be used to obtain all records:

    set sthandle [etsql prepare $dbhandle "select * from customer_table"]
    etsql execute $sthandle
    set record x
    while {$record != ""} {
	set record [etsql fetch $sthandle]
        puts $record
    }

Alternatively, the global variable errorCode can be used. It will always contain the value of SQLCODE after a fetch operation. For example, if there is no record to fetch:

    % etsql fetch %sthandle
    % set errorCode
    ETSQL 100 {no more record}

SQLCODE can be used to determine whether there wasn't another record, whether it was locked, or any other error.

    set sqlcode 0
    while {$sqlcode != 100} {
        catch {set record [etsql fetch $sthandle]}
        set sqlcode [lindex $errorCode 1]
        switch $sqlcode {
            0       { puts $record       ;# got record }
            1       { continue           ;# skip locked record }
            100     { break              ;# no more record }
            default { puts $errorInfo    ;# error
                      break
                    }
        }
    }

In the example above, the catch command is necessary to trap error conditions such as locked record, expression error, etc.

After a successful fetch, the record obtained becomes the current record. This current record can be deleted or updated. Note that after deleting the current record, no record is considered current until another fetch is performed.

    % etsql fetch $sthandle
    1028 {Tom Little} {90 Side Road, Cambridge} 55007
    % etsql delete $sthandle
    SUCCESS
    % etsql fetch $sthandle
    1031 {Bob Peel} {231 Slippery Street, London} 55005
    % etsql update $sthandle $tabname {name rep} {"Robert Peel" 55006}
    SUCCESS

Quoting String Values

When the etsql update command is used to update the current record, the values supplied are treated as string constants by default. In other words, the Empress Tcl/Tk interface will automatically quote the values. For example, this command:

    etsql update $sthandle customer_table {name rep} {O'Connor 55008}

is converted to the following SQL statement:

    UPDATE TABLE 'customer_table' SET ATTR 'name' = 'O''Connor',
                                      ATTR 'rep' = '55008'

Note that if the string value contains a quote, that quote is repeated so that the SQL processor knows that it is not the end of the string value.

On the other hand, what if the value is not a constant, and you don't want it to be quoted ? For example, this command:

    etsql update $sthandle login_table {login_time} {now}

becomes the following SQL statement:

    UPDATE TABLE 'login_table' SET ATTR 'login_time' = 'now'

where the value assigned to attribute login_time is the string now, instead of the current time. (In Empress SQL, now is an operator which returns the current time). To make Empress SQL evaluate the operator now, it must not be quoted.

To handle these cases, the etsql autoquote command is used to specify the behaviour of the etsql update command with regards to quoting. If autoquote is set on (which is the default), values will automatically be quoted. If autoquote is set off, values are not quoted, and it is the responsibility of the user to put any necessary quotes for string values.

Autoquoting is only performed by the etsql update command. For SQL statements which are prepared and executed, values are not automatically quoted. For example:

    % etsql prepare "insert customer_table (name) values (Joe)"
    sth1
    % etsql execute sth1
    Error detected by the SQL processor:
    attribute specification not allowed

The error occurs because the value Joe is not considered to be a constant - it is considered to be an attribute name, because it is not quoted.

For the convenience of the programmer, the etsql quote command can be used to quote string values. It will also repeat any single quote contained in the string value.

    % etsql quote O'Brian
    'O''Brian'

It is good practice to use the quote command for string values when building SQL statements to be prepared and executed.

Transaction Handling

By default, operations on an Empress database take effect immediately, and cannot be cancelled. This behaviour is called autocommit, as each database operation is automatically committed.

In some cases, a group of operations need to be considered as a single transaction so that if one of the operations fail, then the entire transaction should be cancelled. In such cases, autocommit can be turned off, so changes made by database operations (such as inserts, deletes, updates) are not permanent until the transaction is committed.

    etsql autocommit $dbhandle off
    set s1 [etsql prepare $dbh "update t1 set a=10"]
    etsql execute $s1
    set s2 [etsql prepare $dbh "update t2 set a=20"]
    etsql execute $s2
    etsql commit $dbhandle

After committing the transaction, a new transaction is automatically started, if autocommit is off. To undo operations in a transaction, a rollback should be done. This allows an application to cancel previous operations, for example, in case one of the operations has failed. When a rollback is performed, a new transaction is automatically started, if autocommit is off.

    if {[catch {etsql execute $s2}] == 1} {
    	etsql rollback $dbhandle
    } else {
    	etsql commit $dbhandle
    }

One of the concerns regarding the use of transactions is that locks placed on records are not removed until the transaction is committed or rolled back. Thus, it is possible that record locks are accumulated till the maximum is reached. To avoid lock accumulation, the autocommit feature can be used. If autocommit is on, each database operation is automatically committed after execution, hence locks are not accumulated. This is the default setting for any new connection.

    % etsql autocommit $dbhandle on
    SUCCESS

With autocommit on, there is no need to commit the transaction. Also, there is no possibility to undo an operation, since it has already been committed.

Commands Reference

Conventions

Keywords	should be typed exactly as is
Arguments	can be constants or Tcl variables
[ ... ]	indicate that an argument is optional
{ X \| Y }	indicate that the argument can be X or Y