Using the Tcl Interface

Now that you have the SQLite Tcl library installed, let's look at how the interface is used within a Tcl script. In this section you'll see how to open and close a database and to issue commands that insert, update, or delete rows, and query the database. You'll also learn how to handle the resulting dataset in your script.

Opening and Closing a Database

To begin using the SQLite library from Tcl, your scripts first have to import the package.

 package require sqlite 

Run interactively, tclsh will display the version number of the SQLite library.

 $ tclsh % package require sqlite 2.0 

You can access SQLite via Tcl by using a single command named sqlite, which you call as follows:

 sqlite dbcmd database-name 

The database file given in the database-name argument is openedor created if it does not exist. SQLite will attempt to open database-name from the current directory unless a path is specified.

A new composite command with the name given for dbcmd is then created for use within Tcl. The new command interfaces with an open SQLite database using a number of methods, similar to the way in which Tk widgets are created.

The first method we'll look at is the simplestthe close method instructs SQLite to close an open database, after which it will destroy the dbcmd command.

The first Tcl command in the following example would open a database called tcldb from the current directory and bind it to the command db1 within Tcl. The second command then closes the database, after which the command db1 will no longer be available.

 $ tclsh % package require sqlite 2.0 % sqlite db1 tcldb 0x95c7eb0 % db1 close 

As usual, the error messages Tcl displays are helpful and generally no further error-trapping code is required. For instance, if you had tried to execute the preceding code in a directory that you did not have permission to write to, the error displayed would look like this:

 $ tclsh % package require sqlite 2.0 % sqlite db1 tcldb unable to open database: tcldb 

Executing SQL Statements

SQL statements are passed to SQLite from Tcl via the eval method. In its simplest form, the syntax is as follows:

 % dbcmd eval sql-statement 

The script in Listing 8.1 shows how a simple table called contacts can be created from a Tcl script.

Listing 8.1. Executing a CREATE TABLE Statement Using the TCL Interface

 package require sqlite sqlite db1 tcldb db1 eval {   create table contacts (     id INTEGER PRIMARY KEY,     first_name CHAR,     last_name  CHAR,     email      CHAR UNIQUE   ) } 

The SQL command in Listing 8.1 is formatted using spaces and newlines to indent and separate the columns within the CREATE TABLE statement. There is no need to format the SQL statements passed to the eval method in any particular way; however, this formatting is stored in the sqlite_master database and will be retrieved exactly as it was typed when the schema is subsequently queried.

Executing the script should be silent unless there is an error, for instance if the tcldb database cannot be written to or the table cannot be created. If the script in Listing 8.1 is run a second time, for instance, the following error message will be displayed:

 $ tclsh listing8.1.tcl table contacts already exists     while executing "db1 eval {   create table contacts (     id INTEGER PRIMARY KEY,     first_name CHAR,     last_name  CHAR,     email      CHAR UNIQUE   ) } "     (file "listing8.1.tcl" line 4) 

As usual, the error message produced is verbose enough that no further error trapping is required to find out what went wrong.

Using Commands That Change the Database

The script in Listing 8.2 prompts for three pieces of user input before generating an SQL statement to insert a new record into the contacts table.

Listing 8.2. Inserting a New Record with the TCL Interface

 package require sqlite puts "Enter first name:" gets stdin first_name puts "Enter last name:" gets stdin last_name puts "Enter email:" gets stdin email sqlite db1 tcldb set qry "INSERT INTO CONTACTS (first_name, last_name, email)          VALUES ('$first_name', '$last_name', '$email')" db1 eval $qry 

In this listing, we have taken an extra step to assign the SQL query to a new variable, qry, which is then passed to the eval database method. The following output shows successful execution of this script.

 $ tclsh listing8.2.tcl Enter first name: Chris Enter last name: Newman Enter email: [e-mail] 

We can verify that the record has been inserted using the sqlite tool:

 sqlite> select * from contacts; id   first_name  last_name   email ---  ----------  ----------  --------------------- 1    Chris       Newman      [e-mail] 

This program terminates silently if there are no errors, or will return a fairly readable error message if there is a problem. As the email column has the UNIQUE attribute, we can see how an error from an INSERT statement is reported by trying to insert another record with the same email address.

 ... Enter email: [e-mail] column email is not unique     while executing "db1 eval $qry "     (file "listing8.2.tcl" line 17) 

The first line of the output contains the error column email is not unique and tells us the command that caused the error and the line it appeared on. The SQL command itself is not shown on screen and you would need to output the contents of qry to the screen to see it verbatim; although, in this case it's fairly obvious what the query would have been.

