insert more than 256 chars in type memo of MSACCESS

[flibustenet]

Hi,

On a table with a memo column i can insert text only if the len in under 256, just after it crash with a message from ODBC Microsoft (in french: Valeur de précision non valide).

I think after 256 the bind type should be changed but don't know how.

[alexbrainman]

@flibustenet please show small program that crashes with that message.

Thank you.

Alex

[flibustenet]

package main

import (
    "database/sql"
    "fmt"
    "os"

    _ "github.com/alexbrainman/odbc"
    ole "github.com/go-ole/go-ole"
    "github.com/go-ole/go-ole/oleutil"
)

func main() {
    createDB()
    conn := fmt.Sprintf("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=./db.mdb;")
    db, err := sql.Open("odbc", conn)
    if err != nil {
        panic(err)
    }
    err = db.Ping()
    if err != nil {
        panic(err)
    }
    _, err = db.Exec("create table mytable (m memo)")
    if err != nil {
        panic(err)
    }
    ch := "0123456789"
    _, err = db.Exec("insert into mytable (m) values (?)", ch)
    if err != nil {
        panic(err)
    }
    for {
        ch += "0123456789"
        fmt.Fprintf(os.Stderr, "len = %d\n", len(ch))
        _, err = db.Exec("insert into mytable (m) values (?)", ch)
        if err != nil {
            panic(err)
        }
        if len(ch) > 1000 {
            break
        }
    }
}

func createDB() {
    _, err := os.Stat("db.mdb")
    if !os.IsNotExist(err) {
        os.Remove("db.mdb")
    }
    ole.CoInitialize(0)
    unk, err := oleutil.CreateObject("adox.catalog")
    if err != nil {
        panic(err)
    }
    cat, err := unk.QueryInterface(ole.IID_IDispatch)
    if err != nil {
        panic(err)
    }
    _, err = oleutil.CallMethod(cat, "create", "provider=microsoft.jet.oledb.4.0;data source=db.mdb;")
    if err != nil {
        panic(err)
    }

}

len = 240
len = 250
len = 260
panic: SQLBindParameter: {HY104} [Microsoft][ODBC Microsoft Access Driver]Invalid precision value 

goroutine 1 [running]:
main.main()
    /home/wilk/go/src/testodbcmemo/t.go:38 +0x2fb

It's not a problem with the table, as a workaround I could update my row with parts of 255c (update mytable set m=m+?)

[alexbrainman]

Thank you for the sample code. I can reproduce your problem here.

It appears your code updating MEMO column type. And that fails after string gets so long.

github.com/alexbrainman/odbc maps Go string into different ODBC types - sometimes it is SQL_WCHAR, sometimes it is SQL_WVARCHAR and sometimes it is SQL_WLONGVARCHAR. You can see the code in param.go yourself. That code was designed based on my SQL Server experience.

But MS Acess requires

https://docs.microsoft.com/en-us/sql/odbc/microsoft/microsoft-access-data-types

SQL_WLONGVARCHAR for MEMO.

So, if I adjust the code as:

diff --git a/param.go b/param.go
index 4e9e43b..c21c870 100644
--- a/param.go
+++ b/param.go
@@ -64,16 +64,7 @@ func (p *Parameter) BindValue(h api.SQLHSTMT, idx int, v driver.Value) error {
        l *= 2 // every char takes 2 bytes
        buflen = api.SQLLEN(l)
        plen = p.StoreStrLen_or_IndPtr(buflen)
-       switch {
-       case size >= 4000:
-           sqltype = api.SQL_WLONGVARCHAR
-       case p.isDescribed:
-           sqltype = p.SQLType
-       case size <= 1:
-           sqltype = api.SQL_WVARCHAR
-       default:
-           sqltype = api.SQL_WCHAR
-       }
+       sqltype = api.SQL_WLONGVARCHAR
    case int64:
        if -0x80000000 < d && d < 0x7fffffff {
            // Some ODBC drivers do not support SQL_BIGINT.

your program completes successfully.

I am not sure how to adjust the code so it works for both SQL Server and SQL Acess yet. I will do it later - I do not have much free time now. But feel free to adjust your code while you wait.

Thank you.

Alex

[alexbrainman]

@flibustenet can you, please, try https://github.com/alexbrainman/odbc/commit/2d7d0e45c7870320611501683ca6073a0d9dfeed to see if it fixes your problem? Thank you.

Alex

[flibustenet]

I confirm your fix is ok. I thought maybe the driver could be written with spaces like DRIVER = { but it's not allowed, so you fix is very fine. I'll try to improve the access_test like i promised in #84 !

[alexbrainman]

I confirm your fix is ok.

Thank you for checking.

I thought maybe the driver could be written with spaces like DRIVER = { but it's not allowed, so you fix is very fine.

If you look at my fix again, you will see that it can handle spaces between words. It will also work, if driver description is written in small letters. I think it will be fine.

I'll try to improve the access_test like i promised in #84 !

I would not worry about it. I doubt there are other package users (except you), that use MS Access. :-)

Alex