in reply to Perl DBI MS SQL Question

Show some code!

It seems to me that LIMIT should work. I've never messed with MS SQL but when I did some stuff in Access a while back I seem to recall that LIMIT worked on that.

As far as rows returning a negative number I would venture to guess that MS SQL doesn't support something needed to make that function work so it returns -1. You should read the DBI man page for that info.

But again, show us some code and we will be able to help you much more effectively.

Lobster Aliens Are attacking the world!

Replies are listed 'Best First'.
Re: Re: Perl DBI MS SQL Question
by peppiv (Curate) on Jul 12, 2002 at 17:03 UTC
    Yes, of course. My apologies. But again, LIMIT does not work in MSSQL - command not found.

        sub DoSearchActive {
    
     my $dbh = DBI->connect('DBI:mysql:new_db','username','password');
     my $limit_3 = param("limit_3");
     my $limit = param("limit");
     $limit = ($limit_3 + $limit);
     my $spread = ($limit + 1);
     my $spread_2 = ($limit + 10);
     
     my $types = $dbh->selectrow_array('SELECT COUNT(status) FROM appl
+icant WHERE status = "Active"');
     if ($spread_2 >= $types){$spread_2 = $types}
     if ($spread >= $types){$spread = $types}
     print qq(<p style="margin-left: 0; margin-top: 20"><font size="2"
+ face="Arial"><b>There are $types applications listed as 'Active'.<br
+>Results $spread - $spread_2 shown.</b></font></p>);

     print qq($table);
     print qq($table_headers);
     
     my $sth = $dbh->prepare('SELECT * FROM applicant WHERE status = "
+Active" ORDER BY date_col DESC LIMIT ?,10'); 
     $sth->bind_param(1, $limit, DBI::SQL_INTEGER);
     $sth->execute($limit) or die $sth->errstr; 

     while (my @result = $sth->fetchrow_array()) {
        if ($result[21] eq '' || $result[21] eq '0000-00-00') {$shade 
+= $default}
        else {$shade = $default2}
        if ($result[32] eq 'checked') {$fav = $something}
        else {$fav = ''}
        print qq(<tr><td><form method="POST" action="applicant_trackin
+g.pl"><input type="radio" name="selection" value="all"><input type="h
+idden" name="id" value="$result[0]"><br><input type="submit" value="G
+O"></form></td><td bgcolor="$shade"><font size=2 face="Arial">$result
+[20]</font></td><td bgcolor="$shade"><font size="2" face="Arial">$res
+ult[15]</font></td><td bgcolor="$shade"><font size="2" face="Arial">$
+result[16]</font></td><td bgcolor="$shade"><font size="2" face="Arial
+">$result[1], $result[2]</font></td><td bgcolor="$shade"><font size="
+2" face="Arial">$result[28]<br>$result[6]</font></td><td bgcolor="$sh
+ade"><font size="2" face="Arial">$result[11]-$result[12]-$result[13]<
+/font></td><td bgcolor="$shade"><font size="2" face="Arial">@result[1
+7, 18, 19]</font></td><td bgcolor="$shade"><font size="2" face="Arial
+">$result[61]<br>$result[39]<br>$fav</font></td><td><font size="2" fa
+ce="Arial">$result[31]$result[62]$result[63]$result[64]$result[65]</f
+ont></td></tr>\n);
        }  
        print qq(<tr><td colspan="10"><center><form method="POST" acti
+on="applicant_tracking.pl"><input type="hidden" name="limit_3" value=
+"10"><input type="hidden" name="limit" value="$limit"><input type="su
+bmit" name="action11" value="Next"></form></center></td></tr>);
        
     print qq(</table>); 
     
     if ($sth->rows == 0) {
         print qq(<br>There are no employees in the Active folder.\n\n
+);
         }      

     $sth->finish();
     $dbh->disconnect;
     
     &DoSomethingElse;    
}

    [download]


    This allowed me to return results 1-10, then with the click of a button return 11-20, and so forth. jeffa actually helped with the incremental code. Thanks jeffa++

    peppiv
[reply]
[d/l]

In Section Seekers of Perl Wisdom