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

Reply via email to