Server Side Processing MS SQL 1.10.2

Server Side Processing MS SQL 1.10.2

werbeaccwerbeacc Posts: 7Questions: 2Answers: 0

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.

Replies

  • tangerinetangerine Posts: 3,365Questions: 39Answers: 395
  • werbeaccwerbeacc Posts: 7Questions: 2Answers: 0
    edited October 2014

    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;
        }
    
  • werbeaccwerbeacc Posts: 7Questions: 2Answers: 0
    edited October 2014

    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;
        }
    
This discussion has been closed.