Instead of piping the sql into sqlite3.exe, use the ".read" command instead.
C:\Temp\s>sqlite3 test.dat SQLite version 3.4.2 Enter ".help" for instructions sqlite> .read test.sql SQL error near line 10: column id is not unique SQL error near line 12: cannot commit - no transaction is active sqlite> select * from t1; sqlite> .read aborts on first error, whereas piping doesn't know to do that (and presumably there's no way it could know). HTH, Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: Alexander Batyrshin [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 29, 2008 9:37 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to make correct transaction use only SQL? For example i have this tabe: CREATE TABLE t1 ( id int unique ON CONFLICT ROLLBACK, val char ); And I have to execute this sql file: BEGIN TRANSACTION; INSERT INTO t1 (id, val) VALUES(1, 'val1'); INSERT INTO t1 (id, val) VALUES(2, 'val2'); INSERT INTO t1 (id, val) VALUES(3, 'val3'); INSERT INTO t1 (id, val) VALUES(3, 'val4'); -- CONFLICT INSERT INTO t1 (id, val) VALUES(4, 'val5'); COMMIT; If we execute this sql file, only INSERT before CONFLICT case will be rollback-ed, but last one still will be executed and remains in database... cat test.sql | sqlite3 test.db SQL error near line 11: column id is not unique SQL error near line 13: cannot commit - no transaction is active $ sqlite3 test.db SQLite version 3.4.0 sqlite> select * from t1; 4|val5 I what that on conflict _whole_ transaction will ROLLBACK and state of database will be exactly like at moment of execution "BEGIN TRANSACTION". How it is possible using only SQL? ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
