Insert text as []uint8 with MSACCESS

[flibustenet]

When we read a text with interface{}, the type is []uint8 But when I try to insert this text it send incorrect chars (something impossible to read). I don't know if it's related to this driver (it works with postgresql). I can cast to a string but maybe it's a bug ?

str := "hello"
fmt.Println([]uint8(str))
_, err = db.Exec("insert into xxx (txt) values (?)", []uint8(str))
rs, err := db.Query("select txt from xxx")
rs.Next()
var txt []uint8
rs.Scan(&txt)
fmt.Println(txt)

It return

[104 101 108 108 111]
[230 149 168 230 177 172 111]

I use interface{} because I try to read and write tables where I don't know the type of the fields (for replication).

My code look like this

rows, err := db.Query("select * from tva order by code")
colNames, err := rows.Columns()
cols := make([]interface{}, len(colNames))
colPtrs := make([]interface{}, len(colNames))
for i := 0; i < len(colNames); i++ {
    colPtrs[i] = &cols[i]
}
for rows.Next() {
    rows.Scan(colPtrs...)
cs := make([]string, 0, len(colNames))
css := make([]string, 0, len(colNames))
for _, c := range colNames {
    cs = append(cs, fmt.Sprintf("[%s]", c))
    css = append(css, "?")
}
hcs := strings.Join(cs, ",")
hcss := strings.Join(css, ",")
hsql := fmt.Sprintf("insert into tvabck (%s) values (%s)", hcs, hcss)
db.Exec(hsql, cols...)

[alexbrainman]

Use Go string type to read text fields from the database. Does that work?

Alex

[flibustenet]

When I use Go string or if I cast []uint8 to string it works. But it should work with interface{} as input and output isn'it ?

[alexbrainman]

But it should work with interface{} as input and output isn'it ?

I am not sure it will. ODBC interface expects you to provide type of your input / output. If you do not provide the type (by passing interface{}). How is github.com/alexbrainman/odbc package going to know what xxx type is in db.Query("select txt from xxx")? github.com/alexbrainman/odbc uses SQLDescribeCol API to discover that, but maybe MS ACCESS driver returns something unexpected. You should try and debug this yourself - just insert fmt.Printf everywhere interesting. It also could be that Parameter.BindValue does not pass value correctly - you could read txt (from "select txt from xxx") using another program to see what the value is. Again, you can debug what is happening in Parameter.BindValue.

Alex

[flibustenet]

When i read a string with interface{} the TypeOf is []uint that i can use as string i have no problem with that. My question is for insert.

I looked at Parameter.BindValue. If i understand, a parameter of type string is first encoded in UTF16, but a parameter of type []uint8 or []byte is send without encoding (so in UTF8). Then []byte("txt") and "txt" cannot be the same.

So, it's normal and it's not a bug, right ?

[alexbrainman]

If i understand, a parameter of type string is first encoded in UTF16,

You understand correctly. We use SQLBindParameter ODBC API, and that API requires us to specify type of our data. So we decide what type to pass to SQLBindParameter by looking at type of v parameter of Parameter.BindValue. Also if v is a string, then it would be encoded in utf8, but SQLBindParameter does not accept utf8, so we have to convert it into utf16.

but a parameter of type []uint8 or []byte is send without encoding (so in UTF8)

Where does it say that all []byte values are encoded in utf8? []byte type is used to store binary data. For example, you might decide to store picture in some column of your database. Do not use []byte to store strings.

Alex

[flibustenet]

That's sound right, i close the issue.