• Monitoring SQLite error code

    From Jean-Baptiste Gardette@21:1/5 to All on Tue Nov 16 06:11:38 2021
    HI,

    Windows 7
    Tcl 8.6.11
    SQLite 3.35.5

    I want to monitor the SQLite error code when a transaction is rolled back after a SQL statement failesd. See the following script :

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

    Why in the cases 3) and 4) "errorcode" method returns 0 instead 19 ?

    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)