COUNT Field incorrect with MS ACCESS integer

[flibustenet]

I experiment odbc with a MS ACCESS database (I know...). The driver is "Microsoft Access Driver (.mdb, .accdb)" On a table xxx with an integer field x db.Exec("insert into xxx (x) values (?)", 5) return COUNT Field incorrect I think it means that it doesn't find the number of parameters. If i replace 5 by float64(5) it works Same problem with bool (0 / -1)

I found other issues with MSACCESS that I will submit. Is it supposed to work with this database ? Does anybody use it ?

[alexbrainman]

Please, show complete program - something I can run myself. Tell me what it prints, and explain why you think programs output is wrong. This will help me understand what the problem is.

I do not have MS ACCESS myself, but I don't see why it shouldn't work. Maybe I will be able to suggest steps to debug your problem.

Thank you.

Alex

[flibustenet]

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/alexbrainman/odbc"
)

func main() {
    driver := "Microsoft Access Driver (*.mdb, *.accdb)"
    mdb := "f:/wingo/bd.mdb"
    st := fmt.Sprintf("DRIVER={%s};DBQ=%s;", driver, mdb)
    db, err := sql.Open("odbc", st)
    if err != nil {
        panic(err)
    }
    defer db.Close()
    db.Exec("drop table xxx")
    db.Exec("create table xxx (i integer)")
    _, err = db.Exec("insert into xxx (i) values (?)", 8.)
    if err != nil {
        panic(err)
    }
    fmt.Println("ok")
    _, err = db.Exec("insert into xxx (i) values (?)", 5)
    if err != nil {
        fmt.Println("not ok ", err)
    }

}

The result is in french

ok
not ok  SQLExecute: {07002} [Microsoft][Pilote ODBC Microsoft Access]Champ COUNT incorrect

In english : COUNT Field incorrect

Let me know how can i help to debug this.

[flibustenet]

The problem is simply that i cannot insert an integer. In param.BindValue on case int64 (line 81), if i do sqltype = api.SQL_TINYINT instead of BIGINT it works...

[alexbrainman]

I suspect something like what you have discovered. Perhaps your MS ACCESS does not support SQL_BIGINT. Unfortunately we cannot just use SQL_TINYINT here, because SQL_TINYINT is only 1byte large (see for example https://msdn.microsoft.com/en-us/library/system.data.odbc.odbctype(v=vs.110).aspx), but Go database/sql package passes int64 to us, so we must use SQL_BIGINT.

I am not sure what to suggest here. You could just make your own copy of this package and do what you like with it.

Another option is to pass SQL_INTEGER (I hope SQL_INTEGER works for you as well as SQL_TINYINT), if parameter is small enough, otherwise go for SQL_BIGINT.

Alex

[flibustenet]

This works for me:

        if d >= -2147483648 && d <= 2147483647 {
            sqltype = api.SQL_INTEGER
        } else {
            sqltype = api.SQL_BIGINT
        }

Or maybe better

        if d >= -2147483648 && d <= 2147483647 {
            sqltype = api.SQL_INTEGER
            size = 4
        } else {
            sqltype = api.SQL_BIGINT
            size = 8
        }

[alexbrainman]

@flibustenet please try fix_issue_78 branch to see if it fixes your problem. Thank you.

Alex

[flibustenet]

Yes, it works. I will continue to test all the others types. Thanks !