  
The Tcl interface to the SQLite library
(This page was last modified on 2003/08/19 14:31:02 UTC)
The SQLite library is designed to be very easy to use from
a Tcl or Tcl/Tk script.  This document gives an overview of the Tcl
programming interface.
The API
The interface to the SQLite library consists of single
tcl command named 
sqlite.  Because there is only this
one interface command, the interface is not placed in a separate
namespace.
The sqlite command is used as follows:
sqlite  dbcmd  database-nameThe 
sqlite command opens the database named in the second
argument.  If the database does not already exist, it is
automatically created.
The 
sqlite command also creates a new Tcl
command to control the database.  The name of the new Tcl command
is given by the first argument.  This approach is similar to the
way widgets are created in Tk.
The name of the database is just the name of a disk file in which
the database is stored.
Once an SQLite database is open, it can be controlled using 
methods of the 
dbcmd.  There are currently 7 methods
defined:
 busy
 changes
 close
 complete
 eval
 last_insert_rowid
 onecolumn
 timeout
We will explain all of these methods, though not in that order.
We will be begin with the "close" method.
The "close" method
As its name suggests, the "close" method to an SQLite database just
closes the database.  This has the side-effect of deleting the
dbcmd Tcl command.  Here is an example of opening and then
immediately closing a database:
sqlite db1 ./testdbdb1 close
If you delete the 
dbcmd directly, that has the same effect
as invoking the "close" method.  So the following code is equivalent
to the previous:
sqlite db1 ./testdbrename db1 {}
The "eval" method
The most useful 
dbcmd method is "eval".  The eval method is used
to execute SQL on the database.  The syntax of the eval method looks
like this:
dbcmd  eval  sql  ?
array-name  script?
The job of the eval method is to execute the SQL statement or statements
given in the second argument.  For example, to create a new table in
a database, you can do this:
sqlite db1 ./testdbdb1 eval {CREATE TABLE t1(a int, b text)}
The above code creates a new table named t1 with columns
a and b.  What could be simpler?
Query results are returned as a list of column values.  If a
query requests 2 columns and there are 3 rows matching the query,
then the returned list will contain 6 elements.  For example:
db1 eval {INSERT INTO t1 VALUES(1,'hello')}db1 eval {INSERT INTO t1 VALUES(2,'goodbye')}
db1 eval {INSERT INTO t1 VALUES(3,'howdy!')}
set x [db1 eval {SELECT * FROM t1 ORDER BY a}]
The variable $x is set by the above code to
1 hello 2 goodbye 3 howdy!You can also process the results of a query one row at a time
by specifying the name of an array variable and a script following
the SQL code.  For each row of the query result, the value of each
column will be inserted into the array variable and the script will
be executed.  For instance:
db1 eval {SELECT * FROM t1 ORDER BY a} values {    parray values
    puts ""
}
This last code will give the following output:
values(*) = a b
values(a) = 1
values(b) = hello
values(*) = a b
values(a) = 2
values(b) = goodbye
values(*) = a b
values(a) = 3
values(b) = howdy!
For each column in a row of the result, the name of that column
is used as an index in to array.  The value of the column is stored
in the corresponding array entry.  The special array index * is
used to store a list of column names in the order that they appear.
If the array variable name is the empty string, then the value of
each column is stored in a variable with the same name as the column
itself.  For example:
db1 eval {SELECT * FROM t1 ORDER BY a} {} {    puts "a=$a b=$b"
}
From this we get the following output
a=1 b=hello
a=2 b=goodbye
a=3 b=howdy!
The "complete" method
The "complete" method takes a string of supposed SQL as its only argument.
It returns TRUE if the string is a complete statement of SQL and FALSE if
there is more to be entered.
The "complete" method is useful when building interactive applications
in order to know when the user has finished entering a line of SQL code.
This is really just an interface to the 
sqlite_complete() C
function.  Refer to the 
c_interface.htmlC/C++ interface specification for additional information.
The "timeout" method
The "timeout" method is used to control how long the SQLite library
will wait for locks to clear before giving up on a database transaction.
The default timeout is 0 millisecond.  (In other words, the default behavior
is not to wait at all.)
The SQlite database allows multiple simultaneous
readers or a single writer but not both.  If any process is writing to
the database no other process is allows to read or write.  If any process
is reading the database other processes are allowed to read but not write.
The entire database shared a single lock.
When SQLite tries to open a database and finds that it is locked, it
can optionally delay for a short while and try to open the file again.
This process repeats until the query times out and SQLite returns a
failure.  The timeout is adjustable.  It is set to 0 by default so that
if the database is locked, the SQL statement fails immediately.  But you
can use the "timeout" method to change the timeout value to a positive
number.  For example:
db1 timeout 2000The argument to the timeout method is the maximum number of milliseconds
to wait for the lock to clear.  So in the example above, the maximum delay
would be 2 seconds.
The "busy" method
The "busy" method, like "timeout", only comes into play when the
database is locked.  But the "busy" method gives the programmer much more
control over what action to take.  The "busy" method specifies a callback
Tcl procedure that is invoked whenever SQLite tries to open a locked
database.  This callback can do whatever is desired.  Presumably, the
callback will do some other useful work for a short while then return
so that the lock can be tried again.  The callback procedure should
return "0" if it wants SQLite to try again to open the database and
should return "1" if it wants SQLite to abandon the current operation.
The "last_insert_rowid" method
The "last_insert_rowid" method returns an integer which is the ROWID
of the most recently inserted database row.
The "onecolumn" method
The "onecolumn" method works like "eval" in that it evaluates the
SQL query statement given as its argument.  The difference is that
"onecolumn" returns a single element which is the first column of the
first row of the query result.
This is a convenience method.  It saves the user from having to
do a "
[lindex ... 0]" on the results of an "eval"
in order to extract a single column result.
The "changes" method
The "changes" method returns an integer which is the number of rows
in the database that were inserted, deleted, and/or modified by the most
recent "eval" method.
index.htmlBack to the SQLite Home Page
