Re: if (empty record set)
by Mr. Muskrat (Canon) on Jun 26, 2003 at 14:57 UTC
|
if (@bkmark == 0) { or even if (scalar @bkmark == 0) {
length is for use on scalar values. In fact, perlfunc says this about length:
"length EXPR
Returns the length in characters of the value of EXPR. If EXPR is omitted, returns length of $_. Note that this cannot be used on an entire array or hash to find out how many elements these have. For that, use scalar @array and scalar keys %hash respectively."
Update: Two different approaches to the same problem. Heh.
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: if (empty record set)
by hardburn (Abbot) on Jun 26, 2003 at 14:55 UTC
|
Instead of using $sth->fetchrow(), call $sth->rows() instead, which returns the number of rows that were hit:
my $SQLString "select this, that from there";
$sth = $dbh->prepare($SQLString);
$sth->execute();
if($sth->rows() == 0)
{
...........
}
---- I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer
Note: All code is untested, unless otherwise stated
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: if (empty record set)
by Zaxo (Archbishop) on Jun 26, 2003 at 14:59 UTC
|
The fetchrow methods are all suffixed by the return type. You clearly want fetchrow_array.
Also, your conditional puts its argument in scalar context, so if (@bkmark) {...} will suffice. That tests if the array is empty.
After Compline, Zaxo
| [reply] [Watch: Dir/Any] [d/l] |
Re: if (empty record set)
by tcf22 (Priest) on Jun 26, 2003 at 14:59 UTC
|
Using $sth->rows() is the answer if you need to actually work with the data, but if you just need to see if there are rows that match, consider using: (untested)
my $SQLString "select COUNT(1) from there";
$sth = $dbh->prepare($SQLString);
$sth->execute();
@bkmark = $sth->fetchrow;
if($bkmark[0] == 0)
{
...........
}
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
If you are working with mySQL SELECT COUNT (*) FROM ... is particularly fast, the engine being optimised for such things. So if you just want to know if there are any records available, thats the way to do it, rather than fetching the records themselves. If you need the data also, of course it is better to go with the other suggestions. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law
| [reply] [Watch: Dir/Any] [d/l] |
|
on many RDBMS the execute method will return the number of rows like so:
my $query = "SELECT * FROM myTable";
$sth = $dbh->prepare($query);
if(!$sth->execute())
{
...
}
it varies from system to system .. but is another way to accomplish what you want
edit: you could also run this query
SELECT Count(yourPrimaryKeyHere) FROM yourTable if you're on a database that doesn't support execute's return value or $sth->rows | [reply] [Watch: Dir/Any] [d/l] [select] |
|
Thats not the way execute works in DBI. Check the docs, but to summarise for you now
If an error occurs you get undef back
If it doesn't know the number of rows affected get -1 back
If no rows are affected you will get 0E0 back.
otherwise you get the number of rows affected
| [reply] [Watch: Dir/Any] |
|
Re: if (empty record set)
by Anonymous Monk on Jun 26, 2003 at 16:34 UTC
|
$sth->execute();
my $recordset = $sth->fetchall_arrayref();
if (@$recordset) {
# there are records. Do something here
}
else {
# empty recordset. Do something else.
}
Or, if you don't want to get all records at once
my $found = 0;
while (my $rec = $sth->fetchrow_arrayref()) {
#do something
$found = 1;
}
unless ($found) {
# recordset was empty.
# do something else
}
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: if (empty record set)
by digger (Friar) on Jun 26, 2003 at 16:12 UTC
|
I have struggled with this same issue in the past. Just using the rows will not solve the problem, according to the DBI documentation.
For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended.
You would have to do a fetchall_arrayref first, if I am understanding the docs correctly, then you could rely on the rows method.
I ended up using the "SELECT COUNT(*) FROM ..." SQL statement to check the number of records returned.
On a tangent, which method is more efficient? If I do a select count... first, then I might end up executing 2 SQL statements, if I get a count>0 from the select statement. If I do fetchall_arraref then I might end up pulling a large number of records into memory, and slowing things down. The answer is probably "it depends", but your input is appreciated.
digger | [reply] [Watch: Dir/Any] [d/l] [select] |
Re: if (empty record set)
by one4k4 (Hermit) on Jun 26, 2003 at 15:07 UTC
|
I have thoughts about using any array_ref as your result set from a DB call, as I like using hashref, but that's another thread. Here's what I'd do: (untested for syntax errors)
my $i = 0;
while ($sth->fetchrow_hashref){$i++;
##..do other stuff..
}
do_your_no_row_stuff($vars) unless $i;
Or
do_no_row_stuff() unless $sth->fetchrow_hashref;
## Of course, the first row is now useless.. but..
One4k4 - perlmonks@poorheart.com (www.poorheart.com) | [reply] [Watch: Dir/Any] [d/l] [select] |
Re: if (empty record set)
by Anonymous Monk on Jun 26, 2003 at 15:21 UTC
|
Try one of the following:
o Test the value contained in $sth->rows. If it is zero then no records exist. IIRC not all DBDs set this correctly.
o if(@bkmark = $sth->fetchrow){ ... }
Cheers,
Logic | [reply] [Watch: Dir/Any] |