2 # Run this Tcl script to generate the tclsqlite.html file.
4 set rcsid
{$Id: tclsqlite.tcl
,v
1.13 2005/04/03 23:54:45 danielk1977 Exp
$}
6 header
{The
Tcl interface to the SQLite library
}
7 proc METHOD
{name
text} {
8 puts "<a name=\"$name\">\n<h3>The \"$name\" method</h3>\n"
12 <h2
>The
Tcl interface to the SQLite library
</h2
>
14 <p
>The SQLite library is designed to be very easy to use from
15 a
Tcl or
Tcl/Tk script. This document gives an overview of the
Tcl
16 programming interface.
</p
>
20 <p
>The interface to the SQLite library consists of single
21 tcl command named
<b
>sqlite
</b
> (version
2.8) or
<b
>sqlite3
</b
>
22 (version
3.0). Because there is only this
23 one command
, the interface is not placed in a separate
26 <p
>The
<b
>sqlite3
</b
> command is used as follows
:</p
>
29 <b
>sqlite3
</b
> 
; 
;<i
>dbcmd
 
; 
;database-name
</i
>
33 The
<b
>sqlite3
</b
> command opens the database named in the second
34 argument. If the database does not already exist
, it is
35 automatically created.
36 The
<b
>sqlite3
</b
> command also creates a new
Tcl
37 command to control the database. The name of the new
Tcl command
38 is given by the first argument. This approach is similar to the
39 way widgets are created in Tk.
43 The name of the database is just the name of a disk
file in which
44 the database is stored.
48 Once an SQLite database is
open, it can be controlled using
49 methods of the
<i
>dbcmd
</i
>. There are currently
19 methods
76 puts "<li><a href=\"#$m\">$m</a></li>"
82 <p
>The use of each of these methods will be explained in the sequel
, though
83 not in the order shown above.
</p
>
87 ##############################################################################
91 As its name suggests
, the
"close" method to an SQLite database just
92 closes the database. This has the side-effect of deleting the
93 <i
>dbcmd
</i
> Tcl command. Here is an example of opening and then
94 immediately closing a database
:
98 <b
>sqlite3 db1 .
/testdb
<br
>
103 If you delete the
<i
>dbcmd
</i
> directly
, that has the same effect
104 as invoking the
"close" method. So the following code is equivalent
108 <b
>sqlite3 db1 .
/testdb
<br
>
113 ##############################################################################
116 The most useful
<i
>dbcmd
</i
> method is
"eval". The
eval method is used
117 to execute SQL on the database. The syntax of the
eval method looks
121 <i
>dbcmd
</i
> 
; 
;<b
>eval</b
> 
; 
;<i
>sql
</i
>
122  
; 
; 
; 
;?
<i
>array-name
 
;</i
>?
 
;?
<i
>script
</i
>?
126 The job of the
eval method is to execute the SQL statement or statements
127 given in the second argument. For example
, to create a new table in
128 a database
, you can do this
:</p
>
131 <b
>sqlite3 db1 .
/testdb
<br
>
132 db1
eval {CREATE TABLE t1
(a int
, b
text)}</b
>
135 <p
>The above code creates a new table named
<b
>t1
</b
> with columns
136 <b
>a
</b
> and
<b
>b
</b
>. What could be simpler?
</p
>
138 <p
>Query results are returned as a
list of column values. If a
139 query requests
2 columns and there are
3 rows matching the query
,
140 then the returned
list will contain
6 elements. For example
:</p
>
143 <b
>db1
eval {INSERT INTO t1 VALUES
(1,'hello'
)}<br
>
144 db1
eval {INSERT INTO t1 VALUES
(2,'goodbye'
)}<br
>
145 db1
eval {INSERT INTO t1 VALUES
(3,'howdy
!'
)}<br
>
146 set x
[db1
eval {SELECT
* FROM t1 ORDER BY a
}]</b
>
149 <p
>The
variable <b
>$x</b
> is
set by the above code to
</p
>
152 <b
>1 hello
2 goodbye
3 howdy
!</b
>
155 <p
>You can also process the results of a query one row at a
time
156 by specifying the name of an
array variable and a script following
157 the SQL code. For each row of the query result
, the values of all
158 columns will be inserted into the
array variable and the script will
159 be executed. For instance
:</p
>
162 <b
>db1
eval {SELECT
* FROM t1 ORDER BY a
} values
{<br
>
163  
; 
; 
; 
;parray values
<br
>
164  
; 
; 
; 
;puts ""<br
>
168 <p
>This last code will give the following output
:</p
>
177 values
(b
) = goodbye
<p
>
181 values
(b
) = howdy
!</b
>
185 For each column in a row of the result
, the name of that column
186 is used as an index in to
array. The value of the column is stored
187 in the corresponding
array entry. The special
array index
* is
188 used to store a
list of column names in the order that they appear.
192 If the
array variable name is omitted or is the empty
string, then the value of
193 each column is stored in a
variable with the same name as the column
198 <b
>db1
eval {SELECT
* FROM t1 ORDER BY a
} {<br
>
199  
; 
; 
; 
;puts "a=$a b=$b"<br
>
204 From this we get the following output
214 Tcl variable names can appear in the SQL statement of the second argument
215 in any position where it is legal to put a
string or number literal. The
216 value of the
variable is substituted
for the
variable name. If the
217 variable does not exist a NULL values is used. For example
:
221 db1
eval {INSERT INTO t1 VALUES
(5,$bigblob)}
225 Note that it is not necessary to quote the
$bigblob value. That happens
226 automatically. If
$bigblob is a large
string or
binary object
, this
227 technique is not only easier to write
, it is also much more efficient
228 since it avoids making a copy of the content of
$bigblob.
233 ##############################################################################
237 The
"complete" method takes a
string of supposed SQL as its only argument.
238 It returns TRUE
if the
string is a complete statement of SQL and FALSE
if
239 there is more to be entered.
</p
>
241 <p
>The
"complete" method is useful when building interactive applications
242 in order to know when the user has finished entering a line of SQL code.
243 This is really just an interface to the
<b
>sqlite3_complete
()</b
> C
244 function. Refer to the
<a href
="c_interface.html">C
/C
++ interface
</a
>
245 specification
for additional information.
</p
>
248 ##############################################################################
252 The
"copy" method copies data from a
file into a table.
253 It returns the number of rows processed successfully from the
file.
254 The syntax of the copy method looks like this
:</p
>
257 <i
>dbcmd
</i
> 
; 
;<b
>copy
</b
> 
; 
;<i
>conflict-algorithm
</i
>
258  
; 
;<i
>table-name
 
;</i
> 
; 
;<i
>file-name
 
;</i
>
259  
; 
; 
; 
;?
<i
>column-separator
 
;</i
>?
260  
; 
;?
<i
>null-indicator
</i
>?
263 <p
>Conflict-alogrithm must be one of the SQLite conflict algorithms
for
264 the INSERT statement
: <i
>rollback
</i
>, <i
>abort
</i
>,
265 <i
>fail
</i
>,<i
>ignore
</i
>, or
<i
>replace
</i
>. See the SQLite Language
266 section
for <a href
="lang.html#conflict">ON CONFLICT
</a
> for more information.
267 The conflict-algorithm must be specified in
lower case.
270 <p
>Table-name must already exists as a table. File-name must exist
, and
271 each row must contain the same number of columns as defined in the table.
272 If a line in the
file contains more or less than the number of columns defined
,
273 the copy method rollbacks any inserts
, and returns an
error.
</p
>
275 <p
>Column-separator is an optional column separator
string. The
default is
276 the ASCII tab character
\t.
</p
>
278 <p
>Null-indicator is an optional
string that indicates a column value is null.
279 The
default is an empty
string. Note that column-separator and
280 null-indicator are optional positional arguments
; if null-indicator
281 is specified
, a column-separator argument must be specifed and
282 precede the null-indicator argument.
</p
>
284 <p
>The copy method implements similar functionality to the
<b
>.import
</b
>
285 SQLite shell command.
286 The SQLite
2.x
<a href
="lang.html#copy"><b
>COPY
</b
></a
> statement
287 (using the PostgreSQL COPY
file format)
288 can be implemented with this method as
:</p
>
291 dbcmd
 
; 
;copy
 
; 
;$conflictalgo
292  
; 
;$tablename 
; 
; 
;$filename 
;
293  
; 
; 
; 
;\t 
;
299 ##############################################################################
302 <p
>The
"timeout" method is used to control how long the SQLite library
303 will wait
for locks to clear before giving up on a database transaction.
304 The
default timeout is
0 millisecond.
(In other words
, the
default behavior
305 is not to wait at all.
)</p
>
307 <p
>The SQLite database allows multiple simultaneous
308 readers or a single writer but not both. If any process is writing to
309 the database no other process is allows to
read or write. If any process
310 is reading the database other processes are allowed to
read but not write.
311 The entire database shared a single lock.
</p
>
313 <p
>When SQLite tries to
open a database and finds that it is locked
, it
314 can optionally delay
for a short
while and try to
open the
file again.
315 This process repeats until the query times out and SQLite returns a
316 failure. The timeout is adjustable. It is
set to
0 by
default so that
317 if the database is locked
, the SQL statement fails immediately. But you
318 can use the
"timeout" method to change the timeout value to a positive
319 number. For example
:</p
>
321 <blockquote
><b
>db1 timeout
2000</b
></blockquote
>
323 <p
>The argument to the timeout method is the maximum number of milliseconds
324 to wait
for the lock to clear. So in the example above
, the maximum delay
325 would be
2 seconds.
</p
>
328 ##############################################################################
331 <p
>The
"busy" method
, like
"timeout", only comes into play when the
332 database is locked. But the
"busy" method gives the programmer much more
333 control over what action to take. The
"busy" method specifies a callback
334 Tcl procedure that is invoked whenever SQLite tries to
open a locked
335 database. This callback can do whatever is desired. Presumably
, the
336 callback will do some other useful work
for a short
while (such as service
337 GUI events
) then
return
338 so that the lock can be tried again. The callback procedure should
339 return "0" if it wants SQLite to try again to
open the database and
340 should
return "1" if it wants SQLite to abandon the current operation.
343 ##############################################################################
344 METHOD last_insert_rowid
{
346 <p
>The
"last_insert_rowid" method returns an integer which is the ROWID
347 of the most recently inserted database row.
</p
>
350 ##############################################################################
353 <p
>The
"function" method registers new SQL functions with the SQLite engine.
354 The arguments are the name of the new SQL function and a TCL command that
355 implements that function. Arguments to the function are appended to the
356 TCL command before it is invoked.
</p
>
359 The following example creates a new SQL function named
"hex" that converts
360 its numeric argument in to a hexadecimal encoded
string:
364 db function hex
{format 0x
%X
}
369 ##############################################################################
373 The
"nullvalue" method changes the representation
for NULL returned
374 as result of the
"eval" method.
</p
>
380 <p
>The
"nullvalue" method is useful to differ between NULL and empty
381 column values as
Tcl lacks a NULL representation. The
default
382 representation
for NULL values is an empty
string.
</p
>
387 ##############################################################################
390 <p
>The
"onecolumn" method works like
"eval" in that it evaluates the
391 SQL query statement given as its argument. The difference is that
392 "onecolumn" returns a single element which is the first column of the
393 first row of the query result.
</p
>
395 <p
>This is a convenience method. It saves the user from having to
396 do a
"<tt>[lindex ... 0]</tt>" on the results of an
"eval"
397 in order to extract a single column result.
</p
>
400 ##############################################################################
403 <p
>The
"changes" method returns an integer which is the number of rows
404 in the database that were inserted
, deleted
, and
/or modified by the most
405 recent
"eval" method.
</p
>
408 ##############################################################################
409 METHOD total_changes
{
411 <p
>The
"total_changes" method returns an integer which is the number of rows
412 in the database that were inserted
, deleted
, and
/or modified since the
413 current database connection was first opened.
</p
>
416 ##############################################################################
419 <p
>The
"authorizer" method provides access to the sqlite3_set_authorizer
420 C
/C
++ interface. The argument to authorizer is the name of a procedure that
421 is called when SQL statements are being compiled in order to authorize
422 certain operations. The callback procedure takes
5 arguments which describe
423 the operation being coded. If the callback returns the
text string
424 "SQLITE_OK", then the operation is allowed. If it returns
"SQLITE_IGNORE",
425 then the operation is silently disabled. If the
return is
"SQLITE_DENY"
426 then the compilation fails with an
error.
429 <p
>If the argument is an empty
string then the authorizer is disabled.
430 If the argument is omitted
, then the current authorizer is returned.
</p
>
433 ##############################################################################
436 <p
>This method registers a callback that is invoked periodically during
437 query processing. There are two arguments
: the number of SQLite virtual
438 machine opcodes between invocations
, and the TCL command to invoke.
439 Setting the progress callback to an empty
string disables it.
</p
>
441 <p
>The progress callback can be used to display the status of a lengthy
442 query or to process GUI events during a lengthy query.
</p
>
446 ##############################################################################
449 <p
>This method registers new
text collating sequences. There are
450 two arguments
: the name of the collating sequence and the name of a
451 TCL procedure that implements a comparison function
for the collating
455 <p
>For example
, the following code implements a collating sequence called
456 "NOCASE" that sorts in
text order without regard to case
:
460 proc nocase_compare
{a b
} {<br
>
461  
; 
; 
; 
;return [string compare
[string tolower
$a] [string tolower
$b]]<br
>
463 db collate NOCASE nocase_compare
<br
>
467 ##############################################################################
468 METHOD collation_needed
{
470 <p
>This method registers a callback routine that is invoked when the SQLite
471 engine needs a particular collating sequence but does not have that
472 collating sequence registered. The callback can register the collating
473 sequence. The callback is invoked with a single parameter which is the
474 name of the needed collating sequence.
</p
>
477 ##############################################################################
480 <p
>This method registers a callback routine that is invoked just before
481 SQLite tries to commit changes to a database. If the callback throws
482 an exception or returns a non-zero result
, then the transaction rolls back
483 rather than commit.
</p
>
486 ##############################################################################
489 <p
>This method returns the numeric
error code that resulted from the most
490 recent SQLite operation.
</p
>
493 ##############################################################################
496 <p
>The
"trace" method registers a callback that is invoked as each SQL
497 statement is compiled. The
text of the SQL is appended as a single
string
498 to the command before it is invoked. This can be used
(for example
) to
499 keep a log of all SQL operations that an application performs.
