database/sql get number of rows?

[Silas Baronda]

Looking through the database/sql package I see no easy way to get the
number of rows the selected query is returning.

I'm looking for something like this:

rows, _ := stmt.Query("SELECT id FROM cars")
res := make(Result, rows.NumberOfRows())

Is easily possible with the current implementation?

If not, maybe a rows.NumberOfRows()

[Kyle Lemons]

I ran into this the other day too.  Since I haven't written or looked too deeply into any of the bindings, I don't know if this would be a burden or not.  In any case, I think it would have to be "func (*sql.Rows) Count() (int, error)" or something so that the bindings could indicate that it is not implemented if their backend doesn't support an easy way of doing that.

[Mark Severson]

Some drivers might have difficultly implementing such a feature. For example, some database interfaces cannot return the number of affected rows until all of the rows have been iterated over. But never-the-less, I think it would be a valuable feature to have!

I actually ensured that my ODBC driver (https://bitbucket.org/miquella/mgodbc) implemented the Result interface in my rows struct. However, this wouldn't help as the driver's internal rows struct is wrapped by sql.Rows. It would have to be something exposed through sql.Rows before it could be effective.

Perhaps Brad has some input on this?

[Daniel Theophanes]

You could buffer your results:

eg

bitbucket.org/kardianos/tablebuffer

Though it may or may not be ideal for your use case.

[Silas Baronda]

In sqlite3 there is sqlite3_column_count that accepts a statement. I
even patched it for Russ Cox's sqlite3 API before I started using
mattn's sqlite3 API. So it is definitely possible with sqlite3. I'm
not sure what the other drivers have an interface similar to this.

—Silas

[Patrick Mylund Nielsen]

If it's only for allocation purposes, doing

var results []Result

for rows.Next():
results = append(results, res)

should be pretty efficient. It's going to allocate larger arrays
rather quickly, and as a plus your memory usage grows as you are
fetching the rows, not instantly.

[Silas Baronda]

On Sat, Feb 25, 2012 at 5:37 PM, Mark Severson <miqu...@gmail.com> wrote:
> Some drivers might have difficultly implementing such a feature. For
> example, some database interfaces cannot return the number of affected rows
> until all of the rows have been iterated over. But never-the-less, I think
> it would be a valuable feature to have!

I quickly looked over the API for MySQL and I'm pretty sure that you
can do it with mysql_num_rows() it really matters which result format
you use.

For PostgreSQL, I'm not sure as I'm not familiar with it, but I think
PQntuples() might return what I'm are looking for.

With Sqlite3 I've already done it.

For Firebird, I couldn't find my answer quick enough.

>
> I actually ensured that my ODBC driver
> (https://bitbucket.org/miquella/mgodbc) implemented the Result interface in
> my rows struct. However, this wouldn't help as the driver's internal rows
> struct is wrapped by sql.Rows. It would have to be something exposed through
> sql.Rows before it could be effective.
>
> Perhaps Brad has some input on this?

I'm pretty sure that what I'm asking can be done at a DB level, if
said API doesn't support it at DB level then falling back to using
Patrick's method of appending is another option.

[DisposaBoy]

I think you'd need at least `2` or `3` different functions or a different options to tell it what to do and these options would need to be set before the db connection is started.

In the case of MySQL alone:

1. num_rows is for the number of matched rows in a select - fine

2. affected_rows is for the number of ? rows in an updated

I put a question mark in the latter  because it can return 1 of 2 different results that may cause all sorts of subtle bugs. Depending on settings tied to the db `connection` it can return either the number of matched rows, or the number of rows actually updated (MySQL doesn't update a row if it already had the same value(s)).

You'd also need to set options before the query is executed because depending on what you're doing you don't want every query being counted as this can affect performance in odd ways.

[Kyle Lemons]

I think you'd need at least `2` or `3` different functions or a different options to tell it what to do and these options would need to be set before the db connection is started.

In the case of MySQL alone:

1. num_rows is for the number of matched rows in a select - fine

2. affected_rows is for the number of ? rows in an updated

I put a question mark in the latter  because it can return 1 of 2 different results that may cause all sorts of subtle bugs. Depending on settings tied to the db `connection` it can return either the number of matched rows, or the number of rows actually updated (MySQL doesn't update a row if it already had the same value(s)).

You'd also need to set options before the query is executed because depending on what you're doing you don't want every query being counted as this can affect performance in odd ways.

They're talking about a select query.  You'd give 1, as in, the number of times you call Next() before exhausting the rows.  AffectedRows is part of the Result interface (returned by Exec, etc), not the Rows struct (returned by Query).

[DisposaBoy]

Oh, I see :)

[وقــــاص شيــــخ‎]

This is an old post but I thought I'd comment anyway. 

- make a slice of data with length 0

- append data to the slice

the slice will grow to accomodate data. A method for number of rows isn't really needed. Here's my code

func (self *UserDAO) GetUsers() (users []models.User){
selectStatement,err := self.db.Prepare("SELECT userid,firstname,lastname,dob,email FROM users");
if(err!=nil){
panic(err.Error());
}
defer selectStatement.Close();
rows,err := selectStatement.Query();
if(err != nil){
panic(err.Error());
}
defer rows.Close();
users = make([]models.User,0);
var id int;
var fname, lname, dob, email string;

for rows.Next(){
err := rows.Scan(&id,&fname,&lname,&dob,&email);
if(err!=nil){
panic(err.Error());
}
users = append(users,*models.NewUser(id,fname,lname,dob,email));
}
return
}

[Dan Stroot]

This was really helpful - just wanted to say thanks for taking the time to post it.