📄 tclsqlite.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>The Tcl interface to the SQLite library</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <h2>The Tcl interface to the SQLite library</h2><p>The SQLite library is designed to be very easy to use froma Tcl or Tcl/Tk script. This document gives an overview of the Tclprogramming interface.</p><h3>The API</h3><p>The interface to the SQLite library consists of singletcl command named <b>sqlite3</b>Because there is only thisone command, the interface is not placed in a separatenamespace.</p><p>The <b>sqlite3</b> command is used as follows:</p><blockquote><b>sqlite3</b> <i>dbcmd database-name</i></blockquote><p>The <b>sqlite3</b> command opens the database named in the secondargument. If the database does not already exist, it isautomatically created.The <b>sqlite3</b> command also creates a new Tclcommand to control the database. The name of the new Tcl commandis given by the first argument. This approach is similar to theway widgets are created in Tk.</p><p>The name of the database is just the name of a disk file in whichthe database is stored. If the name of the database is an emptystring or the special name ":memory:" then a new database is createdin memory.</p><p>Once an SQLite database is open, it can be controlled using methods of the <i>dbcmd</i>. There are currently 22 methodsdefined.</p><p><ul><li><a href="#authorizer">authorizer</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="#copy">copy</a></li><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="#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="#profile">profile</a></li><li><a href="#progress">progress</a></li><li><a href="#rollback_hook">rollback_hook</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="#transaction">transaction</a></li><li><a href="#update_hook">update_hook</a></li><li><a href="#version">version</a></li></ul></p><p>The use of each of these methods will be explained in the sequel, thoughnot 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 usedto execute SQL on the database. The syntax of the eval method lookslike this:</p><blockquote><i>dbcmd</i> <b>eval</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 statementsgiven in the second argument. For example, to create a new table ina 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 aquery 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 timeby specifying the name of an array variable and a script followingthe SQL code. For each row of the query result, the values of allcolumns will be inserted into the array variable and the script willbe 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 columnis used as an index in to array. The value of the column is storedin the corresponding array entry. The special array index * isused to store a list of column names in the order that they appear.</p><p>If the array variable name is omitted or is the empty string, then the value ofeach column is stored in a variable with the same name as the columnitself. 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><p>Tcl variable names can appear in the SQL statement of the second argumentin any position where it is legal to put a string or number literal. Thevalue of the variable is substituted for the variable name. If thevariable 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 happensautomatically. If $bigstring is a large string or binary object, thistechnique is not only easier to write, it is also much more efficientsince 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 avalue to be inserted as a BLOB even if it also has a text representation,us 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 "@" worksjust like "$".</p><a name="close"></a><h3>The "close" method</h3><p>As its name suggests, the "close" method to an SQLite database justcloses the database. This has the side-effect of deleting the<i>dbcmd</i> Tcl command. Here is an example of opening and thenimmediately 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 effectas invoking the "close" method. So the following code is equivalentto 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 SQLitedatabase transaction. The transaction is committed when the script completes,or it rolls back if the script fails. If the transaction occurs withinanother transaction (even one that is started manually using BEGIN) itis a no-op.</p><p>The transaction command can be used to group together several SQLitecommands in a safe way. You can always start transactions manually usingBEGIN, ofcourse. But if an error occurs so that the COMMIT or ROLLBACK are neverrun, then the database will remain locked indefinitely. Also, BEGINdoes not nest, so you have to make sure no other transactions are activebefore starting a new one. The "transaction" method takes care ofall 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>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -