Go: how to retrieve multiple results from mysql with sql/db package

Question

I'm trying to fetch data from a table using the sql/db package in Go. My database table contains a username "shaw". There are a few rows with the username shaw with different posts in the post column attached to it. I have the following code:

GO:

func ReadData() string {
    db, err := sql.Open("mysql", "user1@/my_db")
    if err != nil {
        fmt.Println(err)
    }
    defer db.Close()

    var tweet string

    rows, err := db.Query("select tweet from posts where username = ?", "shaw")
    if err != nil {
        fmt.Println(err)
    }
    defer rows.Close()

    for rows.Next() {
        err := rows.Scan(&tweet)
        if err != nil {
            fmt.Println(err)
        }
        fmt.Printf("this %s", tweet)
        return tweet
    }
    return ""
}

The result shown only gives me 1 value from the table and not the other values associate with "Shaw". How do I fetch more than 1 result?

According to the documentation here there doesn't seem to be anything wrong.. https://code.google.com/p/go-wiki/wiki/SQLInterface

OneOfOne · Accepted Answer · 2014-08-14 15:22:22Z

4

Because you return the first tweet and exit the function, to return multiple tweets you have to return a slice, for example:

func ReadData() (res []string) {
    db, err := sql.Open("mysql", "user1@/my_db")
    if err != nil {
        fmt.Println(err)
    }
    defer db.Close()

    rows, err := db.Query("select tweet from posts where username = ?", "shaw")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer rows.Close()

    var tweet string
    for rows.Next() {
        err := rows.Scan(&tweet)
        if err != nil {
            fmt.Println(err)
            return
        }
        fmt.Printf("this %s", tweet)
        res = append(res, tweet)
    }
    return
}

edited Aug 14, 2014 at 15:22

answered Aug 14, 2014 at 15:08

OneOfOne

100k22 gold badges195 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3918985 Over a year ago

how do I change that??

OneOfOne Over a year ago

@user3918985 Don't return tweet inside the for rows.Next() { ... } loop, check the edit.

user3918985 Over a year ago

wow thanks ALOT! I was almost pulling my hair out on this one. you really helped!!

Jonathan Lawlor · Accepted Answer · 2014-08-14 17:40:35Z

OneOfOne's answer is good, but you might want to change the function so that instead of returning a slice, it sends the tweets on a channel. That way, you can start consuming results before they are all done being sent. In that case, you'd want something like:

func ReadData() (res chan<- string) {
    db, err := sql.Open("mysql", "user1@/my_db")
    if err != nil {
        fmt.Println(err)
    }

    rows, err := db.Query("select tweet from posts where username = ?", "shaw")
    if err != nil {
        fmt.Println(err)
        db.Close()
        close(res)
        return
    }
    go func(res chan<- string) {
        defer db.Close()
        defer rows.Close()
        defer close(res)
        var tweet string
        for rows.Next() {
            err := rows.Scan(&tweet)
            if err != nil {
                fmt.Println(err)
                return
            }
            fmt.Printf("this %s", tweet)
            res <- tweet
        }
        return
    }(res)
}

Then, you can control the database buffering by changing the buffer of the input res chan<- string. Depending on your application you might not want to close the result - that's up to you.

Collectives™ on Stack Overflow

Go: how to retrieve multiple results from mysql with sql/db package

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related