Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.


Channels ▼
RSS

Regular Expressions: Tcl and Database Managers -- A Survey


August 2001

Regular Expressions: Tcl and Database Managers -- A Survey

by Cameron Laird and Kathryn Soraiz

Welcome! "Regular Expressions" (RE) is a column on scripting languages and related subjects that we've been co-authoring since 1998. After a few months of vacation this summer, RE has found a new home with UnixReview.com. We expect to bring you a new installment each month for a long time to come.

For news on past RE columns and plans for the future, see the RE site at: http://www.regularexpressions.com/. Over the next few months, we expect to describe a new technique for e-mail filtering, ways to improve the visual appearance of your graphical user interface (GUI) applications, underappreciated languages, and more.

This month, though, we begin with a look at Tcl's ability to work with database management systems (DBMSs).


Tcl's Fitness for DB Work

Tcl's a fine language for database (DB) work -- certainly far handier than the C most programmers assume is "natural." It helps to have a guide, though, when you're starting out.

That's characteristic of Tcl. The language has had a consistent character since John Ousterhout first invented it in 1988, to "glue" together other programs that interested him. Through all that time, Tcl has been wonderfully capable and productive for working programmers, and simultaneously a bit mysterious for beginners. If Tcl is like Visual Basic (VB) in that its syntax is easy for non-programmers, it's the opposite of VB in the way it's managed. VB makes a nice first impression, but has a lot of limits. Sometimes it's hard to know where to start with Tcl, but it usually turns out there's a good solution for the problem at hand.

Database connections are an example. Several other languages, including Java, PHP, and Perl, are known for their database facilities. Although Tcl has essentially all the database functionality of these other languages, Tcl programmers have never gone to the trouble of wrapping up that functionality to match market expectations. Therefore, we'll introduce a few of Tcl's database capabilities, and tell you how to get started and where to find out more information.


Tcl's Database Heritage

Oratcl is a prime example of Tcl's traditional focus on technical polish, even at the occasional expense of friendliness to beginners. Oratcl binds Oracle to Tcl, in a combination with superlative engineering credentials. However, Oratcl's cosmetic appearance has been so neglected that it only acquired its own home page within the last couple of years. It seems to have found permanent comfort, though, at: http://sourceforge.net/projects/oratcl. As with all Tcl's other database connections, to use Oratcl, you must first install Tcl, then install Oratcl as an extension. Few vendors bundle Oratcl together with base Tcl.

A trip to the Oratcl site is worth the effort, though. Oratcl 3.1 works with Oracle 8.1 and above, and Tcl 8.1 and above. Oratcl 3.2, released this spring, requires Tcl 8.2 or higher. Oratcl is quite mature; in fact, you might say that it's the senior binding of a major language other than C to a major database. Independent consultant Tom Poindexter began work on Sybtcl (Sybase and Tcl) and Oratcl in 1992. Not long after that, Oracle itself began using Oratcl as the basis for several of its administrative products. Despite this, the company has consistently emphasized C (and later Java), along with proprietary PL/SQL, as the only "proper" ways to access Oracle. Don't let this rather politicized message mislead you; Oratcl and other scripted database bindings are wonderfully productive ways to develop high-quality applications. There appear to be thousands of organizations around the world relying on crucial Oratcl applications in engineering, point-of-sale, industrial automation, electronic commerce, biomedicine, and many other domains.

The best places to read about Oratcl are in two books from O'Reilly and Associates. Poindexter contributed chapters on Oratcl and Sybtcl to the 1997 collection, Tcl/Tk Tools, and both chapters are up to his usual standards for lucidity and organization. While Poindexter actually did most of the writing as early as 1995, the chapters have "aged" well; almost all of their technical details remain usable. Just this year, O'Reilly released Andy Duncan and Sean Hull's Oracle & Open Source. Duncan and Hull are experienced Oracle developers who capture just the information working programmers are likely to need. Two of their book's chapters are "Tcl, Perl, and Python" and "Building Oracle Applications with Perl/Tk and Tcl/Tk."

Todd Helfter at Purdue University now maintains the Oratcl home page, with pointers to versions legacy 3.01 and 2.7 of Oratcl along with 3.1 and 3.2. He observes that, "The second best place [after Poindexter's chapter] to look for how to use Oratcl is a combination of the MAN page and the test suite that comes with the distribution. Both contain many examples, especially of features introduced since Tom's book chapters."

Code samples communicate little in a survey such as this. Most of what's interesting about a particular binding has to do with its internal implementation, rather than the syntax a user sees. However, we do want to show the source for one tiny application. While this is an Oratcl example, the bindings for most other databases look similar. The real point is to contrast this brevity and expressiveness with the contortions typical for report-writers coded in C or Java:

      package require Oratcl
      set connection [oralogon name/pass]
      set cursor [oraopen $connection]
	# Use Oracle's distribution sample database.
      orasql $cursor "select empno, ename from emp by empno"
	# Iterate over all rows the cursor returns.
      orafetch $cursor {
          puts [format "%8s, %-20.20s" @1 @2]
      }
    


ODBC Popular

Open Data Base Connectivity (ODBC) is likely the most widely recognized programming interface for database work, especially in organizations influenced by Microsoft products. Tcl supports ODBC, of course. Roy Nurmi moved Tclodbc 2.2 to SourceForge just last October. He opens the Tclodbc home page with a succinct description of his motivation:

a need for a simple SQL-interface to a DBMS for database administrators and...the particular suitability of the Tcl language for just this kind of task

Nurmi also answers a question that I'm often asked: Can a scripting language be fast enough for database work? He writes:

