The example I created using ROWNUMBER() OVER() had some limitations.
Here is another simple example of pagination that uses a scrollable
cursor rather than the OLAP functions.
This is certainly not the only way to paginate with DB2. I just thought
it might be useful to have a working example in the WEB400 archive.
The script assumes a DB2 file called MYLIB/MYEOP. The MYEOP file
contains four fields: EOPYEAR, EOPPRD, EOPBEGDT, EOPCLSDT.
EOPYEAR = year
EOPPRD = period
EOPBEGDT = begin date
EOPCLSDT = close date
The file holds the begin and close dates for each business period of
every year. I want to scroll back and forth through the file, seeing the
information for one year at a time. (We happen to have 13 business
periods in each year. So I want to see 13 records at a time.)
The script uses a an ordinary SELECT statement to rows records from the
file. However, when executing the statement, the script requests a
scrollable cursor.
$query = "SELECT EOPYEAR, EOPPRD, EOPBGSDT, EOPCLSDT FROM
DOTDATA.EOPMAST";
$result = db2_exec($conn, $query, array('cursor' =>
DB2_SCROLLABLE));
The script then uses a for loop to get individual rows from the result
set. The variable $rn is the row on which we want to start the loop. The
variable $limit is the row on which we want to end the loop.
$i=$rn;
for ($i=$rn; $i<=$limit; $i++)
{
$row = db2_fetch_both($result, $i);
echo "<tr><td>".$row["EOPYEAR"]."</td>";
echo "<td>".$row["EOPPRD"]."</td>";
echo "<td>".$row["EOPBGSDT"]."</td>";
echo "<td>".$row["EOPCLSDT"]."</td></tr>";
}
The script also contains two forms. The form buttons let the user post
requests to scroll forward to the "next" records or backward to the
"previous" records. The session variable $_SESSION["rn"] stores the row
number across scrolling requests.
The only limitation I know about with this approach is it wouldn't work
with the db2_prepare function. (If you can figure out how to get it to
work with db2_prepare, please share!)
The complete script is provided below. Hope this helps.
Thanks,
Kelly
<?php
session_start();
switch ($_POST["scroll"])
{
case "next":
$_SESSION["rn"] = $_SESSION["rn"] + 13;
unset($_POST["scroll"]);
break;
case "previous":
$_SESSION["rn"] = $_SESSION["rn"] - 13;
if ($_SESSION["rn"] < 1)
{
$_SESSION["rn"] = 1;
}
unset($_POST["scroll"]);
break;
default:
$_SESSION["rn"] = 1;
break;
}
$rn = $_SESSION["rn"];
$limit = ($rn + 12);
?>
<html>
<head>
<title>Pagination Example</title>
<!--Optional formatting for the table and the buttons. -->
<style type="text/css">
body {
text-align: center;
}
table {
margin: 10px;
border: 1px solid #000000;
}
th, td {
border: 1px solid #dedede;
padding: 2px 4px 2px 4px;
}
form {
display: inline;
text-align: center;
}
input {
width: 100px;
}
</style>
</head>
<body>
<h2>Years and Periods</h2>
<table>
<tr>
<th>Year</th>
<th>Period</th>
<th>Begin</th>
<th>End</th>
</tr>
<?php
$conn = db2_connect("", "", "");
$query = "SELECT EOPYEAR, EOPPRD, EOPBGSDT, EOPCLSDT FROM
DOTDATA.EOPMAST";
$result = db2_exec($conn, $query, array('cursor' =>
DB2_SCROLLABLE));
$i=$rn;
for ($i=$rn; $i<=$limit; $i++)
{
$row = db2_fetch_both($result, $i);
echo "<tr><td>".$row["EOPYEAR"]."</td>";
echo "<td>".$row["EOPPRD"]."</td>";
echo "<td>".$row["EOPBGSDT"]."</td>";
echo "<td>".$row["EOPCLSDT"]."</td></tr>";
}
db2_close($conn);
?>
</table>
<form method="post" action="pagination2.php">
<input type="hidden" name="scroll" value="previous" />
<input type="submit" value="Previous" />
</form>
<form method="post" action="pagination2.php">
<input type="hidden" name="scroll" value="next" />
<input type="submit" value="Next" />
</form>
</body>
</html>
midrange.com
