Can load server-side data w/ MSSQL Server, but pagination, ordering, and search do not work.

Xythantiops

Hello, I am new to DataTables and I have run into a problem getting pagination, ordering, and searching working with server-side data loaded from an MSSQL database. I followed the example on the page linked below and I was able to successfully load 10 records from my database. It also successfully lists the number of records in the table (44,198). However, none of the other functionality seems to work. If I try to change the number of entries displayed, sort the columns, use the search, or switch pages, then it will display “Processing…” for a second or two and nothing changes.

I know that the MSSQL connection code works differently than the standard MySQL examples, so I assume that the problem lies there and I want to figure out what is causing the issue before I try rewriting anything. Here is the server-side code that I am testing:

<?PHP
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "sourceCodeId";
   
/* DB table to use */
$sTable = "sourceCodes";
 
/* Database connection information */
// Connection information removed
$gaSql['user']      = "";
$gaSql['password']  = "";
$gaSql['db']        = "";
$gaSql['server']    = "";
 
/*
* Columns
* If you don't want all of the columns displayed you need to hardcode $aColumns array with your elements.
* If not this will grab all the columns associated with $sTable
*/
$aColumns = array(
            '0' => 'sourceCodeId',
            '1' => 'sourceCode',
            '2' => 'countryId',
            '3' => 'promotionGroupId',
            '4' => 'productLineId'
);

/*
 * ODBC connection
 */
$connectionInfo = array("UID" => $gaSql['user'], "PWD" => $gaSql['password'], "Database"=>$gaSql['db'],"ReturnDatesAsStrings"=>true);
$gaSql['link'] = sqlsrv_connect( $gaSql['server'], $connectionInfo);
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
       
   
/* Ordering */
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) ) {
    $sOrder = "ORDER BY  ";
    for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
        if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
            $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                ".addslashes( $_GET['sSortDir_'.$i] ) .", ";
        }
    }
    $sOrder = substr_replace( $sOrder, "", -2 );
    if ( $sOrder == "ORDER BY" ) {
        $sOrder = "";
    }
}
   
/* Filtering */
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_GET['sSearch'] )."%' OR ";
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}
/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
    if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )  {
        if ( $sWhere == "" ) {
            $sWhere = "WHERE ";
        } else {
            $sWhere .= " AND ";
        }
        $sWhere .= $aColumns[$i]." LIKE '%".addslashes($_GET['sSearch_'.$i])."%' ";
    }
}
   
/* Paging */
$top = (isset($_GET['iDisplayStart']))?((int)$_GET['iDisplayStart']):0 ;
$limit = (isset($_GET['iDisplayLength']))?((int)$_GET['iDisplayLength'] ):10;
$sQuery = "SELECT TOP $limit ".implode(",",$aColumns)."
    FROM $sTable
    $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
    (
        SELECT $sIndexColumn FROM
        (
            SELECT TOP $top ".implode(",",$aColumns)."
            FROM $sTable
            $sWhere
            $sOrder
        )
        as [virtTable]
    )
    $sOrder";
 
$rResult = sqlsrv_query($gaSql['link'],$sQuery) or die("$sQuery: " . sqlsrv_errors());

$sQueryCnt = "SELECT * FROM $sTable $sWhere";
$rResultCnt = sqlsrv_query( $gaSql['link'], $sQueryCnt ,$params, $options) or die (" $sQueryCnt: " . sqlsrv_errors());
$iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

$sQuery = " SELECT * FROM $sTable ";
$rResultTotal = sqlsrv_query( $gaSql['link'], $sQuery ,$params, $options) or die(sqlsrv_errors());
$iTotal = sqlsrv_num_rows( $rResultTotal );
   
$output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
);
   
while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
    $row = array();
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        if ( $aColumns[$i] != ' ' ) {
            $v = $aRow[ $aColumns[$i] ];
            $v = mb_check_encoding($v, 'UTF-8') ? $v : utf8_encode($v);
            $row[]=$v;
        }
    }
    If (!empty($row)) { $output['aaData'][] = $row; }
}

echo json_encode( $output );

<?php
>
```
?>


And this is the AJAX call that I made to generate the table content:

```js
    $(document).ready(function() {
        $( '#example' ).dataTable({
            "processing":   true,
            "serverSide":   true,
            "ajax":         {
                "url":  "AJAX/ajaxSourceCodes4.php"
            }
        });
    });

I did run the code through the debugger and here is my debugging data.

http://debug.datatables.net/anunet

Let me know if there is anything that I can clarify and thank you in advance for your help,

-Jeffrey

larsonator

What you could do is try and output the actual sql code that gets generated by your script,

    echo $sQueryCnt;
    exit;

you will see the sql output in the response section of your Network traffic,
Analise this with the different parameters sent by dataTables various functions, and you might see what is going wrong.

Xythantiops

Ok, I had trouble echoing the queries, but I was able to append them along with the some other relevant data to the output array and view that. In this example, I had chosen to display 100 records per page, search for records containing DA, and view the 442nd page of those records and none of those search terms were reflected in the query:

// Slashes were removed for readability
SELECT TOP 10 sourceCodeId,sourceCode,countryId,promotionGroupId,productLineId FROM sourceCodes WHERE sourceCodeId NOT IN (SELECT sourceCodeId FROM (SELECT TOP 0 sourceCodeId,sourceCode,countryId,promotionGroupId,productLineId FROM sourceCodes)as [virtTable])

When I look at the output of the $_GET array, I did see values for some of my search terms:

"start":"44100",
"length":"100",
"search":{"value":"DA","regex":"false"}

However, when I checked the variables that were actually being used to generate the query, this is what I found:

"sSearch":null,
"limit":10,
"sTable":"sourceCodes",
"sWhere":"",
"sIndexColumn":"sourceCodeId",
"sOrder":""

It seems like the issue is that even though the proper values are being passed, they are using different variables names than what the server-side code is expecting, so the code is expecting iDisplayLength, but that value is actually being sent as length. At least when I change those names, then the functionality starts to work, so it looks like I need to rewrite the server-side code to use the actual names being passed. I'll work on that and then post the results either today or Monday, so if anyone else is using MSSQL / SQL Server, then they will at least have a starting point that works.