DB2 SQL Pagination with a variable BETWEEN

Question

So, I have to do a pagination for a site database using DB2, showing 10 results per page. However, when I use FETCH FIRST 10 ROWS ONLY, all the pages show the same 10 first results. I searched and figured I had to use BETWEEN 0 AND 9 with ranging variables, but no matter what I do I can't seem to get the SQL code right. I always get the error message odbc_fetch_row(): supplied argument is not a valid ODBC result resource.

I created variables for the offset:

//offset for the query
$limit = 10;
$offset = ($currentpage - 1)  * $limit;
$start = $offset + 1;
$end = min(($offset + $limit), $total);
$offsetend = $end - 1;

And got the info from the DB:

$busca="";
    if(isset($_GET['palavra']))
    {
        $busca=($_GET['palavra']);
    }
    $SQL1 = "SELECT PALAVRA.CODPAL, PALAVRA.NMPAL, PALAVRA.NMLIBTRANS 
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY PALAVRA.NMPAL ASC) AS ROWNUM
    FROM MAXADM.PALAVRA WHERE PALAVRA.NMPAL LIKE '$busca%') AS P 
    WHERE P.ROWNUM BETWEEN $offset AND $offsetend";

    $rs1=getInfo($SQL1);
    while(odbc_fetch_row($rs1))
    {
    $cod_palavra = odbc_result($rs1,"CODPAL");
    $palavra = odbc_result($rs1,"NMPAL");
    $librastrans = odbc_result($rs1,"NMLIBTRANS");
    }

What am I doing wrong? Thank you so much!

Answer 1

As it is currently written, the subselect with the correlation name P contains only one column, ROWNUM:

SELECT PALAVRA.CODPAL, PALAVRA.NMPAL, PALAVRA.NMLIBTRANS 
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY PALAVRA.NMPAL ASC) AS ROWNUM
    FROM MAXADM.PALAVRA WHERE PALAVRA.NMPAL LIKE '$busca%'
) AS P 
WHERE P.ROWNUM BETWEEN $offset AND $offsetend

so you obviously cannot select PALAVRA.CODPAL, PALAVRA.NMPAL, PALAVRA.NMLIBTRANS from it.

Try rewriting the query as

SELECT P.CODPAL, P.NMPAL, P.NMLIBTRANS 
FROM (
  SELECT 
    PALAVRA.CODPAL, PALAVRA.NMPAL, PALAVRA.NMLIBTRANS,
    ROW_NUMBER() OVER (ORDER BY PALAVRA.NMPAL ASC) AS ROWNUM
  FROM MAXADM.PALAVRA WHERE PALAVRA.NMPAL LIKE '$busca%'
) AS P 
WHERE P.ROWNUM BETWEEN $offset AND $offsetend