Problems with Server Side Processing and SQL Server Express
Problems with Server Side Processing and SQL Server Express
Tinydan
Posts: 5Questions: 3Answers: 0
Environment:
I'm trying to get the server side processing functionality working in an environment that uses SQL Server Express and php 5.4. I'm currently running on a 2008 development server. If this environment is an issue I do have a "live" environment" that uses SQL Server 2008 and php 5.4.
HTML Page:
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Test</title>
<!--Datatables Style-->
<link rel="Stylesheet" type="text/css" href="DataTables-1.10.0/media/css/jquery.dataTables.css" />
<!--Jquery-->
<script type="text/javascript" src="DataTables-1.10.0/media/js/jquery.js"></script>
<!--Data Tables-->
<script type="text/javascript" src="DataTables-1.10.0/media/js/jquery.dataTables.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#example').dataTable({
"processing": true,
"serverSide": true,
"ajax": "getRequests.php"
});
});
</script>
</head>
<body>
<div>
<table id="example" class="display" cellpadding="0" width="100%">
<thead>
<tr>
<th>SR_SOA</th>
</tr>
</thead>
<tfoot>
<tr>
<th>SR_SOA</th>
</tr>
</tfoot>
</table>
</div>
</body>
</html>
The headers listed in this table are the exact format of the columns from the database.
PHP:
The PHP code I've used if from here: https://datatables.net/development/server-side/php_mssql_odbc
Please note the connection stings have been altered for security purposes
<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "SR_REQUEST_PK";
/* DB table to use */
$sTable = "SR_REQUEST_TBL";
/* Database connection information */
$gaSql['user'] = "db_user";
$gaSql['password'] = "db_pass";
$gaSql['db'] = "test_db";
$gaSql['server'] = "Server1\SQLEXPRESS";
/*
* 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(SR_SOA);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* 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
>
```
Here's the output if i navigate to that php page:
?>
SELECT TOP 10 SR_SOA FROM SR_REQUEST_TBL WHERE SR_REQUEST_PK NOT IN ( SELECT SR_REQUEST_PK FROM ( SELECT TOP 0 SR_SOA FROM SR_SIM_REQUEST_TBL ) as [virtTable] ) : Array
PHP Error:
```txt
[13-May-2014 17:18:57 Europe/London] PHP Warning: sqlsrv_query() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\dataTables\getRequests.php on line 92
[13-May-2014 17:18:57 Europe/London] PHP Notice: Array to string conversion in C:\inetpub\wwwroot\dataTables\getRequests.php on line 92
Datatables Debug: http://debug.datatables.net/upikub
I think there may be an issue with how I'm connecting but all my connection parameters are the same as the ones I've used for my ODBC driver. Could anyone help shed some light on where I'm going wrong? I'm sure I have the HTML syntax correct and the only changes I made to the php file are the connection parameters. Any help would be greatly appreciated. This is just a test as I'm looking to use DataTables to interact with a table that has 60,000 + Rows in it so Server Side processing is a must