server side per page restricting data.

rakesh_pathak

Hi, i'm trying to make a server side request, want to restrict 25 row per page in data table.
have tried options- start, length, paging but no hope. can't 'LIMIT' the data in query as the database does not support.

tangerine

Are you using pageLength?
https://datatables.net/reference/option/pageLength

SamirSilva

You mean that your database does not support limit the results of the query? What database are you using?

rakesh_pathak

Samir
Its a sybase database

rakesh_pathak

.$( document ).ready(function() {
$('#backup_data').DataTable({
"bProcessing": true,
"serverSide": true,
"ajax":{
url :"response.php", // json datasource
type: "post", // type of method ,GET/POST/DELETE
//"start": 0,
//"length": 25,
error: function(){
$("#backup_data_processing").css("display","none");
}
},
"columns": [
{ "data": "StartDateTime"},
{ "data": "EndDateTime"},
{ "data": "JobDurationInMin"},
{ "data": "jobId"},
{ "data": "clientName"},
{ "data": "policyName"},
{ "data": "statusCode"},
{ "data": "status"},
{ "data": "ScheduleNames"}
]
});
});

rakesh_pathak

Tangerine
Have tried all of them. but no luck.

rakesh_pathak

`$( document ).ready(function() {
$('#backup_data').DataTable({
"bProcessing": true,
"serverSide": true,
"ajax":{
url :"response.php", // json datasource
type: "post", // type of method ,GET/POST/DELETE
//"start": 0,
//"length": 25,
error: function(){
$("#backup_data_processing").css("display","none");
}
},
"columns": [

        { "data": "A"},
        { "data": "B"},
        { "data": "C"},
        { "data": "D"}

    ]
    });   

});`

kthorngren

This Stack Overflow thread discusses some Sybase options for queries that support paging.

Kevin

rakesh_pathak

Kevin, i have already tried with commands like 'TOP' and 'ROWCOUNT'. Here senario is bit different, its restricting the row per page but printing same result for other pages too i.e. the record remains the same . No new rows are displayed.

rakesh_pathak

JSON DATA:--
{draw: "1", recordsTotal: 25, recordsFiltered: 10000,…}
draw: "1"
recordsTotal: 25
recordsFiltered: 10000
data: [{StartDateTime: "04/03/2020 04:30", EndDateTime: "04/03/2020 14:46", JobDurationInMin: "616",…},…]
0: {StartDateTime: "04/03/2020 04:30", EndDateTime: "04/03/2020 14:46", JobDurationInMin: "616",…}
StartDateTime: "04/03/2020 04:30"
EndDateTime: "04/03/2020 14:46"
JobDurationInMin: "616"

jobId: "8376149"

{draw: "2", recordsTotal: 25, recordsFiltered: 10000,…}
draw: "2"
recordsTotal: 25
recordsFiltered: 10000
data: [{StartDateTime: "04/03/2020 04:30", EndDateTime: "04/03/2020 14:46", JobDurationInMin: "616",…},…]
0: {StartDateTime: "04/03/2020 04:30", EndDateTime: "04/03/2020 14:46", JobDurationInMin: "616",…}
StartDateTime: "04/03/2020 04:30"
EndDateTime: "04/03/2020 14:46"
JobDurationInMin: "616"
jobId: "8376149"

As you can see, 2 draw same data.

kthorngren

The place to debug that is in your server script. Are you using a Datataables provided script or your own custom script? You will need to debug the query to make sure it is correct and fetching the expected data.

Kevin

rakesh_pathak

Kevin
but its interesting, when i'm increasing page length from 10-25 data r coming properly. so its i guess problem with the paging.

SamirSilva

Are you trying to use pagination with server side processing?
For example on the first load retrieve the first 25 records, if you click on the next page do another call to the backend and bring the next set of records.

Or you are trying to do a single call and retrieve the whole data and use client side pagination?

SamirSilva

I had a similar issue with server side processing pagination.
This should be something you workaround in the backend, on the class you use for Datatables.

The following is the query that I build in the backend based on what I receive from the Datatables plugin.
It seems that Sybase has the ROW_NUMBER() function as well.

# DB2 does not have full support for the LIMIT and OFFSET commands
# As a work around, an extra loop using ROW_NUMBER is needed

# columns: is the list of column names I'm passing to the function
# orderClause: build the clause based on what you receive from the Datatables request, iterate the object by column since it comes in this format: order[0][column] 2,order[0][dir] asc, order[1][column]   3, order[1][dir]    asc, order[2][column]   4
tableName: I pass this table name to the function based on what table is loaded
# whereClause: as the order clause, iterate the request to get all the column search values and add those to your variable.

start = int(self.request_values['start']) // This comes from the Datatables request
length = int(self.request_values['length']) // This comes from the Datatables request
limit = start
offset = limit + length
query = """ SELECT *
                FROM (
                    SELECT {0},ROW_NUMBER() OVER({1}) AS ROW_NUMBER
                    FROM {2} {3}
                ) PAGING
                WHERE PAGING.ROW_NUMBER > {4} AND PAGING.ROW_NUMBER <= {5}""".format(columns,orderClause,tableName,whereClause,limit,offset)

