the tcl interface to the sqlite library4.htm

在嵌入式操作系统uclinux下应用的数据库sqlite

<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 <b>sqlite3_complete()</b> C
function.  Refer to the <a href="c_interface.html">C/C++ interface</a>
specification for additional information.</p>

<a name="copy">
<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>&nbsp;&nbsp;<b>copy</b>&nbsp;&nbsp;<i>conflict-algorithm</i>
&nbsp;&nbsp;<i>table-name&nbsp;</i>&nbsp;&nbsp;<i>file-name&nbsp;</i>
&nbsp;&nbsp;&nbsp;&nbsp;?<i>column-separator&nbsp;</i>?
&nbsp;&nbsp;?<i>null-indicator</i>?
</blockquote>

<p>Conflict-alogrithm 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.html#conflict">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 specifed and
precede the null-indicator argument.</p>

<p>The copy method implements similar functionality to the <b>.import</b>
SQLite shell command. 
The SQLite 2.x <a href="lang.html#copy"><b>COPY</b></a> statement 
(using the PostgreSQL COPY file format)
can be implemented with this method as:</p>

<blockquote>
dbcmd&nbsp;&nbsp;copy&nbsp;&nbsp;$conflictalgo
&nbsp;&nbsp;$tablename&nbsp;&nbsp;&nbsp;$filename&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;\t&nbsp;
&nbsp;&nbsp;\\N
</blockquote>


<a name="timeout">
<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">
<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.  This callback can do whatever is desired.  Presumably, 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.

<a name="last_insert_rowid">
<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">
<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>
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>


<a name="onecolumn">
<h3>The "onecolumn" method</h3>



<p>The "onecolumn" method works like "eval" 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&nbsp;...&nbsp;0]</tt>" on the results of an "eval"
in order to extract a single column result.</p>

<a name="changes">
<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">
<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">
<h3>The "authorizer" method</h3>



<p>The "authorizer" method provides access to the sqlite3_set_authorizer
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="progress">
<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">
<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><b>
proc nocase_compare {a b} {<br>
&nbsp;&nbsp;&nbsp;&nbsp;return [string compare [string tolower $a] [string tolower $b]]<br>
}<br>
db collate NOCASE nocase_compare<br>
</b></blockquote>

<a name="collation_needed">
<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">
<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="errorcode">
<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">
<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>


<table width="100%">
<tr><td bgcolor="#80a796"></td></tr>
</table>
<small><i>This page last modified on 2004/12/17 15:41:13</i></small>
</body></html>