SQLite

#
# Run this Tcl script to generate the tclsqlite.html file.
#
set rcsid {$Id: tclsqlite.tcl,v 1.15 2005/09/13 07:00:06 drh Exp $}
source common.tcl
header {The Tcl interface to the SQLite library}
proc METHOD {name text} {
  puts "<a name=\"$name\">\n<h3>The \"$name\" method</h3>\n"
  puts $text
}
puts {

command to control the database.  The name of the new Tcl command
is given by the first argument.  This approach is similar to the
way widgets are created in Tk.
</p>

<p>
The name of the database is just the name of a disk file in which
the database is stored.  If the name of the database is an empty
string or the special name ":memory:" then a new database is created
in memory.
</p>

<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 21 methods
defined:</p>

<p>The <b>sqlite3</b> also accepts an optional third argument called
the "mode".  This argument is a legacy from SQLite version 2 and is
currently ignored.</p>

<p>
<ul>
}
foreach m [lsort {
 authorizer
 busy
 cache
 changes
 close
 collate
 collation_needed
 commit_hook
 complete
 copy

</p>

<p>The use of each of these methods will be explained in the sequel, though
not in the order shown above.</p>

}



























##############################################################################
METHOD eval {
<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>

Note that it is not necessary to quote the $bigblob value.  That happens
automatically.  If $bigblob 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 $bigblob.
</p>

}

##############################################################################
METHOD close {

<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>
}

##############################################################################
METHOD transaction {

<p>
The "transaction" method is used to execute a TCL script inside an SQLite
database transaction.  The transaction is committed when the script completes,

<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>
}

##############################################################################
METHOD cache {

<p>
The "eval" method described <a href="#eval">above</a> keeps a cache of
<a href="capi3ref.html#sqlite3_prepare">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>&nbsp;&nbsp;<b>cache size</b>&nbsp;&nbsp;<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>&nbsp;&nbsp;<b>cache flush</b>
</blockquote>

<p>The cache-flush method 
<a href="capi3ref.html#sqlite3_finalize">finalizes</a>
all prepared statements currently
in the cache.</p>

}

##############################################################################
METHOD complete {

<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="capi3ref.html#sqlite3_complete"><b>sqlite3_complete()</b></a> C
function.

}

##############################################################################
METHOD copy {

<p>
The "copy" method copies data from a file into a table.

}



##############################################################################
METHOD onecolumn {

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

in the database that were inserted, deleted, and/or modified since the
current database connection was first opened.</p>
}

##############################################################################
METHOD authorizer {

<p>The "authorizer" method provides access to the 
<a href="capi3ref.html#sqlite3_set_authorizer">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.