On Tue, 2005-07-05 at 08:52 -0500, Ray Mosley wrote:
> AS a DB rookie, I have replaced the .txt files in an Tcl/Tk application with
> a SQLite database, so it still reads very much like file I/O. While in a
> loop I wrote several records to my files, so now I simply do an INSERT.
> I keep reading that you optimize performance by using transactions, so how
> do I do that?
> Do I simply create a string that is my SQL statements, and how do I commit
> the changes - never having done that in the past? My code kind of looks
> like:
>
> set tran_string "BEGIN TRANSACTION\n"
> foreach ...
> append tran_string "<my-insert-statement>\n"
> append tran_string "END TRANSACTION\nCOMMIT TRANSACTION"
> DB eval "$tran_string"
>
You'll need a semicolon after each SQL statement, at least.
set tran_string "BEGIN TRANSACTION;\n"
foreach ...
append tran_string "<my-insert-statement>;\n"
append tran_string "END TRANSACTION;\nCOMMIT TRANSACTION;"
DB eval $tran_string
END TRANSACTION and COMMIT TRANSACTION mean exactly the same
thing. You only need to do that once. It works better to
do them using separate "eval"s. Like this:
set tran_string {}
foreach ...
append tran_string "<my-insert-statement>;\n"
DB eval BEGIN
DB eval $tran_string
DB eval COMMIT
Note also that the "TRANSACTION" keyword is just noise and
can be omitted.
One final hint: in Tcl it is more effecient to put $var inside
your SQL statement and let SQLite worry about escapes rather than
doing so yourself. Like this:
DB eval BEGIN
DB eval {INSERT INTO table1 VALUES($a,$b,$c,$d)}
DB eval COMMIT
--
D. Richard Hipp <[EMAIL PROTECTED]>
