Documentation Source Text

Check-in [c00d5a996d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the documentation on the SQLite TCL interface to talk about options to the "sqlite3" command and the "wal_hook" method.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c00d5a996d5c02dc65234bf66051015543c72483
User & Date: drh 2013-03-18 21:37:12
Context
2013-03-18
21:54
Add a mention of the ZIPVFS extension to the Support page. check-in: c6ec519b53 user: drh tags: trunk
21:37
Update the documentation on the SQLite TCL interface to talk about options to the "sqlite3" command and the "wal_hook" method. check-in: c00d5a996d user: drh tags: trunk
21:05
Use "package require" rather than "load" on the quick-start guide to using SQLite from TCL. check-in: 12ac1ca27f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/tclsqlite.in.

27
28
29
30
31
32
33
34
35
36
37
38
39
40

41
42
43
44
45
46
47
48
49
50
51
52
53

54
55















































56
57
58
59
60
61
62
..
91
92
93
94
95
96
97

98
99
100
101
102
103
104
...
175
176
177
178
179
180
181
182
183



184
185
186
187
188
189
190
...
244
245
246
247
248
249
250
251

252
253
254

255
256
257
258
259
260
261
262
263
...
680
681
682
683
684
685
686



















687
688
689
690
691
692
693
Because there is only this
one command, the interface is not placed in a separate
namespace.</p>

<p>The <b>sqlite3</b> command is used as follows:</p>

<blockquote>
<b>sqlite3</b>&nbsp;&nbsp;<i>dbcmd&nbsp;&nbsp;database-name</i>
</blockquote>

<p>
The <b>sqlite3</b> command opens the database named in the second
argument.  If the database does not already exist, it is
automatically created.

The <b>sqlite3</b> 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.
</p>

<p>
The name of the database is just the name of a disk file in which
the database is stored.  If the name of the database is 
the special name "[:memory:]" then a new database is created
in memory.  If the name of the database is an empty string, then
the database is created in an empty file that is automatically deleted
when the database connection closes.

</p>
















































<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 22 methods
defined.</p>

<p>
<ul>
................................................................................
 timeout
 total_changes
 trace
 transaction
 unlock_notify
 update_hook
 version

}] {
 hd_puts "<li><a href=\"#$m\">$m</a></li>"
}
</tcl>
</ul>
</p>

................................................................................
values(*) = a b<br>
values(a) = 3<br>
values(b) = howdy!</b>
</blockquote>

<p>
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.
</p>

<p>
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:
................................................................................
</p>

<blockquote><b>
db1 eval {INSERT INTO t1 VALUES(5,:bigstring)}
</b></blockquote>

<p>The use of ":" instead of "$" before the name of a variable can 
sometimes be useful if the SQL text is enclosed in "..." instead of

{...}.  When the SQL is contained within "..." then TCL will do
the substitution of $-variables, which can lead to SQL injection if
extreme care is not used.  But TCL will never substitute a :-variable

regardless of whether "..." or {...} are used to enclose the SQL, so
the use of :-variables adds an extra measure of defense against SQL
injection.
</p>

}

##############################################################################
METHOD close {
................................................................................
<ul>
<li>The keyword "INSERT", "UPDATE", or "DELETE", as appropriate</li>
<li>The name of the database which is being changed</li>
<li>The table that is being changed</li>
<li>The rowid of the row in the table being changed</li>
</ul>
}




















