diff options
Diffstat (limited to 'www/tclsqlite.html')
| -rw-r--r-- | www/tclsqlite.html | 1310 |
1 files changed, 1310 insertions, 0 deletions
diff --git a/www/tclsqlite.html b/www/tclsqlite.html new file mode 100644 index 0000000..83db3a0 --- /dev/null +++ b/www/tclsqlite.html @@ -0,0 +1,1310 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>The Tcl interface to the SQLite library</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> + + + +<h1 align=center>The Tcl interface to the SQLite library</h1> + +<p>The SQLite library is designed to be very easy to use from +a <a href="http://www.tcl-lang.org">Tcl or Tcl/Tk</a> script. SQLite +began as a <a href="http://www.tcl-lang.org/doc/tea/">Tcl extension</a> +and the primary <a href="testing.html">test suite</a> for SQLite is written in TCL. SQLite +can be used with any programming language, but its connections to +TCL run deep.</p> + +<p>This document gives an overview of the Tcl +programming interface for SQLite.</p> + +<h3>The API</h3> + +<p>The interface to the SQLite library consists of single +tcl command named <b>sqlite3</b> +Because there is only this +one command, the interface is not placed in a separate +namespace.</p> + +<p>The <b>sqlite3</b> command is mostly used as follows +to open or create a database:</p> + +<blockquote> +<b>sqlite3</b> <i>dbcmd ?database-name? ?options?</i> +</blockquote> + +<p>To get information only, the <b>sqlite3</b> command may be given exactly +one argument, either "-version", "-sourceid" or "-has-codec", which will +return the specified datum with no other effect. +</p> + +<p>With other arguments, the <b>sqlite3</b> command opens the database +named in the second non-option argument, or named "" if there is no such. +If the open succeeds, a new Tcl command named by the first argument is created +and "" is returned. +(This approach is similar to the way widgets are created in Tk.) +If the open fails, an error is raised without creating a Tcl command +and an error message string is returned. +</p> +<p>If the database does not already exist, the default behavior +is for it to be created automatically (though this can be changed by +using the "<b>-create <i>false</i></b>" option). +</p> + +<p> +The name of the database is usually just the name of a disk file in which +the database is stored. If the name of the database is +the special name "<a href="inmemorydb.html">:memory:</a>", then a new database is created +in memory. If the name of the database is an empty string, then +the database is created in an empty file that is automatically deleted +when the database connection closes. <a href="uri.html">URI filenames</a> can be used if +the "<b>-uri yes</b>" option is supplied on the <b>sqlite3</b> command. +</p> + +<p> +Options understood by the <b>sqlite3</b> command include: +</p> + +<blockquote> +<dl> + +<dt><b>-create</b> <i>BOOLEAN</i></dt> +<dd><p> +If true, then a new database is created if one does not already exist. +If false, then an attempt to open a database file that does not previously +exist raises an error. The default behavior is "true". +</p></dd> + +<dt><b>-nomutex</b> <i>BOOLEAN</i></dt> +<dd><p> +If true, then all mutexes for the database connection are disabled. +This provides a small performance boost in single-threaded applications. +</p></dd> + +<dt><b>-readonly</b> <i>BOOLEAN</i></dt> +<dd><p> +If true, then open the database file read-only. If false, then the +database is opened for both reading and writing if filesystem permissions +allow, or for reading only if filesystem write permission is denied +by the operating system. The default setting is "false". Note that +if the previous process to have the database did not exit cleanly +and left behind a <a href="fileformat2.html#hotjrnl">hot journal</a>, then the write permission is required +to recover the database after opening, and the database cannot be +opened read-only. +</p></dd> + +<dt><b>-uri</b> <i>BOOLEAN</i></dt> +<dd><p> +If true, then interpret the filename argument as a <a href="uri.html">URI filename</a>. If +false, then the argument is a literal filename. The default value is +"false". +</p></dd> + +<dt><b>-vfs</b> <i>VFSNAME</i></dt> +<dd><p> +Use an alternative <a href="vfs.html">VFS</a> named by the argument. +</p></dd> + +<dt><b>-fullmutex</b> <i>BOOLEAN</i></dt> +<dd><p> +If true, multiple threads can safely attempt to use the database. +If false, such attempts are unsafe. The default value depends upon +how the extension is built. +</p></dd> + +<dt><b>-nofollow</b> <i>BOOLEAN</i></dt> +<dd><p> +If true, and the database name refers to a symbolic link, +it will not be followed to open the true database file. +If false, symbolic links will be followed. +The default is "false". +</p></dd> + +</dl> +</blockquote> + + + +<p> +Once an SQLite database is open, it can be controlled using +methods of the <i>dbcmd</i>. +There are currently 40 methods +defined.</p> + + +<table border=0 cellpadding=10 width='100%'> +<tr><td valign='top'><ul> +<li><a href="#authorizer">authorizer</a></li> +<li><a href="#backup">backup</a></li> +<li><a href="#bind_fallback">bind_fallback</a></li> +<li><a href="#busy">busy</a></li> +<li><a href="#cache">cache</a></li> +<li><a href="#changes">changes</a></li> +<li><a href="#close">close</a></li> +<li><a href="#collate">collate</a></li> +<li><a href="#collation_needed">collation_needed</a></li> +<li><a href="#commit_hook">commit_hook</a></li> +<li><a href="#complete">complete</a></li> +<li><a href="#config">config</a></li> +<li><a href="#copy">copy</a></li> +<li><a href="#deserialize">deserialize</a></li> +</ul></td> +<td valign='top'><ul> +<li><a href="#enable_load_extension">enable_load_extension</a></li> +<li><a href="#errorcode">errorcode</a></li> +<li><a href="#eval">eval</a></li> +<li><a href="#exists">exists</a></li> +<li><a href="#function">function</a></li> +<li><a href="#incrblob">incrblob</a></li> +<li><a href="#interrupt">interrupt</a></li> +<li><a href="#last_insert_rowid">last_insert_rowid</a></li> +<li><a href="#nullvalue">nullvalue</a></li> +<li><a href="#onecolumn">onecolumn</a></li> +<li><a href="#preupdate">preupdate</a></li> +<li><a href="#profile">profile</a></li> +<li><a href="#progress">progress</a></li> +<li><a href="#restore">restore</a></li> +</ul></td> +<td valign='top'><ul> +<li><a href="#rollback_hook">rollback_hook</a></li> +<li><a href="#serialize">serialize</a></li> +<li><a href="#status">status</a></li> +<li><a href="#timeout">timeout</a></li> +<li><a href="#total_changes">total_changes</a></li> +<li><a href="#trace">trace</a></li> +<li><a href="#trace_v2">trace_v2</a></li> +<li><a href="#transaction">transaction</a></li> +<li><a href="#unlock_notify">unlock_notify</a></li> +<li><a href="#update_hook">update_hook</a></li> +<li><a href="#version">version</a></li> +<li><a href="#wal_hook">wal_hook</a></li> + +</ul></td></tr> +</table> + + +<p>The use of each of these methods will be explained in the sequel, though +not in the order shown above.</p> + +<a name="eval"></a> +<h3>The "eval" method</h3> + +<p> +The most useful <i>dbcmd</i> method is "eval". The eval method is used +to execute SQL on the database. The syntax of the eval method looks +like this:</p> + +<blockquote> +<i>dbcmd</i> <b>eval</b> ?<b>-withoutnulls</b>? <i>sql</i> + ?<i>array-name</i>? ?<i>script</i>? +</blockquote> + +<p> +The job of the eval method is to execute the SQL statement or statements +given in the second argument. For example, to create a new table in +a database, you can do this:</p> + +<blockquote> +<b>sqlite3 db1 ./testdb<br> +db1 eval {CREATE TABLE t1(a int, b text)}</b> +</blockquote> + +<p>The above code creates a new table named <b>t1</b> with columns +<b>a</b> and <b>b</b>. What could be simpler?</p> + +<p>Query results are returned as a list of column values. If a +query requests 2 columns and there are 3 rows matching the query, +then the returned list will contain 6 elements. For example:</p> + +<blockquote> +<b>db1 eval {INSERT INTO t1 VALUES(1,'hello')}<br> +db1 eval {INSERT INTO t1 VALUES(2,'goodbye')}<br> +db1 eval {INSERT INTO t1 VALUES(3,'howdy!')}<br> +set x [db1 eval {SELECT * FROM t1 ORDER BY a}]</b> +</blockquote> + +<p>The variable <b>$x</b> is set by the above code to</p> + +<blockquote> +<b>1 hello 2 goodbye 3 howdy!</b> +</blockquote> + +<p>You can also process the results of a query one row at a time +by specifying the name of an array variable and a script following +the SQL code. For each row of the query result, the values of all +columns will be inserted into the array variable and the script will +be executed. For instance:</p> + +<blockquote> +<b>db1 eval {SELECT * FROM t1 ORDER BY a} values {<br> + parray values<br> + puts ""<br> +}</b> +</blockquote> + +<p>This last code will give the following output:</p> + +<blockquote><b> +values(*) = a b<br> +values(a) = 1<br> +values(b) = hello<p> + +values(*) = a b<br> +values(a) = 2<br> +values(b) = goodbye<p> + +values(*) = a b<br> +values(a) = 3<br> +values(b) = howdy!</b> +</blockquote> + +<p> +For each column in a row of the result, the name of that column +is used as an index in to array and the value of the column is stored +in the corresponding array entry. (Caution: If two or more columns +in the result set of a query have the same name, then the last column +with that name will overwrite prior values and earlier columns with the +same name will be inaccessible.) The special array index * is +used to store a list of column names in the order that they appear. +</p> + +<p> +Normally, NULL SQL results are stored in the array using the +<a href="#nullvalue">nullvalue</a> setting. However, if +the <b>-withoutnulls</b> option is used, then NULL SQL values +cause the corresponding array element to be unset instead. +</p> + +<p> +If the array variable name is omitted or is the empty string, then the value of +each column is stored in a variable with the same name as the column +itself. For example: +</p> + +<blockquote> +<b>db1 eval {SELECT * FROM t1 ORDER BY a} {<br> + puts "a=$a b=$b"<br> +}</b> +</blockquote> + +<p> +From this we get the following output +</p> + +<blockquote><b> +a=1 b=hello<br> +a=2 b=goodbye<br> +a=3 b=howdy!</b> +</blockquote> + +<a name="varsubst"></a> + +<p> +Tcl variable names can appear in the SQL statement of the second argument +in any position where it is legal to put a string or number literal. The +value of the variable is substituted for the variable name. If the +variable does not exist a NULL values is used. For example: +</p> + +<blockquote><b> +db1 eval {INSERT INTO t1 VALUES(5,$bigstring)} +</b></blockquote> + +<p> +Note that it is not necessary to quote the $bigstring value. That happens +automatically. If $bigstring is a large string or binary object, this +technique is not only easier to write, it is also much more efficient +since it avoids making a copy of the content of $bigstring. +</p> + +<p> +If the $bigstring variable has both a string and a "bytearray" representation, +then TCL inserts the value as a string. If it has only a "bytearray" +representation, then the value is inserted as a BLOB. To force a +value to be inserted as a BLOB even if it also has a text representation, +use a "@" character to in place of the "$". Like this: +</p> + +<blockquote><b> +db1 eval {INSERT INTO t1 VALUES(5,@bigstring)} +</b></blockquote> + +<p> +If the variable does not have a bytearray representation, then "@" works +just like "$". Note that ":" works like "$" in all cases so the following +is another way to express the same statement: +</p> + +<blockquote><b> +db1 eval {INSERT INTO t1 VALUES(5,:bigstring)} +</b></blockquote> + +<p>The use of ":" instead of "$" before the name of a variable can +sometimes be useful if the SQL text is enclosed in double-quotes "..." +instead of curly-braces {...}. +When the SQL is contained within double-quotes "..." then TCL will do +the substitution of $-variables, which can lead to SQL injection if +extreme care is not used. But TCL will never substitute a :-variable +regardless of whether double-quotes "..." or curly-braces {...} are +used to enclose the SQL, so the use of :-variables adds an extra +measure of defense against SQL +injection. +</p> + +<a name="close"></a> +<h3>The "close" method</h3> + + +<p> +As its name suggests, the "close" method to an SQLite database just +closes the database. This has the side-effect of deleting the +<i>dbcmd</i> Tcl command. Here is an example of opening and then +immediately closing a database: +</p> + +<blockquote> +<b>sqlite3 db1 ./testdb<br> +db1 close</b> +</blockquote> + +<p> +If you delete the <i>dbcmd</i> directly, that has the same effect +as invoking the "close" method. So the following code is equivalent +to the previous:</p> + +<blockquote> +<b>sqlite3 db1 ./testdb<br> +rename db1 {}</b> +</blockquote> +<a name="transaction"></a> +<h3>The "transaction" method</h3> + + +<p> +The "transaction" method is used to execute a TCL script inside an SQLite +database transaction. The transaction is committed when the script completes, +or it rolls back if the script fails. If the transaction occurs within +another transaction (even one that is started manually using BEGIN) it +is a no-op. +</p> + +<p> +The transaction command can be used to group together several SQLite +commands in a safe way. You can always start transactions manually using +BEGIN, of +course. But if an error occurs so that the COMMIT or ROLLBACK are never +run, then the database will remain locked indefinitely. Also, BEGIN +does not nest, so you have to make sure no other transactions are active +before starting a new one. The "transaction" method takes care of +all of these details automatically. +</p> + +<p> +The syntax looks like this: +</p> + +<blockquote> +<i>dbcmd</i> <b>transaction</b> <i>?transaction-type?</i> + <i>script</i> +</blockquote> + + +<p> +The <i>transaction-type</i> can be one of <b>deferred</b>, +<b>exclusive</b> or <b>immediate</b>. The default is deferred. +</p> +<a name="cache"></a> +<h3>The "cache" method</h3> + + +<p> +The "eval" method described <a href="#eval">above</a> keeps a cache of +<a href="c3ref/prepare.html">prepared statements</a> +for recently evaluated SQL commands. +The "cache" method is used to control this cache. +The first form of this command is:</p> + +<blockquote> +<i>dbcmd</i> <b>cache size</b> <i>N</i> +</blockquote> + +<p>This sets the maximum number of statements that can be cached. +The upper limit is 100. The default is 10. If you set the cache size +to 0, no caching is done.</p> + +<p>The second form of the command is this:</p> + + +<blockquote> +<i>dbcmd</i> <b>cache flush</b> +</blockquote> + +<p>The cache-flush method +<a href="c3ref/finalize.html">finalizes</a> +all prepared statements currently +in the cache.</p> + +<a name="complete"></a> +<h3>The "complete" method</h3> + + +<p> +The "complete" method takes a string of supposed SQL as its only argument. +It returns TRUE if the string is a complete statement of SQL and FALSE if +there is more to be entered.</p> + +<p>The "complete" method is useful when building interactive applications +in order to know when the user has finished entering a line of SQL code. +This is really just an interface to the +<a href="c3ref/complete.html"><b>sqlite3_complete()</b></a> C +function. +<a name="config"></a> +<h3>The "config" method</h3> + + +<p> +The "config" method queries or changes certain configuration settings for +the database connection using the <a href="c3ref/db_config.html">sqlite3_db_config()</a> interface. +Run this method with no arguments to get a TCL list of available +configuration settings and their current values: + +<blockquote> +<i>dbcmd</i> <b>config</b> +</blockquote> + +<p>The above will return something like this: + +<blockquote> +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 +</blockquote> + +<p>Add the name of an individual configuration setting to query the current +value of that setting. Optionally add a boolean value to change a setting. + +<p> +The following four configuration changes are recommended for maximum +application security. Turning off the trust_schema setting prevents +virtual tables and dodgy SQL functions from being used inside of triggers, +views, CHECK constraints, generated columns, and expression indexes. +Turning off the dqs_dml and dqs_ddl settings prevents the use of +double-quoted strings. Turning on defensive prevents direct writes +to shadow tables. + +<blockquote><pre> +db config trusted_schema 0 +db config defensive 1 +db config dqs_dml 0 +db config dqs_ddl 0 +</pre></blockquote> +<a name="copy"></a> +<h3>The "copy" method</h3> + + +<p> +The "copy" method copies data from a file into a table. +It returns the number of rows processed successfully from the file. +The syntax of the copy method looks like this:</p> + +<blockquote> +<i>dbcmd</i> <b>copy</b> <i>conflict-algorithm</i> + <i>table-name </i> <i>file-name </i> + ?<i>column-separator</i>? + ?<i>null-indicator</i>? +</blockquote> + +<p>Conflict-algorithm must be one of the SQLite conflict algorithms for +the INSERT statement: <i>rollback</i>, <i>abort</i>, +<i>fail</i>,<i>ignore</i>, or <i>replace</i>. See the SQLite Language +section for <a href="lang_conflict.html">ON CONFLICT</a> for +more information. The conflict-algorithm must be specified in lower case. +</p> + +<p>Table-name must already exists as a table. File-name must exist, and +each row must contain the same number of columns as defined in the table. +If a line in the file contains more or less than the number of columns defined, +the copy method rollbacks any inserts, and returns an error.</p> + +<p>Column-separator is an optional column separator string. The default is +the ASCII tab character \t. </p> + +<p>Null-indicator is an optional string that indicates a column value is null. +The default is an empty string. Note that column-separator and +null-indicator are optional positional arguments; if null-indicator +is specified, a column-separator argument must be specified and +precede the null-indicator argument.</p> + +<p>The copy method implements similar functionality to the <b>.import</b> +SQLite shell command. +<a name="timeout"></a> +<h3>The "timeout" method</h3> + + +<p>The "timeout" method is used to control how long the SQLite library +will wait for locks to clear before giving up on a database transaction. +The default timeout is 0 millisecond. (In other words, the default behavior +is not to wait at all.)</p> + +<p>The SQLite database allows multiple simultaneous +readers or a single writer but not both. If any process is writing to +the database no other process is allows to read or write. If any process +is reading the database other processes are allowed to read but not write. +The entire database shared a single lock.</p> + +<p>When SQLite tries to open a database and finds that it is locked, it +can optionally delay for a short while and try to open the file again. +This process repeats until the query times out and SQLite returns a +failure. The timeout is adjustable. It is set to 0 by default so that +if the database is locked, the SQL statement fails immediately. But you +can use the "timeout" method to change the timeout value to a positive +number. For example:</p> + +<blockquote><b>db1 timeout 2000</b></blockquote> + +<p>The argument to the timeout method is the maximum number of milliseconds +to wait for the lock to clear. So in the example above, the maximum delay +would be 2 seconds.</p> +<a name="busy"></a> +<h3>The "busy" method</h3> + + +<p>The "busy" method, like "timeout", only comes into play when the +database is locked. But the "busy" method gives the programmer much more +control over what action to take. The "busy" method specifies a callback +Tcl procedure that is invoked whenever SQLite tries to open a locked +database. A single integer argument is appended to the callback before +it is invoke. The argument is the number of prior calls to the busy +callback for the current locking event. It is intended that +the callback will do some other useful work for a short while +(such as service GUI events) then return +so that the lock can be tried again. The callback procedure should +return "0" if it wants SQLite to try again to open the database and +should return "1" if it wants SQLite to abandon the current operation. +<p> +If the busy method is invoked without an argument, the name of the callback +procedure last set by the busy method is returned. If no callback procedure +has been set, an empty string is returned. +<a name="enable_load_extension"></a> +<h3>The "enable_load_extension" method</h3> + + +<p>The extension loading mechanism of SQLite (accessed using the +<a href="lang_corefunc.html#load_extension">load_extension()</a> SQL function) is turned off by default. This is +a security precaution. If an application wants to make use of the +<a href="lang_corefunc.html#load_extension">load_extension()</a> function it must first turn the capability on using +this method.</p> + +<p>This method takes a single boolean argument which will turn the +extension loading functionality on or off.</p> + +<p>For best security, do not use this method unless truly needed, +and run <a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a> or the +"<a href="#config">db config trusted_schema 0</a>" method <u>before</u> +invoking this method. + +<p>This method maps to the <a href="c3ref/enable_load_extension.html">sqlite3_enable_load_extension()</a> C/C++ +interface.</p> +<a name="exists"></a> +<h3>The "exists" method</h3> + + +<p>The "exists" method is similar to "onecolumn" and "eval" in that +it executes SQL statements. The difference is that the "exists" method +always returns a boolean value which is TRUE if a query in the SQL +statement it executes returns one or more rows and FALSE if the SQL +returns an empty set.</p> + +<p>The "exists" method is often used to test for the existence of +rows in a table. For example:</p> + +<blockquote><b> +if {[db exists {SELECT 1 FROM table1 WHERE user=$user}]} {<br> + # Processing if $user exists<br> +} else {<br> + # Processing if $user does not exist<br> +} +</b></blockquote> +<a name="last_insert_rowid"></a> +<h3>The "last_insert_rowid" method</h3> + + +<p>The "last_insert_rowid" method returns an integer which is the ROWID +of the most recently inserted database row.</p> +<a name="function"></a> +<h3>The "function" method</h3> + + +<p>The "function" method registers new SQL functions with the SQLite engine. +The arguments are the name of the new SQL function and a TCL command that +implements that function. Arguments to the function are appended to the +TCL command before it is invoked.</p> + +<p> +For security reasons, it is recommended that applications first set +<a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a> or run the +"<a href="#config">db config trusted_schema 0</a>" +method before using this method.</p> + +<p> +The syntax looks like this: +</p> + +<blockquote> +<i>dbcmd</i> <b>function</b> + <i>sql-name</i> + <i>?options?</i> + <i>script</i> +</blockquote> + +<p> +The following example creates a new SQL function named "hex" that converts +its numeric argument in to a hexadecimal encoded string: +</p> + +<blockquote><b> +db function hex {format 0x%X} +</b></blockquote> + +<p>The "function" method accepts the following options: +<blockquote> +<dl> + +<dt><b>-argcount</b> <i>INTEGER</i></dt> +<dd><p> +Specify the number of arguments that the SQL function accepts. The default +value of -1 means any number of arguments. + +<dt><b>-deterministic</b> +<dd><p> +This option indicates that the function will always return the same +answer given the same argument values. The SQLite query optimizer +uses this information to cache answers from function calls with +constant inputs and reuse the result rather than invoke the function +repeatedly. +</dd> + +<dt><b>-directonly</b> +<dd><p> +This option restricts the function to only be usable by direct +top-level SQL statement. The function will not be accessible to +triggers, views, CHECK constraints, generated columns, or index +expressions. This option is recommended for all application-defined +SQL functions, and is <u>highly recommended</u> for any SQL +function that has side effects or that reveals internal state of +the application. + +<p style="background-color:#ffff90;"><b>Security Warning:</b> +Without this switch, an attacker might be able to change the +schema of a database file to include the new function inside a trigger +or view or CHECK constraint and thereby trick the application into +running the function with parameters of the attacker's choosing. +Hence, if the new function has side effects or reveals internal +state about the application and the -directonly option is not +used, that is a potential security vulnerability.</p> +</dd> + +<dt><b>-innocuous</b> +<dd><p> +This option indicates that the function has no side effects and +does not leak any information that cannot be computed directly +from its input parameters. When this option is specified, the +function may be used in triggers, views, CHECK constraints, +generated columns, and/or index expressions even if +<a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a>. The use of this option is discouraged +unless it is truly needed. +</dd> + +<dt><b>-returntype integer|real|text|blob|any</b> +<dd><p> +This option is used to configure the type of the result returned by the +function. If this option is set to "any" (the default), SQLite attempts to +determine the type of each value returned by the function implementation based +on the Tcl value's internal type. Or, if it is set to "text" or "blob", the +returned value is always a text or blob value, respectively. If this option is +set to "integer", SQLite attempts to coerce the value returned by the function +to an integer. If this is not possible without data loss, it attempts to coerce +it to a real value, and finally falls back to text. If this option is set to +"real", an attempt is made to return a real value, falling back to text if this +is not possible. +</dd> +</dl> + + + +</blockquote> +<a name="nullvalue"></a> +<h3>The "nullvalue" method</h3> + + +<p> +The "nullvalue" method changes the representation for NULL returned +as result of the "eval" method.</p> + +<blockquote><b> +db1 nullvalue NULL +</b></blockquote> + +<p>The "nullvalue" method is useful to differ between NULL and empty +column values as Tcl lacks a NULL representation. The default +representation for NULL values is an empty string.</p> +<a name="onecolumn"></a> +<h3>The "onecolumn" method</h3> + + +<p>The "onecolumn" method works like +"<a href="#eval">eval</a>" in that it evaluates the +SQL query statement given as its argument. The difference is that +"onecolumn" returns a single element which is the first column of the +first row of the query result.</p> + +<p>This is a convenience method. It saves the user from having to +do a "<tt>[lindex ... 0]</tt>" on the results of an "eval" +in order to extract a single column result.</p> +<a name="changes"></a> +<h3>The "changes" method</h3> + + +<p>The "changes" method returns an integer which is the number of rows +in the database that were inserted, deleted, and/or modified by the most +recent "eval" method.</p> +<a name="total_changes"></a> +<h3>The "total_changes" method</h3> + + +<p>The "total_changes" method returns an integer which is the number of rows +in the database that were inserted, deleted, and/or modified since the +current database connection was first opened.</p> +<a name="authorizer"></a> +<h3>The "authorizer" method</h3> + + +<p>The "authorizer" method provides access to the +<a href="c3ref/set_authorizer.html">sqlite3_set_authorizer</a> +C/C++ interface. The argument to authorizer is the name of a procedure that +is called when SQL statements are being compiled in order to authorize +certain operations. The callback procedure takes 5 arguments which describe +the operation being coded. If the callback returns the text string +"SQLITE_OK", then the operation is allowed. If it returns "SQLITE_IGNORE", +then the operation is silently disabled. If the return is "SQLITE_DENY" +then the compilation fails with an error. +</p> + +<p>If the argument is an empty string then the authorizer is disabled. +If the argument is omitted, then the current authorizer is returned.</p> +<a name="bind_fallback"></a> +<h3>The "bind_fallback" method</h3> + + +<p>The "bind_fallback" method gives the application control over how to +handle parameter binding when no TCL variable matches the parameter name. + +<p>When the <a href="#eval">eval method</a> sees a named SQL +parameter such as "$abc" or ":def" or "@ghi" in an SQL statement, it tries +to look up a TCL variable with the same name, and it binds the value +of that TCL variable to the SQL parameter. If no such TCL variable exists, +the default behavior is to bind an SQL NULL value to the parameter. However, +if a bind_fallback proc is specified, then that proc is invoked with the +name of the SQL parameter and the return value from the proc is bound to +the SQL parameter. Or if the proc returns an error, then the SQL statement +aborts with that error. If the proc returns with some code other than +TCL_OK or TCL_ERROR, then the SQL parameter is bound to NULL, as it would +be by default. + +<p>The "bind_fallback" method has a single optional argument. If the argument +is an empty string, then the bind_fallback is cancelled and the default behavior +is restored. If the argument is a non-empty string, then the argument is a +TCL command (usually the name of a proc) to invoke whenever an SQL parameter +is seen that does not match any TCL variable. If the "bind_fallback" method +is given no arguments, then the current bind_fallback command is returned. + +<p>As an example, the following setup causes TCL to throw an error if +an SQL statement contains an parameter that does not match any global +TCL variable: + +<blockquote><pre><b> +proc bind_error {nm} { + error "no such variable: $nm" +} +db bind_fallback bind_error +</b></pre></blockquote> +<a name="progress"></a> +<h3>The "progress" method</h3> + + +<p>This method registers a callback that is invoked periodically during +query processing. There are two arguments: the number of SQLite virtual +machine opcodes between invocations, and the TCL command to invoke. +Setting the progress callback to an empty string disables it.</p> + +<p>The progress callback can be used to display the status of a lengthy +query or to process GUI events during a lengthy query.</p> +<a name="collate"></a> +<h3>The "collate" method</h3> + + +<p>This method registers new text collating sequences. There are +two arguments: the name of the collating sequence and the name of a +TCL procedure that implements a comparison function for the collating +sequence. +</p> + +<p>For example, the following code implements a collating sequence called +"NOCASE" that sorts in text order without regard to case: +</p> + +<blockquote><pre><b> +proc nocase_compare {a b} { + return [string compare [string tolower $a] [string tolower $b]] +} +db collate NOCASE nocase_compare +</b></pre></blockquote> +<a name="collation_needed"></a> +<h3>The "collation_needed" method</h3> + + +<p>This method registers a callback routine that is invoked when the SQLite +engine needs a particular collating sequence but does not have that +collating sequence registered. The callback can register the collating +sequence. The callback is invoked with a single parameter which is the +name of the needed collating sequence.</p> +<a name="commit_hook"></a> +<h3>The "commit_hook" method</h3> + + +<p>This method registers a callback routine that is invoked just before +SQLite tries to commit changes to a database. If the callback throws +an exception or returns a non-zero result, then the transaction rolls back +rather than commit.</p> +<a name="rollback_hook"></a> +<h3>The "rollback_hook" method</h3> + + +<p>This method registers a callback routine that is invoked just before +SQLite tries to do a rollback. The script argument is run without change.</p> +<a name="status"></a> +<h3>The "status" method</h3> + +<p>This method returns status information from the most recently evaluated +SQL statement. The status method takes a single argument which should be +either "steps" or "sorts". If the argument is "steps", then the method +returns the number of full table scan steps that the previous SQL statement +evaluated. If the argument is "sorts", the method returns the number of +sort operations. This information can be used to detect queries that are +not using indices to speed search or sorting.</p> + +<p>The status method is basically a wrapper on the +<a href="c3ref/stmt_status.html">sqlite3_stmt_status()</a> C-language interface.</p> +<a name="update_hook"></a> +<h3>The "update_hook" method</h3> + + +<p>This method registers a callback routine that is invoked just after +each row is modified by an UPDATE, INSERT, or DELETE statement. Four +arguments are appended to the callback before it is invoked:</p> + +<ul> +<li>The keyword "INSERT", "UPDATE", or "DELETE", as appropriate</li> +<li>The name of the database which is being changed</li> +<li>The table that is being changed</li> +<li>The rowid of the row in the table being changed</li> +</ul> + +<p>The callback must not do anything that will modify the database connection +that invoked the update hook such as running queries.</p> +<a name="preupdate"></a> +<h3>The "preupdate" method</h3> + + +<p>This method either registers a callback routine that is invoked just +before each row is modified by an UPDATE, INSERT, or DELETE statement, +or may perform certain operations related to the impending update.</p> + +<p>To register or remove a preupdate callback, use this syntax: +<blockquote> +<i>dbcmd</i> <b>preupdate hook</b> <i>?SCRIPT?</i> +</blockquote> +When a preupdate callback is registered, then prior to each row modification, +the callback is run with these arguments: +<ul> +<li>The keyword "INSERT", "UPDATE", or "DELETE", as appropriate</li> +<li>The name of the database which is being changed</li> +<li>The table that is being changed</li> +<li>The original rowid of the row in the table being changed</li> +<li>The new rowid (if any) of the row in the table being changed</li> +</ul> +The callback must not do anything that will modify the database connection +that invoked the preupdate hook such as running queries.</p> + +<p>When the callback is executing, and only then, these preupdate operations +may be performed by use of the indicated syntax: +<blockquote> +<i>dbcmd</i> <b>preupdate count</b><br> +<i>dbcmd</i> <b>preupdate depth</b><br> +<i>dbcmd</i> <b>preupdate new</b> <i>INDEX</i><br> +<i>dbcmd</i> <b>preupdate old</b> <i>INDEX</i><br> +</blockquote> +</p> + +<p>The <b>count</b> submethod returns the number of columns in the row that is +being inserted, updated, or deleted.</p> +<p>The <b>depth</b> submethod returns the update nesting depth. This will be +0 for a direct insert, update, or delete operation; +1 for inserts, updates, or deletes invoked by top-level triggers; +or higher values for changes resulting from trigger-invoked triggers.</p> + +<p>The <b>old</b> and <b>new</b> submethods return the selected original +or changed column value respectively of the row being updated.</p> + +<p>Note that the Tcl interface (and underlying SQLite library) must have +been built with the preprocessor symbol SQLITE_ENABLE_PREUPDATE_HOOK +defined for the <b>preupdate</b> method to be available.</p> +<a name="wal_hook"></a> +<h3>The "wal_hook" method</h3> + + +<p>This method registers a callback routine that is invoked after transaction +commit when the database is in <a href="wal.html">WAL mode</a>. Two arguments are appended to the +callback command before it is invoked:</p> + +<ul> +<li>The name of the database on which the transaction was committed +<li>The number of entries in the write-ahead log (WAL) file for that database +</ul> + +<p>This method might decide to run a <a href="wal.html#ckpt">checkpoint</a> either itself or as a +subsequent idle callback. Note that SQLite only allows a single WAL hook. +By default this single WAL hook is used for the auto-checkpointing. If you +set up an explicit WAL hook, then that one WAL hook must ensure that checkpoints +are occurring since the auto-checkpointing mechanism will be disabled.</p> + +<p>This method should return an integer value that is equivalent to an +SQLite error code (usually 0 for SQLITE_OK in the case of success or 1 for +SQLITE_ERROR if some error occurs). As in <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a>, the results of +returning an integer that does not correspond to an SQLite error code are +undefined. If the value returned by the script cannot be interpreted as an +integer value, or if the script throws a Tcl exception, no error is returned to +SQLite but a Tcl background-error is raised. +<a name="incrblob"></a> +<h3>The "incrblob" method</h3> + + +<p>This method opens a TCL channel that can be used to read or write +into a preexisting BLOB in the database. The syntax is like this:</p> + +<blockquote> +<i>dbcmd</i> <b>incrblob</b> <b>?-readonly?</b> + <i>?DB? TABLE COLUMN ROWID</i> +</blockquote> + +<p> +The command returns a new TCL channel for reading or writing to the BLOB. +The channel is opened using the underlying +<a href="c3ref/blob_open.html">sqlite3_blob_open()</a> C-language +interface. Close the channel using the <b>close</b> command of TCL. +</p> +<a name="errorcode"></a> +<h3>The "errorcode" method</h3> + + +<p>This method returns the numeric error code that resulted from the most +recent SQLite operation.</p> +<a name="trace"></a> +<h3>The "trace" method</h3> + + +<p>The "trace" method registers a callback that is invoked as each SQL +statement is compiled. The text of the SQL is appended as a single string +to the command before it is invoked. This can be used (for example) to +keep a log of all SQL operations that an application performs. +</p> +<a name="trace_v2"></a> +<h3>The "trace_v2" method</h3> + + +<p>The "trace_v2" method registers a callback that is invoked as each SQL +statement is compiled. The syntax is as follows: + + +<blockquote> +<i>dbcmd</i> <b>trace_v2</b> ?<i>callback</i>? ?<i>mask</i>? +</blockquote> + +<p>This command causes the "callback" script to be invoked whenever +certain conditions occurs. The conditions are determined by the <i>mask</i> +argument, which should be a TCL-list of zero or more of the following +keywords: + +<ul> +<li> <b>statement</b> +<li> <b>profile</b> +<li> <b>row</b> +<li> <b>close</b> +</ul> + +<p>Traces for <b>statement</b> invoke the callback with two arguments +whenever a new SQL statement is run. +The first argument is an integer which is the value of the pointer +to the underlying <a href="c3ref/stmt.html">sqlite3_stmt</a> object. This integer can be used +to correlate SQL statement text with the result of a <b>profile</b> +or <b>row</b> callback. The second argument is the +unexpanded text of the SQL statement being run. By "unexpanded", we +mean that variable substitutions in the text are not expanded into the +variable values. This is different from the behavior of the "trace" +method which does expand variable substitutions. + +<p>Traces for <b>profile</b> invoke the callback with two arguments +as each SQL statement finishes. The first argument is an integer which +is the value of the underlying <a href="c3ref/stmt.html">sqlite3_stmt</a> object. The second +argument is the approximate run-time for the statement in nanoseconds. +The run-time is the best estimate available depending on the capabilities +of the platform on which the application is running. + +<p>Traces for <b>row</b> invoke the callback with a single argument +whenever a new result row is available from an SQL statement. +The argument is an integer which is the value of the underlying +<a href="c3ref/stmt.html">sqlite3_stmt</a> object pointer. + +<p>Traces for <b>close</b> invoke the callback with a single argument +as the database connection is closing. The argument is an integer which +is the value of a pointer to the underlying <a href="c3ref/sqlite3.html">sqlite3</a> object that is +closing. + +<p>There can only be a single trace callback registered on a database +connection. Each use of "trace" or "trace_v2" cancels all prior +trace callback. +<a name="backup"></a> +<h3>The "backup" method</h3> + + +<p>The "backup" method makes a backup copy of a live database. The +command syntax is like this:</p> + +<blockquote> +<i>dbcmd</i> <b>backup</b> ?<i>source-database</i>? <i>backup-filename</i> +</blockquote> + +<p>The optional <i>source-database</i> argument tells which database in +the current connection should be backed up. The default value is <b>main</b> +(or, in other words, the primary database file). To back up TEMP tables +use <b>temp</b>. To backup an auxiliary database added to the connection +using the <a href="lang_attach.html">ATTACH</a> command, use the name of that database as it was assigned +in the <a href="lang_attach.html">ATTACH</a> command.</p> + +<p>The <i>backup-filename</i> is the name of a file into which the backup is +written. <i>Backup-filename</i> does not have to exist ahead of time, but if +it does, it must be a well-formed SQLite database.</p> +<a name="restore"></a> +<h3>The "restore" method</h3> + + +<p>The "restore" method copies the content from a separate database file +into the current database connection, overwriting any preexisting content. +The command syntax is like this:</p> + +<blockquote> +<i>dbcmd</i> <b>restore</b> ?<i>target-database</i>? <i>source-filename</i> +</blockquote> + +<p>The optional <i>target-database</i> argument tells which database in +the current connection should be overwritten with new content. +The default value is <b>main</b> +(or, in other words, the primary database file). To repopulate the TEMP tables +use <b>temp</b>. To overwrite an auxiliary database added to the connection +using the <a href="lang_attach.html">ATTACH</a> command, use the name of that database as it was assigned +in the <a href="lang_attach.html">ATTACH</a> command.</p> + +<p>The <i>source-filename</i> is the name of an existing well-formed SQLite +database file from which the content is extracted.</p> +<a name="serialize"></a> +<h3>The "serialize" method</h3> + + +<p>The "serialize" method creates a BLOB which is a complete copy of an +underlying database. The syntax is like this: + +<blockquote> +<i>dbcmd</i> <b>serialize</b> ?<i>database</i>? +</blockquote> + +<p>The optional argument is the name of the schema or database to be serialized. +The default value is "main". + +<p>This routine returns a TCL byte-array that is the complete content of +the identified database. This byte-array can be written into a file and +then used as an ordinary SQLite database, or it can be sent over a TCP/IP +connection to some other application, or passed to the "deserialize" method +of another database connection. + +<p>This method only functions if SQLite is compiled with -DSQLITE_ENABLE_DESERIALIZE +<a name="deserialize"></a> +<h3>The "deserialize" method</h3> + +<p>The "deserialize" method takes a TCL byte-array that contains an SQLite +database file and adds it to the database connection. The syntax is: + +<blockquote> +<i>dbcmd</i> <b>deserialize</b> ?<i>database</i>? <i>value</i> +</blockquote> + +<p>The option <i>database</i> argument identifies which attached database +should receive the deserialization. The default is "main". + +<p>This command causes SQLite to disconnect from the previous database and +reattach to an in-memory database with the content in <i>value</i>. If <i>value</i> +is not a byte-array containing a well-defined SQLite database, then subsequent +commands will likely return <a href="rescode.html#corrupt">SQLITE_CORRUPT</a> errors. + +<p>This method only functions if SQLite is compiled with -DSQLITE_ENABLE_DESERIALIZE +<a name="interrupt"></a> +<h3>The "interrupt" method</h3> + +<p>The "interrupt" method invokes the <a href="c3ref/interrupt.html">sqlite3_interrupt()</a> interface, causing +any pending queries to halt. +<a name="version"></a> +<h3>The "version" method</h3> + + Return the current library version. For example, "3.23.0". +<a name="profile"></a> +<h3>The "profile" method</h3> + +<p>This method is used to profile the execution of SQL statements run by + the application. The syntax is as follows: +</p> + +<blockquote> +<i>dbcmd</i> <b>profile</b> ?<i>script</i>? +</blockquote> + +<p>Unless <i>script</i> is an empty string, this method arranges for the +<i>script</i> to be evaluated after the execution of each SQL statement. +Two arguments are appended to <i>script</i> before it is invoked: the +text of the SQL statement executed and the time elapsed while executing +the statement, in nanoseconds. +</p> +<p>A database handle may only have a single profile script registered at +any time. If there is already a script registered when the profile method +is invoked, the previous profile script is replaced by the new one. If the +<i>script</i> argument is an empty string, any previously registered +profile callback is canceled but no new profile script is registered. +</p> +<a name="unlock_notify"></a> +<h3>The "unlock_notify" method</h3> + +<p>The unlock_notify method is used access the <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> + interface to the SQLite core library for testing purposes. The use of + this method by applications is discouraged. +</p> + + + |