I assume that you use Perl, otherwise you will not post it here, ;-) (I also assume you use Win32::ODBC).

If I didn't misunderstand your "paginate", I think you mean batch fetching, which is a standard term being used to mean "to fetch multiple rows by issuing one operation".

I don't think Win32::ODBC support this, however you can wrap around its FetchRow function, and create your own FetchRows.

However you have to remember that this gives you the feeling of batch fetching, but it does not improve performance at all, as it does not reduce network travel time, as real batch fetching does.

If you just want "paginate", this function does give it to you. An advantage of this implementation is that it is generic, you do it once, it can be used for any similar purposes without modification.

use Win32::ODBC;
use Data::Dumper;

use strict;

my $Data = new Win32::ODBC("myDSN") || die "failed\n";
$Data->Run("DROP TABLE test1");
$Data->Run('CREATE TABLE test1(a number, b char(1))');
for (1..50) {
        my $statement = "INSERT INTO test1 VALUES($_, ' ')";
        $Data->Run($statement);
}
$Data->Sql("SELECT * FROM test1");

while (1) {
    my @Rows = FetchRows($Data, 4);#fetch 4 rows each time 
    print Dumper(\@Rows);
    last if $Rows[0];#$Rows[0] is used to indicate whether we reached 
+the end of active dataset
}

sub FetchRows {
    my ($Connection, $Rows) = @_;
    my @Rows;
    push @Rows, 0; #
    for (1..$Rows) {
        if ($Data->FetchRow()) {
            my %hash = $Data->DataHash();
            push @Rows, \%hash;
        } else {
            $Rows[0] = 1;
        }
    }
    return @Rows;
}
[download]

Update:

Thanks, poj. No that is not correct. That comment is added on fly, and I fixed it. However THE SOLUTION ITSELF IS TESTED, so no worry.