CODEWITHSUNDEEP
mysqlgo
user3918985
user3918985

Reputation: 4409

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

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

Upvotes: 1

Views: 5599

Answers (2)

Jonathan Lawlor
Jonathan Lawlor

Reputation: 33

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.

Upvotes: 1

OneOfOne
OneOfOne

Reputation: 99215

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
}

Upvotes: 4

Related Questions