The Tcl Database Connectivity interface

This is a draft chapter from the book The Tcl Programming Language, now available in print and electronic form from Amazon, Gumroad and others.

1. Introduction

The Tcl Database Connectivity (TDBC) extension provides a Tcl API for accessing SQL databases. Because this API is independent of the underlying database system, most^[1] of the code accessing databases in an application can run unchanged between different database implementations.

A bit of history

TDBC 1.0, authored by Kevin B. Kenny, was released with Tcl 8.6. Prior to its release, the lack of a standard Tcl database access API lead to a number of extensions with different interfaces including

tclodbc for connecting to databases using ODBC
DIO which is part of Apache Rivet
nstcl and nsdbi, both derived from AOL Server web server
Various database-specific extensions like oratcl for Oracle and pgtcl for PostgreSQL.

With the advent of TDBC, applications can now rely on a standard interface to databases from Tcl.

TDBC is broken up into two layers:

The upper layer, which is what we cover here, is the interface used by applications to access the database.
The lower layer consists of different drivers that implement access to specific databases. At the time of writing, the TDBC distribution includes drivers for MySQL, PostgreSQL, Sqlite3 and any database accessible via an ODBC interface. The TDBC documentation also defines an interface that allows new drivers to be written for other database implementations.

1.1. Installing TDBC

The TDBC extension is included in the standard Tcl 8.6 source distributions as well as binary distributions from ActiveState.

1.2. Loading TDBC

TDBC is loaded with the standard package require Tcl command. The specific package to be loaded depends on which database driver is desired. The packages included in the core distribution are shown in Core TDBC driver packages.

Table 1. Core TDBC driver packages
Package	Database
tdbc::sqlite3	Sqlite3
tdbc::postgres	PostgreSQL
tdbc::mysql	MySQL
tdbc::odbc	Any database that is provides an ODBC interface

Naturally, when dealing with multiple database implementations in an application more than one of these packages may be loaded if desired.

For our code examples, we will make use of the Sqlite3 database and thus load the corresponding package

% package require tdbc::sqlite3
→ 1.0.4

2. Concepts

TDBC follows the same general pattern as other database access API’s and involves roughly the following steps.

First a connection^[2] has to be established to the database (and database provider) of interest. In addition to identifying the database this may also include authorization credentials and other options. All subsequent interactions for the database are done through this connection object and its surrogates.
Next a SQL statement is prepared using the connection object and then executed with the results returned as a result set.
The result set is iterated over to operate on the returned data.
The statement and result sets are freed so as to not use up resources.
Steps 2-4 are repeated as needed.
When all done, the database connection is closed.

TDBC encapsulates all the above abstractions as the TclOO classes tdbc::connection, tdbc::statement and tdbc::resultset.

3. Connecting to databases

A database connection is represented by an object of the appropriate tdbc::DRIVER::connection class. To connect to a database, you create an object of this class specifying the database of interest. The manner in which the database is identified depends on the database driver in use.

3.1. Sqlite3

The tdbc::sqlite3 package must be loaded to access Sqlite3 databases.

package require tdbc::sqlite3

To open a Sqlite3 database, the path to the sqlite3 database file is passed. For example, to open a Sqlite3 database in the current directory,

tdbc::sqlite3::connection create db my-database.sqlite3

This will create the object db representing the database connection to the my-database.sqlite3 database. As we see in a moment, we can operate on the database by invoking methods on this object.

For our sample code, we will create and make use of an in-memory Sqlite3 database. The special token :memory: results in the database being created purely in memory with no disk store. It will be erased once closed which suffices for the sample code purposes.

% set dbconn [tdbc::sqlite3::connection new :memory:]
→ ::oo::Obj124

Note here we use the TclOO method new as opposed to create which generates the connection object name for us.

3.2. ODBC

Open Database Connectivity (ODBC) is an industry standard API for accessing databases. Databases implementations that support this interface can be accessed through the tdbc::odbc package.

package require tdbc::odbc

Windows comes with ODBC support built-in but to use ODBC on Unix or Linux, you may need to install an ODBC package such as unixODBC or iODBC.

To connect to a ODBC database, a connection string has to be specified. This takes the form of a series of attribute name and value pairs that specify the connection characteristics. For example, (assuming we are on Windows)

