Go SQL driver get interface{} column values

Question

I am trying to use go sql driver to read from database tables and I am converting the values to []map[string]interface{}. The column name is the key of the map and the values are of interface{}. I am adding all the columns into an array. I am using the code sample for "RawBytes" at https://github.com/go-sql-driver/mysql/wiki/Examples as an example to start with.

However, in the example -all the column values are converted to string as follows,

// Fetch rows
for rows.Next() {
    // get RawBytes from data
    err = rows.Scan(scanArgs...)
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }

    // Now do something with the data.
    // Here we just print each column as a string.
    var value string
    for i, col := range values {
        // Here we can check if the value is nil (NULL value)
        if col == nil {
            value = "NULL"
        } else {
            value = string(col) //ATTN : converted to string here
        }
        fmt.Println(columns[i], ": ", value)
    }
    fmt.Println("-----------------------------------")
}

Is there a way to retain it as interface{} so I can do the necessary type casting while using the columns from []map[string]interface{}

It's better to use a struct if you know the structure of your database. — Pravin Mishra, Mar 17 '15 at 15:26
Have you looked into this (http://stackoverflow.com/questions/17840963/create-a-map-in-golang-from-database-rows) question? — Pravin Mishra, Mar 17 '15 at 15:28
@PravinMishra Yes the answer for the question(http://stackoverflow.com/questions/17840963/create-a-map-in-golang-from-database-rows) is what I am doing right now, but my interface{} is all strings because I do a string(col) which I want to avoid as it defies the type concepts. Using struct is a valid alternative, if I can't do anything I will go with the struct alternative — g0c00l.g33k, Mar 18 '15 at 01:58

score 3 · Accepted Answer · edited May 23 '17 at 12:06

See this https://stackoverflow.com/questions/20271123/go-lang-sql-in-parameters answer which my answer is based on. Using that you can do something like this:

var myMap = make(map[string]interface{})
rows, err := db.Query("SELECT * FROM myTable")
defer rows.Close()
if err != nil {
    log.Fatal(err)
}
colNames, err := rows.Columns()
if err != nil {
    log.Fatal(err)
}
cols := make([]interface{}, len(colNames))
colPtrs := make([]interface{}, len(colNames))
for i := 0; i < len(colNames); i++ {
    colPtrs[i] = &cols[i]
}
for rows.Next() {
    err = rows.Scan(colPtrs...)
    if err != nil {
        log.Fatal(err)
    }
    for i, col := range cols {
        myMap[colNames[i]] = col
    }
    // Do something with the map
    for key, val := range myMap {
        fmt.Println("Key:", key, "Value Type:", reflect.TypeOf(val))
    }
}

Using the reflect package you can then get the Type for each column as needed as demonstrated with the loop at the end.

This is generic and will work with any table, number of columns etc.

score 0 · Answer 2 · answered Aug 10 '20 at 12:19

AFter a long struggle i found out the solution. Check belowfunction that converts sql.RawBytes to Int64. This can be easily altered to fit any data type

func GetInt64ColumnValue(payload sql.RawBytes) (int64, error) {

content := reflect.ValueOf(payload).Interface().(sql.RawBytes) // convert to bytes
data := string(content) //convert to string  
i, err := strconv.ParseInt(data,10,64)  // convert to int or your preferred data type

if err != nil {

    log.Printf("got error converting %s to int error %s ",data,err.Error())
    return 0, err
}

return i, nil

}

Go SQL driver get interface{} column values

2 Answers2

Linked