Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

pagination script to work with SQL server

by pearlgirl (Initiate)
on May 23, 2013 at 20:57 UTC ( #1035024=perlquestion: print w/ replies, xml ) Need Help??
pearlgirl has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone, I'm using this script for pagination:
declare \@limit int declare \@offset int set \@offset = $offset; set \@limit = $limit; declare \@idxini int declare \@idxfim int select \@idxfim = \@offset * \@limit select \@idxini = \@idxfim - (\@limit-1); WITH paging AS ( SELECT ROW_NUMBER() OVER (order by table_ID) AS rowid, * FROM table ) select * from paging where rowid between \@idxini and \@idxfim order by rowid; ; END_ROW2
I need to add joins, and specify other columns besides rowid and *, but it stops working ( no results returned) as soon as I modify it from the format below. It HAS to be 1 table and it HAS to be "select rowid, *"... Even just removing the * stops it from working. I tried running different variations of the script in SQL Server (joining other tables, changing columns, etc.) and it works just fine. It's only within the perl file that it starts acting finicky. I have no idea why. Can someone help me, please? Any recommendations on other pagination scripts that work with SQL Server are welcome too. Thank you!

Comment on pagination script to work with SQL server
Download Code
Re: pagination script to work with SQL server
by erix (Vicar) on May 23, 2013 at 21:05 UTC

    pearlgirl where is the perl?

      I tried debugging , and I used SQL Profiler too. The sql is correct. When I copy and paste it into the SQL Server it runs perfectly fine. This is the perl code:
      my $sql = <<END_ROW2; declare \@limit int declare \@offset int set \@offset = $offset; set \@limit = $limit; declare \@idxini int declare \@idxfim int select \@idxfim = \@offset * \@limit select \@idxini = \@idxfim - (\@limit-1); WITH paging AS ( SELECT ROW_NUMBER() OVER (order by column_ID) AS rowid FROM Table1 N RIGHT OUTER JOIN Table2 NE ON NE.Event_Type = N.Event_Type ) select * from paging where rowid between \@idxini and \@idxfim order by rowid; ; END_ROW2 my $sth = $a_dbh->prepare($sql); my $rc = $sth->execute(); if (!$rc) { &pl_fatalSQLError($sql,$DBI::errstr); } my $rowArrayref=$sth->fetchall_arrayref({}); # Showing page number with link for(my $i=1;$i<$pagenum;$i++) { print "<a href='page.pl?PageType=$type&page=$i'>$i</a> "; } print "<br>"; # Display result page wise foreach my $row(@$rowArrayref) { print "<br>".$$row{'column_ID'}; } print "</p>";
        oops "Anonymous" was me :) Thought I was logged in
Re: pagination script to work with SQL server
by Anonymous Monk on May 23, 2013 at 21:18 UTC

    It's only within the perl file that it starts acting finicky. I have no idea why. Can someone help me, please?

    Show that, use Data::Dump to examine this "sql script" before sending it to the server, and you'll see what the problem is -- Basic debugging checklist

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1035024]
Approved by ww
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (10)
As of 2014-10-31 18:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (222 votes), past polls