tdbc::odbc::connection create db "Driver={SQL Server};Server=localhost;Trusted_Connection=Yes;Database=YourDatabaseName;"

will return a connection object for the SQL Server database YourDatabaseName on the local system. Notice additional attributes can be specified in the connection string. For instance, the Trusted_Connection=Yes attribute and value specify that the credentials of the Windows account under which the application is running are to be used for authorization.

Depending on the system and the database, you can define a data source name (DSN) that stores the data used to construct a connection string. Then you can simply specify the DSN to connect to the database. So the above call would then become

tdbc::odbc::connection create db "DSN=YourDSN"

You can use the ODBC utilities in TDBC to define DSN’s if the underlying ODBC implementation supports the ODBC Installer API. Alternatively, on Windows systems, you can use the ODBC applet in the Control Panel to define and configure DSN’s. On Unix/Linux, unixODBC and iODBC both provide GUI and command line means of defining DSN’s.

Connection strings are ODBC driver specific and sometimes difficult to get right. The The Connection Strings Reference web site is a useful resource to understand and construct these.

In addition to the common options supported by all TDBC drivers, some ODBC environments support the -parent option which results in a prompt for user credentials if required. See the tdbc::odbc reference for details on its use.

As described later, the package also implements some utility commands for interacting with the system ODBC manager.

3.3. MySQL

Connecting to MySQL requires the tdbc::mysql package.

package require tdbc::mysql

The package differs from Sqlite3 and ODBC in that the connection is specified through a set of options as opposed to being passed as a file name or connection string argument. These options are shown in MySQL and PostgreSQL connection options.

Table 2. MySQL and PostgreSQL connection options
Option	Description
`-host HOSTNAME`	The name of the system on which the database server is running. Defaults to the local system.
`-port PORTNUMBER`	The TCP/IP port number on which the server is listening for connections.
`-user USERNAME`	The name of the user name to be used to access the database. Defaults to the current user id of the process.
`-password PASSWORD`	The password to be presented to the server. By default no password is presented.
`-database DATABASE`	The name of the database to default to if no database is specified in a query. Defaults to the default database for the user specified by the `-user` option.

The MySQL driver supports additional driver-specific options that we do not describe here. See the tdbc::mysql reference pages for details.

3.4. PostgreSQL

Connecting to PostgreSQL database is more or less identical to what was described previously for MySQL. The tdbc::postgres package is loaded

package require tdbc::postgres

and the connection is made using the options show in MySQL and PostgreSQL connection options.

The PostgreSQL driver also supports additional driver-specific options. See the tdbc::postgres reference pages.

3.5. Connection options

All TDBC drivers understand the common set of options shown in Connection options common to all TDBC drivers.

Table 3. Connection options common to all TDBC drivers
Option	Description
`-encoding NAME`	Name of the character encoding to be used on the connection. NAME should be one of the names returned by the Tcl `encoding` command. It is generally not necessary to specify this but be aware that drivers differ in their handling of this option.
`-isolation ISOLATION`	Specifies the transaction isolation level needed for transactions on the database. ISOLATION must be one of `readuncommitted`, `readcommitted`, `repeatableread`, or `serializable`. See the tdbc::connection reference for details.
`-timeout MILLISECS`	Specifies the interval after which an operation should time out with an error. The default value of `0` indicates no timeout. The operations to which the timeout is applicable differs between the various drivers and databases. Refer to the appropriate reference pages for each driver.
`-readonly BOOLEAN`	If specified as `true` or `1`, the connection will not modify the database.

3.6. Configuring connections

The values of the options that can be specified at the time a tdbc::connection object is created can be retrieved via the configure method of the connection object. The same method can also be used to modify the values of some options.

So to retrieve the configuration of our in-memory sample database.

% $dbconn configure
→ -encoding utf-8 -isolation serializable -readonly 0 -timeout 0

We can also pass one or more configuration options to be modified.

% $dbconn configure -timeout 1000

3.7. Freeing resources

When no longer required, database connections must be closed by invoking the close method on the connection object.

db close

This will also close and release resources related to tdbc::statement and tdbc::resultset objects created through the connection.

4. Basic operations

4.1. Executing SQL

Executing a SQL statement involves first retrieving a tdbc::statement object via the prepare method of a tdbc::connection. Thus to add a table to our sample in-memory database that we connected to earlier,

