CODEWITHSUNDEEP
postgresqlgo
Sue Mynott
Sue Mynott

Reputation: 1328

Example in database/sql using sql.ErrNoRows crashes and burns

I have a small Go program which uses a a postgresql db. In it there is a query which can return no rows, and the code I'm using to deal with this isn't working correctly.

    // Get the karma value for nick from the database.
func getKarma(nick string, db *sql.DB) string {
    var karma int
    err := db.QueryRow("SELECT SUM(delta) FROM karma WHERE nick = $1", nick).Scan(&karma)
    var karmaStr string
    switch {
    case err == sql.ErrNoRows:
        karmaStr = fmt.Sprintf("%s has no karma.", nick)
    case err != nil:
        log.Fatal(err)
    default:
       karmaStr = fmt.Sprintf("Karma for %s is %d.", nick, karma)
    }
    return karmaStr
}

This logic is taken directly from the Go documentation. When there are no rows corresponding to nick, the following error occurs:

2016/07/24 19:37:07 sql: Scan error on column index 0: converting driver.Value type <nil> ("<nil>") to a int: invalid syntax

I must be doing something stupid - clues appreciated.

Upvotes: 2

Views: 2766

Answers (2)

Spencer Judd
Spencer Judd

Reputation: 409

I believe your issue is that you're getting a NULL value back from the database, which go translates into nil. However, you're scanning into an integer, which has no concept of nil. One thing you can do is scan into a type that implements the sql.Scanner interface (and can handle NULL values), e.g., sql.NullInt64.

In the example code in the documentation, I'd assume they have a NOT NULL constraint on the username column. I think the reason for this is because they didn't want to lead people to believe that you have to use NULL-able types across the board.

Upvotes: 2

Sue Mynott
Sue Mynott

Reputation: 1328

I reworked the code to get the results I wanted.

// Get the karma value for nick from the database.
func getKarma(nick string, db *sql.DB) string {
    var karma int
    rows, err := db.Query("SELECT SUM(delta) FROM karma WHERE nick = $1", nick)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    karmaStr := fmt.Sprintf("%s has no karma.", nick)
    if rows.Next() {
        rows.Scan(&karma)
        karmaStr = fmt.Sprintf("Karma for %s is %d.", nick, karma)
    }
    return karmaStr
}

Tempted to submit a documentation patch of some sort to the database/sql package.

Upvotes: 1

Related Questions