rakesh_pathak

@SamirSilva exactly, i'm trying to achieve paging with server side processing .

rakesh_pathak

Kevin here is my server side script(test)
`<?php
//include connection file
include_once("connection.php");

// initilize all variable
$params = $columns = $totalRecords = $data = array();
$params = $_REQUEST;

$columnIndex = $params['order'][0]['column']; // Column index
$columnName = $params['columns'][$columnIndex]['data']; // Column name

$where = $sqlTot = $sqlRec = "";

// check search value exist
if( !empty($params['search']['value']) ) {   
    //$where .=" WHERE ";
    $where .=" AND (StartDateTime LIKE '%".$params['search']['value']."%' ";    
    $where .=" OR EndDateTime LIKE '%".$params['search']['value']."%' ";
    $where .=" OR JobDurationInMin LIKE '%".$params['search']['value']."%' ";
    $where .=" OR jobId LIKE '%".$params['search']['value']."%' ";
    $where .=" OR domain_JobArchive.clientName LIKE '%".$params['search']['value']."%' ";
    $where .=" OR domain_JobArchive.policyName LIKE '%".$params['search']['value']."%' ";
    $where .=" OR domain_JobArchive.statusCode LIKE '%".$params['search']['value']."%' ";
    $where .=" OR status LIKE '%".$params['search']['value']."%' ";
    $where .=" OR ScheduleNames LIKE '%".$params['search']['value']."%' )";
}

// getting total number records without any search
$sql = "SELECT DISTINCT TOP ".$params['length']." START AT ".$params['start']."
DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.startTime) as DATETIME), 'dd/mm/yyyy hh:mm') as StartDateTime,
DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'dd/mm/yyyy hh:mm') as EndDateTime,
NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime)/60 as 'JobDurationInMin',
nb_JobFiles.jobId,domain_JobArchive.clientName,domain_JobArchive.policyName,domain_JobArchive.statusCode,   
CASE 
WHEN (JobDurationInMin <= 1440 and domain_JobArchive.statusCode <= 1) THEN 'SUCCESS'
WHEN (domain_JobArchive.scheduleName IN ('Monthly-Full','Weekly-Full') and domain_JobArchive.statusCode <= 1) THEN 'SUCCESS'
WHEN (domain_JobArchive.policyname like '%l3%' and JobDurationInMin <= 10080 and domain_JobArchive.statusCode <= 1) THEN 'SUCCESS'
ELSE 'FAILED'
END AS 'status',
    CASE
    WHEN (domain_JobArchive.policyname like '%LX%' and domain_JobArchive.scheduleName = ' ')  THEN 'LINUX-ParentJob'
    ELSE domain_JobArchive.scheduleName
    END AS 'ScheduleNames'
FROM nb_JobFiles,domain_JobArchive,domain_job,lookup_JobStatusCode,lookup_JobType
WHERE
domain_JobArchive.Id=nb_JobFiles.jobId
and domain_JobArchive.type=domain_job.type
and domain_JobArchive.type in (0,1,4,6,7,20,22,28,35,100)
and lookup_JobStatusCode.id=domain_JobArchive.statusCode
and domain_Job.policyId=domain_JobArchive.policyId
AND domain_JobArchive.policytype != '4'
AND domain_JobArchive.clientName NOT IN ('cbicdg-gehenna')
AND domain_JobArchive.scheduleName NOT IN ('Default-Application-Backup-data','Default-Application-Backup','Default_24x7_Window')
AND domain_JobArchive.id NOT IN (select id from domain_JobArchive where policyName LIKE '%LX%' AND scheduleName != '')
AND UTCBigIntToNomTime(domain_JobArchive.endtime) >= DATEADD(DAY, -1, GETDATE())
ORDER BY nb_JobFiles.jobId";

$sqlTot .= $sql;
$sqlRec .= $sql;
//concatenate search sql if value exist
if(isset($where) && $where != '') {

    $sqlTot .= $where;
    $sqlRec .= $where;
}

//$sqlRec .=  " ORDER BY ". $columnName."  ".$params['order'][0]['dir']." SET ROWCOUNT ".$params['length'];

//echo $sqlRec;

$queryTot = odbc_exec($conn, $sqlTot) or die("database error:". odbc_error($conn));


$totalRecords = odbc_num_rows($queryTot);

$queryRecords = odbc_exec($conn, $sqlRec) or die("error to fetch backup data");

//iterate on results row and create new index array of data
while( $row = odbc_fetch_array($queryRecords) ) { 
    $data[] = $row;
}   

$json_data = array(
        "draw"            => ( $params['draw'] ),   
        "recordsTotal"    => ( $totalRecords ),  
        "recordsFiltered" => ( $totalRecords ),
        "data"            => $data   // total data array
        );

echo json_encode($json_data);  // send data as json format

<?php >` ?>