Server Side Processing MS SQL 1.10.2

werbeacc

Hello,

im looking for an MS SQL optimized version of the new ssp_class.php (Datatables 1.10.2).
For example, the paging function should not work because a LIMIT statement is used.
Has anyone ever adapted it or can help to configure it for MS SQL.
Can further instructions cause problems?

I use successfully PDO with Sybase for the MS SQL Connection.

tangerine

Did you search the forum?
For example: http://datatables.net/forums/discussion/9683/pagination-with-mssql-backend-example-code

werbeacc

Thanks, but i have problems to implement this in this snippet:
also the $resFilterLength function must be edited for MS SQL

static function simple ( $request, $sql_details, $table, $primaryKey, $columns )
{
$bindings = array();
$db = self::sql_connect( $sql_details );

// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );


// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
    "SELECT TOP 10 ".implode(", ", self::pluck($columns, 'db'))."
     FROM $table 
     $where 
     $order "
     //$limit
);

// Data set length after filtering
/*$resFilterLength = self::sql_exec( $db,
    "SELECT FOUND_ROWS()"
);*/

$recordsFiltered = $resFilterLength[0][0];

// Total data set length
$resTotalLength = self::sql_exec( $db,
    "SELECT COUNT({$primaryKey})
     FROM  $table"
);
$recordsTotal = $resTotalLength[0][0];

static function limit ( $request, $columns )
    {
        $limit = '';

        if ( isset($request['start']) && $request['length'] != -1 ) {
            $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
        }

        return $limit;
    }

werbeacc

Its now successfully implemented. Definitely not the most efficient way, but it works for the beginning. I used the new OFFSET in MS SQL 2012. So its only since 2012 compatible...
For someone who needs it too:

static function simple ( $request, $sql_details, $table, $primaryKey, $columns )
    {
        $bindings = array();
        $db = self::sql_connect( $sql_details );

        // Build the SQL query string from the request
        $limit = self::limit( $request, $columns );
        $order = self::order( $request, $columns );
        $where = self::filter( $request, $columns, $bindings );
        
        // Main query to actually get the data
        $data = self::sql_exec( $db, $bindings,
            "SELECT ".implode(", ", self::pluck($columns, 'db'))."
             FROM $table 
             $where 
             $order 
             $limit "
        );
        
        // Data set length after filtering
        $resFilterLength = self::sql_exec( $db, $bindings,
            "SELECT ".implode(", ", self::pluck($columns, 'db'))."
             FROM $table 
             $where 
             $order "
        );
        $recordsFiltered = count($resFilterLength);

        // Total data set length
        $resTotalLength = self::sql_exec( $db,
            "SELECT COUNT({$primaryKey})
             FROM $table"
        );
        $recordsTotal = $resTotalLength[0][0];
        
        
        /*echo "<pre>";
        print_r(self::data_output( $columns, $data ));
        echo "</pre>";*/
        
        /*
         * Output
         */
        return array(
            "draw"            => intval( $request['draw'] ),
            "recordsTotal"    => intval( $recordsTotal ),
            "recordsFiltered" => intval( $recordsFiltered ),
            "data"            => self::data_output( $columns, $data )
        );
    }

and

static function limit ( $request, $columns )
    {
        $limit = '';

        if ( isset($request['start']) && $request['length'] != -1 ) {
            $limit = "OFFSET ".intval($request['start'])." ROWS FETCH NEXT ".intval($request['length'])." ROWS ONLY";
        }

        return $limit;
    }