Checking if a result/result set is nil

[elithrar]

Is there a simple way to do this? It'd be nice to be able to delineate between driver errors (no relation, column doesn't exist, etc) and a "no rows in result set" error without having to do string comparison.

e.g. A DB error will pass an error that will throw a HTTP 500 to the user and log it; a record that doesn't will just through a HTTP 404.

Something like the below:

        l := Listing{}
        l.Id = "1c9ea98d8ca5DDDD2631" // This doesn't exist
        row := db.QueryRowx("SELECT * FROM listings WHERE id = $1", l.Id)
        if row.IsNil() {
            return false, nil
        }
        err = row.StructScan(&l)
        if err != nil {
            return false, err
        }

[kisielk]

I think you should be able to compare the return value of StructScan to sql.ErrNoRows

[elithrar]

That works—thanks! I had database/sql as a side-effect import (since I’m leaning on sqlx) and didn’t spot that. Might have to dig through the database/sql API a little more.

Additionally: it looks like the best way to do this when using sqlx.Queryx to pull into a slice of structs is check if rows.Next() == false (or !rows.Next() if you like) prior to err := sqlx.StructScan(&rows.Rows, &dest).

On 26 Jan 2014, at 11:38 AM, Kamil Kisiel notifications@github.com wrote:

I think you should be able to compare the return value of StructScan to sql.ErrNoRows

—
Reply to this email directly or view it on GitHub.

[elithrar]

This is somewhat related to the above—I'm pulling a set of results from the DB (using Postgres' full text search capabilities):

rows, err := db.Queryx(`SELECT * FROM listings
WHERE tsv @@ plainto_tsquery('pg_catalog.english', $1)
ORDER BY expiry_date DESC LIMIT $2`, searchTerm, pageLimit) // pageLimit is 10 in this case

if rows.Next() == false { // or rows.Rows.Next() ?
    ...
}

err = sqlx.StructScan(&rows.Rows, &results)
if err != nil {
    ...
}

I'm doing some testing and exactly one record matches the search term provided. Running the query directly (via the CLI) results the expected row (singular). When running this query using sqlx however, rows.Next() returns true and rows.Rows.Next() returns false.

If I change it to ensure that at least two (2) rows are returned, rows.Rows.Next() also returns true and StructScan populates my slice of structs (&results) as expected.

In the case where I'm expecting anywhere between 0 and ∞ results, how should I be confirming that there are > 0 rows returned before calling StructScan(&rows.Rows, &results)? I want to scan the results into a slice of structs even if there is just one result to keep the code generic (and in most cases, there will rarely be just one result).

[jmoiron]

You want to know if there are rows before you go to scan? Is this to save on allocation? You can use the db.Select shortcut, or just go ahead and sqlx.StructScan(&rows.Rows, &results) anyway; if there are no rows, then len(results) == 0.

If you want to avoid that allocation, you could make a zero-length &results and pass that in:

rows, err := db.Queryx(...)
results := make([]MyType, 0, 0)
err = rows.StructScan(&rows.Rows, &results)

Keep in mind that I'm thinking very strongly of changing rows.Rows from sql.Rows to *sql.Rows to avoid that copy and some potentially sticky situations that can arise from it.

Edit Also, in my tests, rows.Rows.Next() and rows.Next() return the same results. They should in fact be the same function. If you call one after the other with a single result, then you will get true followed by false, as the first call to Next will have moved the cursor past the last row.

[elithrar]

@jmoiron Thanks for looking into this.

I'm not particularly fussed on saving on allocation (but it is a nice thing to do). I'm already allocating the result slice as the function returns (listings []*Listing, exists bool, error err) up front anyway.

You're right that I could replace db.Queryx(...) + sqlx.StructScan(&rows.Rows, &results) with a single db.Select(...) and I'll probably do that as it saves me having to also drop in an explicit defer rows.Close() as well.

I was definitely running into that last gotcha though since I was checking the value of rows.Next() before proceeding with the scan, which was (not obviously to me!) causing the cursor to move on.

    rows, err := db.Queryx(`SELECT id, title, company, location FROM listings WHERE tsv @@ plainto_tsquery('pg_catalog.english', $1) ORDER BY expiry_date DESC LIMIT $2`, searchTerm, pageLimit)
    if err != nil {
        return ...
    }
    defer rows.Close()

    if rows.Next() == false {
        return ...
    }

    err = sqlx.StructScan(&rows.Rows, &results)
    if err != nil {
            return ...
    }

Thanks again for investigating/helping out.

[jmoiron]

Ah yes, sqlx.StructScan is going to do the standard:

for rows.Next() {
    ...
}

It sorta assumes that you've processed as far as the cursor has gone. This is unlike rows.Scan; but the analogy of that is rows.StructScan; sqlx.StructScan is there basically to implement select. It's a confusing name conflict; I think originally sqlx was just these functions, and eventually I realized that the iterative is really valuable for conserving memory and added all the wrapping.

Going to close this as I think everything is working more or less as it should. Glad I could help!