go-sql-driver and bit fields

[mattf...@gmail.com]

Hi, 

I'm pretty new to programming in go and I'm trying to use go-sql-driver to query a bit field.  I always get the error:

  sql: Scan error on column index 0: sql/driver: couldn't convert "\x01" into type bool

Any ideas?  If I query from a field of type int(11) instead, it seems to work just fine.  

Thanks,

Matt

package main

import (

"database/sql"

"fmt"

_ "github.com/go-sql-driver/mysql"

)

func main() {

db, _ := sql.Open("mysql", "testuser:testuser@tcp(localhost:3306)/test")

bitTester, _ := db.Prepare("select bitValue from test_table where id = ?")

var bitValue bool

err := bitTester.QueryRow(1).Scan(&bitValue)

if err != nil {

panic(err.Error())

}

fmt.Println("Test 1 bitValue", bitValue)

}

[Arne Hormann]

Hi Matt, can you please post your CREATE statement?

[mattf...@gmail.com]

Thank you for your response.  The create statement is:

CREATE TABLE `TEST_TABLE` (

  `id` int(11) DEFAULT NULL,

  `bitValue` bit(1) DEFAULT NULL,

  `intValue` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

and the data I populated with was:

INSERT INTO TEST_TABLE VALUES (0, 0, 0);

INSERT INTO TEST_TABLE VALUES (1, 1, 1);

Two other observations:

1. When I change "select bitValue" to "select intValue" in my program, I get the answer I expect

2. If I change the type of the bitValue variable from bool to int, it is still broken but with a different error.

I'm grateful for any help you can provide.

Matt

[mattf...@gmail.com]

I think I have more information about what's causing the problem.  In sql/driver/convert.go at line 73, we convert a byte array to a bool using:

b, err := strconv.ParseBool(string(s))

It seems like that is really dependent on what's in the "s" byte array though.  Ascii character values stored as bytes (for example 't', 'f', 'true', 'false') could be converted, but true bit values are not handled well.  I downloaded the go source wrote some tests and changed the select case for []byte to:

switch {

case len(s) == 1 && s[0] == 1:

return true, nil

case len(s) == 1 && s[0] == 0:

return false, nil

default:

b, err := strconv.ParseBool(string(s))

if err != nil {

return nil, fmt.Errorf("sql/driver: couldn't convert %q into type bool", s)

}

return b, nil

}

This appears to fix the problem for me.  What do you think of this approach?  Is there somewhere in the mysql driver where we should be doing this instead?

If you feel like this is the right way to do the fix, I'd be happy to file a defect in go and provide the patch.

Thanks,

Matt

[Arne Hormann]

Hi Matt,

bitsets are not among the types directly supported by database/sql (http://golang.org/pkg/database/sql/driver/#Value) and go-sql-driver/mysql closely follows database/sql/driver.

It contains some extensions - but none for custom types except for NullTime, which is missing in database/sql but still among the supported types.

Concerning your change, I don't think your change would be excepted as is. Maybe as a new Value implementation, but then you'd still need to add it to all possible conversions.

I think it would be best to read the value as either int or sql.RawBytes and use a custom conversion function in your own code.

Arne