Could't convert <nil> into type ...?

Question

I tried to using database/sql for query database row into a Go type, my codes snippet following:

type User struct {
    user_id     int64
    user_name   string
    user_mobile string
    password    string
    email       interface{}
    nickname    string
    level       byte
    locked      bool
    create_time string
    comment     string  // convert <nil> to *string error 
}

func TestQueryUser(t *testing.T) {
    db := QueryUser(driverName, dataSourceName)

    stmtResults, err := db.Prepare(queryAll)
    defer stmtResults.Close()

    var r *User = new(User)

    arr := []interface{}{
        &r.user_id, &r.user_name, &r.user_mobile, &r.password, &r.email,
        &r.nickname, &r.level, &r.locked, &r.create_time, &r.comment,
    }

    err = stmtResults.QueryRow(username).Scan(arr...)

    if err != nil {
        t.Error(err.Error())
    }

    fmt.Println(r.email)

}

MySQL:

As your see, some fields that has NULL value, so I have to set interface{} type into User struct of Go, which convert NULL to nil.

--- FAIL: TestQueryUser (0.00s)
        user_test.go:48: sql: Scan error on column index 9: unsupported Scan, storing driver.Value type <nil> into type *string

Somebody has a better way？ or I must change the MySQL field and set its DEFAULT ' '

Might it help to identify User struct field as pointers to those values where NULL is possible? I mean: stype User struct{user_name *string} and so on..? — ArkadyB
– ArkadyB, Commented Apr 18, 2016 at 7:52

fzerorubigd · Accepted Answer · 2017-03-25 14:51:18Z

4

First the short answer : There is some types in sql package for example sql.NullString (for nullable string in your table and guess Nullint64 and NullBool and ... usage :) ) and you should use them in your struct.

The long one : There is two interface for this available in go , first is Scanner and the other is Valuer for any special type in database, (for example,I use this mostly with JSONB in postgres) you need to create a type, and implement this two(or one of them) interface on that type.

the scanner is used when you call Scan function. the data from the database driver, normally in []byte is the input and you are responsible for handling it. the other one, is used when the value is used as input in query. the result "normally" is a slice of byte (and an error) if you need to only read data, Scanner is enough, and vice-versa, if you need to write parameter in query the Valuer is enough

for an example of implementation, I recommend to see the types in sql package.

Also there is an example of a type to use with JSONB/JSON type in postgresql

// GenericJSONField is used to handle generic json data in postgres
type GenericJSONField map[string]interface{}


// Scan convert the json field into our type
func (v *GenericJSONField) Scan(src interface{}) error {
    var b []byte
    switch src.(type) {
    case []byte:
        b = src.([]byte)
    case string:
        b = []byte(src.(string))
    case nil:
        b = make([]byte, 0)
    default:
        return errors.New("unsupported type")
    }

    return json.Unmarshal(b, v)
}

// Value try to get the string slice representation in database
func (v GenericJSONField) Value() (driver.Value, error) {
    return json.Marshal(v)
}

driver value is often []byte but string and nil is acceptable. so this could handle null-able fields too.

edited Mar 25, 2017 at 14:51

answered Apr 18, 2016 at 10:31

fzerorubigd

1,8842 gold badges17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Avinash Over a year ago

Could you please demonstrate "The long one" with an example?

fzerorubigd Over a year ago

@Avinash add an example for postgresql JSONB/JSON

Petr Lozovitskii · Accepted Answer · 2025-02-28 14:03:21Z

0

Not a kind of solution, but a hack. You may 'ask' MySQL to give whatewer you want in case of NULL using IFNULL function. Here is an example:

SELECT IFNULL(email, '') FROM users;

It gives '' (empty string) instead of NULL. The same may be done for bool:

SELECT IFNULL(state, false) from providers;

It gives 'false' in case of NULL.

answered Feb 28 at 14:03

Petr Lozovitskii

2674 silver badges11 bronze badges

Collectives™ on Stack Overflow

Could't convert <nil> into type ...?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related