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

I

int eighty

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.
 
J

J. Gleixner

int said:
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.]
 
I

int eighty

int said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top