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.

[slaiyer]

The same code works with trino-go-client. Not sure how they make it possible to send type info across. OP's code snippet is great for parsing dynamic queries where the structure of the results is not known ahead of time.

[naughtyGitCat]

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.

same workaround, but sqlite3 driver just works fine

• go version go1.22.2 darwin/arm64
• sqlite3
• mysql 8.0
• go-sql-driver v1.8.1
• go-sqlite3 v1.14.22

table t1 (id int, name string/varchar)

-- sqlite3
create table t1 (id int, name string);
-- mysql
create table t1 (id int, name varchar(18));

-- test data
insert into t1 values (1,'x');
insert into t1 values (2,'y');

result:

go-sqlite3	go-sql-driver
[[1,"x"],[2,"y"]]	[[1,"eA=="],[2,"eQ=="]]

```go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	_ "github.com/mattn/go-sqlite3"
	"slices"
)


func main() {
	db, err := sql.Open("mysql", "root:******@tcp(127.0.0.1:3306)/test?charset=utf8")
	if err != nil {
		panic(err)
	}
	rows, err := db.Query("select * from t1")
	if err != nil {
		panic(err)
	}
	cols, err := rows.Columns()
	if err != nil {
		panic(err)
	}
	values := make([]interface{}, len(cols))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	var allValues [][]interface{}

	for rows.Next() {
		err = rows.Scan(scanArgs...)
		if err != nil {
			panic(err)
		}
		fmt.Println(fmt.Sprintf("values: %#v", values))
		allValues = append(allValues, slices.Clone(values))
	}
	fmt.Println(fmt.Sprintf("allValues: %#v", allValues))
	//for i := range allValues {
	//	for j := range allValues[i] {
	//		fmt.Println(fmt.Sprintf("%d,%T", allValues[i][j], allValues[i][j]))
	//	}
	//}
	bytes, err := json.Marshal(allValues)
	if err != nil {
		return
	}
	fmt.Println(string(bytes))
}

func main1() {
	db, err := sql.Open("sqlite3", "./awesomeProject.db")
	if err != nil {
		panic(err)
	}
	rows, err := db.Query("select * from t1")
	if err != nil {
		panic(err)
	}
	cols, err := rows.Columns()
	if err != nil {
		panic(err)
	}
	values := make([]interface{}, len(cols))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	var allValues [][]interface{}
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		if err != nil {
			panic(err)
		}
		allValues = append(allValues, slices.Clone(values))
	}
	bytes, err := json.Marshal(allValues)
	if err != nil {
		return
	}
	fmt.Println(string(bytes))
}

@methane @julienschmidt

and adding Prepare method before raw query doest not help, it return same as not add Prepare

my purpose is "receive sql from grpc call, execute sql, serialize data, return json to grpc call", in this situation, the various structs can not be predefined in hardcode way, and return all string typed value means wrong result

[methane]

@naughtyGitCat
Recent version converts int/float into int/float, not []byte.
#1452

If you need more types, you should use ColumnType.
https://pkg.go.dev/database/sql#ColumnType

[naughtyGitCat]

@vh
After encountering the same trouble, I referred to Grafana's solution and made a tool
https://github.com/naughtyGitCat/anonymous-query-scan
hoping it can help you