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?

In reply to DBD::Sybase $sth->rows returns -1 by Perl300

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.