I believe Allan JARDINE has done a tremendous contribution and deserves a decent return anyway. The mentioned example is basicly an AJAX server processing example and it is not a paging example. So, it may not be adequate to name it as a discrepancy. Nevertheless, it has to be fixed to continue our endevour.
Thee is a simple solution to this which requires a better understanding of server processing with a 'json' object and event handling.
Define a new(global) json variable above the $(document).ready function:
...
json jsonARS;
...
Get the json variable returned from the server and put it in the global jsonARS var.
Place in the $(document).ready function:
...
"sAjaxSource": "./server_processing.jsp",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some extra data to the sender */
aoData.push( { "name": "more_data", "value": "my_value" } );
$.getJSON( sSource, aoData, function (json) {
/* Do whatever additional processing you want on the callback, then tell DataTables */
//alert("OK");
jsonARS = json;
fnCallback(json);
} );
}
...
Handle the two_button bttons with:
...
$('#example_prev').click( function() {
...
get the osettings structure pointer to acces the _iDisplayLength
...
var oSettings = oTable.fnSettings();
...
use jsonARS to access the jsonARS.iTotalDisplayRecords
...
Please note that, I handle the below cases in event processing:
1- Do not go forward over the end of the total data.
2- Do not go backwards before the beginning of the total data.
3- Do not go further than the end of the displayed(selected) data.
etc.
The complete working code follows.
Cheers.
Ali R+
serverSideJQdtARS_3.jsp
-----------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<link rel="shortcut icon" type="image/ico" href="DataTables-1.7.5/media/images/favicon.ico" />
<title>DataTables example</title>
<style type="text/css" title="currentStyle">
@import "DataTables-1.7.5/media/css/demo_page.css";
@import "DataTables-1.7.5/media/css/demo_table.css";
</style>
<script type="text/javascript" language="javascript" src="DataTables-1.7.5/media/js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="DataTables-1.7.5/media/js/jquery.dataTables.js"></script>
<script type="text/javascript" charset="utf-8">
var jsonARS;
$(document).ready(function() {
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
// "sPaginationType": "full_numbers",
"sPaginationType": "two_button",
"aaSorting": [[0, 'desc'], [1, 'desc']],
"sAjaxSource": "./server_processing.jsp",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some extra data to the sender */
aoData.push( { "name": "more_data", "value": "my_value" } );
$.getJSON( sSource, aoData, function (json) {
/* Do whatever additional processing you want on the callback, then tell DataTables */
//alert("OK");
jsonARS = json;
fnCallback(json);
} );
},
"fnCallback": function ( json ) {
}
} );
$('#example_next').click( function() {
//alert('paginate click handler');
var oSettings = oTable.fnSettings();
// alert("iDisplayStart="+oSettings._iDisplayStart+
// "iDisplayLength="+oSettings._iDisplayLength+
// "iTotalRecords="+jsonARS.iTotalRecords+
// "iTotalDisplayRecords="+jsonARS.iTotalDisplayRecords
// );
if (((oSettings._iDisplayStart+ oSettings._iDisplayLength) < jsonARS.iTotalRecords) &&
(jsonARS.iTotalDisplayRecords == oSettings._iDisplayLength))
oSettings._iDisplayStart += oSettings._iDisplayLength;
oSettings.oApi._fnDraw( oSettings );
} );
$('#example_prev').click( function() {
//alert('paginate click handler');
var oSettings = oTable.fnSettings();
oSettings._iDisplayStart -= oSettings._iDisplayLength;
if (oSettings._iDisplayStart < 0) oSettings._iDisplayStart = 0;
oSettings.oApi._fnDraw( oSettings );
} );
} );
</script>
</head>
<body id="dt_example">
<div id="container">
<div class="full_width big">
<i>DataTables</i> server-side processing example
</div>
<H1>Live example</H1>
<DIV id=dynamic>
<TABLE id=example class=display border=0 cellSpacing=0 cellPadding=0>
<THEAD>
<TR>
<TH width="20%">Rendering engine</TH>
<TH width="25%">Browser</TH>
<TH width="25%">Platform(s)</TH>
<TH width="15%">Engine version</TH>
<TH width="15%">CSS grade</TH></TR></THEAD>
<TBODY>
<TR>
<TD class=dataTables_empty colSpan=5>Loading data from server
</TD>
</TR>
</TBODY>
</TABLE>
</DIV>
</div>
</body>
</html>
server_processing.jsp
---------------------
<%--
Document : server_processingARS.jsp
Created on : 04.?ub.2011, 17:53:53
Author : Ali Riza SARAL
--%>
<%@page import="java.sql.Connection"%>
<%@page import="atg.taglib.json.util.*"%>
<%@page import= "java.util.*"%>
<%@page import= "java.sql.*"%>
<%@page import= "org.apache.commons.lang.*"%>
<%
class Utilities {
Utilities() {
}
;
String escape_string(String input) {
return StringEscapeUtils.escapeHtml(input);
}
}
%>
<%
System.out.println("test arsssssssssssssssssssssss");
Utilities util = new Utilities();
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
String[] aColumns = {"engine", "browser", "platform", "version", "grade"};
/* Indexed column (used for fast and accurate table cardinality) */
String sIndexColumn = "id";
/* DB table to use */
String sTable = "ajax";
/* Database connection information */
String user = "root";
String password = "3391309";
String db = "jquerydb";
String server = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost/" + db,
user, password);
if (!conn.isClosed()) {
System.out.println("Successfully connected to "
+ "MySQL server using TCP/IP...");
}
} catch (Exception e) {
System.err.println("Exception: " + e.getMessage());
} finally {
// try {
// if (conn != null) {
// //conn.close();
// }
// } catch (SQLException e) {
// }
}
/*
* Paging
*/
String sLimit = "";
if (request.getParameter("iDisplayStart") != null
&& request.getParameter("iDisplayLength") != "-1") {
sLimit = "LIMIT " + util.escape_string(request.getParameter("iDisplayStart")) +
", " + util.escape_string(request.getParameter("iDisplayLength"));
}
/*
* Ordering
*/
String sOrder = "";
if (request.getParameter("iSortCol_0") != null) {
sOrder = "ORDER BY ";
for (int i = 0; i < Integer.valueOf(request.getParameter("iSortingCols")); i++) {
if (request.getParameter("bSortable_" + Integer.valueOf(request.getParameter("iSortCol_" + String.valueOf(i)))).equals("true")) {
sOrder += aColumns[Integer.valueOf(request.getParameter("iSortCol_" + String.valueOf(i)))] + " "
+ util.escape_string(request.getParameter("sSortDir_" + String.valueOf(i))) + ", ";
}
}
sOrder = sOrder.substring(0, sOrder.length()-2);
if (sOrder.equals("ORDER BY")) {
sOrder = "";
}
sOrder += " ";
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
String sWhere = "";
System.out.println("sSearch="+ request.getParameter("sSearch"));
if (request.getParameter("sSearch") != "") {
sWhere = "WHERE (";
for (int i = 0; i < aColumns.length; i++) {
sWhere += aColumns[i] + " LIKE '%" + util.escape_string(request.getParameter("sSearch")) + "%' OR ";
}
sWhere = sWhere.substring(0, sWhere.length()-3);
sWhere += ')';
}
/* Individual column filtering */
for (int i = 0; i < aColumns.length; i++) {
if (request.getParameter("bSearchable_" + String.valueOf(i)) == "true"
&& request.getParameter("sSearch_" + String.valueOf(i)) != "") {
if (sWhere == "") {
sWhere = "WHERE ";
} else {
sWhere += " AND ";
}
sWhere += aColumns[i] + " LIKE '%" + util.escape_string(request.getParameter("sSearch_" + String.valueOf(i))) + "%' ";
}
}
/*
* SQL queries
* Get data to display
*/
String strColumnNames = "";
for (int i=0;i < aColumns.length;i++){
strColumnNames = strColumnNames + ", "+ aColumns[i];
}
strColumnNames = strColumnNames.substring(1);
String sQuery = "SELECT "
+ strColumnNames
+ " FROM " + sTable
+ " "
+ sWhere
+ sOrder
+ sLimit;
System.out.println("sQuery="+sQuery);
Statement s = conn.createStatement();
s.executeQuery(sQuery);
/* Data set length after filtering */
ResultSet rs = s.getResultSet();
int count = 0;
while (rs.next()) {
String engineCol = rs.getString("engine");
System.out.println("engine = " + engineCol);
++count;
}
int iFilteredTotal = count;
rs.close();
s.close();
System.out.println("Selected count="+iFilteredTotal);
/* Total data set length */
s = conn.createStatement();
String sQuery2 = " SELECT COUNT(*) AS rowcount FROM " + sTable;
rs = s.executeQuery(sQuery2);
rs.next();
int iTotal = rs.getInt("rowcount");
rs.close();
s.close();
System.out.println("Total count="+iTotal);
/*
* Output
*/
String output = "{" +
"\"sEcho\" : "+ request.getParameter("sEcho")+ ", " +
"\"iTotalRecords\" : " + String.valueOf(iTotal) + ", " +
"\"iTotalDisplayRecords\" : " + String.valueOf(iFilteredTotal) + ", " +
// "\"iDisplayLength\" : " + "10" + ", " +
// "\"iDisplayStart\" : " + "10" + ", " +
"\"aaData\" : [" + "";
Object[][] aaData= new Object[iFilteredTotal][5];
s = conn.createStatement();
s.executeQuery(sQuery);
rs = s.getResultSet();
int rowNum=0;
while ( rs.next() )
{
System.out.println("rowNum="+ rowNum);
//if (rowNum > 3) break;
Object[] aRow = {"","","",0,""};
Object[] row = {"","","",0,""};
for ( int i=0 ; i<aColumns.length ; i++ )
{
System.out.println("i="+ i +" colVal="+ rs.getString( aColumns[i]));
if ( aColumns[i] == "version" )
{
/* Special output formatting for 'version' column */
row[i] = (rs.getInt( aColumns[i] )==0) ? "-" : rs.getInt(aColumns[i]);
}
else if ( aColumns[i] != " " )
{
/* General output */
row[i] = rs.getString( aColumns[i]);
}
}
aaData[rowNum] = row;
System.out.println("rowNum("+rowNum +") row[0]="+row[0].toString());
rowNum++;
}
rs.close();
s.close();
String valRow = " [ ";
for (int rowN = 0; rowN < iFilteredTotal; rowN++) {
System.out.println("rowN="+rowN);
for (int colN = 0; colN < aColumns.length; colN++) {
System.out.println(" colN="+colN);
if (aColumns[colN] == "version" )
valRow = valRow + " " + aaData[rowN][colN].toString() + " , ";
else if ( aColumns[colN] != " " )
valRow = valRow + "\"" + aaData[rowN][colN].toString() + "\" , ";
}
valRow = valRow.substring(0, valRow.length()-2);
valRow = valRow + " ], ";
System.out.println("valRow="+valRow);
output = output + valRow;
valRow = " [ ";
}
System.out.println("output="+output);
output = output.substring(0, output.length()-2);
output = output + " ] }";
out.print(output);
%>
