Prepared statement are broken after sqlite error

[threez]

I created a prepared statement for an INSERT. If the insert failed, e.g. because a UNIQUE constraint was violated, the prepared statement is broken and needs to be recreated in order to work again.

I have an example project, here where you can find the code to recreate the statement. If i remove it, my tests fail. I created a simple version of the problem here:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    var db *sql.DB
    db, _ = sql.Open("sqlite3", ":memory:")
    db.Exec(`CREATE TABLE IF NOT EXISTS Users (
        uid INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        CONSTRAINT SingleKeys UNIQUE (name) ON CONFLICT ROLLBACK
    );`)
    stmt, _ := db.Prepare(`INSERT INTO Users (name) VALUES (?);`)
    _, e1 := stmt.Exec("test")
    fmt.Printf("Should NOT return error: %s\n", e1)
    _, e2 := stmt.Exec("test")
    fmt.Printf("Should return error: %s\n", e2)
    _, e3 := stmt.Exec("test1")
    fmt.Printf("Should NOT return error: %s\n", e3)
    stmt1, _ := db.Prepare(`INSERT INTO Users (name) VALUES (?);`)
    _, e4 := stmt1.Exec("test1")
    fmt.Printf("Should NOT return error: %s\n", e4)
}

[mattn]

Once you get error for the statement, you need to make it again.
It can't reset the statement by calling sqlite3_reset.

[threez]

but you can make this transparently right? Otherwise i have to do it all over the code. This would not be DRY.

[mattn]

but you can make this transparently right?

Hmm, this driver doesn't know whether the code need to make same statement again.

[threez]

I implemented a mechanism in my pull request. Please review.

[mattn]

_, e1 := stmt.Exec("test")
if e1 != nil {
  return e1
}

We may not want to repair statement. Sorry this is a spec of my driver.

[threez]

MySQL and ProstgreSQL don't behave like this. I think it breaks the go database/sql interface.

[mattn]

BTW, why e4 should be error? When e3 is nil, test1 is inserted. So e4 should not be nil, I think.

[mattn]

If that e4 is not nil is right behavior, this issue will be fixed by below's patch.

diff --git a/sqlite3.go b/sqlite3.go
index abfc2fe..c5c25ed 100644
--- a/sqlite3.go
+++ b/sqlite3.go
@@ -425,10 +425,12 @@ func (r *SQLiteResult) RowsAffected() (int64, error) {
 // Execute the statement with arguments. Return result object.
 func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error) {
    if err := s.bind(args); err != nil {
+       C.sqlite3_reset(s.s)
        return nil, err
    }
    rv := C.sqlite3_step(s.s)
    if rv != C.SQLITE_ROW && rv != C.SQLITE_OK && rv != C.SQLITE_DONE {
+       C.sqlite3_reset(s.s)
        return nil, s.c.lastError()
    }

[threez]

Correct, if e2 doesn't break the stmt, e3 will not be set and e4 will then be an error. If the diff you proposes fixes the problem I would appreciate the change. I could tell you then if it works like MySQL and PostgreSQL.

[antoine-lizee]

This got me stuck for a while. I updated because of this thread, and now it works fine. Thanks!