Another reason this script might fail is if there are single quote characters in the user input. When evaluating the query, the strings read from stdin are replaced literally, so if we try to add a name containing an apostrophe (the same ASCII character as the single quote in SQL), it causes a syntax error as shown in the following example:

 Enter first name: Paddy Enter last name: O'Brien Enter email: [e-mail] unrecognized token: "@"     while executing "db1 eval $qry "     (file "listing8.2.tcl" line 17) 

The error message this time is not so helpful, but if we consider the query that has actually been passed to the eval method we can see where the problem arises.

 INSERT INTO CONTACTS (first_name, last_name, email) VALUES ('Paddy', 'O'Brien', '[e-mail]') 

The apostrophe has skewed the pairing of single quotes in the querynow 'O' and ', ' are contained in quotes, and the SQL statement is nonsense. The first error the SQLite parser comes across is the @ sign occurring outside of quotes, so that is the error reported.

To avoid this problem you should escape single quotes before they are used inside an SQL statement. This can be done easily using string map. The following example shows how to modify the code from Listing 8.2 to grab user input for the last_name field to eliminate any problems with apostrophes in the name:

 puts "Enter last name:" gets stdin last_name set last_name [string map {' ''} $last_name] 

The string map command finds any occurrence of one single quote in last_name and replaces it with two quotes. The resulting string is assigned back to last_name. SQLite uses two adjacent single quotes to represent a single quote within a string, so with this change in place the SQL generated by our script for the previous example now looks like this:

 INSERT INTO CONTACTS (first_name, last_name, email) VALUES ('Paddy', 'O''Brien', '[e-mail]') 

For this example only the last_name field was a problem, but you should consider all of your user input to ensure that such problems are avoided.

The INSERT statement we've used in the preceding example creates an implicit transaction in which the single new record is added to the database. Where an explicit transaction is required around a group of statements, you can invoke a BEGIN TRANSACTION command through the SQLite Tcl interface, and similarly end the transaction with a COMMIT TRANSACTION or ROLLBACK TRANSACTION instruction.

Listing 8.3 shows how several INSERT commands can be executed in a single transaction using SQLite's Tcl interface.

Listing 8.3. Using a SQLite Transaction from Tcl to Insert Multiple Rows

 package require sqlite sqlite db1 tcldb db1 eval { BEGIN TRANSACTION } db1 eval {   INSERT INTO CONTACTS (first_name, last_name, email)   VALUES ('Tom', 'Thomas', '[e-mail]') } db1 eval {   INSERT INTO CONTACTS (first_name, last_name, email)   VALUES ('Bill', 'Williams', '[e-mail]') } db1 eval {   INSERT INTO CONTACTS (first_name, last_name, email)   VALUES ('Jo', 'Jones', '[e-mail]') } db1 eval { COMMIT TRANSACTION } puts "Last rowid inserted: [db1 last_insert_rowid]" 

In Listing 8.3 we begin by passing the BEGIN TRANSACTION command to SQLite and end the transaction by saving changes to the database with the COMMIT TRANSACTION command.

At the very end of the script, we use a new method on the db1 command. The last_insert_rowid method returns the most recently assigned value of the INTEGER PRIMARY KEY fieldin this example, the id field on the contacts table. Running the script will produce output similar to the following:

 $ tclsh listing8.3.tcl Last rowid inserted: 5 

When an UPDATE or DELETE operation is performed on a database, a number of rows will be changed or removedassuming of course that the WHERE clause finds some matching rows that the operation is to be performed upon. To find how many rows were affected, we can use the changes database method.

Listing 8.4 performs an UPDATE on the contacts table that converts the first_name and last_name fields to uppercase using the SQL function upper(). As there is no WHERE clause, every row in the table will be affected and the value returned by the changes method will be the total number of rows in the table.

Listing 8.4. Using the changes Method to Find How Many Rows Were Affected by an UPDATE

 package require sqlite sqlite db1 tcldb db1 eval {   UPDATE CONTACTS   SET first_name = upper(first_name),       last_name  = upper(last_name) } puts "Rows updated: [db1 changes]" 

Running this script will result in the changes being made to the database and the number of rows that have been changed will be displayed.

 $ tclsh listing8.4.tcl Rows updated: 5 

Note

The number of affected rows returned by the changes method is the total number of rows that could be affected by the operation regardless of whether or not the record is changed by itin other words the number of rows matched by the WHERE clause or the total number of rows in the table if no WHERE clause is given. If you were to run the script in Listing 8.4 a second time, it will still show the same number of changes having been made, even though the strings are already in uppercase and their values are not changed.

Fetching Records from the Database

We have already seen how to pass an SQL statement to SQLite using the eval database method. Now let's look at how the result of a SELECT statement can be processed in Tcl.

To simply assign the entire result set from a SELECT statement to a variable in Tcl, you can assign the return value of the eval method as shown in Listing 8.5.

Listing 8.5. Assigning the Output of a SELECT Statement to a Variable

 package require sqlite sqlite db1 tcldb set result [db1 eval { SELECT first_name, last_name FROM contacts } ] puts $result 

When the script in Listing 8.5 is executed, the output will be similar to the following:

 $ tclsh listing8.5.tcl CHRIS NEWMAN PADDY O'BRIEN TOM THOMAS BILL WILLIAMS JO JONES 

This could be more useful. Although everything we asked for in the SELECT has indeed been assigned to result, there is nothing to indicate what is what. To the eye it's fairly clear that each pair of words output makes up a full name, but the data is not in a format that we can work with in a program. This method of assigning the result of a query is really only suitable for simple, single-row results.

Fortunately the Tcl interface allows you to process the data returned by a SELECT query one row at a time by specifying the name of an array variable and a script immediately after the SQL code. The value of each column will be inserted into the named array and the supplied code will be executed once for each row in the query result.

Listing 8.6 shows how the same query on the contacts table can be processed one row at a time.

Listing 8.6. Processing the Result of a SELECT Query One Row at a Time

 package require sqlite sqlite db1 tcldb db1 eval {   SELECT first_name, last_name FROM contacts } result {   parray result } 

The code to be executed for each row returned is simply to dump the contents of the result array to screen using parray. By doing so we can see exactly what elements are created in this array, as shown in the following output:

 $ tclsh listing8.6.tcl result(*)                 = first_name last_name result(first_name)        = CHRIS result(last_name)         = NEWMAN result(typeof:first_name) = CHAR result(typeof:last_name)  = CHAR result(*)                 = first_name last_name result(first_name)        = PADDY result(last_name)         = O'BRIEN result(typeof:first_name) = CHAR result(typeof:last_name)  = CHAR ... 

Not only are the selected values made available as elements of result using their column names as the keys, but their respective data types are also fetched into an element with a key named typeof:column_name.

Additionally, for each row returned, result(*) will contain a list of columns returned by the query. If your query was SELECT * FROM tablename, the value of result(*) is, usefully, the list of actual column names returned and not just the * character.

If the name of the array variable given is an empty string, the value of each column will be stored in a scalar variable with the same name as the column itself and the subsequent code executed only once. This property of the SQLite Tcl interface is demonstrated in Listing 8.7.

Listing 8.7. Processing the Result of a SELECT Query One Row at a Time

 package require sqlite sqlite db1 tcldb db1 eval { SELECT * FROM contacts } {} {   puts "$first_name $last_name" } 

This time the values of all the columns in the contacts table are fetched into variables with the same names as the columns, so we can output the values from the first_name and last_name columns by referencing the first_name and last_name variables. The following output shows the result of running Listing 8.7:

 $ tclsh listing8.7.tcl CHRIS NEWMAN PADDY O'BRIEN TOM THOMAS BILL WILLIAMS JO JONES 

The onecolumn Database Method

If you want to select only a single value from the database, the onecolumn database method can be used in place of eval. The behavior is identical except that only the first column of the first row of the result is returned, regardless of the number of columns and rows in the result of the query.

This is a convenience function only; the same behavior can be achieved with the eval method and using lindex in Tcl to fetch the first column of the first row. The following two pieces of code behave in the same way:

 set result [db1 onecolumn { SELECT * FROM contacts }] set result [lindex [db1 eval { SELECT * FROM contacts }] 0] 

Validating an SQL Statement

The SQLite Tcl interface provides a database method that allows you to check whether or not an SQL statement is complete before attempting to execute it using eval or onecolumn. This method is called complete and is called as follows:

 if [ dbcmd complete sql-statement ] {   # Action if statement is complete } else {   # Action if statement is not complete } 

Note

The complete method is not a check for valid SQL syntax. Its underlying function is sqlite_complete() from the C/C++ library, for which the logic is little more than to make sure an SQL statement ends with a semicolon. It is a little cleverer than thisfor instance, semicolons can appear within a CREATE TRIGGER commandbut the statement is not complete until the semicolon following the keyword END.

The script in Listing 8.8 reads one line of input at a time and continues until a complete SQL statement has been entered.

Listing 8.8. Checking Whether an SQL Statement Is Complete

 package require sqlite sqlite db1 tcldb gets stdin qry while { ![ db1 complete $qry ] } {   gets stdin line   set qry "$qry $line" } puts "Query is complete:" puts $qry 

A typical execution of this script might look like this:

 $ tclsh listing8.8.tcl SELECT * FROM contacts WHERE email like '%.co.uk'; Query is complete: SELECT * FROM contacts WHERE email like '%.co.uk'; result(*)                 = id first_name last_name email result(email)             = [e-mail] result(first_name)        = CHRIS result(id)                = 1 result(last_name)         = NEWMAN result(typeof:email)      = CHAR result(typeof:first_name) = CHAR result(typeof:id)         = INTEGER result(typeof:last_name)  = CHAR 

Adjusting the Database Timeout Action

By default, SQLite will not wait for a lock to clear on the database file before giving up on a database transaction. SQLite can support multiple simultaneous processes reading from a database, but everything must stop when a write action takes placeno reading or other writing can be done at the same time.

There will be situations where you want to adjust the length of time that SQLite will wait for a lock to clear before giving up. This can be done with the timeout method, called like this:

 dbcmd timeout ms 

The value ms is the maximum length of time to wait, in milliseconds. Therefore a value of 1000 would mean the maximum delay is one second.

The busy method is similar to the timeout method, but offers you more control over what action should be taken when a lock cannot be obtained within the specified amount of time.

The busy method specifies a Tcl procedure to be used as a callback whenever a locked database cannot be opened. The callback can perform any action you want before returning to the main program to try to obtain a lock again. The callback procedure takes a single count parameter, which tracks how many times it has been called, and must return zero to force the calling script to retry the database transaction. A non-zero value will cause the operation to be interrupted.

Listing 8.9 shows how the busy callback can be used. This example simply dumps off to the callback if the database is locked and prints a message to the screen. The script will allow the callback to be called five times before giving up on the lock.

Listing 8.9. Using a Busy Callback to Handle a Locked Database

 package require sqlite sqlite db1 tcldb proc mycallback {file count} {   puts "This is the callback function, try $count"   if {$count < 5} {     # Do something useful whilst waiting     return 0   } else {     return 1   } } db1 busy mycallback db1 eval { UPDATE contacts SET first_name = upper(first_name) } 

Unless the tcldb database is already locked, this script will execute silently. To test the busy callback, lock the database for writing by issuing a BEGIN TRANSACTION command from another session. The following output is produced when the script fails to obtain a lock on five successive tries:

 $ tclsh listing8.9.tcl This is the callback function, try 1 This is the callback function, try 2 This is the callback function, try 3 This is the callback function, try 4 This is the callback function, try 5 database is locked     while executing "db1 eval { UPDATE contacts SET first_name = upper(first_name) }"     (file "listing8.9.tcl" line 17) 

Adding New SQL Functions

The SQLite Tcl extension allows the SQL language to be extended with new functions implemented as Tcl code. This is a very powerful feature that means you can extend SQL on demand using a familiar language, without having to learn C or recompile SQLite.

Note

Although the Tcl extension supports regular user-defined functions, aggregating functionswhich are supported by some other language interfacescannot be created in Tcl.

The function database method is used to specify a Tcl function that is to be registered as an SQL function. It takes two arguments, the name of the Tcl function itself and the name with which it is to be referenced in SQLthe names can be different.

Listing 8.10 shows how a user-defined function that reverses the characters in a string can be added to SQL.

Listing 8.10. Registering a User-Defined SQL Function Using the Tcl Library

 package require sqlite sqlite db1 tcldb proc reverse_string {str} {   set len [string length $str]   set rev ""   for {set x [expr $len - 1]} {$x>=0} {incr x -1} {     set rev $rev[string index $str $x]   }   return $rev } db1 function reverse reverse_string puts [db1 onecolumn {SELECT reverse('Hello, world')}] 

The Tcl function in this example is called reverse_string and takes a single string argument. The first thing we do is find the length of this string to set up a loop.

 set len [string length $str] 

Then we perform a loop taking one character at a time from the end of the string and building a new string in reverse order. Because the first character of a string is found at index 0, the loop actually runs from len-1 to 0.

 for {set x [expr $len - 1]} {$x>=0} {incr x -1} {   set rev $rev[string index $str $x] } 

Finally, with the reversed string stored in rev, we return the result of the operation.

 return $rev 

We register this function in SQL using the function method. Listing 8.10 assigns a different name to the SQL function than the Tcl functionthe SQL function is to be called simply reverse.

 db1 function reverse reverse_string 

Finally, to test our custom function we perform a SELECT on a fixed string. Of course, now that the SQL function is available, it can be applied to a database column or expression.

 puts [db1 onecolumn {SELECT reverse('Hello, world')}] 

Executing the sample script gives the following output, as expected:

 dlrow ,olleH