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