MSSQL server-side processing example?

tcash21

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.

tcash21

In case anyone is interested, I fixed it by doing the following:

[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]

tcash21

However, I am still unable to display the JSON data in the table. I'm getting the "JSON data from server could not be parsed" error. I've validated the JSON using jsonlint.com and it appears fine. I'm using jquery 1.7.2.

Please help!

allan

If you run your table through the debugger, we might be able to tell you what is happening.

Allan

tcash21

Thanks, wasn't aware of that option.

Here's the link:
http://debug.datatables.net/alunog

I can see the JSON data when I just run the PHP script.

allan

You are getting a 404 error from the server: "The requested URL was not found on this server"

Apparently the script "script/server_processing.php" - doesn't exist at that location.

Allan

tcash21

Doh! s/script/scripts/ Thank you!

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.

gilbertg

Dude, why can't you share your finished working MSSQL script? I have to convert the example from MYSQL to MSSQL as well, and I am sure I am not the only one..

mediolaterally

I also need to convert the example to MSSQL. Could you post your finished script?

doose

I have everything working in MSSQL except the SQL_CALC_FOUND_ROWS conversion and the FOUND_ROWS count.

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.

doose

LIMIT Fixed...

/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "AND id BETWEEN ".intval( $_GET['iDisplayStart'] )." AND ".
intval( $_GET['iDisplayLength'] );
}

doose

I'm new to data tables but got my mssql connection working after spending 2 days hacking together other forum users code.

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

doose

I'm new to data tables but got my mssql connection working after spending 2 days hacking together other forum users code.

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]

icelander13

Great work! I have a dumb question: I am having trouble with my server name \ instance name. Is there a trick to the syntax? I get my server name and instance name from SQL studio, but they do not seem to work.

icelander13

FYI, I discovered what I was doing wrong: This example requires MS SQL Extensions on the PHP server, and I do not have those installed. It looked like a non-trivial activity to install MS Extensions, so I went in a different direction: I modified your example and exchanged ODBC commands for the MS SQL commands.

doose

not sure I follow or who you are replying to but glad you got it working!