Server-side processing with AJAX and MS SQLSRV

dojocasino

I am running into difficulty when attempting to use server-side processing with MS SQL Server 2008 and AJAX. The browser alerts the following error:

DataTables warning: tableid=test-datatable - SQLSTATE[42000]:[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'OFFSET'

My JavaScript is as follows:

$(document).ready(function () {
                $('#test-datatable').dataTable({
                    ajax: {
                        url: "includes/EBR.php",
                        type: 'POST'
                    },
                    serverSide: true,
                    processing: true,
                    "dom": "Tfrtip",
                    "responsive": "true",
                    "columns": [
                        {data: "Student.Surname"},
                        {data: "Student.Forename"},
                        {data: "Modules.ModuleCode"},
                        {data: 'CourseParts.PartDescription'},
                        {data: 'ExamBoards.ExamBoard'},
                        {data: 'ExamBoardResults.MCQScore'},
                        {data: 'ExamBoardResults.OverallMark'},
                        {data: 'CourseWorkOutcomes.Outcome'},
                        {data: 'Modules.ModuleShortTitle'}
                    ]
                });
            });

Server-side script (EBR.php) :

<?php
// DataTables PHP library
include( "DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;

$data = Editor::inst( $db, 'ExamBoardResults' )
        
    ->fields( 
                Field::inst( 'Student.Surname' ),
        Field::inst( 'Student.Forename' ),
                Field::inst( 'Modules.ModuleCode' ),
                Field::inst( 'Modules.ModuleShortTitle' ),
                Field::inst( 'CourseParts.PartDescription' ),
                Field::inst( 'ExamBoards.ExamBoard' ),
                Field::inst( 'ExamBoardResults.MCQScore' ),
                Field::inst( 'ExamBoardResults.OverallMark' ),
                Field::inst( 'CourseWorkOutcomes.Outcome' )
        )
        
        
    ->leftJoin( 'ExamBoards', 'ExamBoards.id', '=', 'ExamBoardResults.ExamBoardID' )
        ->leftJoin( 'Modules', 'Modules.ModuleID', '=', 'ExamBoardResults.ModuleID' )
        ->leftJoin( 'Student', 'Student.StudentID', '=', 'ExamBoardResults.StudentID' )
        ->leftJoin( 'CourseWorkOutcomes', 'CourseWorkOutcomes.ID', '=', 'ExamBoardResults.CWOutcomeID' )
        ->leftJoin( 'CourseParts', 'CourseParts.PartID', '=', 'Modules.CoursePartID' )
        
    ->process($_POST)
    ->data();

if ( ! isset($_POST['action']) ) {
    // Get a list of sites for the `select` list
    $data['CWOutcomes'] = $db
        ->selectDistinct( 'CourseWorkOutcomes', 'ID as value, Outcome as label' )
        ->fetchAll();
}

echo json_encode( $data );

Unfortunately I am unable to provide a link to the page I'm working on as it's held on an intranet server. Grateful for any help or suggestions as this issue is preventing me from progressing with the site.

allan

Hi,

The LIMIT and OFFSET commands were added in SQL Server 2012. It sounds like you might be using an older version - is that correct?

Thanks,
Allan

dojocasino

Hi Allan,

Thanks for the reply. We're using SQL Server 2008, so I expect that may be where the problem lies.

Are there any easy workarounds available for this problem? I am not an expert in PHP or SQL, and am not confident I could write my own server-side script for 2008 use.

allan

I wish there was an easy workaround in SQL Server prior to 2012 - but it isn't particularly trivial. Unfortunately implementing something like that in the Editor libraries would be a lot or work, which is why I elected to limit their server-side processing support to just SQL Server 2012+ for SQL Server.

How many records are you working with? You would only need server-side processing if you are using 50'000+.

Allan

dojocasino

We're currently dealing with 6,150 records, and I'm aiming to future-proof the site so that future additional records will not slow the system down too much.

While the performance hit from performing client-side sorting and searching on my fast development computer is fairly negligible, the site I'm developing is intended to be responsive for mobile devices, and I've so far had sluggish performance when testing the table out on an iPad.

Thanks for your help. I will likely implement some more filters for the data to reduce the loading times, as I don't believe it's likely we'll be upgrading the SQL server anytime soon.