% set stmt [$dbconn prepare {CREATE TABLE Accounts (Name text, AcctNo text, \
    Balance double)}]
→ ::oo::Obj124::Stmt::1

The execute method of the tdbc::statement object is then invoked to run the SQL statement.

% set res [$stmt execute]
→ ::oo::Obj125::ResultSet::1

The execute method returns a tdbc::resultset object which we will examine later. For now, we free up both objects by invoking their close method. Like tdbc::connection objects, tdbc::statement and tdbc::resultset objects should also be freed when no longer required.

% $stmt close

Note that closing the $stmt also closes any contained resultset objects so we do not need to explicitly close $res here. Similarly, tdbc::statement objects that are not closed explicitly will be closed when the owning tdbc::connection object is closed. However, for the sake of saving resources, it is generally a good idea to explicitly release them when no longer needed. Since we have more we want to do with the connection and are not closing it, we explicitly close $stmt.

Insertions and queries follow a similar pattern.

% set stmt [$dbconn prepare {INSERT INTO Accounts (Name, AcctNo, Balance) VALUES \
    ('Tom', 'A001', 100.00)}]
→ ::oo::Obj124::Stmt::2
% $stmt execute
→ ::oo::Obj127::ResultSet::1
% $stmt close

Will also close the result set returned by execute

This multi-step sequence of prepare and execute can be a little tedious and TDBC provides some methods that act as wrappers and make it more convenient. We will discuss these and their pros and cons a little later.

4.2. Passing values to SQL

The above example hard-coded the values that were to be inserted into the table. Naturally, that is not a viable option when values are not known apriori at the time a program is written.

TDBC allows for Tcl variable values to be passed into the SQL statement by replacing all names within the SQL that begin with : by their ''corresponding'' values. These values may either come from Tcl variables of the same name or from a dictionary passed in. Both are illustrated below.

% set stmt [$dbconn prepare {
    INSERT INTO Accounts (Name, AcctNo, Balance) VALUES (:name, :acctno, \
        :balance)
}]
→ ::oo::Obj124::Stmt::3
% foreach {name acctno balance} {
    Dick  A002 200.00
    Harry A003 300.00
} {
    $stmt execute
}

Here the values to be inserted are picked up from Tcl variables.

Alternatively, we can pass in a dictionary to the execute command. The values will be picked up from the keys of the same name in the dictionary.

% $stmt execute {acctno A004 name Moe balance 100.00} 
→ ::oo::Obj129::ResultSet::3
% $stmt close

Order of elements does not matter

Note from the sequence above that a prepared statement can be executed multiple times with different values.

4.3. Retrieving data

Querying data from the database follows the same exact pattern but now we also have to extract data from the tdbc::resultset object returned by any execute invocations.

% set stmt [$dbconn prepare {SELECT Name, Balance from Accounts}]
→ ::oo::Obj124::Stmt::4
% set res [$stmt execute]
→ ::oo::Obj133::ResultSet::1

We now have a tdbc::resultset object holding the result of the query.

Each row in the result set can be returned with the nextlist and nextdict methods. In both cases, each call returns the next row from the result set. The row is stored in a variable whose name is passed to the method and the method itself returns 1 when a row is returned successfully and 0 if there is no more data to be returned.

The difference between the two is that nextlist returns the row as a list in the same order as returned by the columns method while nextdict returns the row as a dict with column names as keys.

% $res columns
→ Name Balance
% $res nextlist val
→ 1
% print_list $val
→ Tom
  100.0
% while {[$res nextdict val]} {
    print_dict $val
}
→ Balance   = 200.0
  Name      = Dick
  Balance   = 300.0
  Name      = Harry
  Balance   = 100.0
...Additional lines omitted...

4.4. Convenience methods

As we have seen above, database operations involve calling the prepare and execute methods and then freeing the tdbc::statement and tdbc::resultset objects. To ensure proper cleanup, the sequence has to be wrapped in try or catch blocks. So in pseudocode the code looks roughly like this:

set stmt [$dbconn prepare SQL_STATEMENT]
try {
    set res [$stmt execute]
    try {
        Loop using [$res nextdict] or [$res nextlist]
    } finally {
        $res close
    }
} finally {
    $stmt close
}

TDBC provides some convenience methods that takes care of all the boilerplate in the above.

