| 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> → Build the SQLite extension but do not |
| 223 |
move the files into the installation directory. |
| 224 |
</li><li><b>tclextension-list</b> → Show all SQLite extensions available to |
| 225 |
the Tcl interpreter specified to ./configure or by TCLDIR. |
| 226 |
</li><li><b>tclextension-uninstall</b> → Uninstall the SQLite extension. |
| 227 |
</li><li><b>tclextension-verify</b> → 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> <i>dbcmd ?database-name? ?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> <b>eval</b> ?<b>-withoutnulls</b>? |
| 451 |
?<b>-asdict</b>? ? <i>sql</i> |
| 452 |
<i>var-name</i>? ?<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 [db1 eval {SELECT * FROM t1 ORDER BY a}]</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 |
parray values<br> |
| 495 |
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 |
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> <b>transaction</b> <i>?transaction-type?</i> |
| 665 |
<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> <b>cache size</b> <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> <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> <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> <b>copy</b> <i>conflict-algorithm</i> |
| 770 |
<i>table-name </i> <i>file-name </i> |
| 771 |
?<i>column-separator</i>? |
| 772 |
?<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 {[db exists {SELECT 1 FROM table1 WHERE user=$user}]} {<br> |
| 885 |
# Processing if $user exists<br> |
| 886 |
} else {<br> |
| 887 |
# 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> <b>function</b> |
| 919 |
<i>sql-name</i> |
| 920 |
<i>?options?</i> |
| 921 |
<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>[lindex ... 0]</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 [string compare [string tolower $a] [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> <b>preupdate hook</b> <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> <b>preupdate count</b><br> |
| 1227 |
<i>dbcmd</i> <b>preupdate depth</b><br> |
| 1228 |
<i>dbcmd</i> <b>preupdate new</b> <i>INDEX</i><br> |
| 1229 |
<i>dbcmd</i> <b>preupdate old</b> <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> <b>incrblob</b> <b>?-readonly?</b> |
| 1283 |
<i>?DB? TABLE COLUMN 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> <b>trace_v2</b> ?<i>callback</i>? ?<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> <b>backup</b> ?<i>source-database</i>? <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> <b>restore</b> ?<i>target-database</i>? <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> <b>serialize</b> ?<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> <b>deserialize</b> ?<i>database</i>? <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> <b>profile</b> ?<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> <b>format</b> ?<i>options</i>? <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 |
|