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 name of the database is just the name of a disk file in which
the database is stored.
The use of each of these methods will be explained in the sequel, though
not in the order shown above.
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:
sqlite3 db1 ./testdb
db1 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 values of all
columns 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 omitted or 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!
Tcl variable names can appear in the SQL statement of the second argument
in any position where it is legal to put a string or number literal. The
value of the variable is substituted for the variable name. If the
variable does not exist a NULL values is used. For example:
db1 eval {INSERT INTO t1 VALUES(5,$bigblob)}
Note that it is not necessary to quote the $bigblob value. That happens
automatically. If $bigblob is a large string or binary object, this
technique is not only easier to write, it is also much more efficient
since it avoids making a copy of the content of $bigblob.
The "copy" method
The "copy" method copies data from a file into a table.
It returns the number of rows processed successfully from the file.
The syntax of the copy method looks like this:
dbcmd copy conflict-algorithm
table-name file-name
?column-separator ?
?null-indicator?
Conflict-alogrithm must be one of the SQLite conflict algorithms for
the INSERT statement: rollback, abort,
fail,ignore, or replace. See the SQLite Language
section for ON CONFLICT for more information.
The conflict-algorithm must be specified in lower case.
Table-name must already exists as a table. File-name must exist, and
each row must contain the same number of columns as defined in the table.
If a line in the file contains more or less than the number of columns defined,
the copy method rollbacks any inserts, and returns an error.
Column-separator is an optional column separator string. The default is
the ASCII tab character \t.
Null-indicator is an optional string that indicates a column value is null.
The default is an empty string. Note that column-separator and
null-indicator are optional positional arguments; if null-indicator
is specified, a column-separator argument must be specifed and
precede the null-indicator argument.
The copy method implements similar functionality to the .import
SQLite shell command.
The SQLite 2.x COPY statement
(using the PostgreSQL COPY file format)
can be implemented with this method as:
dbcmd copy $conflictalgo
$tablename $filename
\t
\\N
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 2000
The 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.