4.4.1. The `allrows` method

The allrows method encapsulates the above pseudocode where the loop processing consists of simply collecting all results returned by nextdict or nextlist into a single list.

The method is supported by all three object types:

In the case of tdbc::resultset, allrows simply iterates over the result set collecting the output of nextlist or nextdict methods.
In the case of tdbc::statement, the method executes the statement and then collects rows from the returned result set as described in the previous case.
In the case of tdbc::connection, the method prepares the SQL passed as a parameter then executes the returned statement as described in the previous case.

In all cases, the allrows method takes care to free up objects and resources appropriately even in case of errors.

Below we illustrate a simple query using the different methods, first without using allrows.

% set query_values [dict create amount 200]
→ amount 200
% set stmt [$dbconn prepare {
    SELECT Name FROM Accounts WHERE Balance >= :amount
}]
→ ::oo::Obj124::Stmt::5
% set rows {}
% try {
    set res [$stmt execute $query_values]
    try {
        while {[$res nextdict row]} {
            lappend rows $row
        }
    } finally {
        $res close
    }
} finally {
    $stmt close
}
% print_list $rows
→ Name Dick
  Name Harry

Now the same code but using the allrows method of the tdbc::resultset object. Note this returns the rows as dictionaries by default.

% set stmt [$dbconn prepare {
    SELECT Name FROM Accounts WHERE Balance >= :amount
}]
→ ::oo::Obj124::Stmt::6
% set rows {}
% try {
    set res [$stmt execute $query_values]
    try {
        set rows [$res allrows] 
    } finally {
        $res close
    }
} finally {
    $stmt close
}
→ {Name Dick} {Name Harry}
% print_list $rows
→ Name Dick
  Name Harry

Replaces the inner loop in the previous example

Above, we have only saved writing the innermost loop in the code. We can go another step further and use the allrows method of the tdbc::statement object. Note the difference from the allrows method of the tdbc::resultset in that here we need to pass in the values to be used for querying to the allrows method.

% set rows {}
% set stmt [$dbconn prepare {
    SELECT Name FROM Accounts WHERE Balance >= :amount
}]
→ ::oo::Obj124::Stmt::7
% try {
    set rows [$stmt allrows $query_values] 
} finally {
    $stmt close
}
→ {Name Dick} {Name Harry}
% print_list $rows
→ Name Dick
  Name Harry

We do not have to explicitly deal with result sets

Finally, we go the whole hog and invoke allrows on the database connection itself. Obviously, in this case we have to tell it the SQL we want to run in addition to passing in the query values.

% set rows [$dbconn allrows {
    SELECT Name FROM Accounts WHERE Balance >= :amount
} $query_values] 
→ {Name Dick} {Name Harry}
% print_list $rows
→ Name Dick
  Name Harry

We do not have to deal with statements

Given this last illustration is so much shorter than the previous examples, why would one pick any of the others? The Tcl Database Connectivity paper provides some hints:

With very large databases and result sets, allrows may be unworkable because of the infeasibility of collecting all rows in memory before processing.
Explicitly dealing with result sets allows for fine-grained control of the iteration, for example terminating the iteration based on some complex rules outside of SQL’s capabilities.

The format of each returned row can be controlled by the -as option which may take the values lists or dicts (default). An additional option -columnsvariable allows retrieval of the names of the returned columns.

% set rows [$dbconn allrows -as lists -columnsvariable cols {
    SELECT Name,Balance FROM Accounts WHERE Balance >= :amount
} $query_values]
→ {Dick 200.0} {Harry 300.0}
% print_list $rows
→ Dick 200.0
  Harry 300.0
% puts $cols
→ Name Balance

4.4.2. The `foreach` method

The foreach method has a purpose very similar to allrows except that instead simply collecting results into a list, it executes a given script for every row in the result set. Because of its similarity to allrows, we do not discuss it in detail but only illustrate it as invoked on a tdbc::connection object. Note that tdbc::statement and tdbc::resultset also implement the method in suitably modified form.

% $dbconn foreach row {
    SELECT Name FROM Accounts WHERE Balance >= :amount
} $query_values {
    puts $row
}
→ Name Dick
  Name Harry

Like allrows, foreach also takes care of all intermediate bookkeeping.

5. Miscellaneous topics

