database/sql scan to int error (with mymysql and gomysql drivers)

[Patrick Crosby]

I've been experimenting with database/sql with two different drivers
(go-mysql-driver and mymysql). If I have a NULL value in an integer
column, I'm getting strconv errors when scanning into an &int32.

With go-mysql-driver, I get:

sql: Scan error on column index 6: converting string "" to a int32:
strconv.ParseInt: parsing "": invalid syntax

(the driver code says it returns nil or []byte for every column in
rows.Next, so I would think driver.Value would be nil, but it looks
like []byte instead).

With mymysql/godrv, I get:

sql: Scan error on column index 6: converting string "<nil>" to a
int32: strconv.ParseInt: parsing "<nil>": invalid syntax

With mymysql/godrv, I can scan using a database/sql.NullInt64 as the
destination.

In this case, I'd prefer a null column to be scanned as a zero value
for the destination. Is that possible? Is that up to the driver or
is there something in database/sql that I'm missing?

Thanks,

Patrick

--
http://www.stathat.com
Invent stats on the fly. Track data instantly, up to the minute, accurately.

[Andy Balholm]

On Wednesday, July 11, 2012 1:37:31 PM UTC-7, Patrick Crosby wrote:

In this case, I'd prefer a null column to be scanned as a zero value
for the destination.  Is that possible?  Is that up to the driver or
is there something in database/sql that I'm missing?

NULL and 0 are not the same thing. If you want NULL to be interpreted as 0, modify your query by using the COALESCE function in SQL: COALESCE(x, 0) returns the value of x, except that it returns 0 when x is NULL. 

Or you could scan into a NullInt64 variable like this:

var i int

var ni sql.NullInt64

r.Scan(&ni)

if ni.Valid {

    i = int(ni.Int64)

} else {

    i = 0

}

[Patrick Crosby]

On Wed, Jul 11, 2012 at 5:07 PM, Andy Balholm <andyb...@gmail.com> wrote:
> On Wednesday, July 11, 2012 1:37:31 PM UTC-7, Patrick Crosby wrote:
>>
>> In this case, I'd prefer a null column to be scanned as a zero value
>> for the destination. Is that possible? Is that up to the driver or
>> is there something in database/sql that I'm missing?
>
>
> NULL and 0 are not the same thing. If you want NULL to be interpreted as 0,
> modify your query by using the COALESCE function in SQL: COALESCE(x, 0)
> returns the value of x, except that it returns 0 when x is NULL.

Yes, I know. But here's my logic: in SQL (or at least mysql) if you
don't have a default value in the schema for a column and you don't
provide a value with insert, it inserts NULL for many column types.
In Go, any uninitialized variable gets the zero value for its type.
So it would make sense that an uninitialized SQL column would
correspond with an uninitialized Go variable.

I understand that in many situations, you want to differentiate
between 0 and NULL (and in those situations, you can scan in to
sql.NullInt64), but it would be nice if there was an option where
uninitialized columns were scanned to uninitialized variables.

Patrick

[Patrick Mylund Nielsen]

If you don't care if the field is NULL or 0, you can just use NullInt64 and ignore Valid, e.g.: 

var ni sql.NullInt64

r.Scan(&ni)

n := ni.Int64

> In Go, any uninitialized variable gets the zero value for its type.
> So it would make sense that an uninitialized SQL column would
> correspond with an uninitialized Go variable.

I think a better analogy is nil vs. non-nil pointers. NULL and 0 are distinct in SQL: NULL indicates something is missing and/or isn't applicable, whereas 0 indicates that something with a value of 0 exists, and is applicable.

If it doesn't make sense for the value of this field to be "missing", and (zero) values are applicable, then maybe what you really want to do is add a NOT NULL constraint to that column.

[Ben Ellis]

You can deal with null columns by making a type implement sql.Scanner and then you can get any behavior you'd like out of a null column.

The sql package itself uses this interface to deal with strings that may be null, integers that may be null, booleans that may be null, and floats that may be null.

http://golang.org/pkg/database/sql/#Scanner

http://golang.org/pkg/database/sql/#NullBool

http://golang.org/pkg/database/sql/#NullFloat64

http://golang.org/pkg/database/sql/#NullInt64

http://golang.org/pkg/database/sql/#NullString

On Wednesday, December 12, 2012 10:52:50 AM UTC-5, Gytha Ogg wrote:

Hi there,

Any suggestions if I want to scan a null time value (am able to scan non-null values of this column into time.Time or string fields). 

I get this - 

Error:  sql: Scan error on column index 0: unsupported driver -> Scan pair: <nil> -> *time.Time

Thanks!

[Saranya]

Thanks! Realised that my driver (pq for postgresql) actually supports a type called NullTime!

Best regards,

Saranya

--
 
 

[Julien Schmidt]

While working on another issue i noticed that there is a problem with returning nil on NULL values and wondered why nobody did complain about this (even the tests didn't fail on this), but well ... you did here... 
Those who are interested can track the issue here: http://code.google.com/p/go-mysql-driver/issues/detail?id=20

Also please don't hesitate to open a new issue or contact me by mail if you encounter any kind of problems with Go-MySQL-Driver.