Tcl as a Database Applications Language

Here you can find out all about our use of Tcl in database apps. This page has had 2661 visits so far.

Relational DB programmers have traditionally used a variety of tools and languages. After all, no one can live by ANSI SQL alone, and even the vendors' supersets fall short of being 'real programming languages.' SQL has its place, and its place is embedded.

There are a zillion little report languages out there, besides trusty old COBOL and C. The zillion little report languages suffer from being database-specific, vendor-specific, platform-specific ... COBOL's a bit creaky for the modern hacker, and C is just so darn labour intensive. Enter Tcl. With extensions for Oracle, Sybase, Postgres, Ingres, and probably other more obscure DB that I don't deal with, Tcl looks pretty good compared to the competition.

But I didn't mention Perl, the other scripting language that people use a lot for DB apps. Yes, I know. It's terse and powerful, and if you are already Perl-literate it's a good choice. But the learning curve for Tcl is shorter and shallower, and the legibility of the code after you've written it is generally better. And when Perl programmers want to write GUI apps, they use ... PerlTk. (So if you're going to write Tk to create and manipulate your widgets, why not just write the whole thing in Tcl?)

One reason Tcl makes such a dandy language for embedded SQL apps is its easy, intuitive, flexible handling of strings -- its rich string and list functions really help, too. I've one caveat to offer here, though: core Tcl is weak in this regard without the TclX extension, and so I'd advise anyone doing any kind of info mechanics to eschew core Tcl and work only with TclX. From here on in, please understand `Tcl` to mean 'Tcl with the TclX extension.

Tcl is very good at constructing data structures dynamically, on the fly, as needed. Its data structures are very malleable. You have associative arrays, lists, keyed lists, arrays of lists and keyed list, dynamic array index generation, list push and pop, list sort, list intersect, list replace, reference by name, reference by value, dynamic construction of variable names, recursive evaluation, on-the-fly code construction with evaluation, and so on. When dealing with data retrieval, parsing, and manipulation, these qualities come in handy.

To Tcl, everything is a string. This makes it easy to retrieve and use quantitative data out of string representations, and to wander freely between string and numeric format. Some folks complain that Tcl has 'weak typing' but IMHO, Tcl's typelessness is one of its great attractions for the info hacker.

Anyway, there are several discrete types of database applications, and we use Tcl for all of them.

• Report Generation
  Tcl makes a great report writing language. For example, here's a nasty little General Ledger I wrote using Tcl, which our campus MIS department has not yet (2 years later) been able to reproduce using their toolkit (COBOL and vendor-supplied 4GL). You'll find other report generators in the Knowledge Base section.
• Data Conversion, Migration, Ingestion
  Here I tell part of the tale of our migration from DTR32 under VMS to Sybase under Unix. I used Tcl in combination with vendor bulk inload/dump utilities for Data Migration. We also have to convert data from FITS format to various database representations. One such code ingests headers from an image archiving system called 'Save the Bits'. It's called FITS Header Ingestion, and though ugly as sin, it does work. And it was written in less than a month. You will find more on the subject of data mungeing in Web/Database Interfaces, where the ingestion of FITS table extensions is discussed briefly.
• Data Entry, Data Browse, Data Maintenance
  I used Tcl/Tk to implement a fairly full-featured GUI Forms package for Sybase. This GUI Forms software is freely available, but somewhat complex to install. If you'd like to try it out, let me know.
• Web/Database Interfaces
  That's the hot topic these days. I've been using Tcl for CGI scripting for about 3 years, even before CGI-Tcl made it so much easier. Beats me how Perl got its reputation as 'the only scripting language for Web/DB work' -- in Web/Database Interfaces you can find out more about how we've been doing this kind of work.
• DBA, Advanced User Interface
  Forms are nice, but frustrating for certain global operations or for advanced SQL users and DBA. I extended the wisql code originally written by Tom Poindexter to make it useful for both naive and advanced users, and the resulting Advanced GUI is in use here and there at various Sybase sites around the world.
• DBA Goodies
  Ever wondered about the load on your Sybase server? I wrote this silly little Sybase PerfMeter in about a day. It's not the most elegant code, but it does what I want.

Some of the source is online in the detail pages above. Other stuff is available via Anonymous FTP.