SQLite Forum

concrete example of what I mean :

package require sqlite3
sqlite3 db1 :memory:

db1 eval {CREATE TABLE t1(a TEXT NOT NULL);
          INSERT INTO t1 VALUES('first')}

# 1) Use of SQL keywords BEGIN + ROLLBACK + "errorcode" method

  catch {
     db1 eval {BEGIN}
     db1 eval {UPDATE t1 SET a = NULL}
  }
  puts [db1 errorcode]     ;# returns 19 (constraint failed)
  db1 eval {ROLLBACK}
  puts [db1 errorcode]     ;# returns 0
      
# 2) Use of SQL keywords BEGIN + ROLLBACK + "rollback_hook" method (callback) + "errorcode" method

  # callback intended to be invoked *just before* SQLite tries to do a rollback
  db1 rollback_hook {
     puts [db1 errorcode]     ;# returns 19 (constraint failed)
  }

  catch {
     db1 eval {BEGIN}
     db1 eval {UPDATE t1 SET a = NULL}
  }
  db1 eval {ROLLBACK}

# 3) Use of "transaction" method + "errorcode" method

  catch {
     db1 transaction {
        db1 eval {UPDATE t1 SET a = NULL}
     }
  }
  puts [db1 errorcode]     ;# returns 0 (instead of 19 (constraint failed))

# 4) Use of "transaction" method + "rollback_hook" method (callback) + "errorcode" method

  # callback intended to be invoked *just before* SQLite tries to do a rollback
  db1 rollback_hook {
     puts [db1 errorcode]     ;# returns 0 (instead of 19 (constraint failed))
  }

  catch {
     db1 transaction {
        db1 eval {UPDATE t1 SET a = NULL}
     }
  }


There is another problem, in a succession of transactions of SQL statements, the first call to rollback_hook doesn't behave correctly, however the folowing calls seem to do "the right thing" :

sqlite3 db1 :memory:

db1 rollback_hook {
   puts [db1 errorcode]
}

db1 eval {CREATE TABLE t1(a TEXT NOT NULL);
          INSERT INTO t1 VALUES('first')}

catch {
   db1 eval {BEGIN}
   db1 eval {UPDATE t1 SET a = NULL}
}
db1 eval "ROLLBACK"     ;# first call to rollback_hook -> returns 0 here

catch {
   db1 eval {BEGIN}
   db1 eval {UPDATE t1 SET a = NULL}
}
db1 eval "ROLLBACK"     ;# second call to rollback_hook -> returns 19 here