rows.Scan to byte arrays

[elgs]

Issue description

rows.Scan into byte array while other database (sqlite, pgx) drivers scan into data of proper types.

Example code

Assuming we have the following table test:

+----+-------+
| ID | NAME  |
+----+-------+
|  1 | Alpha |
|  2 | Beta  |
|  3 | Gamma |
+----+-------+

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	_ "github.com/jackc/pgx/v5/stdlib"
	_ "github.com/mattn/go-sqlite3"
	_ "modernc.org/sqlite"
)

func main() {
	// db, _ := sql.Open("sqlite", "test.sqlite3")
	// db, _ := sql.Open("sqlite3", "test.sqlite3")
	db, _ := sql.Open("mysql", "user:pass#@tcp(localhost:3306)/test_db")
	// db, _ := sql.Open("pgx", "postgres://user:pass@localhost:5432/test_db")

	resultMaps, _ := QueryToMaps(db, "SELECT * FROM test")
	fmt.Printf("maps: %+v\n", resultMaps)

	// with sqlite driver
	// maps: [map[ID:1 NAME:Alpha] map[ID:2 NAME:Beta] map[ID:3 NAME:Gamma]]

	// with sqlite3 driver
	// maps: [map[ID:1 NAME:Alpha] map[ID:2 NAME:Beta] map[ID:3 NAME:Gamma]]

	// with postgres driver
	// maps: [map[id:1 name:Alpha] map[id:2 name:Beta] map[id:3 name:Gamma]]

	// with mysql driver
	// maps: [map[ID:[49] NAME:[65 108 112 104 97]] map[ID:[50] NAME:[66 101 116 97]] map[ID:[51] NAME:[71 97 109 109 97]]]

}

func QueryToMaps(db *sql.DB, sqlStatement string, sqlParams ...any) ([]map[string]any, error) {
	results := []map[string]any{}
	rows, err := db.Query(sqlStatement, sqlParams...)
	if err != nil {
		return results, err
	}
	cols, _ := rows.Columns()
	lenCols := len(cols)

	rawResult := make([]any, lenCols)
	dest := make([]any, lenCols) // A temporary any slice
	for i := range rawResult {
		dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
	}
	for rows.Next() {
		result := make(map[string]any, lenCols)
		rows.Scan(dest...)
		for i, raw := range rawResult {
			result[cols[i]] = raw
		}
		results = append(results, result)
	}
	return results, nil
}

Configuration

Driver version (or git SHA):

github.com/go-sql-driver/mysql v1.7.0 h1:ueSltNNllEqE3qcWBTD0iQd3IpL/6U+mJxLkazJ7YPc=
github.com/go-sql-driver/mysql v1.7.0/go.mod h1:OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI=

Go version: run go version in your console

$ go version
go version go1.20.1 darwin/arm64

Server version: E.g. MySQL 5.6, MariaDB 10.0.20

$ mysqld --version
mysqld  Ver 10.11.2-MariaDB for osx10.18 on arm64 (Homebrew)

Server OS: E.g. Debian 8.1 (Jessie), Windows 10

macOS 13.2.1 (22D68)

[methane]

#1386 (comment)

[elgs]

For anyone with the same problem:

func QueryToMaps(db *sql.DB, sqlStatement string, sqlParams ...any) ([]map[string]any, error) {
	results := []map[string]any{}
	rows, err := db.Query(sqlStatement, sqlParams...)
	if err != nil {
		return results, err
	}
	cols, _ := rows.Columns()
        colTypes, _ := rows.ColumnTypes()
	lenCols := len(cols)

	rawResult := make([]any, lenCols)
	dest := make([]any, lenCols) // A temporary any slice
	for i := range rawResult {
		dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
	}
	for rows.Next() {
		result := make(map[string]any, lenCols)
		rows.Scan(dest...)
		for i, raw := range rawResult {
			if v, ok := raw.([]byte); ok {
				value := string(v)
				switch colTypes[i].DatabaseTypeName() {
				case "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT", "YEAR":
					raw, _ = strconv.Atoi(value)
				case "TINYINT", "BOOL", "BOOLEAN", "BIT":
					raw, _ = strconv.ParseBool(value)
				case "FLOAT", "DOUBLE", "DECIMAL":
					raw, _ = strconv.ParseFloat(value, 64)
				case "DATETIME", "TIMESTAMP":
					raw, _ = time.Parse("2006-01-02 15:04:05", value)
				case "DATE":
					raw, _ = time.Parse("2006-01-02", value)
				case "TIME":
					raw, _ = time.Parse("15:04:05", value)
				case "NULL":
					raw = nil
                                default:
                                        raw = value
				}
			}
			result[cols[i]] = raw
		}
		results = append(results, result)
	}
	return results, nil
}