The most common disadvantage script languages are claimed to suffer is inefficiency. However, when working with remote databases, the question of efficiency is in most cases a question of the particular DBMS and database design. The script language is used just for communicating with the DBMS, and possible inefficiencies in a script language are neglible for the whole task in question.
This is simply true. In fact, I've been on several projects that adopted a Tcl-database solution to improve performance. This makes sense when you realize how slick and quick the native Tcl graphical user interface, Tk, is, compared to many database-specific "form builders" and "client designers."

Finally, Nurmi made a point with me that applies to all the bindings listed here:

One quite marvelous thing with Tcl I have found out is that database access code seems to be relatively easy to port, e.g., from Windows to Unix, which often is not the case with ... [other] tools. Tcl/Tk users of course know this beforehand, but newcomers may not know it for certain.
He's right; database applications developed with Tcl most often just work correctly when moved between different operating systems. This can surprise developers accustomed to other client-server application toolkits.


Sybtcl a Peer to Oratcl

Sybtcl is Oratcl's brother. Poindexter started them both almost a decade ago, wrote chapters on both for Tcl/Tk Tools, and created parallel command sets in the two extensions. The current home for Sybtcl is: http://www.nyx.net/~tpoindex/tcl.html#Sybtcl. The latest sources are available through: ftp://ftp.scriptics.com/pub/tcl/nightly-cvs/sybtcl.


Postgres Gives Open Source Capabilities

Postgres pairs with Tcl to make an all-open-source combination that's widely used in Web applications and departmental projects. Two combinations, in fact: along with pgtcl, the analogue to Oratcl and the other bindings here, Postgres also boasts a special form of Tcl. PL/Tcl is Tcl for stored procedures, that is, executable code within the database server. While most high-end database management systems offer stored procedures, they generally do so only in a more-or-less proprietary language. Postgres is, to the best of my knowledge, unique in exposing Tcl as a language for stored procedures.

The home pages for pgtcl and PL/Tcl are: http://www.postgresql.org/docs/programmer/pgtcl.htm and http://www.postgresql.org/docs/postgres/c4436.htm, respectively.


DB2 Competes with Oracle

IBM's DB2 database management system is like Postgres in that during the last two years it emerged from the obscurity of a "specialized" product to wide acceptance on popular servers. Unlike Postgres, though, which has a long history of co-operation with Tcl, the DB2 binding at: http://www.neosoft.com/tcl/ftparchive/sorted/databases/db2tcl is not actively maintained. Most DB2 workers with Tcl appear to use DB2's built-in ODBC driver.

Informix , Adabas , and several other industrial-strength SQL-based databases popular in particular countries or vertical markets also have more or less current Tcl bindings.


But Where's Tcl's DBI?

Suppose you want to change an application from Oracle to Sybase -- all with the same data and capabilities, but a different "back-end." As the code example above suggests, you need not only to replace the Oratcl library with Sybtcl, but also to transform your Tcl source code to use syblogon rather than oralogon, and so on. Couldn't there just be a logon?

Yes: Perl's DBI module demonstrates that. While there's been talk of a DBI-like interface for Tcl, no one has yet gone to the trouble of making such a thing publicly available.

At least not until recently. Last December, Artur Trzewik released his dbitotcl (http://www.xdobry.de/dbitotcl), which re-uses Perl's DBI library to fashion Tcl interfaces to specific database managers by way of Perl. Although that might sound "improper" in some vague sense to other programmers, Tcl developers are likely to use it quite cheerfully unless a specific technical issue -- speed or memory usage, perhaps -- turns out to constrain its success. By March of this year, dbitotcl had reached its 0.14 release.


Niche Databases

Plenty of lesser known database products also enjoy custom Tcl interfaces. The first database project on which one of us used Tcl involved MySQL. That rather old-fashioned binding was serviceable, though flawed. Peter Campbell supports a more modern one, called "FastBase MySQL + Tcl Interface", or fbsql, at: http://www.fastbase.co.nz/fbsql/index.html. It improves on the earlier mytcl and tcl-sql in its scalability and maintainability. Also, fbsql appears to be more careful in memory usage.

We often write about TclKit, built on top of the lightweight MetaKit database manager. Recent releases of TclKit enhance its already remarkable usability with the inclusion of the clever Tequila "consistency server" (http://www.equi4.com/jcw/wiki.cgi/32.html). See: http://starbase.neosoft.com/~claird/comp.lang.tcl/tclkit.html for an introduction to TclKit's virtues as a data manager.

Even farther afield, bindings to dbm, LDAP, and several proprietary data management products are also mature. The OpenLDAP project (http://www.openldap.org/) maintains one of two actively-used LDAP-Tcl bindings. The GraphViz repository at: http://www.graphviz.org/pub/ collects bindings to dbm and gdbm.

In a future column, we'll show how scripting languages might contribute even more value when working with fluky little databases that no one knows. The conclusion for now: if you've got database work to do, Tcl is a fine choice. Keep in mind that you'll probably have to visit an unpolished Web home site to download your first copy of a binding. However, it's become quite a bit easier during the last year to locate those sites. Two Web pages, http://tcl.activestate.com:8002/resource/software/extensions/database/ and http://mini.net/tcl/620, independently aim to keep up with DB news. In any case, once you've located the DB binding you want, expect your progress to be smooth.

Kathryn Soraiz and Cameron Laird often do database work through their consultancy, Phaseit, Inc. They spent much of the '80s and '90s working with proprietary data managers and their proprietary scripting languages.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.