Golang Mysql scan returns zeros, when data is there?

Question

Having a bit of a head scratcher moment. I've got the following struct:

type Room struct {
    ID              int
    Name            string
    RoomType        int
    CreatedAt       time.Time
    UpdatedAt       time.Time
    DeletedAt       time.Time
    GroupId         int
    BlockId         int
    ProjectId       int
    RoomLength      float64
    RoomWidth       float64
    CeilingHeight   float64
    CeilingColorHex string
    WallColorHex    string
    FloorColorHex   string
    CeilingColorRgb string
    WallColorRgb    string
    FloorColorRgb   string
}

Reading it out in rest api with:

database := db.New()
stmt, err := database.Prepare("SELECT * FROM room WHERE block_id = ?")
if err != nil {
    panic(err)
}
defer stmt.Close()

rows, err := stmt.Query(c.Param("id"))
if err != nil {
    panic(err)
}
defer rows.Close()

var rooms []common.Room
for rows.Next() {
    var r common.Room
    err := rows.Scan(&r.ID, &r.Name, &r.RoomType, &r.CreatedAt, &r.UpdatedAt, &r.DeletedAt,
        &r.GroupId, &r.BlockId, &r.ProjectId, &r.RoomLength, &r.RoomWidth, &r.CeilingHeight,
        &r.CeilingColorHex, &r.WallColorHex, &r.FloorColorHex, &r.CeilingColorRgb, &r.WallColorRgb,
        &r.FloorColorRgb)
    if err = rows.Err(); err != nil {
        panic(err)
    }

    fmt.Printf("Found: %v", r)
    rooms = append(rooms, r)
}

Yet, the resulting payload is:

{3 Loki #1 3 2018-09-25 08:42:38 +0000 UTC 2018-09-25 14:52:39 +0000 UTC 0001-01-01 00:00:00 +0000 UTC 0 0 0 0 0 0      }

I'm specifically after the length/width, which (above) are 0. Yet in the db:

mysql> select * from room where id = 3 \G
*************************** 1. row ***************************
               id: 3
             name: Loki #1
        room_type: 3
       created_at: 2018-09-25 08:42:38
       updated_at: 2018-09-25 14:52:39
       deleted_at: NULL
         group_id: 0
         block_id: 1
       project_id: 0
      room_length: 10
       room_width: 7
   ceiling_height: 4
ceiling_color_hex: #c0c0c0
   wall_color_hex: #a9a9a9
  floor_color_hex: #708090
ceiling_color_rgb: 192,192,192
   wall_color_rgb: 169,169,169
  floor_color_rgb: 112,128,144
1 row in set (0.00 sec)

I thought it may have something to do with differing types, but after changing them in the db, and then in code, no change. Can anyone explain why .Scan isn't picking up certain values?

Thanks!

Your statement has a condition on block_id while the query you did is on id, does it matter in your situation ? — xiaomi
– xiaomi, Commented Sep 25, 2018 at 13:52
Could you check the err from err := rows.Scan(...) ? This error occurs if the value cannot be stored without loss of information. — xiaomi
– xiaomi, Commented Sep 25, 2018 at 14:12
I panic() on err, directly underneath the call to Scan() and there currently isn't an error — NoGall
– NoGall, Commented Sep 25, 2018 at 14:20
@LokiSinclair You panic on err from rows.Err(), but you are failing to check the error from rows.Scan(...). — Gavin
– Gavin, Commented Sep 25, 2018 at 15:49
@LokiSinclair That is not the case. err is assigned the value from rows.Scan(...), but then you immediately re-assign the value from rows.Err() to it, which overwrites the error from Scan. — Gavin
– Gavin, Commented Sep 25, 2018 at 16:08

xiaomi · Accepted Answer · 2018-09-25 16:26:53Z

6

First of all, check the error which come from rows.Scan(). You only check the error from rows.Err() which is a different kind of error, and not related to the scan.

err := rows.Scan(&r.ID, &r.Name, &r.RoomType, &r.CreatedAt, &r.UpdatedAt, &r.DeletedAt,
    &r.GroupId, &r.BlockId, &r.ProjectId, &r.RoomLength, &r.RoomWidth, &r.CeilingHeight,
    &r.CeilingColorHex, &r.WallColorHex, &r.FloorColorHex, &r.CeilingColorRgb, &r.WallColorRgb,
    &r.FloorColorRgb)
if err != nil {
    panic(err) // Error related to the scan
}
if err = rows.Err(); err != nil {
    panic(err) // Error related to the iteration of rows
}

When the value from deleted_at is return as NULL, Scan will return an error such as unsupported Scan, storing driver.Value type <nil> into type *time.Time and the rest of your struct will get zero values.

It means that your room struct has to change to use a pointer to time.

CreatedAt       *time.Time
UpdatedAt       *time.Time
DeletedAt       *time.Time

However, you may need to add into you sql.Open() the parameter parseTime sql.Open("mysql", "user:password@/dbname?parseTime=true) to parse the time correctly for mysql.

You should then received a valid *time.Time when it is set, or nil when it is NULL.

edited Sep 25, 2018 at 16:26

answered Sep 25, 2018 at 16:00

xiaomi

6,7433 gold badges32 silver badges35 bronze badges

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

3 Comments

NoGall Over a year ago

Thank you, I'll give it a go tomorrow. I'm not worried about nulls as I've read all the horror stories and have configured db to always have defaults. Cheers.

xiaomi Over a year ago

@LokiSinclair if you deal with default such as zero value instead of null, you can keep time.Time

NoGall Over a year ago

You were correct, I was overwriting the consumed error. Once I'd separated them, I saw that, even though deleted_at was zero time, it still wouldn't parse without the field being a pointer. Perfect, thank you!

Collectives™ on Stack Overflow

Golang Mysql scan returns zeros, when data is there?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related