Checking if a result/result set is nil about sqlx HOT 6 CLOSED

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

from sqlx.

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 [email protected] 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.

from sqlx.

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).

from sqlx.

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.

from sqlx.

@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.

from sqlx.

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!

from sqlx.