How can i Check if my Db.Query returns null rows

Question

Following is my code to get multiple rows from db and it works.


    defer db.Close()
    for rows.Next() {
    err = rows.Scan(&a)
    if err != nil {
        log(err)
    }

How can I check if the rows contains No row?

Even I tried like below

if err == sql.ErrNoRows {
        fmt.Print(No rows)
    }

and also checked while scanning

 err = rows.Scan(&a)
 if err == sql.ErrNoRows {
        fmt.Print(No rows)
    }

I don't understand which one gives ErrNoRows either *Rows or err or Scan

Brits · Accepted Answer · 2020-02-07T05:48:22.150

QueryRow returns a *Row (not a *Rows) and you cannot iterate through the results (because it's only expecting a single row back). This means that rows.Scan in your example code will not compile).

If you expect your SQL query to return a single resullt (e.g. you are running a count() or selecting using an ID) then use QueryRow; for example (modified from here):

id := 43
var username string
err = stmt.QueryRow("SELECT username FROM users WHERE id = ?", id).Scan(&username)
switch {
case err == sql.ErrNoRows:
    log.Fatalf("no user with id %d", id)
case err != nil:
    log.Fatal(err)
default:
    log.Printf("username is %s\n", username)
}

If you are expecting multiple rows then use Query() for example (modified from here):

age := 27
rows, err := db.Query("SELECT name FROM users WHERE age=?", age)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
names := make([]string, 0)
for rows.Next() {
    var name string
    if err := rows.Scan(&name); err != nil {
        log.Fatal(err)
    }
    names = append(names, name)
}
// Check for errors from iterating over rows.
if err := rows.Err(); err != nil {
    log.Fatal(err)
}
// Check for no results
if len(names) == 0 {
    log.Fatal("No Results")
}
log.Printf("%s are %d years old", strings.Join(names, ", "), age)

The above shows one way of checking if there are no results. If you are not putting the results into a slice/map then you can keep a counter or set a boolean within the loop. Note that no error will be returned if there are no results (because this is a perfectly valid outcome) and the SQL package provides no way to check the number of results other than iterate through them (if all you are interested in is the number of results then run select count(*)...).

my bad sorry its Query not Queryrow by the way i don't need the count i want to know if the db contains rows or not if no rows in the db then i'll return before instead of checking row.next() — Hai U, Feb 07 '20 at 05:37
No worries - hopefully the above answers your question regardless? (I tried to provide an answer for both ways of retrieving the results) — Brits, Feb 07 '20 at 05:39
so `if len(names) == 0 { log.Fatal("No Results") }` by finding len is the only way to know whether we have data in rows or not — Hai U, Feb 07 '20 at 05:50
Sorry missed the second half of your comment. I've added some extra info; basically you need to attempt to iterate through the results (i.e. call ```Next```). The SQL package does not provide any other options (see [this question](https://stackoverflow.com/questions/37629357/how-to-get-count-of-sql-rows-without-using-next) for a bit more info) — Brits, Feb 07 '20 at 05:52
" finding len is the only way" - Not the only way but I think it's usually the simplest approach. You can also set a boolean to true within the ```for rows.Next() {``` loop or increment a count (but you cannot avoid calling rows.Next if using the standard SQL package).. — Brits, Feb 07 '20 at 05:58

How can i Check if my Db.Query returns null rows

1 Answers1