Equivalent DBI code from ADO

From: Carl Ringwall (cringwall_at_yahoo.com)
Date: 05/14/04 

Date: 14 May 2004 06:58:26 -0700

We would like to port some our web applications to our FreeBSD server.
 We are using ActiveState PerlScript on NT, and I can get how to
convert most of it using Apache::ASP and DBI, but I can't seem to
understand how to get the recordset paging/ prev/next link business to
work with DBI. I think I'm missing something- I thought I could put
this out to any DBI gurus that may have the time to gently point me in
the right direction. Here is my ADO code that allows for paging thru
data on a web page, using prev/next links. Is there a way to do this
with DBI? Any help or pointers to some online resources would be very
helpful to me.

I'm not trying to get a GPL vs M$ thing going here, please! I'm only
interested in cross-platform harmony.

Carl Ringwall
Journeyman Perl Hacker

###########sample code
use strict;

# Win32::ASP is for GetFormValues and print stmts

use Win32::ASP qw(:strict);
my $conn = $Server->CreateObject("ADODB.Connection");
my $rs = $Server->CreateObject("ADODB.Recordset");
my $dsn = "mydsn";

#receive current page from querystring
my $current_page = GetFormValue("current_page");

if (! $current_page) {
        $current_page = "1";
        }

#prepare sql statement
my $SQL = qq~
SELECT
field1, field2
FROM table
~;

# open db connection
#ado cursor- client
$rs->{CursorLocation} = 3;
$conn->Open($dsn);
$rs->Open($SQL, $conn,,,);

# define variables for counting in table
my $record_count;
my $page_count;
my $page_size = 10;
my $loop_counter = 0;

#loop thru the records
if (! $rs->{EOF}) {
#if not end of field
        $record_count = $rs->{RecordCount};
        #get the num of records
        $rs->{PageSize} = $page_size;
        #set the page size
        $page_count = $rs->{PageCount};
        #get the page count

$rs->{AbsolutePage} = $current_page;

%>
<HTML>
 <h3>Search Results</h3>

<% print "$record_count records matched your search, page
$current_page of $page_count"; %>

<%
my $next_page = $current_page + 1;
if ($next_page > $page_count){
        $next_page = 1;
        }
        
my $prev_page = $current_page - 1;
if ($prev_page < 1){
        $prev_page = $page_count;
        }

#build querystring to store recordset state

my $qs = "&"."querystring to maintain state of prev/next pages goes
here";
# append this querystring to prev/next links below
%>
<br>
<div id="prevnext"><span id="results">
<a href='searchresults.asp?current_page=<%=$prev_page.$qs%>'>Prev</a>
<a href='searchresults.asp?current_page=<%=$next_page.$qs%>'>Next</a>

<table>

<tr>
                <%
                while (! $rs->{EOF} and $loop_counter <= $page_size) {
                # loop while not eof and the counter is less OR eq than stated page
size
                #DISPLAY RESULTS HERE
                $loop_counter ++;
                $rs->MoveNext();

}
%>
</HTML>