Is there a database-independent way of finding out how many rows were
returned by a given query using Ruby DBI? Every method I’ve looked at that
seems like a candidate has not yielded a reasonable result. I suppose I
could do fetch_all.size, but that seems wasteful.
In case it matters I’m using PostgreSQL as the database.
Ben
What i think you are looking for is recordset#rows -
Returns the RPC (Row Processed Count) of the last
executed statement, or nil if no such exists.
From this
page:http://ruby-dbi.sourceforge.net/DBD_SPEC.html
You might check out this page too
http://www.kitebird.com/articles/ruby-dbi.html#TOC_1
Which seems to be down now but provides some good
examples, if i recall, of rows.
Ive had success with this with mysql - but using dbi,
it should not matter if you’re using postegres.
:paul
— Ben Giddings bg-rubytalk@infofiend.com wrote:
Is there a database-independent way of finding out
how many rows were
returned by a given query using Ruby DBI? Every
method I’ve looked at that
seems like a candidate has not yielded a reasonable
result. I suppose I
could do fetch_all.size, but that seems wasteful.In case it matters I’m using PostgreSQL as the
database.Ben
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
paul vudmaska wrote:
What i think you are looking for is recordset#rows -
Returns the RPC (Row Processed Count) of the last
executed statement, or nil if no such exists.
That’s what I thought I should be using, but it is always zero:
irb(main):097:0> dbh.execute(“SELECT Username FROM Users”).fetch_all
[[“ben”], [“harry”]]
irb(main):098:0> dbh.execute(“SELECT Username FROM Users”).rows
0
irb(main):099:0> sth = dbh.prepare(“SELECT Username FROM Users”)
…
irb(main)
0> sth.execute
[nil]
irb(main):101:0> sth.rows
0
irb(main):102:0> sth.fetch_all
[[“ben”], [“harry”]]
This appears to be a bug, doesn’t it?
Ben
paul vudmaska wrote:
What i think you are looking for is recordset#rows
Returns the RPC (Row Processed Count) of the last
executed statement, or nil if no such exists.That’s what I thought I should be using, but it is
always zero:irb(main):097:0> dbh.execute(“SELECT Username FROM
Users”).fetch_all
[[“ben”], [“harry”]]
irb(main):098:0> dbh.execute(“SELECT Username FROM
Users”).rows
0
irb(main):099:0> sth = dbh.prepare(“SELECT Username
FROM Users”)
…
irb(main)
[nil]
irb(main):101:0> sth.rows
0
irb(main):102:0> sth.fetch_all
[[“ben”], [“harry”]]This appears to be a bug, doesn’t it?
Ben
Certainly, it seems misleading…the page at kitebird
is up now and below is a snip that explains this.
For statements that return no result set, such as
INSERT or DELETE, do returns a count of the number of
rows processed.>>PLV _ this is what i thot you were
looking for<<
For statements that return rows, such as SELECT, you
can use the statement handle after invoking execute to
get row and column counts or information about each of
the columns in the result set:
The row and column counts are not available directly.
To get the row count, either count the rows as you
fetch them, or fetch them into a data structure and
see how many elements it contains…
<<
sa = $db.select_all(‘Select * from person’)
p sa.size #> think this returns what you are looking
for.
Paul
— Ben Giddings bg-rubytalk@infofiend.com wrote:
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
paul vudmaska wrote:
>What i think you are looking for is recordset#rows -
>Returns the RPC (Row Processed Count) of the last
>executed statement, or nil if no such exists.That's what I thought I should be using, but it is always zero:
irb(main):097:0> dbh.execute("SELECT Username FROM Users").fetch_all
[["ben"], ["harry"]]
irb(main):098:0> dbh.execute("SELECT Username FROM Users").rows
0
Try this one:
dbh.execute("SELECT Username FROM Users")['pg_row_count']
This is a Postgres-specific function, but should return what you except.
Alternatively you can always use SELECT COUNT(Username).
irb(main):099:0> sth = dbh.prepare("SELECT Username FROM Users")
...
irb(main)
[nil]
irb(main):101:0> sth.rows
0
irb(main):102:0> sth.fetch_all
[["ben"], ["harry"]]This appears to be a bug, doesn't it?
What exactly should be a bug? That sth.execute returns [nil]?
That's not a bug, but even so should be changed to nil or self.
Regards,
Michael
On Sun, Oct 12, 2003 at 05:17:19AM +0900, Ben Giddings wrote:
Try this one:
dbh.execute(“SELECT Username FROM Users”)[‘pg_row_count’]
This is a Postgres-specific function, but should return what you
except.
Hmm, and there isn’t a more general way of doing that, other than
returning all entries and doing a .size?
Alternatively you can always use SELECT COUNT(Username).
Yeah, but I was hoping to avoid doing two queries.
irb(main):099:0> sth = dbh.prepare(“SELECT Username FROM Users”)
…
irb(main)
[nil]
irb(main):101:0> sth.rows
0
irb(main):102:0> sth.fetch_all
[[“ben”], [“harry”]]This appears to be a bug, doesn’t it?
What exactly should be a bug? That sth.execute returns [nil]?
That’s not a bug, but even so should be changed to nil or self.
The buggy part is that sth.rows returns 0. From my reading of the DBI
documentation (sparse as it is), says that it should return the number
of rows processed, or nil. Another poster said that this variable is
used only when doing an update or delete. If that’s the case, it seems
like a SELECT statement should set it to nil. I still think that it
should be set to a real value, however.
As for sth.execute, I really don’t know what that should return, but an
array containing just nil doesn’t seem very useful.
Ben
On Sunday, Oct 12, 2003, at 11:08 US/Eastern, Michael Neumann wrote:
>Try this one:
>
> dbh.execute("SELECT Username FROM Users")['pg_row_count']
>
>This is a Postgres-specific function, but should return what you
>except.Hmm, and there isn't a more general way of doing that, other than
returning all entries and doing a .size?
Undoubtedly, this would be a quite useful feature.
Unluckily, this information is not available for every database.
Nevertheless, it's now on my (very long) todo list.
>What exactly should be a bug? That sth.execute returns [nil]?
>That's not a bug, but even so should be changed to nil or self.The buggy part is that sth.rows returns 0. From my reading of the DBI
documentation (sparse as it is), says that it should return the number
of rows processed, or nil. Another poster said that this variable is
used only when doing an update or delete. If that's the case, it seems
like a SELECT statement should set it to nil. I still think that it
should be set to a real value, however.
DBD::Pg's rows method return the result of rows_affected (part of C
interface) which may return 0. The problem is that I can't simply
return nil when rows_affected returns 0, because a RPC of 0 is a valid
one (DELETE ... WHERE 0=1). So I would have to check wether it's a
query or an update/delete, which can be as simple as =~ /select/i but
occasionally can be much harder.
As for sth.execute, I really don't know what that should return, but an
array containing just nil doesn't seem very useful.
That's just because Ruby returns the last value of a method. I now
return nil instead.
Regards,
Michael
On Mon, Oct 13, 2003 at 02:24:28AM +0900, Ben Giddings wrote:
On Sunday, Oct 12, 2003, at 11:08 US/Eastern, Michael Neumann wrote: