Debian Codegraph @

www/tclsqlite.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>The Tcl interface to the SQLite library</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 The Tcl interface to the SQLite library
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
126 <div class="fancy-toc1"><a href="#building_and_installing_the_sqlite_extension_for_tcl">2. Building And Installing The SQLite Extension for Tcl</a></div>
127 <div class="fancy-toc2"><a href="#how_the_build_process_works">2.1. How the build process works</a></div>
128 <div class="fancy-toc2"><a href="#additional_build_documentation">2.2. Additional build documentation</a></div>
129 <div class="fancy-toc1"><a href="#the_tcl_interface_to_the_sqlite_library">3. The Tcl interface to the SQLite library</a></div>
130 <div class="fancy-toc2"><a href="#the_eval_method">3.1. The eval method</a></div>
131 <div class="fancy-toc2"><a href="#the_close_method">3.2. The close method</a></div>
132 <div class="fancy-toc2"><a href="#the_transaction_method">3.3. The transaction method</a></div>
133 <div class="fancy-toc2"><a href="#the_cache_method">3.4. The cache method</a></div>
134 <div class="fancy-toc2"><a href="#the_complete_method">3.5. The complete method</a></div>
135 <div class="fancy-toc2"><a href="#the_config_method">3.6. The config method</a></div>
136 <div class="fancy-toc2"><a href="#the_copy_method">3.7. The copy method</a></div>
137 <div class="fancy-toc2"><a href="#the_timeout_method">3.8. The timeout method</a></div>
138 <div class="fancy-toc2"><a href="#the_busy_method">3.9. The busy method</a></div>
139 <div class="fancy-toc2"><a href="#the_enable_load_extension_method">3.10. The enable_load_extension method</a></div>
140 <div class="fancy-toc2"><a href="#the_exists_method">3.11. The exists method</a></div>
141 <div class="fancy-toc2"><a href="#the_last_insert_rowid_method">3.12. The last_insert_rowid method</a></div>
142 <div class="fancy-toc2"><a href="#the_function_method">3.13. The function method</a></div>
143 <div class="fancy-toc2"><a href="#the_nullvalue_method">3.14. The nullvalue method</a></div>
144 <div class="fancy-toc2"><a href="#the_onecolumn_method">3.15. The onecolumn method</a></div>
145 <div class="fancy-toc2"><a href="#the_changes_method">3.16. The changes method</a></div>
146 <div class="fancy-toc2"><a href="#the_total_changes_method">3.17. The total_changes method</a></div>
147 <div class="fancy-toc2"><a href="#the_authorizer_method">3.18. The authorizer method</a></div>
148 <div class="fancy-toc2"><a href="#the_bind_fallback_method">3.19. The bind_fallback method</a></div>
149 <div class="fancy-toc2"><a href="#the_progress_method">3.20. The progress method</a></div>
150 <div class="fancy-toc2"><a href="#the_collate_method">3.21. The collate method</a></div>
151 <div class="fancy-toc2"><a href="#the_collation_needed_method">3.22. The collation_needed method</a></div>
152 <div class="fancy-toc2"><a href="#the_commit_hook_method">3.23. The commit_hook method</a></div>
153 <div class="fancy-toc2"><a href="#the_rollback_hook_method">3.24. The rollback_hook method</a></div>
154 <div class="fancy-toc2"><a href="#the_status_method">3.25. The status method</a></div>
155 <div class="fancy-toc2"><a href="#the_update_hook_method">3.26. The update_hook method</a></div>
156 <div class="fancy-toc2"><a href="#the_preupdate_method">3.27. The preupdate method</a></div>
157 <div class="fancy-toc2"><a href="#the_wal_hook_method">3.28. The wal_hook method</a></div>
158 <div class="fancy-toc2"><a href="#the_incrblob_method">3.29. The incrblob method</a></div>
159 <div class="fancy-toc2"><a href="#the_errorcode_method">3.30. The errorcode method</a></div>
160 <div class="fancy-toc2"><a href="#the_trace_method">3.31. The trace method</a></div>
161 <div class="fancy-toc2"><a href="#the_trace_v2_method">3.32. The trace_v2 method</a></div>
162 <div class="fancy-toc2"><a href="#the_backup_method">3.33. The backup method</a></div>
163 <div class="fancy-toc2"><a href="#the_restore_method">3.34. The restore method</a></div>
164 <div class="fancy-toc2"><a href="#the_serialize_method">3.35. The serialize method</a></div>
165 <div class="fancy-toc2"><a href="#the_deserialize_method">3.36. The deserialize method</a></div>
166 <div class="fancy-toc2"><a href="#the_interrupt_method">3.37. The interrupt method</a></div>
167 <div class="fancy-toc2"><a href="#the_version_method">3.38. The version method</a></div>
168 <div class="fancy-toc2"><a href="#the_profile_method">3.39. The profile method</a></div>
169 <div class="fancy-toc2"><a href="#the_unlock_notify_method">3.40. The unlock_notify method</a></div>
170 <div class="fancy-toc2"><a href="#the_format_method">3.41. The format method</a></div>
171 </div>
172 </details>
173 </div>
174
175
176
177
178
179 <h1 id="introduction"><span>1. </span>Introduction</h1>
180
181 <p>The SQLite library is designed to be very easy to use from
182 a <a href="http://www.tcl-lang.org">Tcl or Tcl/Tk</a> script. SQLite
183 began as a <a href="http://www.tcl-lang.org/doc/tea/">Tcl extension</a>
184 and the primary <a href="testing.html">test suite</a> for SQLite is written in Tcl and uses
185 the SQLite extension for Tcl described by this document. SQLite
186 can be used with any programming language, but its connections to
187 Tcl run deep.</p>
188
189 <p>This document gives an overview of the Tcl
190 programming interface for SQLite. For tips specific to processing
191 JSON in Tcl, see <a href="tcljson.html">this doc</a>.
192 </p>
193
194 <h1 id="building_and_installing_the_sqlite_extension_for_tcl"><span>2. </span>Building And Installing The SQLite Extension for Tcl</h1>
195
196 <p>As of SQLite version 3.48.0 (2025-01-14), the preferred method for
197 building the SQLite Extension for Tcl is to use the
198 <a href="amalg-v-canon.html#canon">canonical SQLite source tree</a> and run the makefile
199 with the "tclextension-install" target. If you have multiple Tcl versions
200 installed, you can specify a particular Tcl installation to install the
201 SQLite extension on by using the --with-tclsh=PATH command-line option
202 on unix. For example, if you have a custom Tcl 9.0 installation at
203 $HOME/tcl you can install the SQLite for that installation by running:
204
205 </p><blockquote><pre>
206 ./configure --with-tclsh=$HOME/tcl/bin/tclsh9.0
207 make tclextension-install
208 </pre></blockquote>
209
210 <p>On Windows, use the TCLDIR environment variable to specify the top-level
211 directory of your Tcl installation. Again, if you have a custom Tcl
212 installation at %HOME%/tcl, your command would be:
213
214 </p><blockquote><pre>
215 nmake /f Makefile.msc TCLDIR=%HOME%\tcl tclextension-install
216 </pre></blockquote>
217
218 <p>In both cases, there are additional makefile targets that might be
219 useful:
220
221 </p><ul>
222 <li><b>tclextension</b> &rarr; Build the SQLite extension but do not
223 move the files into the installation directory.
224 </li><li><b>tclextension-list</b> &rarr; Show all SQLite extensions available to
225 the Tcl interpreter specified to ./configure or by TCLDIR.
226 </li><li><b>tclextension-uninstall</b> &rarr; Uninstall the SQLite extension.
227 </li><li><b>tclextension-verify</b> &rarr; Verify that the installed SQLite extension
228 uses the same version of SQLite as source tree from which the command is run.
229 </li></ul>
230
231 <p>After you have the SQLite extension installed with your Tcl interpreter
232 using the techniques above,
233 you can start it up by running the following command from any tclsh or wish:
234
235 </p><blockquote><pre>
236 package require sqlite3
237 </pre></blockquote>
238
239 <h2 id="how_the_build_process_works"><span>2.1. </span>How the build process works</h2>
240
241 <p>The SQLite adaptor for Tcl is implemented by a source-code
242 file name <a href="https://sqlite.org/src/file/src/tclsqlite.c">src/tclsqlite.c</a>.
243 This one file is appended to the <a href="amalgamation.html">amalgamation</a> ("sqlite3.c") that
244 implements SQLite to create a new source file
245 "tclsqlite3.c". Compile tclsqlite3.c into a shared library in order
246 to make the run-time loadable Tcl extension that implements SQLite.
247 The "tclextension-install" make target does that and then also
248 constructs a suitable pkgIndex.tcl script and moves both the shared
249 library and the pkgIndex.tcl script into the appropriate installation
250 directory.
251
252 </p><p>There is a makefile target to build tclsqlite3.c:
253
254 </p><blockquote><pre>
255 make tclsqlite3.c
256 </pre></blockquote>
257
258 <p>The tclsqlite3.c file does not have to be compiled into a shared
259 library. The tclsqlite3.c library can be statically linked into an
260 application that also statically links the Tcl library, to construct
261 a stand-alone executable. The "testfixture" executable used for testing
262 SQLite works this way.
263
264 </p><h2 id="additional_build_documentation"><span>2.2. </span>Additional build documentation</h2>
265
266 <p>See the <a href="https://sqlite.org/src/file/doc/tcl-extension-testing.md">src/tcl-extension-testing.md</a>
267 document in the SQLite sources for step-by-step instructions on how
268 the SQLite extension for TCL is tested. That document might provide further
269 hints on how to build the SQLite extension for Tcl should you run into trouble.
270
271 </p><h1 id="the_tcl_interface_to_the_sqlite_library"><span>3. </span>The Tcl interface to the SQLite library</h1>
272
273 <p>The SQLite extension for Tcl consists of a single new
274 Tcl command named <b>sqlite3</b>.
275 Because there is only this
276 one command, the interface is not placed in a separate
277 namespace.</p>
278
279 <p>The <b>sqlite3</b> command is mostly used as follows
280 to open or create a database:</p>
281
282 <blockquote>
283 <b>sqlite3</b>&nbsp;&nbsp;<i>dbcmd&nbsp;&nbsp;?database-name?&nbsp;&nbsp;?options?</i>
284 </blockquote>
285
286 <p>To get information only, the <b>sqlite3</b> command may be given exactly
287 one argument, either "-version", "-sourceid" or "-has-codec", which will
288 return the specified datum with no other effect.
289 </p>
290
291 <p>With other arguments, the <b>sqlite3</b> command opens the database
292 named in the second non-option argument, or named "" if there is no such.
293 If the open succeeds, a new Tcl command named by the first argument is created
294 and "" is returned.
295 (This approach is similar to the way widgets are created in Tk.)
296 If the open fails, an error is raised without creating a Tcl command
297 and an error message string is returned.
298 </p>
299 <p>If the database does not already exist, the default behavior
300 is for it to be created automatically (though this can be changed by
301 using the "<b>-create <i>false</i></b>" option).
302 </p>
303
304 <p>
305 The name of the database is usually just the name of a disk file in which
306 the database is stored. If the name of the database is
307 the special name "<a href="inmemorydb.html">:memory:</a>", then a new database is created
308 in memory. If the name of the database is an empty string, then
309 the database is created in an empty file that is automatically deleted
310 when the database connection closes. <a href="uri.html">URI filenames</a> can be used if
311 the "<b>-uri yes</b>" option is supplied on the <b>sqlite3</b> command.
312 </p>
313
314 <p>
315 Options understood by the <b>sqlite3</b> command include:
316 </p>
317
318 <blockquote>
319 <dl>
320
321 <dt><b>-create</b> <i>BOOLEAN</i></dt>
322 <dd><p>
323 If true, then a new database is created if one does not already exist.
324 If false, then an attempt to open a database file that does not previously
325 exist raises an error. The default behavior is "true".
326 </p></dd>
327
328 <dt><b>-nomutex</b> <i>BOOLEAN</i></dt>
329 <dd><p>
330 If true, then all mutexes for the database connection are disabled.
331 This provides a small performance boost in single-threaded applications.
332 </p></dd>
333
334 <dt><b>-readonly</b> <i>BOOLEAN</i></dt>
335 <dd><p>
336 If true, then open the database file read-only. If false, then the
337 database is opened for both reading and writing if filesystem permissions
338 allow, or for reading only if filesystem write permission is denied
339 by the operating system. The default setting is "false". Note that
340 if the previous process to have the database did not exit cleanly
341 and left behind a <a href="fileformat2.html#hotjrnl">hot journal</a>, then the write permission is required
342 to recover the database after opening, and the database cannot be
343 opened read-only.
344 </p></dd>
345
346 <dt><b>-uri</b> <i>BOOLEAN</i></dt>
347 <dd><p>
348 If true, then interpret the filename argument as a <a href="uri.html">URI filename</a>. If
349 false, then the argument is a literal filename. The default value is
350 "false".
351 </p></dd>
352
353 <dt><b>-vfs</b> <i>VFSNAME</i></dt>
354 <dd><p>
355 Use an alternative <a href="vfs.html">VFS</a> named by the argument.
356 </p></dd>
357
358 <dt><b>-fullmutex</b> <i>BOOLEAN</i></dt>
359 <dd><p>
360 If true, multiple threads can safely attempt to use the database.
361 If false, such attempts are unsafe. The default value depends upon
362 how the extension is built.
363 </p></dd>
364
365 <dt><b>-nofollow</b> <i>BOOLEAN</i></dt>
366 <dd><p>
367 If true, and the database name refers to a symbolic link,
368 it will not be followed to open the true database file.
369 If false, symbolic links will be followed.
370 The default is "false".
371 </p></dd>
372
373 </dl>
374 </blockquote>
375
376
377
378 <p>
379 Once an SQLite database is open, it can be controlled using
380 methods of the <i>dbcmd</i>.
381 There are currently 41 methods
382 defined.</p>
383
384
385 </p><table border="0" cellpadding="10" width="100%">
386 <tr><td valign="top"><ul>
387 <li><a href="#authorizer">authorizer</a></li>
388 <li><a href="#backup">backup</a></li>
389 <li><a href="#bind_fallback">bind_fallback</a></li>
390 <li><a href="#busy">busy</a></li>
391 <li><a href="#cache">cache</a></li>
392 <li><a href="#changes">changes</a></li>
393 <li><a href="#close">close</a></li>
394 <li><a href="#collate">collate</a></li>
395 <li><a href="#collation_needed">collation_needed</a></li>
396 <li><a href="#commit_hook">commit_hook</a></li>
397 <li><a href="#complete">complete</a></li>
398 <li><a href="#config">config</a></li>
399 <li><a href="#copy">copy</a></li>
400 <li><a href="#deserialize">deserialize</a></li>
401 </ul></td>
402 <td valign='top'><ul>
403 <li><a href="#enable_load_extension">enable_load_extension</a></li>
404 <li><a href="#errorcode">errorcode</a></li>
405 <li><a href="#eval">eval</a></li>
406 <li><a href="#exists">exists</a></li>
407 <li><a href="#format">format</a></li>
408 <li><a href="#function">function</a></li>
409 <li><a href="#incrblob">incrblob</a></li>
410 <li><a href="#interrupt">interrupt</a></li>
411 <li><a href="#last_insert_rowid">last_insert_rowid</a></li>
412 <li><a href="#nullvalue">nullvalue</a></li>
413 <li><a href="#onecolumn">onecolumn</a></li>
414 <li><a href="#preupdate">preupdate</a></li>
415 <li><a href="#profile">profile</a></li>
416 <li><a href="#progress">progress</a></li>
417 </ul></td>
418 <td valign='top'><ul>
419 <li><a href="#restore">restore</a></li>
420 <li><a href="#rollback_hook">rollback_hook</a></li>
421 <li><a href="#serialize">serialize</a></li>
422 <li><a href="#status">status</a></li>
423 <li><a href="#timeout">timeout</a></li>
424 <li><a href="#total_changes">total_changes</a></li>
425 <li><a href="#trace">trace</a></li>
426 <li><a href="#trace_v2">trace_v2</a></li>
427 <li><a href="#transaction">transaction</a></li>
428 <li><a href="#unlock_notify">unlock_notify</a></li>
429 <li><a href="#update_hook">update_hook</a></li>
430 <li><a href="#version">version</a></li>
431 <li><a href="#wal_hook">wal_hook</a></li>
432
433 </ul></td></tr>
434 </table>
435
436
437 <p>The use of each of these methods will be explained in the sequel, though
438 not in the order shown above.</p>
439
440 <a name="eval"></a>
441
442
443 <h2 id="the_eval_method"><span>3.1. </span>The eval method</h2>
444 <p>
445 The most useful <i>dbcmd</i> method is "eval". The eval method is used
446 to execute SQL on the database. The syntax of the eval method looks
447 like this:</p>
448
449 <blockquote>
450 <i>dbcmd</i>&nbsp;&nbsp;<b>eval</b>&nbsp;&nbsp;?<b>-withoutnulls</b>?
451 ?<b>-asdict</b>?&nbsp;&nbsp;?&nbsp;&nbsp;<i>sql</i>
452 &nbsp;&nbsp;<i>var-name</i>?&nbsp;&nbsp;?<i>script</i>?
453 </blockquote>
454
455 <p>
456 The job of the eval method is to execute the SQL statement or statements
457 given in the second argument. For example, to create a new table in
458 a database, you can do this:</p>
459
460 <blockquote>
461 <b>sqlite3 db1 ./testdb<br>
462 db1 eval {CREATE TABLE t1(a int, b text)}</b>
463 </blockquote>
464
465 <p>The above code creates a new table named <b>t1</b> with columns
466 <b>a</b> and <b>b</b>. What could be simpler?</p>
467
468 <p>Query results are returned as a list of column values. If a
469 query requests 2 columns and there are 3 rows matching the query,
470 then the returned list will contain 6 elements. For example:</p>
471
472 <blockquote>
473 <b>db1 eval {INSERT INTO t1 VALUES(1,'hello')}<br>
474 db1 eval {INSERT INTO t1 VALUES(2,'goodbye')}<br>
475 db1 eval {INSERT INTO t1 VALUES(3,'howdy!')}<br>
476 set x &#91;db1 eval {SELECT * FROM t1 ORDER BY a}&#93;</b>
477 </blockquote>
478
479 <p>The variable <b>$x</b> is set by the above code to</p>
480
481 <blockquote>
482 <b>1 hello 2 goodbye 3 howdy!</b>
483 </blockquote>
484
485 <p>You can also process the results of a query one row at a time by
486 specifying the name of an array or, with the <code>-asdict</code>
487 flag, a dict variable and a script following the SQL code. For each
488 row of the query result, the values of all columns will be inserted
489 into the array resp. dict variable and the script will be executed.
490 For instance:</p>
491
492 <blockquote>
493 <b>db1 eval {SELECT * FROM t1 ORDER BY a} values {<br>
494 &nbsp;&nbsp;&nbsp;&nbsp;parray values<br>
495 &nbsp;&nbsp;&nbsp;&nbsp;puts ""<br>
496 }</b>
497 </blockquote>
498
499 <p>This last code will give the following output:</p>
500
501 <blockquote><b>
502 values(*) = a b<br>
503 values(a) = 1<br>
504 values(b) = hello<p>
505 </p></b></blockquote>
506 <blockquote><b>
507 values(*) = a b<br>
508 values(a) = 2<br>
509 values(b) = goodbye<p>
510 </p></b></blockquote>
511 <blockquote><b>
512 values(*) = a b<br>
513 values(a) = 3<br>
514 values(b) = howdy!</b>
515 </blockquote>
516
517 <p>
518 For each column in a row of the result, the name of that column
519 is used as an index into the array and the value of the column is stored
520 in the corresponding array entry. (Caution: If two or more columns
521 in the result set of a query have the same name, then the last column
522 with that name will overwrite prior values and earlier columns with the
523 same name will be inaccessible.) The special array index * is
524 used to store a list of column names in the order that they appear.
525 </p>
526
527 <p>
528 Normally, NULL SQL results are stored in the array using the
529 <a href="#nullvalue">nullvalue</a> setting. However, if
530 the <b>-withoutnulls</b> option is used, then NULL SQL values
531 cause the corresponding array element to be unset instead.
532 </p>
533
534 <p>
535 If the array variable name is omitted or is the empty string, then the value of
536 each column is stored in a variable with the same name as the column
537 itself. For example:
538 </p>
539
540 <blockquote>
541 <b>db1 eval {SELECT * FROM t1 ORDER BY a} {<br>
542 &nbsp;&nbsp;&nbsp;&nbsp;puts "a=$a b=$b"<br>
543 }</b>
544 </blockquote>
545
546 <p>
547 From this we get the following output
548 </p>
549
550 <blockquote><b>
551 a=1 b=hello<br>
552 a=2 b=goodbye<br>
553 a=3 b=howdy!</b>
554 </blockquote>
555
556 <a name="varsubst"></a>
557
558 <p>
559 Tcl variable names can appear in the SQL statement of the second argument
560 in any position where it is legal to put a string or number literal. The
561 value of the variable is substituted for the variable name. If the
562 variable does not exist a NULL values is used. For example:
563 </p>
564
565 <blockquote><b>
566 db1 eval {INSERT INTO t1 VALUES(5,$bigstring)}
567 </b></blockquote>
568
569 <p>
570 Note that it is not necessary to quote the $bigstring value. That happens
571 automatically. If $bigstring is a large string or binary object, this
572 technique is not only easier to write, it is also much more efficient
573 since it avoids making a copy of the content of $bigstring.
574 </p>
575
576 <p>
577 If the $bigstring variable has both a string and a "bytearray" representation,
578 then TCL inserts the value as a string. If it has only a "bytearray"
579 representation, then the value is inserted as a BLOB. To force a
580 value to be inserted as a BLOB even if it also has a text representation,
581 use a "@" character to in place of the "$". Like this:
582 </p>
583
584 <blockquote><b>
585 db1 eval {INSERT INTO t1 VALUES(5,@bigstring)}
586 </b></blockquote>
587
588 <p>
589 If the variable does not have a bytearray representation, then "@" works
590 just like "$". Note that ":" works like "$" in all cases so the following
591 is another way to express the same statement:
592 </p>
593
594 <blockquote><b>
595 db1 eval {INSERT INTO t1 VALUES(5,:bigstring)}
596 </b></blockquote>
597
598 <p>The use of ":" instead of "$" before the name of a variable can
599 sometimes be useful if the SQL text is enclosed in double-quotes "..."
600 instead of curly-braces {...}.
601 When the SQL is contained within double-quotes "..." then TCL will do
602 the substitution of $-variables, which can lead to SQL injection if
603 extreme care is not used. But TCL will never substitute a :-variable
604 regardless of whether double-quotes "..." or curly-braces {...} are
605 used to enclose the SQL, so the use of :-variables adds an extra
606 measure of defense against SQL
607 injection.
608 </p>
609
610 <a name="close"></a>
611
612 <h2 id="the_close_method"><span>3.2. </span>The close method</h2>
613
614 <p>
615 As its name suggests, the "close" method to an SQLite database just
616 closes the database. This has the side-effect of deleting the
617 <i>dbcmd</i> Tcl command. Here is an example of opening and then
618 immediately closing a database:
619 </p>
620
621 <blockquote>
622 <b>sqlite3 db1 ./testdb<br>
623 db1 close</b>
624 </blockquote>
625
626 <p>
627 If you delete the <i>dbcmd</i> directly, that has the same effect
628 as invoking the "close" method. So the following code is equivalent
629 to the previous:</p>
630
631 <blockquote>
632 <b>sqlite3 db1 ./testdb<br>
633 rename db1 {}</b>
634 </blockquote>
635
636 <a name="transaction"></a>
637
638 <h2 id="the_transaction_method"><span>3.3. </span>The transaction method</h2>
639
640 <p>
641 The "transaction" method is used to execute a TCL script inside an SQLite
642 database transaction. The transaction is committed when the script completes,
643 or it rolls back if the script fails. If the transaction occurs within
644 another transaction (even one that is started manually using BEGIN) it
645 is a no-op.
646 </p>
647
648 <p>
649 The transaction command can be used to group together several SQLite
650 commands in a safe way. You can always start transactions manually using
651 BEGIN, of
652 course. But if an error occurs so that the COMMIT or ROLLBACK are never
653 run, then the database will remain locked indefinitely. Also, BEGIN
654 does not nest, so you have to make sure no other transactions are active
655 before starting a new one. The "transaction" method takes care of
656 all of these details automatically.
657 </p>
658
659 <p>
660 The syntax looks like this:
661 </p>
662
663 <blockquote>
664 <i>dbcmd</i>&nbsp;&nbsp;<b>transaction</b>&nbsp;&nbsp;<i>?transaction-type?</i>
665 &nbsp;&nbsp;<i>script</i>
666 </blockquote>
667
668
669 <p>
670 The <i>transaction-type</i> can be one of <b>deferred</b>,
671 <b>exclusive</b> or <b>immediate</b>. The default is deferred.
672 </p>
673
674 <a name="cache"></a>
675
676 <h2 id="the_cache_method"><span>3.4. </span>The cache method</h2>
677
678 <p>
679 The "eval" method described <a href="#eval">above</a> keeps a cache of
680 <a href="c3ref/prepare.html">prepared statements</a>
681 for recently evaluated SQL commands.
682 The "cache" method is used to control this cache.
683 The first form of this command is:</p>
684
685 <blockquote>
686 <i>dbcmd</i>&nbsp;&nbsp;<b>cache size</b>&nbsp;&nbsp;<i>N</i>
687 </blockquote>
688
689 <p>This sets the maximum number of statements that can be cached.
690 The upper limit is 100. The default is 10. If you set the cache size
691 to 0, no caching is done.</p>
692
693 <p>The second form of the command is this:</p>
694
695
696 <blockquote>
697 <i>dbcmd</i>&nbsp;&nbsp;<b>cache flush</b>
698 </blockquote>
699
700 <p>The cache-flush method
701 <a href="c3ref/finalize.html">finalizes</a>
702 all prepared statements currently
703 in the cache.</p>
704
705 <a name="complete"></a>
706
707 <h2 id="the_complete_method"><span>3.5. </span>The complete method</h2>
708
709 <p>
710 The "complete" method takes a string of supposed SQL as its only argument.
711 It returns TRUE if the string is a complete statement of SQL and FALSE if
712 there is more to be entered.</p>
713
714 <p>The "complete" method is useful when building interactive applications
715 in order to know when the user has finished entering a line of SQL code.
716 This is really just an interface to the
717 <a href="c3ref/complete.html"><b>sqlite3_complete()</b></a> C
718 function.
719
720 <a name="config"></a>
721
722 </p><h2 id="the_config_method"><span>3.6. </span>The config method</h2>
723
724 <p>
725 The "config" method queries or changes certain configuration settings for
726 the database connection using the <a href="c3ref/db_config.html">sqlite3_db_config()</a> interface.
727 Run this method with no arguments to get a TCL list of available
728 configuration settings and their current values:
729
730 </p><blockquote>
731 <i>dbcmd</i>&nbsp;&nbsp;<b>config</b>
732 </blockquote>
733
734 <p>The above will return something like this:
735
736 </p><blockquote>
737 defensive 0 dqs_ddl 1 dqs_dml 1 enable_fkey 0 enable_qpsg 0 enable_trigger 1 enable_view 1 fts3_tokenizer 1 legacy_alter_table 0 legacy_file_format 0 load_extension 0 no_ckpt_on_close 0 reset_database 0 trigger_eqp 0 trusted_schema 1 writable_schema 0
738 </blockquote>
739
740 <p>Add the name of an individual configuration setting to query the current
741 value of that setting. Optionally add a boolean value to change a setting.
742
743 </p><p>
744 The following four configuration changes are recommended for maximum
745 application security. Turning off the trust_schema setting prevents
746 virtual tables and dodgy SQL functions from being used inside of triggers,
747 views, CHECK constraints, generated columns, and expression indexes.
748 Turning off the dqs_dml and dqs_ddl settings prevents the use of
749 double-quoted strings. Turning on defensive prevents direct writes
750 to shadow tables.
751
752 </p><blockquote><pre>
753 db config trusted_schema 0
754 db config defensive 1
755 db config dqs_dml 0
756 db config dqs_ddl 0
757 </pre></blockquote>
758
759 <a name="copy"></a>
760
761 <h2 id="the_copy_method"><span>3.7. </span>The copy method</h2>
762
763 <p>
764 The "copy" method copies data from a file into a table.
765 It returns the number of rows processed successfully from the file.
766 The syntax of the copy method looks like this:</p>
767
768 <blockquote>
769 <i>dbcmd</i>&nbsp;&nbsp;<b>copy</b>&nbsp;&nbsp;<i>conflict-algorithm</i>
770 &nbsp;&nbsp;<i>table-name&nbsp;</i>&nbsp;&nbsp;<i>file-name&nbsp;</i>
771 &nbsp;&nbsp;&nbsp;&nbsp;?<i>column-separator</i>?
772 &nbsp;&nbsp;?<i>null-indicator</i>?
773 </blockquote>
774
775 <p>Conflict-algorithm must be one of the SQLite conflict algorithms for
776 the INSERT statement: <i>rollback</i>, <i>abort</i>,
777 <i>fail</i>, <i>ignore</i>, or <i>replace</i>. See the SQLite Language
778 section for <a href="lang_conflict.html">ON CONFLICT</a> for
779 more information. The conflict-algorithm must be specified in lower case.
780 </p>
781
782 <p>Table-name must already exist as a table. File-name must exist, and
783 each row must contain the same number of columns as defined in the table.
784 If a line in the file contains more or less than the number of columns defined,
785 the copy method rollbacks any inserts, and returns an error.</p>
786
787 <p>Column-separator is an optional column separator string. The default is
788 the ASCII tab character \t. </p>
789
790 <p>Null-indicator is an optional string that indicates a column value is null.
791 The default is an empty string. Note that column-separator and
792 null-indicator are optional positional arguments; if null-indicator
793 is specified, a column-separator argument must be specified and
794 precede the null-indicator argument.</p>
795
796 <p>The copy method implements similar functionality to the <b>.import</b>
797 SQLite shell command.
798
799 <a name="timeout"></a>
800
801 </p><h2 id="the_timeout_method"><span>3.8. </span>The timeout method</h2>
802
803 <p>The "timeout" method is used to control how long the SQLite library
804 will wait for locks to clear before giving up on a database transaction.
805 The default timeout is 0 millisecond. (In other words, the default behavior
806 is not to wait at all.)</p>
807
808 <p>The SQLite database allows multiple simultaneous
809 readers or a single writer but not both. If any process is writing to
810 the database no other process is allows to read or write. If any process
811 is reading the database other processes are allowed to read but not write.
812 The entire database shared a single lock.</p>
813
814 <p>When SQLite tries to open a database and finds that it is locked, it
815 can optionally delay for a short while and try to open the file again.
816 This process repeats until the query times out and SQLite returns a
817 failure. The timeout is adjustable. It is set to 0 by default so that
818 if the database is locked, the SQL statement fails immediately. But you
819 can use the "timeout" method to change the timeout value to a positive
820 number. For example:</p>
821
822 <blockquote><b>db1 timeout 2000</b></blockquote>
823
824 <p>The argument to the timeout method is the maximum number of milliseconds
825 to wait for the lock to clear. So in the example above, the maximum delay
826 would be 2 seconds.</p>
827
828 <a name="busy"></a>
829
830 <h2 id="the_busy_method"><span>3.9. </span>The busy method</h2>
831
832 <p>The "busy" method, like "timeout", only comes into play when the
833 database is locked. But the "busy" method gives the programmer much more
834 control over what action to take. The "busy" method specifies a callback
835 Tcl procedure that is invoked whenever SQLite tries to open a locked
836 database. A single integer argument is appended to the callback before
837 it is invoked. The argument is the number of prior calls to the busy
838 callback for the current locking event. It is intended that
839 the callback will do some other useful work for a short while
840 (such as service GUI events) then return
841 so that the lock can be tried again. The callback procedure should
842 return "0" if it wants SQLite to try again to open the database and
843 should return "1" if it wants SQLite to abandon the current operation.
844 </p><p>
845 If the busy method is invoked without an argument, the name of the callback
846 procedure last set by the busy method is returned. If no callback procedure
847 has been set, an empty string is returned.
848
849 <a name="enable_load_extension"></a>
850
851 </p><h2 id="the_enable_load_extension_method"><span>3.10. </span>The enable_load_extension method</h2>
852
853 <p>The extension loading mechanism of SQLite (accessed using the
854 <a href="lang_corefunc.html#load_extension">load_extension()</a> SQL function) is turned off by default. This is
855 a security precaution. If an application wants to make use of the
856 <a href="lang_corefunc.html#load_extension">load_extension()</a> function it must first turn the capability on using
857 this method.</p>
858
859 <p>This method takes a single boolean argument which will turn the
860 extension loading functionality on or off.</p>
861
862 <p>For best security, do not use this method unless truly needed,
863 and run <a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a> or the
864 "<a href="#config">db config trusted_schema 0</a>" method <u>before</u>
865 invoking this method.
866
867 </p><p>This method maps to the <a href="c3ref/enable_load_extension.html">sqlite3_enable_load_extension()</a> C/C++
868 interface.</p>
869
870 <a name="exists"></a>
871
872 <h2 id="the_exists_method"><span>3.11. </span>The exists method</h2>
873
874 <p>The "exists" method is similar to "onecolumn" and "eval" in that
875 it executes SQL statements. The difference is that the "exists" method
876 always returns a boolean value which is TRUE if a query in the SQL
877 statement it executes returns one or more rows and FALSE if the SQL
878 returns an empty set.</p>
879
880 <p>The "exists" method is often used to test for the existence of
881 rows in a table. For example:</p>
882
883 <blockquote><b>
884 if {&#91;db exists {SELECT 1 FROM table1 WHERE user=$user}&#93;} {<br>
885 &nbsp;&nbsp;&nbsp;# Processing if $user exists<br>
886 } else {<br>
887 &nbsp;&nbsp;&nbsp;# Processing if $user does not exist<br>
888 }
889 </b></blockquote>
890
891 <a name="last_insert_rowid"></a>
892
893 <h2 id="the_last_insert_rowid_method"><span>3.12. </span>The last_insert_rowid method</h2>
894
895 <p>The "last_insert_rowid" method returns an integer which is the ROWID
896 of the most recently inserted database row.</p>
897
898 <a name="function"></a>
899
900 <h2 id="the_function_method"><span>3.13. </span>The function method</h2>
901
902 <p>The "function" method registers new SQL functions with the SQLite engine.
903 The arguments are the name of the new SQL function and a TCL command that
904 implements that function. Arguments to the function are appended to the
905 TCL command before it is invoked.</p>
906
907 <p>
908 For security reasons, it is recommended that applications first set
909 <a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a> or run the
910 "<a href="#config">db config trusted_schema 0</a>"
911 method before using this method.</p>
912
913 <p>
914 The syntax looks like this:
915 </p>
916
917 <blockquote>
918 <i>dbcmd</i>&nbsp;&nbsp;<b>function</b>
919 &nbsp;&nbsp;<i>sql-name</i>
920 &nbsp;&nbsp;<i>?options?</i>
921 &nbsp;&nbsp;<i>script</i>
922 </blockquote>
923
924 <p>
925 The following example creates a new SQL function named "hex" that converts
926 its numeric argument into a hexadecimal encoded string:
927 </p>
928
929 <blockquote><b>
930 db function hex {format 0x%X}
931 </b></blockquote>
932
933 <p>The "function" method accepts the following options:
934 </p><blockquote>
935 <dl>
936
937 <dt><b>-argcount</b> <i>INTEGER</i></dt>
938 <dd><p>
939 Specify the number of arguments that the SQL function accepts. The default
940 value of -1 means any number of arguments.
941
942 </p></dd><dt><b>-deterministic</b>
943 </dt><dd><p>
944 This option indicates that the function will always return the same
945 answer given the same argument values. The SQLite query optimizer
946 uses this information to cache answers from function calls with
947 constant inputs and reuse the result rather than invoke the function
948 repeatedly.
949 </p></dd>
950
951 <dt><b>-directonly</b>
952 </dt><dd><p>
953 This option restricts the function to only be usable by direct
954 top-level SQL statements. The function will not be accessible to
955 triggers, views, CHECK constraints, generated columns, or index
956 expressions. This option is recommended for all application-defined
957 SQL functions, and is <u>highly recommended</u> for any SQL
958 function that has side effects or that reveals internal state of
959 the application.
960
961 </p><p style="background-color:#ffff90;"><b>Security Warning:</b>
962 Without this switch, an attacker might be able to change the
963 schema of a database file to include the new function inside a trigger
964 or view or CHECK constraint and thereby trick the application into
965 running the function with parameters of the attacker's choosing.
966 Hence, if the new function has side effects or reveals internal
967 state about the application and the -directonly option is not
968 used, that is a potential security vulnerability.</p>
969 </dd>
970
971 <dt><b>-innocuous</b>
972 </dt><dd><p>
973 This option indicates that the function has no side effects and
974 does not leak any information that cannot be computed directly
975 from its input parameters. When this option is specified, the
976 function may be used in triggers, views, CHECK constraints,
977 generated columns, and/or index expressions even if
978 <a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a>. The use of this option is discouraged
979 unless it is truly needed.
980 </p></dd>
981
982 <dt><b>-returntype integer|real|text|blob|any</b>
983 </dt><dd><p>
984 This option is used to configure the type of the result returned by the
985 function. If this option is set to "any" (the default), SQLite attempts to
986 determine the type of each value returned by the function implementation based
987 on the Tcl value's internal type. Or, if it is set to "text" or "blob", the
988 returned value is always a text or blob value, respectively. If this option is
989 set to "integer", SQLite attempts to coerce the value returned by the function
990 to an integer. If this is not possible without data loss, it attempts to coerce
991 it to a real value, and finally falls back to text. If this option is set to
992 "real", an attempt is made to return a real value, falling back to text if this
993 is not possible.
994 <br>
995 As of version 3.51, a <code>break</code> propagating from an SQL function will
996 be interpreted as an SQL NULL, as distinct from an empty string.
997 </p></dd>
998 </dl>
999
1000 </blockquote>
1001
1002 <a name="nullvalue"></a>
1003
1004 <h2 id="the_nullvalue_method"><span>3.14. </span>The nullvalue method</h2>
1005
1006 <p>
1007 The "nullvalue" method changes the representation for NULL returned
1008 as result of the "eval" method.</p>
1009
1010 <blockquote><b>
1011 db1 nullvalue NULL
1012 </b></blockquote>
1013
1014 <p>The "nullvalue" method is useful to differ between NULL and empty
1015 column values as Tcl lacks a NULL representation. The default
1016 representation for NULL values is an empty string.</p>
1017
1018 <a name="onecolumn"></a>
1019
1020 <h2 id="the_onecolumn_method"><span>3.15. </span>The onecolumn method</h2>
1021
1022 <p>The "onecolumn" method works like
1023 "<a href="#eval">eval</a>" in that it evaluates the
1024 SQL query statement given as its argument. The difference is that
1025 "onecolumn" returns a single element which is the first column of the
1026 first row of the query result.</p>
1027
1028 <p>This is a convenience method. It saves the user from having to
1029 do a "<tt>&#91;lindex&nbsp;...&nbsp;0&#93;</tt>" on the results of an "eval"
1030 in order to extract a single column result.</p>
1031
1032 <p>Because method names can be shortened to any unique prefix, this method
1033 is usually written as "one" instead of its full name "onecolumn".
1034
1035 <a name="changes"></a>
1036
1037 </p><h2 id="the_changes_method"><span>3.16. </span>The changes method</h2>
1038
1039 <p>The "changes" method returns an integer which is the number of rows
1040 in the database that were inserted, deleted, and/or modified by the most
1041 recent "eval" method.</p>
1042
1043
1044 <a name="total_changes"></a>
1045
1046 <h2 id="the_total_changes_method"><span>3.17. </span>The total_changes method</h2>
1047
1048 <p>The "total_changes" method returns an integer which is the number of rows
1049 in the database that were inserted, deleted, and/or modified since the
1050 current database connection was first opened.</p>
1051
1052 <a name="authorizer"></a>
1053
1054 <h2 id="the_authorizer_method"><span>3.18. </span>The authorizer method</h2>
1055
1056 <p>The "authorizer" method provides access to the
1057 <a href="c3ref/set_authorizer.html">sqlite3_set_authorizer</a>
1058 C/C++ interface. The argument to authorizer is the name of a procedure that
1059 is called when SQL statements are being compiled in order to authorize
1060 certain operations. The callback procedure takes 5 arguments which describe
1061 the operation being coded. If the callback returns the text string
1062 "SQLITE_OK", then the operation is allowed. If it returns "SQLITE_IGNORE",
1063 then the operation is silently disabled. If the return is "SQLITE_DENY"
1064 then the compilation fails with an error.
1065 </p>
1066
1067 <p>If the argument is an empty string then the authorizer is disabled.
1068 If the argument is omitted, then the current authorizer is returned.</p>
1069
1070 <a name="bind_fallback"></a>
1071
1072 <h2 id="the_bind_fallback_method"><span>3.19. </span>The bind_fallback method</h2>
1073
1074 <p>The "bind_fallback" method gives the application control over how to
1075 handle parameter binding when no TCL variable matches the parameter name.
1076
1077 </p><p>When the <a href="#eval">eval method</a> sees a named SQL
1078 parameter such as "$abc" or ":def" or "@ghi" in an SQL statement, it tries
1079 to look up a TCL variable with the same name, and it binds the value
1080 of that TCL variable to the SQL parameter. If no such TCL variable exists,
1081 the default behavior is to bind an SQL NULL value to the parameter. However,
1082 if a bind_fallback proc is specified, then that proc is invoked with the
1083 name of the SQL parameter and the return value from the proc is bound to
1084 the SQL parameter. Or if the proc returns an error, then the SQL statement
1085 aborts with that error. If the proc returns with some code other than
1086 TCL_OK or TCL_ERROR, then the SQL parameter is bound to NULL, as it would
1087 be by default.
1088
1089 </p><p>The "bind_fallback" method has a single optional argument. If the argument
1090 is an empty string, then the bind_fallback is cancelled and the default behavior
1091 is restored. If the argument is a non-empty string, then the argument is a
1092 TCL command (usually the name of a proc) to invoke whenever an SQL parameter
1093 is seen that does not match any TCL variable. If the "bind_fallback" method
1094 is given no arguments, then the current bind_fallback command is returned.
1095
1096 </p><p>As an example, the following setup causes TCL to throw an error if
1097 an SQL statement contains an parameter that does not match any global
1098 TCL variable:
1099
1100 </p><blockquote><pre><b>
1101 proc bind_error {nm} {
1102 error "no such variable: $nm"
1103 }
1104 db bind_fallback bind_error
1105 </b></pre></blockquote>
1106
1107 <a name="progress"></a>
1108
1109 <h2 id="the_progress_method"><span>3.20. </span>The progress method</h2>
1110
1111 <p>This method registers a callback that is invoked periodically during
1112 query processing. There are two arguments: the number of SQLite virtual
1113 machine opcodes between invocations, and the TCL command to invoke.
1114 Setting the progress callback to an empty string disables it.</p>
1115
1116 <p>The progress callback can be used to display the status of a lengthy
1117 query or to process GUI events during a lengthy query.</p>
1118
1119 <a name="collate"></a>
1120
1121 <h2 id="the_collate_method"><span>3.21. </span>The collate method</h2>
1122
1123 <p>This method registers new text collating sequences. There are
1124 two arguments: the name of the collating sequence and the name of a
1125 TCL procedure that implements a comparison function for the collating
1126 sequence.
1127 </p>
1128
1129 <p>For example, the following code implements a collating sequence called
1130 "NOCASE" that sorts in text order without regard to case:
1131 </p>
1132
1133 <blockquote><pre><b>
1134 proc nocase_compare {a b} {
1135 return &#91;string compare &#91;string tolower $a] &#91;string tolower $b]]
1136 }
1137 db collate NOCASE nocase_compare
1138 </b></pre></blockquote>
1139
1140 <a name="collation_needed"></a>
1141
1142 <h2 id="the_collation_needed_method"><span>3.22. </span>The collation_needed method</h2>
1143
1144 <p>This method registers a callback routine that is invoked when the SQLite
1145 engine needs a particular collating sequence but does not have that
1146 collating sequence registered. The callback can register the collating
1147 sequence. The callback is invoked with a single parameter which is the
1148 name of the needed collating sequence.</p>
1149
1150 <a name="commit_hook"></a>
1151
1152 <h2 id="the_commit_hook_method"><span>3.23. </span>The commit_hook method</h2>
1153
1154 <p>This method registers a callback routine that is invoked just before
1155 SQLite tries to commit changes to a database. If the callback throws
1156 an exception or returns a non-zero result, then the transaction rolls back
1157 rather than commit.</p>
1158
1159 <a name="rollback_hook"></a>
1160
1161 <h2 id="the_rollback_hook_method"><span>3.24. </span>The rollback_hook method</h2>
1162
1163 <p>This method registers a callback routine that is invoked just before
1164 SQLite tries to do a rollback. The script argument is run without change.</p>
1165
1166 <a name="status"></a>
1167
1168 <h2 id="the_status_method"><span>3.25. </span>The status method</h2>
1169
1170 <p>This method returns status information from the most recently evaluated
1171 SQL statement. The status method takes a single argument which should be
1172 either "steps" or "sorts". If the argument is "steps", then the method
1173 returns the number of full table scan steps that the previous SQL statement
1174 evaluated. If the argument is "sorts", the method returns the number of
1175 sort operations. This information can be used to detect queries that are
1176 not using indices to speed search or sorting.</p>
1177
1178 <p>The status method is basically a wrapper on the
1179 <a href="c3ref/stmt_status.html">sqlite3_stmt_status()</a> C-language interface.</p>
1180
1181 <a name="update_hook"></a>
1182
1183 <h2 id="the_update_hook_method"><span>3.26. </span>The update_hook method</h2>
1184
1185 <p>This method registers a callback routine that is invoked just after
1186 each row is modified by an UPDATE, INSERT, or DELETE statement. Four
1187 arguments are appended to the callback before it is invoked:</p>
1188
1189 <ul>
1190 <li>The keyword "INSERT", "UPDATE", or "DELETE", as appropriate</li>
1191 <li>The name of the database which is being changed</li>
1192 <li>The table that is being changed</li>
1193 <li>The rowid of the row in the table being changed</li>
1194 </ul>
1195
1196 <p>The callback must not do anything that will modify the database connection
1197 that invoked the update hook such as running queries.</p>
1198
1199 <a name="preupdate"></a>
1200
1201 <h2 id="the_preupdate_method"><span>3.27. </span>The preupdate method</h2>
1202
1203 <p>This method either registers a callback routine that is invoked just
1204 before each row is modified by an UPDATE, INSERT, or DELETE statement,
1205 or may perform certain operations related to the impending update.</p>
1206
1207 <p>To register or remove a preupdate callback, use this syntax:
1208 </p><blockquote>
1209 <i>dbcmd</i>&nbsp;&nbsp;<b>preupdate&nbsp;&nbsp;hook</b>&nbsp;&nbsp;<i>?SCRIPT?</i>
1210 </blockquote>
1211 When a preupdate callback is registered, then prior to each row modification,
1212 the callback is run with these arguments:
1213 <ul>
1214 <li>The keyword "INSERT", "UPDATE", or "DELETE", as appropriate</li>
1215 <li>The name of the database which is being changed</li>
1216 <li>The table that is being changed</li>
1217 <li>The original rowid of the row in the table being changed</li>
1218 <li>The new rowid (if any) of the row in the table being changed</li>
1219 </ul>
1220 The callback must not do anything that will modify the database connection
1221 that invoked the preupdate hook such as running queries.
1222
1223 <p>When the callback is executing, and only then, these preupdate operations
1224 may be performed by use of the indicated syntax:
1225 </p><blockquote>
1226 <i>dbcmd</i>&nbsp;&nbsp;<b>preupdate&nbsp;&nbsp;count</b><br>
1227 <i>dbcmd</i>&nbsp;&nbsp;<b>preupdate&nbsp;&nbsp;depth</b><br>
1228 <i>dbcmd</i>&nbsp;&nbsp;<b>preupdate&nbsp;&nbsp;new</b>&nbsp;&nbsp;<i>INDEX</i><br>
1229 <i>dbcmd</i>&nbsp;&nbsp;<b>preupdate&nbsp;&nbsp;old</b>&nbsp;&nbsp;<i>INDEX</i><br>
1230 </blockquote>
1231
1232
1233 <p>The <b>count</b> submethod returns the number of columns in the row that is
1234 being inserted, updated, or deleted.</p>
1235 <p>The <b>depth</b> submethod returns the update nesting depth. This will be
1236 0 for a direct insert, update, or delete operation;
1237 1 for inserts, updates, or deletes invoked by top-level triggers;
1238 or higher values for changes resulting from trigger-invoked triggers.</p>
1239
1240 <p>The <b>old</b> and <b>new</b> submethods return the selected original
1241 or changed column value respectively of the row being updated.</p>
1242
1243 <p>Note that the Tcl interface (and underlying SQLite library) must have
1244 been built with the preprocessor symbol SQLITE_ENABLE_PREUPDATE_HOOK
1245 defined for the <b>preupdate</b> method to be available.</p>
1246
1247 <a name="wal_hook"></a>
1248
1249 <h2 id="the_wal_hook_method"><span>3.28. </span>The wal_hook method</h2>
1250
1251 <p>This method registers a callback routine that is invoked after transaction
1252 commit when the database is in <a href="wal.html">WAL mode</a>. Two arguments are appended to the
1253 callback command before it is invoked:</p>
1254
1255 <ul>
1256 <li>The name of the database on which the transaction was committed
1257 </li><li>The number of entries in the write-ahead log (WAL) file for that database
1258 </li></ul>
1259
1260 <p>This method might decide to run a <a href="wal.html#ckpt">checkpoint</a> either itself or as a
1261 subsequent idle callback. Note that SQLite only allows a single WAL hook.
1262 By default this single WAL hook is used for the auto-checkpointing. If you
1263 set up an explicit WAL hook, then that one WAL hook must ensure that checkpoints
1264 are occurring since the auto-checkpointing mechanism will be disabled.</p>
1265
1266 <p>This method should return an integer value that is equivalent to an
1267 SQLite error code (usually 0 for SQLITE_OK in the case of success or 1 for
1268 SQLITE_ERROR if some error occurs). As in <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a>, the results of
1269 returning an integer that does not correspond to an SQLite error code are
1270 undefined. If the value returned by the script cannot be interpreted as an
1271 integer value, or if the script throws a Tcl exception, no error is returned to
1272 SQLite but a Tcl background-error is raised.
1273
1274 <a name="incrblob"></a>
1275
1276 </p><h2 id="the_incrblob_method"><span>3.29. </span>The incrblob method</h2>
1277
1278 <p>This method opens a TCL channel that can be used to read or write
1279 into a preexisting BLOB in the database. The syntax is like this:</p>
1280
1281 <blockquote>
1282 <i>dbcmd</i>&nbsp;&nbsp;<b>incrblob</b>&nbsp;&nbsp;<b>?-readonly?</b>
1283 &nbsp;&nbsp;<i>?DB?&nbsp;&nbsp;TABLE&nbsp;&nbsp;COLUMN&nbsp;&nbsp;ROWID</i>
1284 </blockquote>
1285
1286 <p>
1287 The command returns a new TCL channel for reading or writing to the BLOB.
1288 The channel is opened using the underlying
1289 <a href="c3ref/blob_open.html">sqlite3_blob_open()</a> C-language
1290 interface. Close the channel using the <b>close</b> command of TCL.
1291 </p>
1292
1293 <a name="errorcode"></a>
1294
1295 <h2 id="the_errorcode_method"><span>3.30. </span>The errorcode method</h2>
1296
1297 <p>This method returns the numeric error code that resulted from the most
1298 recent SQLite operation.</p>
1299
1300
1301 <a name="trace"></a>
1302
1303 <h2 id="the_trace_method"><span>3.31. </span>The trace method</h2>
1304
1305 <p>The "trace" method registers a callback that is invoked as each SQL
1306 statement is compiled. The text of the SQL is appended as a single string
1307 to the command before it is invoked. This can be used (for example) to
1308 keep a log of all SQL operations that an application performs.
1309 </p>
1310
1311 <a name="trace_v2"></a>
1312
1313 <h2 id="the_trace_v2_method"><span>3.32. </span>The trace_v2 method</h2>
1314
1315 <p>The "trace_v2" method registers a callback that is invoked as each SQL
1316 statement is compiled. The syntax is as follows:
1317
1318
1319 </p><blockquote>
1320 <i>dbcmd</i>&nbsp;&nbsp;<b>trace_v2</b>&nbsp;&nbsp;?<i>callback</i>?&nbsp;&nbsp;?<i>mask</i>?
1321 </blockquote>
1322
1323 <p>This command causes the "callback" script to be invoked whenever
1324 certain conditions occurs. The conditions are determined by the <i>mask</i>
1325 argument, which should be a TCL-list of zero or more of the following
1326 keywords:
1327
1328 </p><ul>
1329 <li> <b>statement</b>
1330 </li><li> <b>profile</b>
1331 </li><li> <b>row</b>
1332 </li><li> <b>close</b>
1333 </li></ul>
1334
1335 <p>Traces for <b>statement</b> invoke the callback with two arguments
1336 whenever a new SQL statement is run.
1337 The first argument is an integer which is the value of the pointer
1338 to the underlying <a href="c3ref/stmt.html">sqlite3_stmt</a> object. This integer can be used
1339 to correlate SQL statement text with the result of a <b>profile</b>
1340 or <b>row</b> callback. The second argument is the
1341 unexpanded text of the SQL statement being run. By "unexpanded", we
1342 mean that variable substitutions in the text are not expanded into the
1343 variable values. This is different from the behavior of the "trace"
1344 method which does expand variable substitutions.
1345
1346 </p><p>Traces for <b>profile</b> invoke the callback with two arguments
1347 as each SQL statement finishes. The first argument is an integer which
1348 is the value of the underlying <a href="c3ref/stmt.html">sqlite3_stmt</a> object. The second
1349 argument is the approximate run-time for the statement in nanoseconds.
1350 The run-time is the best estimate available depending on the capabilities
1351 of the platform on which the application is running.
1352
1353 </p><p>Traces for <b>row</b> invoke the callback with a single argument
1354 whenever a new result row is available from an SQL statement.
1355 The argument is an integer which is the value of the underlying
1356 <a href="c3ref/stmt.html">sqlite3_stmt</a> object pointer.
1357
1358 </p><p>Traces for <b>close</b> invoke the callback with a single argument
1359 as the database connection is closing. The argument is an integer which
1360 is the value of a pointer to the underlying <a href="c3ref/sqlite3.html">sqlite3</a> object that is
1361 closing.
1362
1363 </p><p>There can only be a single trace callback registered on a database
1364 connection. Each use of "trace" or "trace_v2" cancels all prior
1365 trace callback.
1366
1367 <a name="backup"></a>
1368
1369 </p><h2 id="the_backup_method"><span>3.33. </span>The backup method</h2>
1370
1371 <p>The "backup" method makes a backup copy of a live database. The
1372 command syntax is like this:</p>
1373
1374 <blockquote>
1375 <i>dbcmd</i>&nbsp;&nbsp;<b>backup</b>&nbsp;&nbsp;?<i>source-database</i>?&nbsp;&nbsp;<i>backup-filename</i>
1376 </blockquote>
1377
1378 <p>The optional <i>source-database</i> argument tells which database in
1379 the current connection should be backed up. The default value is <b>main</b>
1380 (or, in other words, the primary database file). To back up TEMP tables
1381 use <b>temp</b>. To backup an auxiliary database added to the connection
1382 using the <a href="lang_attach.html">ATTACH</a> command, use the name of that database as it was assigned
1383 in the <a href="lang_attach.html">ATTACH</a> command.</p>
1384
1385 <p>The <i>backup-filename</i> is the name of a file into which the backup is
1386 written. <i>Backup-filename</i> does not have to exist ahead of time, but if
1387 it does, it must be a well-formed SQLite database.</p>
1388
1389 <a name="restore"></a>
1390
1391 <h2 id="the_restore_method"><span>3.34. </span>The restore method</h2>
1392
1393 <p>The "restore" method copies the content from a separate database file
1394 into the current database connection, overwriting any preexisting content.
1395 The command syntax is like this:</p>
1396
1397 <blockquote>
1398 <i>dbcmd</i>&nbsp;&nbsp;<b>restore</b>&nbsp;&nbsp;?<i>target-database</i>?&nbsp;&nbsp;<i>source-filename</i>
1399 </blockquote>
1400
1401 <p>The optional <i>target-database</i> argument tells which database in
1402 the current connection should be overwritten with new content.
1403 The default value is <b>main</b>
1404 (or, in other words, the primary database file). To repopulate the TEMP tables
1405 use <b>temp</b>. To overwrite an auxiliary database added to the connection
1406 using the <a href="lang_attach.html">ATTACH</a> command, use the name of that database as it was assigned
1407 in the <a href="lang_attach.html">ATTACH</a> command.</p>
1408
1409 <p>The <i>source-filename</i> is the name of an existing well-formed SQLite
1410 database file from which the content is extracted.</p>
1411
1412 <a name="serialize"></a>
1413
1414 <h2 id="the_serialize_method"><span>3.35. </span>The serialize method</h2>
1415
1416 <p>The "serialize" method creates a BLOB which is a complete copy of an
1417 underlying database. The syntax is like this:
1418
1419 </p><blockquote>
1420 <i>dbcmd</i>&nbsp;&nbsp;<b>serialize</b>&nbsp;&nbsp;?<i>database</i>?
1421 </blockquote>
1422
1423 <p>The optional argument is the name of the schema or database to be serialized.
1424 The default value is "main".
1425
1426 </p><p>This routine returns a TCL byte-array that is the complete content of
1427 the identified database. This byte-array can be written into a file and
1428 then used as an ordinary SQLite database, or it can be sent over a TCP/IP
1429 connection to some other application, or passed to the "deserialize" method
1430 of another database connection.
1431
1432 <a name="deserialize"></a>
1433
1434 </p><h2 id="the_deserialize_method"><span>3.36. </span>The deserialize method</h2>
1435
1436 <p>The "deserialize" method takes a TCL byte-array that contains an SQLite
1437 database file and adds it to the database connection. The syntax is:
1438
1439 </p><blockquote>
1440 <i>dbcmd</i>&nbsp;&nbsp;<b>deserialize</b>&nbsp;&nbsp;?<i>database</i>?&nbsp;&nbsp;<i>value</i>
1441 </blockquote>
1442
1443 <p>The option <i>database</i> argument identifies which attached database
1444 should receive the deserialization. The default is "main".
1445
1446 </p><p>This command causes SQLite to disconnect from the previous database and
1447 reattach to an in-memory database with the content in <i>value</i>. If <i>value</i>
1448 is not a byte-array containing a well-defined SQLite database, then subsequent
1449 commands will likely return <a href="rescode.html#corrupt">SQLITE_CORRUPT</a> errors.
1450
1451 <a name="interrupt"></a>
1452
1453 </p><h2 id="the_interrupt_method"><span>3.37. </span>The interrupt method</h2>
1454
1455 <p>The "interrupt" method invokes the <a href="c3ref/interrupt.html">sqlite3_interrupt()</a> interface, causing
1456 any pending queries to halt.
1457
1458 <a name="version"></a>
1459
1460 </p><h2 id="the_version_method"><span>3.38. </span>The version method</h2>
1461
1462 <p>Return the current library version. For example, "3.23.0".
1463
1464 <a name="profile"></a>
1465
1466 </p><h2 id="the_profile_method"><span>3.39. </span>The profile method</h2>
1467
1468 <p>This method is used to profile the execution of SQL statements run by
1469 the application. The syntax is as follows:
1470 </p>
1471
1472 <blockquote>
1473 <i>dbcmd</i>&nbsp;&nbsp;<b>profile</b>&nbsp;&nbsp;?<i>script</i>?
1474 </blockquote>
1475
1476 <p>Unless <i>script</i> is an empty string, this method arranges for the
1477 <i>script</i> to be evaluated after the execution of each SQL statement.
1478 Two arguments are appended to <i>script</i> before it is invoked: the
1479 text of the SQL statement executed and the time elapsed while executing
1480 the statement, in nanoseconds.
1481 </p>
1482 <p>A database handle may only have a single profile script registered at
1483 any time. If there is already a script registered when the profile method
1484 is invoked, the previous profile script is replaced by the new one. If the
1485 <i>script</i> argument is an empty string, any previously registered
1486 profile callback is canceled but no new profile script is registered.
1487 </p>
1488
1489 <a name="unlock_notify"></a>
1490
1491 <h2 id="the_unlock_notify_method"><span>3.40. </span>The unlock_notify method</h2>
1492
1493 <p>The unlock_notify method is used to access the <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a>
1494 interface to the SQLite core library for testing purposes. The use of
1495 this method by applications is discouraged.
1496 </p>
1497 <a name="format"></a>
1498
1499 <h2 id="the_format_method"><span>3.41. </span>The format method</h2>
1500
1501 <p>
1502 The format method was introduced with SQLite <a href="releaselog/3_52_0.html">version 3.52.0</a>
1503 (2026-03-06). The format method provides an interface into
1504 the <a href="https://sqlite.org/src/file/ext/qrf">Query Result Formatter (QRF)</a>
1505 subsystem.
1506 </p>
1507
1508 <blockquote>
1509 <i>dbcmd</i>&ensp;<b>format</b>&ensp;?<i>options</i>?&ensp;<i>SQL</i>
1510 </blockquote>
1511
1512 <p>
1513 The format method executes the SQL given as an argument, formats the
1514 output in accordance with options, and returns the formatted result
1515 as a string. The QRF is a complex subsystem, and so there are a lot
1516 of options. Each option corresponds to a field in the
1517 <a href="https://sqlite.org/src/file/ext/qrf/README.md#spec">sqlite3_qrf_spec object</a>
1518 The TCL names for the various options, their
1519 allowed arguments, and the sqlite3_qrf_spec fields those options control
1520 are listed below. Refer to the
1521 <a href="https://sqlite.org/src/file/ext/qrf/README.md">QRF documentation</a>
1522 for specifies on the meaning of each option.
1523 </p>
1524
1525 <p>
1526 Unspecified options default to "auto" or 0 or an empty string, as
1527 appropriate.
1528 </p>
1529
1530 <blockquote><dl>
1531
1532 <td><b>-style</b> <i>STYLE</i>
1533 <dd><p>
1534 STYLE can be one of "auto", "box", "column", "count", "csv", "eqp",
1535 "explain", "html", "insert", "json", "jobject", "line", "list",
1536 "markdown", "off", "quote", "stats", "statsest", "statsvm", or "table".
1537 This option determines the eStyle value of sqlite3_qrf_spec.
1538 </p></dd>
1539
1540 </td><td><b>-esc</b> <i>ESC</i>
1541 <dd><p>
1542 ESC can be one of "auto", "off", "ascii", or "symbol".
1543 This option determines the eEsc value of sqlite3_qrf_spec.
1544 </p></dd>
1545
1546 </td><td><b>-text</b> <i>ENCODING</i>
1547 <dd><p>
1548 ENCODING can be one of "auto", "csv", "html", "json", "plain",
1549 "relaxed", "sql", or "tcl".
1550 This option determines the eText value of sqlite3_qrf_spec.
1551 </p></dd>
1552
1553 </td><td><b>-title</b> <i>ENCODING</i>
1554 <dd><p>
1555 ENCODING can be one of "on", "off", "auto", "csv", "html", "json",
1556 "plain", "relaxed", "sql", or "tcl".
1557 This option determines the eTitle and bTitles values of sqlite3_qrf_spec.
1558 </p></dd>
1559
1560 </td><td><b>-blob</b> <i>ENCODING</i>
1561 <dd><p>
1562 ENCODING can be one of "auto", "hex", "json", "tcl", "text", or "size".
1563 This option determines the eBlob value of sqlite3_qrf_spec.
1564 </p></dd>
1565
1566 </td><td><b>-border</b> auto|off|on
1567 <dd><p>
1568 This option controls whether or not the outer border is drawn around
1569 the entire result when the --style is "box" or "table". The default
1570 is that the border is drawn. But if this setting is "off", then the
1571 border is omitted. This option controls the
1572 bBorder value of sqlite3_qrf_spec.
1573 </p></dd>
1574
1575 </td><td><b>-wordwrap</b> auto|off|on
1576 <dd><p>
1577 This option controls the bWordWrap value of sqlite3_qrf_spec.
1578 </p></dd>
1579
1580 </td><td><b>-textjsonb</b> auto|off|on
1581 <dd><p>
1582 This option controls the bTextJsonb value of sqlite3_qrf_spec.
1583 </p></dd>
1584
1585 </td><td><b>-splitcolumn</b> auto|off|on
1586 <dd><p>
1587 This option controls the bSplitColumn value of sqlite3_qrf_spec.
1588 </p></dd>
1589
1590 </td><td><b>-defaultalign</b> <i>ALIGNMENT</i>
1591 <dd><p>
1592 ALIGNMENT can be one of "auto", "bottom", "c", "center", "e", "left",
1593 "middle", "n", "ne", "nw", "right", "s", "se", "sw", "top", or "w".
1594 This option controls the eDfltAlign value of sqlite3_qrf_spec.
1595 </p></dd>
1596
1597 </td><td><b>-titlealign</b> <i>ALIGNMENT</i>
1598 <dd><p>
1599 ALIGNMENT can be one of "auto", "bottom", "c", "center", "e", "left",
1600 "middle", "n", "ne", "nw", "right", "s", "se", "sw", "top", or "w".
1601 This option controls the eTitleAlign value of sqlite3_qrf_spec.
1602 </p></dd>
1603
1604 </td><td><b>-align</b> <i>ALIGNMENT-LIST</i>
1605 <dd><p>
1606 ALIGNMENT-LIST is a TCL list containing zero or more tokens where
1607 each token is one of:
1608 "auto", "bottom", "c", "center", "e", "left",
1609 "middle", "n", "ne", "nw", "right", "s", "se", "sw", "top", or "w".
1610 This option determines the nAlign and aAlign values of sqlite3_qrf_spec.
1611 </p></dd>
1612
1613 </td><td><b>-wrap</b> <i>INTEGER</i>
1614 <dd><p>
1615 This option determines the nWrap values of sqlite3_qrf_spec.
1616 </p></dd>
1617
1618 </td><td><b>-screenwidth</b> <i>INTEGER</i>
1619 <dd><p>
1620 This option determines the nScreenWidth values of sqlite3_qrf_spec.
1621 </p></dd>
1622
1623 </td><td><b>-linelimit</b> <i>INTEGER</i>
1624 <dd><p>
1625 This option determines the nLineLimit values of sqlite3_qrf_spec.
1626 </p></dd>
1627
1628 </td><td><b>-charlimit</b> <i>INTEGER</i>
1629 <dd><p>
1630 This option determines the nCharLimit values of sqlite3_qrf_spec.
1631 </p></dd>
1632
1633 </td><td><b>-titlelimit</b> <i>INTEGER</i>
1634 <dd><p>
1635 This option determines the nTitleLimit values of sqlite3_qrf_spec.
1636 </p></dd>
1637
1638 </td><td><b>-multiinsert</b> <i>INTEGER</i>
1639 <dd><p>
1640 This option determines the nMultiInsert values of sqlite3_qrf_spec.
1641 </p></dd>
1642
1643 </td><td><b>-widths</b> <i>INTEGER-LIST</i>
1644 <dd><p>
1645 INTEGER-LIST is a TCL list of integer.
1646 This option determines the nWidth and aWidth values of sqlite3_qrf_spec.
1647 </p></dd>
1648
1649 </td><td><b>-columnsep</b> <i>STRING/i>
1650 <dd><p>
1651 This option set the zColumnSep value of sqlite3_qrf_spec.
1652 </p></dd>
1653
1654 </i></td><td><b>-rowsep</b> <i>STRING/i>
1655 <dd><p>
1656 This option set the zRowSep value of sqlite3_qrf_spec.
1657 </p></dd>
1658
1659 </i></td><td><b>-tablename</b> <i>STRING/i>
1660 <dd><p>
1661 This option set the zTableName value of sqlite3_qrf_spec.
1662 </p></dd>
1663
1664 </i></td><td><b>-null</b> <i>STRING/i>
1665 <dd><p>
1666 This option set the zNull value of sqlite3_qrf_spec.
1667 </p></dd>
1668 </i></td></dl></blockquote>
1669 <p align="center"><small><i>This page was last updated on 2026-03-21 20:08:30Z </small></i></p>
1670