After committing transaction, using db directly raises invalid transaction handle (expecting explicit transaction start) error

[ctengiz]

This is the same error with #23. I think with recent commits it is popped out again with current master code. Please see the attached example.

package main

import (
    "bufio"
    "crypto/rand"
    "encoding/hex"
    "fmt"
    "log"
    "os"
    "path/filepath"

    "database/sql"

    _ "github.com/nakagami/firebirdsql"
)

func readLine(input string) string {
    reader := bufio.NewReader(os.Stdin)
    fmt.Print(input)
    text, _ := reader.ReadString('\n')
    return text
}

func tempFileName(prefix string) string {
    randBytes := make([]byte, 16)
    rand.Read(randBytes)
    return filepath.Join(os.TempDir(), prefix+hex.EncodeToString(randBytes)+".fdb")
}

func handleError(err error) {
    if err != nil {
        log.Fatal(err)
    }
}

func beginTx(conn *sql.DB) *sql.Tx {
    tx, err := conn.Begin()
    handleError(err)
    return tx
}

func newConnection(createDB bool, tempPath string) *sql.DB {
    var err error
    var conn *sql.DB
    var sqlTxt string

    connectionString := "sysdba:masterkey@localhost:3050" + tempPath

    if createDB {
        conn, err = sql.Open("firebirdsql_createdb", connectionString)
        handleError(err)

        sqlTxt = `
        create table t1 (
            id bigint generated by default as identity primary key,
            testval integer
        )
        `
        _, err = conn.Exec(sqlTxt)
        handleError(err)

        fmt.Println("db created")
    } else {
        conn, err = sql.Open("firebirdsql", connectionString)
        handleError(err)
    }

    return conn
}

func main() {
    var tx *sql.Tx
    var err error
    var conn *sql.DB
    var sqlTxt string

    var testVal int
    var testID int64

    tempPath := tempFileName("test_autocommit")

    conn = newConnection(true, tempPath)

    // Insert values to table without transaction
    sqlTxt = "insert into t1(testval) values (1)"
    _, err = conn.Exec(sqlTxt)

    // Insert values to table with transaction
    tx = beginTx(conn)
    sqlTxt = "insert into t1(testval) values (2)"
    _, err = tx.Exec(sqlTxt)
    handleError(err)
    err = tx.Commit()
    handleError(err)

    fmt.Println("Values inserted")

    //Values are inserted, we have committed we should be able to use DB directly without explicit transaction start
    //Instead we get invalid transaction handle (expecting explicit transaction start) error
    //if we use conn.Begin() just here the error disappear
    rows, err := conn.Query("select * from t1 order by id")
    if err != nil {
        handleError(err)
    }
    defer rows.Close()

    for rows.Next() {
        err := rows.Scan(&testID, &testVal)
        handleError(err)
        fmt.Println("(id, testval): ", testID, ",", testVal)

    }
}

[jerasa]

I can confirm that the bug still exists. Is there any chance of getting fixes for the problem? Thx

[saaremaa]

I think this is not a mistake, but a feature of the Firebird database. If the request is placed in a transaction, everything works as expected.

package main

import (
    "bufio"
    "crypto/rand"
    "encoding/hex"
    "fmt"
    "log"
    "os"
    "path/filepath"

    "database/sql"

    _ "github.com/nakagami/firebirdsql"
)

func readLine(input string) string {
    reader := bufio.NewReader(os.Stdin)
    fmt.Print(input)
    text, _ := reader.ReadString('\n')
    return text
}

func tempFileName(prefix string) string {
    randBytes := make([]byte, 16)
    rand.Read(randBytes)
    return filepath.Join(os.TempDir(), prefix+hex.EncodeToString(randBytes)+".fdb")
}

func handleError(err error) {
    if err != nil {
        log.Fatal(err)
    }
}

func beginTx(conn *sql.DB) *sql.Tx {
    tx, err := conn.Begin()
    handleError(err)
    return tx
}

func newConnection(createDB bool, tempPath string) *sql.DB {
    var err error
    var conn *sql.DB
    var sqlTxt string

    connectionString := "sysdba:masterkey@localhost:3050" + tempPath

    if createDB {
        conn, err = sql.Open("firebirdsql_createdb", connectionString)
        handleError(err)

        sqlTxt = `
        create table t1 (
            id bigint generated by default as identity primary key,
            testval integer
        )
        `
        _, err = conn.Exec(sqlTxt)
        handleError(err)

        fmt.Println("db created")
    } else {
        conn, err = sql.Open("firebirdsql", connectionString)
        handleError(err)
    }

    return conn
}

func main() {
    var tx *sql.Tx
    var err error
    var conn *sql.DB
    var sqlTxt string

    var testVal int
    var testID int64

    tempPath := tempFileName("test_autocommit")

    conn = newConnection(true, tempPath)

    // Insert values to table without transaction
    sqlTxt = "insert into t1(testval) values (1)"
    _, err = conn.Exec(sqlTxt)

    // Insert values to table with transaction
    tx = beginTx(conn)
    sqlTxt = "insert into t1(testval) values (2)"
    _, err = tx.Exec(sqlTxt)
    handleError(err)
    err = tx.Commit()
    handleError(err)

    fmt.Println("Values inserted")

    tx = beginTx(conn)
    rows, err := tx.Query("select * from t1 order by id")
    if err != nil {
        handleError(err)
    }
    err = tx.Commit()
    handleError(err)
    defer rows.Close()

    for rows.Next() {
        err := rows.Scan(&testID, &testVal)
        handleError(err)
        fmt.Println("(id, testval): ", testID, ",", testVal)

    }
}