Mysql: Returned values are always []byte

[vh]

The function below is to map db rows to json, but mysql driver always returns values as []byte

func getJSON(sqlString string) (string, error) {
  rows, err := db.Query(sqlString)
  if err != nil {
      return "", err
  }
  defer rows.Close()
  columns, err := rows.Columns()
  if err != nil {
      return "", err
  }
  count := len(columns)
  tableData := make([]map[string]interface{}, 0)
  values := make([]interface{}, count)
  valuePtrs := make([]interface{}, count)
  for rows.Next() {
      for i := 0; i < count; i++ {
          valuePtrs[i] = &values[i]
      }
      rows.Scan(valuePtrs...)
      entry := make(map[string]interface{})
      for i, col := range columns {
          var v interface{}
          val := values[i]
          b, ok := val.([]byte)
          if ok {
              v = string(b)
          } else {
              v = val
          }
          entry[col] = v
      }
      tableData = append(tableData, entry)
  }
  jsonData, err := json.Marshal(tableData)
  if err != nil {
      return "", err
  }
  fmt.Println(string(jsonData))
  return string(jsonData), nil 
}

[arnehormann]

What would you expect it to return? Does your code fail? If so, how?

[vh]

@arnehormann I expect that driver should return integer as int not slice of bytes. Code works fine, but here is the result:

b, ok := val.([]byte)
if ok {
  v = string(b)
} else {
  v = val
}

values are always represented as strings.

[arnehormann]

That's a specialty of MySQL: you have to use prepared statements to get the native types. MySQL has two protocols, one transmits everything as text, the other as the "real" type. And that binary protocol is only used when you use prepared statements. The driver is pretty much powerless to enforce a protocol and the text protocol takes less resources on the server.

This may help you:

stmt, err := db.Prepare(sqlString)
if err != nil { ...... }
defer stmt.Close()
rows, err := stmt.Query()

[julienschmidt]

Don't scan into interface{} but the type you would expect, the database/sql package converts the returned type for you then.

See also #366

[vh]

@arnehormann That works! Thanks a lot!

[buchenglei]

@arnehormann i tried your method, i can get 'int' type ,but i can't get 'float' type, what did i wrong?

[AmitM30]

and what about boolean types ? any suggestion ?

[f1mishutka]

Does not work yet :(

[callnothing]

@arnehormann i tried your method, i can get 'int' type ,but i can't get 'float' type, what did i wrong?

same question

[methane]

Very low level knowledge is needed to understand what type is used in binary protocol.
You should not expect you can always get type you expect by binary type.

The right approach is:

Don't scan into interface{} but the type you would expect, the database/sql package converts the returned type for you then.