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

Hello,

I am using perl, v5.10.1 and DBD::Sybase (version 1.11) and trying to get number of rows returned from queries run against MS SQL Server (version 11)

The code I am using is a working code but we have moved from MySQL to MS SQL Server and the $sth->rows that used to give me number of rows returned by a DB query is now returning -1 for every query.

As far as I could find, $sth->rows should work for DBD::Sybase as well.

Here is the sample code (just to show the behavior):

#!/opt/apps/perl/perl5101/bin/perl
use strict;
use warnings;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use CGI qw/:standard/;

$| = 1;

my $db_server = "<hostname>";
my $db_user = "<user_name>";
my $password = '<password>';

my $query = new CGI;
print $query->header;

my $form_user = "something.like.this.com\\" . $query->param('site_user
+');
my $domain_user = param('user') || $form_user; #1
my @userNameString = split /\\+/, $domain_user; #2
my $userName = $userNameString[1]; #3

my $db_action;
my $sql;
my $sth;
my %widget=();
my $logfile = "LogFileName.log";
open(my $fh, ">>", $logfile) or die "Could not open file '$logfile' $!
+";
print $fh "Start of script: ". localtime()."\n";

my $dbh = DBI->connect("DBI:Sybase:server=$db_server",$db_user,$passwo
+rd) ;
die "unable to connect to server $DBI::errstr" unless $dbh;

my $sql18 = "SELECT role FROM table_1 WHERE user = ?";#Returns only 1 
+row
my $sth18 = $dbh->prepare($sql18);
$sth18->execute($userName);
$dbh->commit;
my $rowCount = $sth18->rows;
my $role;
while (my @row_18 = $sth18->fetchrow_array) {
    $role = $row_18[0];
}

print "<html>\n";
    print "<head>\n";
    print "</head>\n";
print "<body>\n";
    print "<form id=\"form1\" method=\"post\">\n";
    print "The role of user \"$userName\" is: $role\n";
    print "</br>\n";
    print "The number of rows returned is: $rowCount\n";
    print "</br>\n";
    print "<input type=\"submit\" name=\"submitButton\" value=\"Submit
+\" id=\"button1\"/>\n";
    print"<input type=\"submit\" name=\"resetButton\" value=\"Reset\" 
+id=\"button2\"/>\n";
    print "</form>\n";
print "</body>\n";
print "</html>\n";

[download]

In output I see "The number of rows returned is: -1"

In code I am using $sth->rows at 6 places for 6 different queries and it still gives correct row count on existing code which is for MySQL.

Can you please help me to find out what am I missing here?

Replies are listed 'Best First'.
Re: DBD::Sybase $sth->rows returns -1
by choroba (Cardinal) on Jan 06, 2016 at 17:25 UTC
    The documentation is pretty clear:
    Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

    You called ->rows before fetching all the rows. 

    ($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,

    [download]
[reply]
[d/l]
[select]
Re: DBD::Sybase $sth->rows returns -1
by hotchiwawa (Scribe) on Jan 06, 2016 at 18:03 UTC
    Hi Perl300 :)

    You don't need a Commit on Select statements.

    Don't forget to close the connection with $dbh->disconnect;
    I think it's a good practice to set the RaiseError flag.

    Example: 
    my $dbh = DBI->connect('DBI:Oracle:payroll', 
{RaiseError => 1}) or die "Couldn't connect to database: " . DBI->errs
+tr;

    [download]
    Peace
[reply]
[d/l]
[select]
Re: DBD::Sybase $sth->rows returns -1
by Perl300 (Friar) on Jan 06, 2016 at 18:13 UTC
    Thank you choroba and hotchiwawa. My apology for not checking documentation for DBI. (I was looking around DBD::Sybase)

    Would it be possible to use sth->rows or any other method to find the number of rows for select queries? I see that one option is to run each query twice, once the normal query needed and second time by using SELECT COUNT(*) FROM .... by keeping part after "FROM" same and then using $sth->rows on it. I am trying to find the better option.

    I tried to call sth->rows after fetching all the rows, i.e. after 

    while (my @row_18 = $sth18->fetchrow_array) {
    $role = $row_18[0];
}

    [download]
    but it still returns -1.

    How can I call sth->rows after fetching all the rows?

[reply]
[d/l]
[select]

      You could use

      my $rowCount;
my $role;
while (my @row_18 = $sth18->fetchrow_array) {
  $role = $row_18[0];
  ++$rowCount;
}

      [download]

      Are you using the while loop to get the last record of many ?

      poj
[reply]
[d/l]
        Thank you for your suggestion poj.

        I am using while loop to get all the fetched records. In select queries I am using your suggestion where ever possible or else writing another query with select count(*)

        But even in case of UPDATE queries I am getting -1 for $sth->rows and trying to figure out how to handle that.

[reply]
Re: DBD::Sybase $sth->rows returns -1
by hotchiwawa (Scribe) on Jan 06, 2016 at 19:12 UTC
    I suggest you to use objects by reference (really quicker), not by value, so fetchrow_hashref or fetchall_hashref or fetch_arrayref...
    my $sth = $dbh->prepare('SELECT firstname, lastname FROM user WHERE ag
+e >= ? ORDER BY age desc LIMIT 200');
$sth->execute(...);

my $row = undef;
while ($row = $sth->fetchrow_hashref) 
{
   print $row->{ firstname }, ' ', $row->{ lastname }, "\n";
}

    [download]
    Rem: LIMIT the maximum rows returned, if you can.
    Edit: code updated
[reply]
[d/l]
      Edit: it's $row->{..} not $data->{..}, I renamed the variable :/
[reply]

        As a general rule, it's much better to edit or update your original post (if you're not Anonymous!) rather than to add another node which may be overlooked and which just clutters up the place. Please see How do I change/delete my post?.

        Give a man a fish:  <%-{-{-{-<

[reply]
[d/l]

        FYI you can edit your post by clicking on its title.


        The way forward always starts with a minimal test.
[reply]

Back to Seekers of Perl Wisdom