how can i use the datatable with a view from sql server ?

Andreina95

i need to visualize a view from sql server, but when i use a code that i find in this page (dhttps://datatables.net/development/server-side/php_mssql_odbc) i obtain the following error

galcott

Are you converting the SQL Server recordset into JSON format? Here's some sample code that I use, in VBScript. Not very elegant but it works. In the for loop you need a line for each field in the recordset. My recordset has 6 fields so it's array elements 0 through 5.

' Embedded double quotes have to be preceded by a backslash to avoid JSON errors.
Set rs = CreateObject("ADODB.Recordset")
rs.Open "select herb_code, herb_name, cost, inventory, low_inv, replace(notes, char(34), '\' + char(34)) from herbs", ConnString, 0
s = "{""data"":["     'the JSON has to begin with this
Arr = rs.getRows()
For i = 0 to UBound(Arr,2)
  s = s & "[" & """" & Arr(0,i) & ""","
  s = s & """" & Arr(1,i) & ""","
  s = s & """" & Arr(2,i) & ""","
  s = s & """" & Arr(3,i) & ""","
  s = s & """" & Arr(4,i) & ""","
  s = s & """" & Arr(5,i) & """" & "],"
Next
s = left(s,len(s)-1)  'knock off trailing comma
s = s & "]}"
rs.Close
Response.Write s

allan

Did you follow the instructions in the tech note the error message links to? If so, what is the server returning given that its not valid JSON?

Allan

Andreina95

hi, i already past that problem but now i can't filter and use the pagination of the datatable, you have any idea ? how cant i resolve this problem...

this is my code:

<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
// add your columns here!!!
$aColumns = array( 'ID', 'Name', 'Lastname', 'Start_Date', 'Card_Access', 'Department','Status' );

/* MSSQL Database infomation */
$serverName ='';
$connectionInfo = array("Database"=>"", "UID"=>"", "PWD"=>"", "CharacterSet"=>"UTF-8");
$conn = sqlsrv_connect($serverName,$connectionInfo);

if($conn)
  {
   //echo "Connection Established";
  }
   else{
    echo "Connection could not be Established";
    die ( 'Can not connect to server' );
}

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "ID";

/* DB table to use */
$sTable = "";

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
  {
   header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
   die( $sErrorMessage );
  }

  /* 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 = "";
          }
      }
/* escape function */
function mssql_escape($data) {
  if(is_numeric($data))
     return $data;
$unpacked = unpack('H*hex', $data);
return '0x' . $unpacked['hex'];
}

/* Filtering */
$sWhere = "";
   if ( isset($_POST['search']['value']) && $_POST['search']['value'] != "" ) {
        $sWhere = "WHERE (";
   for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_POST['search']['value'] )."%' 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($conn, $sQuery);

            if($rResult === false){
             die(sqlsrv_errors(SQLSRV_ERR_ERRORS));
            }

/* Data set length after filtering */
$sQueryCnt = "SELECT * FROM $sTable $sWhere";
$rResultCnt = sqlsrv_query($conn, $sQueryCnt );
$iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

/* Total data set length */
$sQuery = "
  SELECT COUNT(id)
  FROM $sTable
  ";
$rResultTotal = sqlsrv_query($conn, $sQuery );
$aResultTotal = sqlsrv_fetch_array($rResultTotal, SQLSRV_FETCH_NUMERIC);

$iTotal = $aResultTotal[0];


/* Output */
$draw = (isset($_POST['draw']))?((int)$_POST['draw']):1 ;
$output = array(
  "draw" => $draw,
  "recordsTotal" => $iTotal,
  "recordsFiltered" => $iFilteredTotal,
  "data" => array()
);

while ( $aRow = sqlsrv_fetch_array( $rResult, SQLSRV_FETCH_ASSOC) )
{
 $row = array();
 for ( $i=0 ; $i<count($aColumns) ; $i++ )
 {
 /* General output */
 $row[$aColumns[$i]] = $aRow[ $aColumns[$i] ];
 }
 $row['Start_Date'] = $row['Start_Date']->format('Y-m-d');
 $output['data'][] = array_values($row);
}

echo json_encode( $output );

?>

allan

The script you linked to is a legacy one (which is noted at the top of the page). Have you placed DataTables' server-side processing into legacy mode? Details for that are available here.

Allan

Andreina95

in my code (javascript) is that's how I have it

$(document).ready(function() {
       var table = $('#Userzktime').DataTable( {
           "bJQueryUI": true,
           "sPaginationType": "full_numbers",
           "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],

           "processing": true,
               "serverSide": true,
            "ajax": "php/Datatable_User_zktime.php",

             "columnDefs": [ {
                   "className": "dt-center", "targets": "_all",
                   "targets": -1,
                    "data": null,
                    "defaultContent":"<button id='edit'  class='fa fa-trash-o li'> </i> <span class='le'></span></a></button>"
               } ]
      } );

     $('#Userzktime tbody').on( 'click', '#edit', function () {
     var data = table.row( $(this).parents('tr') ).data();
          var id=data[1];
             $('#id').val(id);
             alert(id);
          //  $('#confirm-disa').modal('show');
        });
  });

allan

It doesn't look like you've enabled the legacy server-side processing. In which case it is sending parameters that your server-side script isn't expecting.

You need to either:

1. Update the server-side script to use the new parameters
2. Use legacy parameters - as detailed in the link I gave above.

Allan

Andreina95

i don't understand , i see the example i don't see any difference with my code

tangerine

i see the example i don't see any difference with my code

That is exactly why Allan advised you to update your server-side script to use the new parameters.
Otherwise you have to follow Allan's alternative advice to use legacy parameters in your own code.

Andreina95

Sorry, I am new in this, i always work with mysql, so far I test the datatable with sql and I don't know exactly how it works with sql server

With mysql never gave me these kind of problems

allan

Add this line:

$.fn.dataTable.ext.legacy.ajax = true;

before you initialise DataTables.

That will cause DataTables to send the legacy parameters to the server (required for that script since it uses the legacy parameters).

Allan

promis

good