Select Count(*) result

SQLMan has asked for the wisdom of the Perl Monks concerning the following question:

When I run the code below and print $ncount it always
returns -1 . It makes no difference if the record is there or not. It seems that it is not running the
statement. But if I print $sql I get this result.
Can anybody help me. Thanks

Select Count(*) From ClearTransferData    Where AccountNo = '25010002'
and  TransferFlag = 'TRI' and TransferType = 'NCT'
and RejectType = 'C' and AdpNumber = '       ' and
 Shares = '0.00000' and ProcessingDate = '2003-05-15 00:00:00.000'

my $sql="Select Count(*) From ClearTransferData
Where AccountNo = '$data[0]' and  TransferFlag = '$data[1]' and
TransferType = '$data[2]' and RejectType = '$data[3]' and
AdpNumber = '$data[4]' and Shares = '$data[5]' and ProcessingDate = '$
+data[6]' ";

$result = $dbh->do($sql);
 my $ncount = $result;
 print "$sql\n";
print "$ncount\n";
[download]

update (broquaint): added <code> tags

Comment on Select Count(*) result Select or Download Code

Replies are listed 'Best First'.
Re: Select Count(*) result by thraxil (Prior) on May 16, 2003 at 14:15 UTC
`do` returns the number of rows affected by an update, it does not return the results of a select. you want to use `prepare()`, `execute()` and then a `fetch*` instead. i recommend `perldoc DBI` for more information. the examples are quite clear. anders pearson	[reply]
Re: Select Count(*) result by Rhandom (Curate) on May 16, 2003 at 14:27 UTC
As stated before - perldoc DBI is your friend. However - for a quick answer - do is great but as previously stated doesn't work with select. If you want to avoid the prepare, execute, fetchrow path you can do: `my ($count) = $dbh->selectrow_array($sql); # or if you substitute '$data[\d+]' with ? in your sql my ($count) = $dbh->selectrow_array($sql, {}, @data);` [download] my @a=qw(random brilliant braindead); print $a[rand(@a)];	[reply] [d/l]
Re: Select Count(*) result by hardburn (Abbot) on May 16, 2003 at 14:16 UTC
Please place <code> tags around your code. It makes it much easier to read, and your square brackets will be properly escaped. Also, use placeholders in your SQL, like this: `my $sql="Select Count() From ClearTransferData Where AccountNo = ? and TransferFlag = ? and TransferType = ? and RejectType = ? and AdpNumber = ? and Shares = ? and ProcessingDate = ? "; my $sth = $dbh->prepare($sql) or die $dbh->errstr; $sth->execute(@data); # Fetch results with $sth->fetchrow_array(), or whatever $sth->disconnect();` [download] This is safer (since it will automagicaly escape naughty database characters), might* be faster (depending on your database and the specifics of what you're doing), and is better if you end up using `$dbh->prepare_cached->($sql)` instead (since the data isn't contain in the SQL, the same statement can be refetched with less performance hit, even when the data chages). `$dbh->do()` is for very simple cases (like `SELECT COUNT() FROM some_table`). You don't have a simple case. ---- 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] [d/l] [select]
Re: Re: Select Count(*) result by thraxil (Prior) on May 16, 2003 at 14:32 UTC
$dbh->do() is for very simple cases (like SELECT COUNT() FROM some_table). You don't have a simple case.* actually, `$dbh->do()` won't work for that either. from the documentation: Returns the number of rows affected or "undef" on error. A return value of "-1" means the number of rows is not known, not applicable, or not available. This method is typically most useful for non-"SELECT" statements that either cannot be prepared in advance (due to a limitation of the driver) or do not need to be executed repeatedly. It should not be used for "SELECT" statements because it does not return a statement handle (so you can't fetch any data). anders pearson	[reply]