Query returns -1 if row present (DBI, SQL Server 2000)

Hello,

I am using the DBI module to interface with a SQL Server 2000 database
-- connection, INSERT, UPDATE, and SELECT (when 0 rows exist in
resultset) are fine. However, if the resultset contains a row, a
value of -1 is returned. The query is very simple:

my $sth = $dbh->prepare(q{SELECT TOP 1 id FROM host WHERE ip = ?});
$sth->execute($ip);
print "Looked up $ip: " . $sth->rows . " ($DBI::errstr)\n";

if ($sth->rows == 0) {
# do something
}

elsif ($sth->rows > 0) {
# do something else
}

else {
# error
}

I do not believe this to is a permission problem with SQL Server as
the initial SELECT runs and jumps properly when $sth->rows is 0.
However the code always ends up in the else block if a row is returned
from the initial SELECT. The SELECT query also runs fine in Query
Analyzer when entered manually. Oh, it may also be worth mentioning
that $DBI::errstr is empty after the execute call for the initial
SELECT query.

TIA.

Re: Query returns -1 if row present (DBI, SQL Server 2000)

int eighty wrote:
> Hello,
>
> I am using the DBI module to interface with a SQL Server 2000 database
> -- connection, INSERT, UPDATE, and SELECT (when 0 rows exist in
> resultset) are fine. However, if the resultset contains a row, a
> value of -1 is returned. The query is very simple:
>
> my $sth = $dbh->prepare(q{SELECT TOP 1 id FROM host WHERE ip = ?});
> $sth->execute($ip);
> print "Looked up $ip: " . $sth->rows . " ($DBI::errstr)\n";
>
> if ($sth->rows == 0) {
> # do something
> }
>
> elsif ($sth->rows > 0) {
> # do something else
> }
>
> else {
> # error
> }
>
> I do not believe this to is a permission problem with SQL Server as
> the initial SELECT runs and jumps properly when $sth->rows is 0.
> However the code always ends up in the else block if a row is returned
> from the initial SELECT. The SELECT query also runs fine in Query
> Analyzer when entered manually. Oh, it may also be worth mentioning
> that $DBI::errstr is empty after the execute call for the initial
> SELECT query.

Possibly, reading the documentation will help.

"Returns the number of rows affected by the last row affecting command,
or -1 if the number of rows is not known or not available. "

[ continue reading docs for more information.]

Re: Query returns -1 if row present (DBI, SQL Server 2000)

On Oct 29, 2:35 pm, "J. Gleixner"
wrote:
> int eighty wrote:
> > Hello,
>
> > I am using the DBI module to interface with a SQL Server 2000 database
> > -- connection, INSERT, UPDATE, and SELECT (when 0 rows exist in
> > resultset) are fine. However, if the resultset contains a row, a
> > value of -1 is returned. The query is very simple:
>
> > my $sth = $dbh->prepare(q{SELECT TOP 1 id FROM host WHERE ip = ?});
> > $sth->execute($ip);
> > print "Looked up $ip: " . $sth->rows . " ($DBI::errstr)\n";
>
> > if ($sth->rows == 0) {
> > # do something
> > }
>
> > elsif ($sth->rows > 0) {
> > # do something else
> > }
>
> > else {
> > # error
> > }
>
> > I do not believe this to is a permission problem with SQL Server as
> > the initial SELECT runs and jumps properly when $sth->rows is 0.
> > However the code always ends up in the else block if a row is returned
> > from the initial SELECT. The SELECT query also runs fine in Query
> > Analyzer when entered manually. Oh, it may also be worth mentioning
> > that $DBI::errstr is empty after the execute call for the initial
> > SELECT query.
>
> Possibly, reading the documentation will help.
>
> "Returns the number of rows affected by the last row affecting command,
> or -1 if the number of rows is not known or not available. "
>
> [ continue reading docs for more information.]

What a surprise that something I often advocate actually works. That
is an unexpected aspect of the rows method, as I anticipated the
functionality would be similar to http://us.php.net/manual/en/function.mysqli-num-rows.php
The documentation doesn't lie, though. Many thanks.