MSSQL server-side processing example?
MSSQL server-side processing example?
Hi, I think I'm very close to having a working example with SQL Server Express, but there are a few things I cannot seem to accomplish.
For one, SQL_CALC_FOUND_ROWS is not an option in MSSQL and neither is FOUND_ROWS().
I've tried changing those both to @@ROWCOUNT as that is the only alternative I have been able to find.
Right now my JSON is returning this:
{"sEcho":0,"iTotalRecords":2998,"iTotalDisplayRecords":2998,"aaData":[[0,null,null,null],[0,null,null,null],[0,null,null,null],[0,null,null,null],[0,null,null,null],
with [0,null,null,null] repeating almost 3k times.
Thanks for any help.
For one, SQL_CALC_FOUND_ROWS is not an option in MSSQL and neither is FOUND_ROWS().
I've tried changing those both to @@ROWCOUNT as that is the only alternative I have been able to find.
Right now my JSON is returning this:
{"sEcho":0,"iTotalRecords":2998,"iTotalDisplayRecords":2998,"aaData":[[0,null,null,null],[0,null,null,null],[0,null,null,null],[0,null,null,null],[0,null,null,null],
with [0,null,null,null] repeating almost 3k times.
Thanks for any help.
This discussion has been closed.
Replies
[code]
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT *
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mssql_query( $sQuery, $gaSql['link'] ) or die(mssql_get_last_message());
/* Data set length after filtering */
$sQuery = "
SELECT @@ROWCOUNT
";
$rResultFilterTotal = mssql_query( $sQuery, $gaSql['link'] ) or die(mssql_get_last_message());
$aResultFilterTotal = mssql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
[/code]
Please help!
Allan
Here's the link:
http://debug.datatables.net/alunog
I can see the JSON data when I just run the PHP script.
Apparently the script "script/server_processing.php" - doesn't exist at that location.
Allan
This debugger tool is super useful. I've got it working now just need to rig something for pagination since LIMIT doesn't work in MSSQL.
When I have finished converted these to some kind of MSQL SP or PHP function I'll post my code and help anyone else out.
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "AND id BETWEEN ".intval( $_GET['iDisplayStart'] )." AND ".
intval( $_GET['iDisplayLength'] );
}
My code is below and everything works, paging, filtering, ordering, I'm not using the ODBC connection either, just your standard mssql_connect.
CODE is below;
<?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( 'col1', 'col2', 'col2', 'etc', 'etc' );
/* MSSQL Database infomation */
$host = 'my_server\my_instance';
$user = "sa";
$pass = "my_pw";
$db = "my_db";
/* MSSQL Connection */
$link = mssql_connect ( $host, $user, $pass ) or die ( 'Can not connect to server' );
/* MSSQL Database Selection */
mssql_select_db ( $db, $link ) or die ( 'Can not select database' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "my_id_col";
/* DB table to use */
$sTable = "my_table";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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 $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mssql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
My code is below and everything works, paging, filtering, ordering, I'm not using the ODBC connection either, just your standard mssql_connect.
CODE is below;
[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( 'col1', 'col2', 'col2', 'etc', 'etc' );
/* MSSQL Database infomation */
$host = 'my_server\my_instance';
$user = "sa";
$pass = "my_pw";
$db = "my_db";
/* MSSQL Connection */
$link = mssql_connect ( $host, $user, $pass ) or die ( 'Can not connect to server' );
/* MSSQL Database Selection */
mssql_select_db ( $db, $link ) or die ( 'Can not select database' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "my_id_col";
/* DB table to use */
$sTable = "my_table";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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 $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mssql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[/code]