5.1. Transactions

There are a couple of ways an application may make use of transactions. We describe both below.

5.1.1. Using the `transaction` method

The first is making use of the transaction method of tdbc::connection. This begins a transaction on the connection and evaluates the passed script. If the script terminates ''normally'' - with a return, break or continue - the transaction is committed. For other return codes, including errors, the transaction is rolled back and the error is rethrown.

Use of the method is illustrated by the simplistic example below to transfer funds from one account to another.

% set transfer { from "Tom" to "Dick" amount 50 }
→  from "Tom" to "Dick" amount 50
% $dbconn transaction {
    $dbconn allrows -as lists -- {
        UPDATE Accounts
        SET Balance = Balance - :amount
        WHERE Name=:from
    } $transfer 

    puts "Within transaction: [$dbconn allrows -as lists -- {
            SELECT Name, Balance FROM ACCOUNTS WHERE Name=:from
    } $transfer]" 

    error "Pretend something went wrong"
    $dbconn allrows -as lists -- {
        UPDATE Accounts
        SET Balance = Balance + :amount
        WHERE Name=:to
    } $transfer 
}
Ø Within transaction: {Tom 50.0}
  Pretend something went wrong
% $dbconn allrows -as lists -- {
    SELECT Name, Balance FROM Accounts WHERE Name=:from
} $transfer 
→ {Tom 100.0}

	Deduct from Tom’s balance
	Verify balance updated within transaction
	Add to Dick’s balance
	Verify Tom’s balance restored to original

Notice that Tom’s balance is restored as the transaction was aborted by an error exception.

5.1.2. Using `begintransaction`, `commit` and `rollback`

In cases where the sequence of operations in a transaction cannot be neatly wrapped in a script that can be passed to the transaction method, an application can explicitly manage the transaction itself by calling the begintransaction method of a tdbc::connection object. Then at some later point, it can either call the commit or rollback methods to either complete or abort the transaction.

Rewriting the previous example,

% set transfer { from "Tom" to "Dick" amount 50 }
→  from "Tom" to "Dick" amount 50
%
% $dbconn begintransaction 
% $dbconn allrows -as lists -- {
    UPDATE Accounts
    SET Balance = Balance - :amount
    WHERE Name=:from
} $transfer
%
% puts "Within transaction: [$dbconn allrows -as lists -- {
            SELECT Name, Balance FROM ACCOUNTS WHERE Name=:from
} $transfer]"
→ Within transaction: {Tom 50.0}
%
% if {[catch {
    error "Pretend something went wrong"
    $dbconn allrows -as lists -- {
        UPDATE Accounts
        SET Balance = Balance + :amount
        WHERE Name=:to
    } $transfer
}]} {
    $dbconn rollback 
} else {
    $dbconn commit 
}
% $dbconn allrows -as lists -- {
    SELECT Name, Balance FROM Accounts WHERE Name=:from
} $transfer
→ {Tom 100.0}

	Begin the transaction
	On error, rollback the transaction
	On success, commit the transaction

5.2. Handling NULL values

Noting that the empty string "" is not the same as the SQL NULL value, there is no way to represent NULL in Tcl where everything is a string. In some applications, the distinction is not important and the empty string can be used interchangeably with NULL. In cases where the distinction is important, the dictionary-based interface to TDBC methods should be used as illustrated here.

Writing NULL values

To write NULL to a table column, pass to the appropriate method a dictionary containing the bound variable values for the columns. A NULL will be stored in any column for which a corresponding key is not present in the dictionary.

% $dbconn allrows {
    INSERT INTO Accounts (Name, AcctNo, Balance) VALUES (:name, :acctno, \
        :balance)
} {name Curly}

Similarly, to retrieve data containing NULL, use one of the forms that returns rows as dictionaries. If a value is NULL, the returned dictionary for the row will not contain the corresponding key.

% $dbconn allrows {SELECT Name, AcctNo, Balance FROM Accounts WHERE Name='Curly'}
→ {Name Curly}

Note that the keys AcctNo and Balance are missing.

You could have also used the tdbc::resultset::nextdict method for the same purpose.

Note the result when list format is used.

% $dbconn allrows -as lists {SELECT Name, AcctNo FROM Accounts WHERE Name='Curly'}
→ {Curly {}}

