| # 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
  ## etsql autocommit Syntax `etsql autocommit` `dbhandle` { `on` | `off` } Purpose: Toggle between automatic transaction commit and manual commit, for the session specified. Arguments:
  `dbhandle` | Database handle obtained from `etsql connect`
  Returns: `SUCCESS` Autocommit mode set Notes: With automatic commit, each SQL operation is immediately committed. If `autocommit` is already `on` and an attempt is made to set it on again, no action is taken. If `autocommit` is already `off` and an attempt is made to set it off again, no action is taken. When setting `autocommit on`, the current transaction is implicitly committed. When setting `autocommit off`, a new transaction is started. ## etsql autoquote Syntax `etsql autoquote` { `on` | `off` } Purpose: Specify whether the `etsql update` command should automatically quote attribute values. Returns: `SUCCESS` Autoquote mode set Notes: Setting the `autoquote` mode will only affect subsequent `etsql update` commands. With `autoquote on`, the `etsql update` command will automatically put quotes around string values. In addition, if the string value contains quotes, the quotes will be repeated to indicate that they are not string value terminators. If `autoquote` is `off`, the `etsql update` command will not put quotes around values. This is useful when the value is actually a SQL expression such as `quantity * 10`, `length(name)`, etc. The default is `autoquote on`. Example
          % etsql autoquote on
          SUCCESS
          % etsql update $sth $tabname {name} {Joe}
          SUCCESS
          % etsql autoquote off
          SUCCESS
          % etsql update $sth $tabname {name} {Bill}
          Error detected by the SQL processor:
          attribute 'Bill' does not exist
          % etsql update $sth $tabname {name} {'Bill'}
          SUCCESS
  ## etsql bind_param Syntax `etsql bind_param` `sthandle para_num value` Purpose: Bind a Tcl variable to a prepared SQL statement input parameter (input parameters marked as ? in SQL statements). Returns: `SUCCESS` Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  `param_num` | Number of the parameter. Numbered left to right in prepared statement, starting at 1.
  `value` | Tcl variable to bind.
  Notes: Only input parameters can be bound in this interface. Example

          % set sthandle [ etsql prepare $dbhandle \
              "select * from customer_table where rep > ? or rep < ?" ]
          sth1
          % set var1 5200
          % set var2 4900
          % etsql bind_param (sthandle, 1 $var1 )
          SUCCESS
          % etsql bind_param (sthandle, 2 $var2)
          SUCCESS
          % etsql execute $sthandle
          SUCCESS
  ## etsql commit Syntax `etsql commit` `dbhandle` Purpose: Commit (make permanent) all database operations executed in this transaction. Arguments:
  `dbhandle` | Database handle obtained from `etsql connect`
  Returns: `SUCCESS` Transaction committed Notes: The current transaction in the session associated with the specified database handle is terminated, and a new transaction is started. If `autocommit` mode is `on`, no action is taken. ## etsql connect Syntax `etsql connect` `database_name` [`username`] [`password`] Purpose: Start a session on the specified database. Arguments:
  `database_name` | Name of database to connect to
  `username` | Name of user to establish connection
  `password` | Password of user
  Returns: Database handle, which is used by other commands to refer to this connection Notes: The database name must be a valid Empress database. It can be either a physical (absolute or relative) pathname to the database directory, or it can be a logical database name. Dynamic SQL Logical connection
      etsql connect ("dbase1")
  where `dbase1` is a logical name in a DB Server file Physical Connection
      etsql connect ("/a/b/c/dbase1")
  where `dbase1` is a real database. ODBC Logical Connection
      etsql connect ("dbase1")
  where `dbase1` is a logical name in an `odbc.ini` file Physical Connection
      etsql connect ("SERVER=sv_name;DATABASE=dbasename;[UID=username;PWD=password]")
  - Note the option to set the username and password within the datasource specification. In the Dynamic SQL version the username and password are ignored, in the ODBC version this must be a user in the server password file. SQL statements can still include database names in the table specifications, to allow operations on databases other than the current database. By default, the `autocommit` mode is `on` for the new session. Example
            % etsql connect empress_db
            dbh1

            % etsql connect bad_db
            open failed

            % catch {set dbhandle [etsql connect empress_db]}
            0

            % set dbhandle
            dbh2

            % catch {set dbhandle [etsql connect bad_db]}
            1
  ## etsql delete Syntax `etsql delete` `sthandle` `tabname` Purpose: Delete the current record in a cursor Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  `tabname` | Name of table containing record to delete
  Returns: `SUCCESS` Record deleted successfully Notes: The statement handle must refer to a `SELECT` statement which has already been executed. The cursor must be still open (i.e. not finished), and a fetch must have been performed - otherwise there is no current record to delete. The table specified must be part of the cursor. In this version, the table name must not be a complex name - i.e. it should consist of alphanumeric letters and the underscore character only, and it should not begin with a number. Cannot delete if cursor is a join of multiple tables. Cannot delete if cursor uses aggregate functions. Example
          % set sth [etsql prepare $dbh "select * from company"]
          sth8
          % etsql execute $sth
          SUCCESS
          % etsql fetch $sth
          {Empress Software} 1 {3100 Steeles Ave} (905)513-8888
          % etsql delete $sth company
          SUCCESS
  ## etsql destroy Syntax `etsql destroy` `sthandle` Purpose: Destroy a statement handle. This frees up the statement handle for reuse. Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  Returns: `SUCCESS` always returned Notes: The local version of this interface is limited to 128 statement handles at any one time. Therefore it is advisable to use `etsql destroy` to remove unwanted statement handles and free them up for reuse. This function replaces `etsql reprepare` in the previous version of the interface. ## etsql disconnect Syntax `etsql disconnect` [`dbhandle`] Purpose: Terminate the specified database session Arguments:
  `dbhandle` | Database handle obtained from `etsql connect`
  Returns: `SUCCESS` Disconnection completed Notes: If the transaction was not committed, it will be rolled back. After disconnecting, the database handle is no longer valid. If no argument is provided, all sessions are disconnected. ## etsql execute Syntax `etsql execute` `sthandle` Purpose: Execute a prepared statement identified by a statement handle Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  Returns:
  `SUCCESS` | Statement (which is not a `DELETE` or `UPDATE`) executed successfully.
  `integer` | Number of records affected by `DELETE` or `UPDATE` operation.
  Notes: A prepared statement can be executed any number of times. The SQL statement associated with the handle is sent to Empress SQL processor to be parsed and executed. For `SELECT` statements, `etsql execute` does the following: If it is the first execution of the statement, a retrieval is initialized by declaring a cursor If it is not the first execution of the statement, the cursor for the statement automatically closed The cursor is opened (but no attempt is made to fetch records). Tables involved in the cursor are opened in deferred mode. In this version, a maximum of 100 items can be selected in a `SELECT`. Example
          % set sthandle [ etsql prepare $dbhandle \
              "update customer_table set rep = 55005 where name match '* Jones'" ]
          sth1
          % etsql execute $sthandle
          2

          % set selhandle [ etsql prepare $dbhandle \
              "select * from customer_table where rep = 55001"
          sth2
          % etsql execute $selhandle
          SUCCESS

          % set sth [ etsql prepare $dbhandle "select * from bad_table" ]
          sth3
          % etsql execute $sth
          Open cursor error
          table 'bad_table' does not exist
  ## etsql fetch Syntax `etsql fetch` `sthandle` Purpose: Fetch the next row in a cursor and return the values of the selected items. Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  Returns: A list of values obtained from fetched row. Notes: The statement handle must refer to a `SELECT` statement which has already been executed. If the fetch is successful, it returns a list of `n` values, where `n` is the number of columns selected. Null values are represented by empty strings. Tcl does not handle binary data, so nulls are returned when bulk (blob) values are fetched. If there is no row to fetch, the command returns an empty string instead of a list. The command raises an error condition if an error or locked record is encountered. The `etsql fetch` command sets the `errorCode` global variable to: ETSQL SQLCODE ErrorMessage where SQLCODE is one of the following:
  0 | Row fetched successfully
  1 | Locked row
  100 | No more row
  < 0 | Error occurred
  Example
          % set sth [etsql prepare $dbhandle "select * from films"]
          sth7
          % etsql execute $sth
          SUCCESS
          % etsql titles $sth
          film_num title actors release_date type
          % etsql fetch $sth
          1021 {12 Monkeys} {Bruce Willis} {} thriller

          % set sqlcode 0
          % while {$sqlcode != 100} {
              catch {set record [etsql fetch $sth]}
              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
                          }
              }
          }
  ## etsql finish Syntax `etsql finish` `sthandle` Purpose: Close a cursor associated with the statement handle. Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  Returns: `SUCCESS` Cursor closed successfully Notes: If the statement is not a `SELECT`, or has not been executed, the command does nothing. ## etsql prepare Syntax `etsql prepare` `dbhandle` `SQL_statement` Purpose: Prepare a SQL statement for later execution. Arguments:
  `dbhandle` | Database handle obtained from `etsql connect`
  `SQL_statement` | SQL statement to prepare
  Returns: Statement handle, which is used by other commands to refer to this prepared SQL statement Notes: Data Definition Language (DDL) commands such as `CREATE`, `ALTER`, `DROP` etc. are allowed. Data Manipulation Language (DML) commands such as `INSERT`, `SELECT`, `UPDATE` etc. are allowed. Transaction commands are not allowed. Statement handles are no longer valid after disconnecting the session. Example
          % etsql prepare $dbhandle \
              "update customer_table set rep = 55005 where name match '* Jones'"
          sth1
  ## etsql quote Syntax `etsql quote` `value` Purpose: Quote a string value Arguments:
  `value` | Unquoted string value
  Returns: Quoted string value Notes: Quotes are placed at the beginning and at the end of the original value. If the original value contains quotes, the quotes will be repeated to indicate that they are not string value terminators. Useful for building SQL statements which are executed. Example
          % set sql_st "select * from customer_table where name = "
          select * from customer_table where name =
          % set value O'Leary
          O'Leary
          % append sql_st [etsql quote $value]
          select * from customer_table where name = 'O''Leary'
          % etsql prepare $dbhandle $sql_st
          SUCCESS
  ## etsql rollback Syntax `etsql rollback` `dbhandle` Purpose: Rollback (undo) all database operations executed in this transaction. Arguments:
  `dbhandle` | Database handle obtained from `etsql connect`
  Returns: `SUCCESS` Transaction committed Notes: The current transaction in the session associated with the specified database handle is terminated, and a new transaction is started. If `autocommit` mode is `on`, no action is taken. ## etsql titles Syntax `etsql titles` `sthandle` Purpose: Obtain the names of the selected items (columns) in a query. Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  Returns: List containing the names of items selected Notes: The statement handle must refer to a `SELECT` statement which has already been executed. Example
          % set sth [ etsql prepare $dbhandle \
              "select * from customer_tables where name match '* Jones'" ]
          sth4
          % etsql execute $sth
          SUCCESS
          % etsql titles $sth
          id name address rep

          % set sth [etsql prepare $dbhandle \
              "select count print num_rec, max(id) from customer_table" ]
          sth5
          % etsql execute $sth
          SUCCESS
          % etsql titles $sth
          num_rec EXPRESSION_2
  ## etsql types Syntax `etsql types` `sthandle` Purpose: Obtain the data types of the selected items (columns) in a query. Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  Returns: List containing the data types of the selected items. Data types are returned as one of the following strings BINARY CHAR DATE DECIMAL FLOAT INTEGER TEXT TIME TIMESTAMP Notes: The statement handle must refer to a `SELECT` statement which has already been executed. Example
          % set sth [ etsql prepare $dbhandle \
              "select * from customer_table where name match '* Jones'" ]
          sth4
          % etsql execute $sth
          SUCCESS
          % etsql types $sth
          INTEGER CHAR TEXT INTEGER

          % set sth [etsql prepare $dbhandle \
              "select count print num_rec, max(id) from customer_table" ]
          sth5
          % etsql execute $sth
          SUCCESS
          % etsql titles $sth
          num_rec EXPRESSION_2
  ## etsql update Syntax `etsql update` `sthandle` `tabname` `attrlist` `valuelist` Purpose: Update the current record in a cursor Arguments:
  `sthandle` | Statement handle obtained from `etsql prepare`
  `tabname` | Name of table containing record to update
  `attrlist` | List of attributes to update
  `valuelist` | List of values to assign to attributes
  Returns: `SUCCESS` Record updated successfully Notes: The statement handle must refer to a `SELECT` statement which has already been executed. The cursor must be still open (i.e. not finished), and a fetch must have been performed - otherwise there is no current record to update. The table specified must be part of the cursor. The attribute and value lists must be Tcl lists with the same number of elements. If the `autoquote` option is on (which is the default), string values are automatically quoted. Cannot update if cursor is a join of multiple tables. Cannot update if cursor uses aggregate functions. Examples
          % set sth [etsql prepare $dbh "select * from company"]
          sth9
          % etsql execute $sth
          SUCCESS
          % etsql titles $sth
          comp_name comp_num address phone
          % etsql fetch $sth
          {Mom & Pop Store} 12 {90 Side Street} (905)513-1668
          % etsql update $sth company {address phone}\
      		 {"12 Long Road" (905)513-0503}
          SUCCESS
  ## etsql version Syntax `etsql` version Purpose: Identify the version of the Empress Tcl/Tk Interface Returns: Information on this version Example
          % etsql version
          Empress Tcl/Tk Interface v1.1 (Dynamic SQL) FW