##############################################################################
METHOD incrblob {

<p>This method opens a TCL channel that can be used to read or write
into a preexisting BLOB in the database.  The syntax is like this:</p>








|




|
|
>
|






|




|
>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







 







|
|
>
>
>







 







|
>
|


>
|
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
...
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
...
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
...
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
...
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
Because there is only this
one command, the interface is not placed in a separate
namespace.</p>

<p>The <b>sqlite3</b> command is used as follows:</p>

<blockquote>
<b>sqlite3</b>&nbsp;&nbsp;<i>dbcmd&nbsp;&nbsp;database-name&nbsp;&nbsp;?options?</i>
</blockquote>

<p>
The <b>sqlite3</b> command opens the database named in the second
argument.  If the database does not already exist, the default behavior
is for it to be created automatically (though this can be changed by
using the "<b>-create false</b>" option).
The <b>sqlite3</b> command always 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.
</p>

<p>
The name of the database is usually just the name of a disk file in which
the database is stored.  If the name of the database is 
the special name "[:memory:]" then a new database is created
in memory.  If the name of the database is an empty string, then
the database is created in an empty file that is automatically deleted
when the database connection closes.  [URI filenames] can be used if
the "<b>-uri yes</b>" option is supplied on the <b>sqlite3</b> command.
</p>

<p>
Options understood by the <b>sqlite3</b> command include:
</p>

<blockquote>
<dl>

<dt><b>-create</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, then a new database is created if one does not already exist.
If false, then an attempt to open a database file that does not previously
exist raises an error.  The default behavior is "true".
</p></dd>

<dt><b>-nomutex</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, then all mutexes for the database connection are disabled.
This provides a small performance boost in single-threaded applications.
</p></dd>

<dt><b>-readonly</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, then open the database file read-only.  If false, then the
database is opened for both reading and writing if filesystem permissions
allow, or for reading only if filesystem write permission is denigned
by the operating system.  The default setting is "false".  Note that
if the previous process to have the database did not exits cleanly
and left behind a [hot journal], then the write permission is required
to recover the database after opening, and the database cannot be
opened read-only.
</p></dd>

<dt><b>-uri</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, then interpret the filename argument as a [URI filename].  If
false, then the argument is a literal filename.  The default value is
"false".
</p></dd>

<dt><b>-vfs</b> <i>VFSNAME</i></dt>
<dd><p>
Use an alternative [VFS] named by the argument.
</p></dd>

</dl>
</blockquote>

<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 22 methods
defined.</p>

<p>
<ul>
................................................................................
 timeout
 total_changes
 trace
 transaction
 unlock_notify
 update_hook
 version
 wal_hook
}] {
 hd_puts "<li><a href=\"#$m\">$m</a></li>"
}
</tcl>
</ul>
</p>

................................................................................
values(*) = a b<br>
values(a) = 3<br>
values(b) = howdy!</b>
</blockquote>

<p>
For each column in a row of the result, the name of that column
is used as an index in to array and the value of the column is stored
in the corresponding array entry.  (Caution:  If two or more columns
in the result set of a query have the same name, then the last column
with that name will overwrite prior values and earlier columns with the
same name will be inaccessible.) The special array index * is
used to store a list of column names in the order that they appear.
</p>

<p>
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:
................................................................................
</p>

<blockquote><b>
db1 eval {INSERT INTO t1 VALUES(5,:bigstring)}
</b></blockquote>

<p>The use of ":" instead of "$" before the name of a variable can 
sometimes be useful if the SQL text is enclosed in double-quotes "..."
instead of curly-braces {...}.  
When the SQL is contained within double-quotes "..." then TCL will do
the substitution of $-variables, which can lead to SQL injection if
extreme care is not used.  But TCL will never substitute a :-variable
regardless of whether double-quotes "..." or curly-braces {...} are 
used to enclose the SQL, so the use of :-variables adds an extra 
measure of defense against SQL
injection.
</p>

}

##############################################################################
METHOD close {
................................................................................
<ul>
<li>The keyword "INSERT", "UPDATE", or "DELETE", as appropriate</li>
<li>The name of the database which is being changed</li>
<li>The table that is being changed</li>
<li>The rowid of the row in the table being changed</li>
</ul>
}

##############################################################################
METHOD wal_hook {

<p>This method registers a callback routine that is invoked after transaction
commit when the database is in [WAL mode].  Two arguments are appended to the
callback command before it is invoked:</p>

<ul>
<li>The name of the datebase on which the transaction was committed
<li>The number of entries in the write-ahead log (WAL) file for that database
</ul>

<p>This method might decide to run a [checkpoint] either itself or as a
subsequent idle callback.  Note that SQLite only allows a single WAL hook.
By default this single WAL hook is used for the auto-checkpointing.  If you
set up an explicit WAL hook, then that one WAL hook must ensure that checkpoints
are occurring since the auto-checkpointing mechanism will be disabled.</p>
}

##############################################################################
METHOD incrblob {

<p>This method opens a TCL channel that can be used to read or write
into a preexisting BLOB in the database.  The syntax is like this:</p>