In this case there is no way to distinguish whether the stored value in the database was actually "" or NULL.

5.3. Stored procedures

Stored procedures can be invoked with the preparecall method of a tdbc::connection object. Like the prepare method, this also returns a tdbc::statement object which can then be used as described earlier.

5.4. Introspection

All TDBC classes allow for introspection and inspection of the meta-information associated with databases. We only describe a few of these. See TDBC reference pages for a full list and details.

TDBC keeps track of the objects that are still open within a database connection.

% $dbconn statements
→ ::oo::Obj124::Stmt::4
% $dbconn resultsets
→ ::oo::Obj133::ResultSet::1

Clearly we forgot to release some objects. This is actually useful for a final cleanup, for example on a per request basis to a web server that leaves the database connection open.

We can retrieve schema information from the database…

% $dbconn tables
→ accounts {type table name accounts tbl_name Accounts rootpage 2 sql {CREATE T...

…or about columns within a database…

% print_dict [$dbconn columns Accounts]
→ acctno    = cid 1 name acctno type text notnull 0 pk 0 precision 0 scale 0 nu...
  balance   = cid 2 name balance type double notnull 0 pk 0 precision 0 scale 0...
  name      = cid 0 name name type text notnull 0 pk 0 precision 0 scale 0 null...

…or about a specific column.

% print_dict [$dbconn columns Accounts Balance]
→ balance   = cid 2 name balance type double notnull 0 pk 0 precision 0 scale 0...

See TDBC reference pages for the meaning of the returned dictionaries.

5.5. ODBC utilities

The tdbc::odbc package provides some utility commands related to interacting with the ODBC manager. Some of these depend on the system ODBC manager’s support of the ODBC Installer API.

The tdbc::odbc::drivers command enumerates the installed ODBC drivers on the system.

% package require tdbc::odbc
→ 1.0.4
% print_dict [tdbc::odbc::drivers]
→ SQL Server   = APILevel=2 ConnectFunctions=YYY CPTimeout=60 DriverODBCVer=03....

The tdbc::odbc::datasources command enumerates the configured ODBC data sources on the system.

% print_dict [tdbc::odbc::datasources]
→ Excel Files              = Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.x...
  MS Access Database       = Microsoft Access Driver (*.mdb, *.accdb)
  Visio Database Samples   = Microsoft Access Driver (*.mdb, *.accdb)

Finally, you can create a new ODBC data source with the tdbc::odbc::datasource command. The reference pages have examples of this.

6. References

TIPTDBC: Tcl Database Connectivity (TDBC), Kevin B. Kenny et al, Tcl Improvement Proposal #350, http://www.tcl.tk/cgi-bin/tct/tip/350
KBKPAPER: Tcl Database Connectivity, Kevin B. Kenny, Tcl Conference Proceedings, 2008. The original paper describing TDBC. Available from http://www.tclcommunityassociation.org/wub/proceedings/Proceedings-2008/proceedings/tdbc/tcl2k8-kenny-withfonts.pdf
TDBCREF: TDBC reference pages, http://www.tcl.tk/man/tcl8.6/TdbcCmd/contents.htm
WWWCONNSTR: The Connection Strings Reference, https://www.connectionstrings.com/

1. Some code will be necessarily specific to databases because of differing capabilities and quirks in database implementations.

2. Not to be confused with a network connection.

The Tcl Database Connectivity interface

1. Introduction

1.1. Installing TDBC

1.2. Loading TDBC

2. Concepts

3. Connecting to databases

3.1. Sqlite3

3.2. ODBC

3.3. MySQL

3.4. PostgreSQL

3.5. Connection options

3.6. Configuring connections

3.7. Freeing resources

4. Basic operations

4.1. Executing SQL

4.2. Passing values to SQL

4.3. Retrieving data

4.4. Convenience methods

4.4.1. The allrows method

4.4.2. The foreach method

5. Miscellaneous topics

5.1. Transactions

5.1.1. Using the transaction method

5.1.2. Using begintransaction, commit and rollback

5.2. Handling NULL values

5.3. Stored procedures

5.4. Introspection

5.5. ODBC utilities

6. References

4.4.1. The `allrows` method

4.4.2. The `foreach` method

5.1.1. Using the `transaction` method

5.1.2. Using `begintransaction`, `commit` and `rollback`