printing results from a query with database/sql

[Peter Kleiweg]

I wrote this function to print the results from a query with
database/sql. The function doesn't now how many columns there
are. All it nows is that all columns are of type string.

My question is, can this be done in a better or simpler way? I
am not sure if the use of new() is correct, I have never used
new() before. And I think *(fields[i].(*string)) looks a bit
convoluted.

func printTable(rows *sql.Rows) {
cols, _ := rows.Columns()
n := len(cols)

for i := 0; i < n; i++ {
fmt.Print(cols[i], "\t")
}
fmt.Println()

var fields []interface{}
for i := 0; i < n; i++ {
fields = append(fields, new(string))
}
for rows.Next() {
rows.Scan(fields...)
for i := 0; i < n; i++ {
fmt.Print(*(fields[i].(*string)), "\t")
}
fmt.Println()
}
}

--
Peter Kleiweg
http://pkleiweg.home.xs4all.nl/

[Peter Kleiweg]

I changed the code so it handles values of different types, using a
prefix in column labels to indicate the type:

func printTable(rows *sql.Rows) {

pr := func(t interface{}) (r string) {
r = "\\N"
switch v := t.(type) {
case *sql.NullBool:
if v.Valid {
r = fmt.Sprintf("%v", v.Bool)
}
case *sql.NullString:
if v.Valid {
r = v.String
}
case *sql.NullInt64:
if v.Valid {
r = fmt.Sprintf("%6d", v.Int64)
}
case *sql.NullFloat64:
if v.Valid {
r = fmt.Sprintf("%.2f", v.Float64)
}
case *time.Time:
if v.Year() > 1900 {
r = v.Format("_2 Jan 2006")
}
default:
r = fmt.Sprintf("%#v", t)
}
return
}

c, _ := rows.Columns()
n := len(c)

// print labels

for i := 0; i < n; i++ {

if len(c[i]) > 1 && c[i][1] == ':' {
fmt.Print(c[i][2:], "\t")
} else {
fmt.Print(c[i], "\t")
}
}
fmt.Print("\n\n")

// print data
var field []interface{}

for i := 0; i < n; i++ {

switch {
case c[i][:2] == "b:":
field = append(field, new(sql.NullBool))
case c[i][:2] == "f:":
field = append(field, new(sql.NullFloat64))
case c[i][:2] == "i:":
field = append(field, new(sql.NullInt64))
case c[i][:2] == "s:":
field = append(field, new(sql.NullString))
case c[i][:2] == "t:":
field = append(field, new(time.Time))
default:
field = append(field, new(sql.NullString))
}
}
for rows.Next() {
checkErr(rows.Scan(field...))

for i := 0; i < n; i++ {

fmt.Print(pr(field[i]), "\t")
}
fmt.Println()
}
fmt.Println()
}

func checkErr(err error) {
if err != nil {
_, filename, lineno, ok := runtime.Caller(1)
if ok {
fmt.Fprintf(os.Stderr, "%v:%v: %v\n", filename,
lineno, err)
}
panic(err)
}
}

Here is an example call:

rows, err = db.Query("select id as \"i:id\", name as \"s:name\",
date as \"t:date\" from `item`")
checkErr(err)
printTable(rows)

[sdeg...@8thlight.com]

You can use .Columns() on a sql.Rows (http://golang.org/pkg/database/sql/#Rows.Columns)

Also you can use *interface{} (http://golang.org/pkg/database/sql/#Rows.Scan) and then do type switching, including on nil, for a few simple types (http://golang.org/pkg/database/sql/#Scanner)

-Steven

[Dougx]

For anyone else who digs up this old thread and finds that 1) there are no examples of this, and 2) the way of doing it is entirely not obvious, you probably want to do something like a cleaned up and more generic version of:

     if self.Rows.Next() {
        var values []interface{}
        var generic = reflect.TypeOf(values).Elem()
        for i := 0; i < len(cols); i++ {
          values = append(values, reflect.New(generic).Interface())
        }

        self.Rows.Scan(values...)
        for i := 0; i < len(cols); i++ {
          var raw_value = *(values[i].(*interface{}))
          var raw_type = reflect.TypeOf(raw_value)
          switch {
            case raw_type == reflect.TypeOf(int64(0)):
              rtn[cols[i]] = raw_value.(int64)
          }
        }

~
Doug.

[Julien Schmidt]

http://play.golang.org/p/njPBsg0JjD

[Julien Schmidt]

Oh, this is just a snippet. This doesn't compile like this ;)
Based on https://github.com/Go-SQL-Driver/MySQL/wiki/Examples#rawbytes

[Dougx]

Oh, that's quite elegant.

Wish you didn't always get byte[] back from Scan() though; eg. if http://play.golang.org/p/jxza3pbqq9 could print out the actual types, you could use the switch statement to cast the interface value to the appropriate type and return a strongly typed map[string]interface{}.

Alas, that doesn't seem to be possible~

~
Doug.

[Mike Hughes]

Yeah, I wonder if that limitation is coming from the DB driver level, or "database/sql" package level. There should be a way for the query results to include the nearest equivalent golang type (although I'm sure there would be much debate over what maps to what!)

I just threw together http://play.golang.org/p/gPM36Atkal (just an extension of Julien's post with reflection to test types) and it seems that everything except date/time values is actually of type []uint8 ! This is using the github.com/bmizerany/pq postgresql driver.

[Mike Hughes]

Upon further testing, it seems it does depend on the driver. In github.com/bmizerany/pq it looks like the "decode" function in https://github.com/bmizerany/pq/blob/master/encode.go assigns fairly reasonable types for everything, but there doesn't seem to be any separation of strings and arrays of bytes. I suppose there doesn't really need to be, but it seems that the pgsql oid types are already listed in https://github.com/bmizerany/pq/blob/master/types.go so it may just be a matter of extending the switch function in encode.go to cast certain oids to certain types if you really needed to.

[Julien Schmidt]

This is not really a limitation but the effect of a design choice by the database/sql/driver package which says the that the driver might return any of the following types:

int64
float64
bool
[]byte
string   [*] everywhere except from Rows.Next.
time.Time

Source:  http://golang.org/pkg/database/sql/driver/#Value

Since most drivers communicate via a text protocol with the server, the obvious choice is []bytes most of the times. But there are a few exceptions. For example the database/sql package is currently not able to convert from strings / []byte to time.Time, so the driver must do this to support time.Time. Moreover the database/sql package is not able to convert from time.Time to []byte / string / sql.RawBytes. For performance-critical applications is sql.RawBytes often a must. This is the reason why Go-MySQL-Driver currently does not not return time.Time values for date / datetime mysql values (But it will be able to do this optionally in Release Candidate 1).
But there are further exceptions (inconsistencies). For example MySQL uses a binary protocol for prepared statements instead of the text protocol. In this case the driver returns an int64 or float64 for numeric values while it returns []byte for 'normal' queries.