SQLite Forum

Tcl interface : pb with "rollback_hook" method
Login

Tcl interface : pb with "rollback_hook" method

(1) By anonymous on 2021-11-09 14:30:06 [link] [source]

SQLite 3.35.5
Tcl 8.6.11

I want to monitor the error code when a transaction is rolled back after a SQL statement fails.

sqlite3 db1 filedb

proc callBack {} {
  puts [db1 errorcode]
}

db1 rollback_hook callBack

db1 transaction {
   db1 eval "UPDATE table SET ..."   -> statement fails
}


The "errorcode" method (executed in callBack proc) returns always 0 (SQLITE_OK)
instead of the error code of the statement.

I don't understand what is hapenning, I need some help please.

Thanks

(2) By anonymous on 2021-11-16 14:19:38 in reply to 1 [source]

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

(3) By Keith Medcalf (kmedcalf) on 2021-11-16 17:27:03 in reply to 2 [link] [source]

Can you point to where in the documentation is contained the statement that the errorcode on the connection has any validity at all during hook processing?

Or are you making assumptions that the procedure you are using will give you the results you desire (which it apparently does not).

(4) By anonymous on 2021-11-17 08:27:36 in reply to 3 [link] [source]

Thank for your reply. Sorry to be late in my response (its 9.00 AM here in Europe).
I am very happy with SQLite and its Tcl interface, wich works great and the doc is consise and very clear.
However the problem I enconter here is strange to me.
The asumptions, I am aware of, are :

1) the script is processed in the order as the written text (here I think this assumption is true)

2) the callback invoked by SQLite + Tcl is processed after the result code of the last SQL statement is stored somewhere internaly by SQLite (I con't verify this)

3) the "rollback_hook" method works as written in the doc

I can't point where in the doc is contained that the error code has any validity at all during hook processing.
My sole source of information is the doc of Tcl interface wich says :
"error code" method : "This method returns the numeric error code that resulted from the most recent SQLite operation."
"rollback_hook" method : "This method registers a callback routine that is invoked just before SQLite tries to do a rollback. The script argument is run without change."

I am awere that the problem may be in Tcl interpreter behaviour and I have posted the same report on Tcl mailing list.