Re: [sqlite] How to make correct transaction use only SQL?
Thank you =) This is good idea :) On Jan 30, 2008 2:44 PM, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > 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] > - > > -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to make correct transaction use only SQL?
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] -
[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] -
