Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> I pulled the following from the bind.test file. It shows how
> sqlite_prepare is used to test the parameter binding, and how the
> sqlite_column_count and sqlite_column_name APIs are used.
>
Most of the interfaces in bind.test are special-purpose
testing hacks that are only available if you build
the "testfixture". They are not a part of the standard
TCL API.
Here is a quick review of the official TCL API:
You connect to the database by creating a database
object using the "sqlite3" command:
sqlite3 db test.db
In the example above, the name of the newly created
database object is "db" and the database file is "test.db".
You can substitute appropriate values here, of course.
All interaction is by invoking methods on the database
object. The most frequently used method is "eval".
Use it to evaluate SQL statements.
db eval {CREATE TABLE ex1(a,b,c)}
db eval {
INSERT INTO ex1 VALUES(1,2,3);
}
It is best to include the SQL inside {...}. You can
use TCL variable names inside the {...} and SQLite will
recognize them and do the appropriate bindings to
access them automatically. For example, to insert the
content of a file as a blob, you could do this:
set in [open somefile.txt]
set file_content [read $in]
close $in
db eval {
INSERT INTO ex1 VALUES(4,5,$file_content)
}
Notice that you do *not* have to quote $file_content
or escape any internal characters. TCL is using the
binding mechanism to make this happen.
Note also that TCL is using sqlite3_prepare() internally.
It keeps a cache of recently used statements and
automatically reuses them if they are invoked again.
Query results are returned as a list from the eval method.
Or, you can add a script after the SQL that is run once
for each row in the result set:
db eval {SELECT * FROM ex1} {
puts "a=$a b=$b c=$c"
}
Within the script that follows eval, the value of each
column is loaded into variables with the same name as the
column. So, for example, the value of column "a" is
loaded into a variable name "a". Warning: This creates
a problem if you have two or more columns with the same
name in your result set. You are advised to use unique
column names.
Here is a quick shorthand:
db eval {SELECT * FROM ex1} break
When the script is "break" the eval stops at the first
row. This leaves the contents of the columns of the
first row in variables named $a, $b, and $c. The
"continue" command in the eval script also works as
you would expect.
One more important feature is the transaction method.
db transaction {
# some script
db eval {... whatever ...}
# more script
}
The transaction method runs its content inside an SQLite
transaction. If the script fails, the transaction is
automatically rolled back. If the script succeeds the
transaction automatically commits. Transaction methods
can be nested. Using the transaction method is much,
much nicer than manually trying to do BEGIN and COMMIT.
Three things to take away from this overview:
(1) The TCL API adds new syntax to the TCL language
(the scripts eval and transactio methods). This
is an exceedingly powerful mechanism which cannot
be accomplished with Algol-derived languages such
as Perl, Python, Ruby, or Java. You have to look
outside of parsed languages to Lisp and Scheme
to find this kind of raw power.
(2) Everything is done at a very high level. No
calls to prepare() or step() or finalize() or
column_text(). Conversions and caching are all
handled transparently and automatically. You
do not have to think about them. This allows
you to devote more brain cycles to the problem
you are trying to solve.
(3) There is no support for cursors anywhere in any
of this. Sorry.
