Retrieve Unknown Rows & Columns From a MySQL Table (Golang)

[Alexandre K]

Hello Everyone,

I'm new to Golang and I am trying to figure out how to retrieve multiple unknown columns and rows from a table.

I have an example table called ANIMALS.

I am trying to follow the example at the bottom of this GUIDE.

Here is the code that I've sewn together:

package main

import (
        "database/sql"
        "fmt"
        "log"

        _ "github.com/go-sql-driver/mysql"
)

func main() {
//Connect to database and check for errors  
        db, err := sql.Open("mysql",
                "script:scriptPassword@tcp($HOSTNAME:3306)/Testing")
        if err != nil {
                log.Println(err)
        }

        var str string
        rows, err = db.Query("SELECT * FROM animals").Scan(&str)
        if err != nil && err != sql.ErrNoRows {
                log.Fatal(err)
        }

        cols, err := rows.Columns() // Remember to check err afterwards
        if err != nil {
                log.Fatal(err)
        }

        vals := make([]interface{}, len(cols))
        for i, _ := range cols {
        vals[i] = new(sql.RawBytes)
}

        for rows.Next() {
        err = rows.Scan(vals...)
        // Now you can check each element of vals for nil-ness,
        if err != nil {
                log.Fatal(err)
        }
        // Here is where I get lost
        // How should I access Vals and print the values it finds???
        }

I am trying to get Golang to print the values to the linux terminal using something like the code below.

fmt.Println(mysqlValues)

How do I retrieve the MySQL values stored in the interface in my code provided above????

[dja...@gmail.com]

Hi,

something like (not tested): 

package main

import (
        "database/sql"
        "fmt"
        "log"

        _ "github.com/go-sql-driver/mysql"
)

func main() {
//Connect to database and check for errors  
        db, err := sql.Open("mysql",
                "script:scriptPassword@tcp($HOSTNAME:3306)/Testing")
        if err != nil {
                log.Println(err)
        }

        var str string
        rows, err = db.Query("SELECT * FROM animals").Scan(&str)
        if err != nil && err != sql.ErrNoRows {
                log.Fatal(err)
        }

        cols, err := rows.Columns() // Remember to check err afterwards
        if err != nil {
                log.Fatal(err)
        }

        vals := make([]interface{}, len(cols))

        for rows.Next() {
        for i := range cols {
            vals[i] = &vals[i]
                }

        err = rows.Scan(vals...)
        // Now you can check each element of vals for nil-ness,
        if err != nil {
                log.Fatal(err)
        }

        for i := range cols {

                    fmt.Println(vals[i]) 

                }
        }

Jamil Djadala

[Alexandre K]

Thank you so much for your response!

The code you added probably works, but I'm getting held up by something else.

I'm running into a set of errors when I execute this on the command line...It seems the "rows" value I'm creating doesn't have the functionality that the guide says it would????

# command-line-arguments

./2multi.go:22: rows.Columns undefined (type error has no field or method Columns)

./2multi.go:30: rows.Next undefined (type error has no field or method Next)

./2multi.go:34: rows.Scan undefined (type error has no field or method Scan)

Here's an updated version of my code

package main

import (

       "database/sql"

       "fmt"

       "log"

        _ "github.com/go-sql-driver/mysql"

)

func main() {

//Connect to database and check for errors  

       db, err := sql.Open("mysql",

               "script:script1!@tcp(10.14.0.173:3306)/dbaTesting")

       if err != nil {

               log.Println(err)

       }

        var str string

       rows := db.QueryRow("SELECT * FROM animals").Scan(&str)

        cols, err := rows.Columns() // Remember to check err afterwards

       if err != nil {

               log.Fatal(err)

       }

        vals := make([]interface{}, len(cols))

        for rows.Next() {

       for i := range cols {

           vals[i] = &vals[i]

               }

       err = rows.Scan(vals...)

       // Now you can check each element of vals for nil-ness,

       if err != nil {

               log.Fatal(err)

       }

       for i := range cols {

                   fmt.Println(vals[i])

                }

       }

}

Shouldn't the value I create in "rows" be able to pass arguments to the "db" class I'm referencing when I'm creating "rows?"

[Steven Hartland]

QueryRow(..) returns a *Row but you then chained a .Scan(..) which returns an error as compile error suggests.

I'm guessing you want rows, if so have a look at:
https://golang.org/pkg/database/sql/#DB.Query

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alexandre K]

Hey Steve,

So I initially tried that, and Golang reported that Query() is for a single value context:

# command-line-arguments

./2multi.go:20: multiple-value db.Query() in single-value context

Since the table has 2 columns, do I need to scan each column to it's own variable like this?

var str1 string

var str2 string

rows := db.QueryRow("SELECT * FROM animals").Scan(&str1, &str2)

[dja...@gmail.com]

Again not tested:

package main

import (

        "database/sql"

        "fmt"

        "log"

        _ "github.com/go-sql-driver/mysql"

)

func main() {

//Connect to database and check for errors  

        db, err := sql.Open("mysql",

                "script:script1!@tcp(10.14.0.173:3306)/dbaTesting")

        if err != nil {

                log.Println(err)

        }

        rows,err := db.Query("SELECT * FROM animals")

        if err != nil {

                log.Fatal(err)

        }

        defer rows.Close()

[Alexandre K]

So this appears to be working.

But I believe something needs to be converted into strings.

This is what the code is returning:

user@server:~/folder/~# go run query.go 

[50]

[99 97 116]

[49]

[100 111 103]

[52]

[108 97 120]

[54]

[111 115 116 114 105 99 104]

[51]

[112 101 110 103 117 105 110]

[53]

[119 104 97 108 101]

[Alexandre K]

So, I'm really close to accomplishing my goal.

Can anyone help me with the very last command IN THIS CODE???

[Tamás Gulácsi]

for i, v := range vals {
fmt.Printf("%d. %v\n",vals[i])
}