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

Discussion in 'Perl Misc' started by int eighty, Oct 29, 2007.

  1. int eighty

    int eighty Guest

    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.
     
    int eighty, Oct 29, 2007
    #1
    1. Advertising

  2. int eighty

    J. Gleixner Guest

    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.]
     
    J. Gleixner, Oct 29, 2007
    #2
    1. Advertising

  3. int eighty

    int eighty Guest

    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.
     
    int eighty, Oct 29, 2007
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Z2F1cmF2?=

    Uploading Data From MS Acess 2000 ti SQL server 2000

    =?Utf-8?B?Z2F1cmF2?=, Jan 9, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    848
    Mary Chipman [MSFT]
    Jan 9, 2006
  2. Ralf Wahner
    Replies:
    5
    Views:
    655
    Bob Foster
    Dec 24, 2003
  3. Tim Uckun
    Replies:
    0
    Views:
    184
    Tim Uckun
    Aug 30, 2006
  4. aidy
    Replies:
    2
    Views:
    176
    Damjan Rems
    Aug 7, 2008
  5. Bob
    Replies:
    1
    Views:
    96
    Reinhard Pagitsch
    Jun 14, 2005
Loading...

Share This Page