mssql serverside_processing
mssql serverside_processing
Hi Allan and all,
I'm trying to implement server_processing but my database is mssql other than mysql.
Has any one done this before and got a working example?
Basically when I cann direclty the php page it will work; altough the display is something like:
{"sEcho": , "iTotalRecords": 173047, "iTotalDisplayRecords": 00219398, "aaData": [ ["00219398"," ","SI-4G-SSL-PREM","Aw Bug Fix","3"],["00227789"," ","SI-GT-CGC16-SSL","Aw Bug Fix","3"]"
but this is not the problem my problem is that when I call the html page I can see that up to the connection to the database and db selection is fine but the query is not actually issued.
Here's my code, you'll see I've done some changes from the original one, i.e I had to remove SQL_CALC_FOUND_ROWSfrom the SELECT as the server would return
"Invalid column name 'SQL_CALC_FOUND_ROWS"
[code]
root@ubuntu:/var/www/salvo/dataTables-1.5/mytest# cat test1.php
<?php
/* mssql connection */
$myServer = "xxxxx";
$myUser = "xxxxx";
$myPass = "xxxxx";
$myDB = "xxxxx";
$connection = mssql_pconnect( $myServer, $myUser, $myPass ) or
die( 'Could not open connection to server' );
mssql_select_db( $myDB, $connection) or
die( 'Could not select database '. $gaSql['db'] );
// die( 'Could not select database '. $myDB );
/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mssql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mssql_real_escape_string( $_GET['iDisplayLength'] );
}
/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i
[/code]
......
I'm trying to implement server_processing but my database is mssql other than mysql.
Has any one done this before and got a working example?
Basically when I cann direclty the php page it will work; altough the display is something like:
{"sEcho": , "iTotalRecords": 173047, "iTotalDisplayRecords": 00219398, "aaData": [ ["00219398"," ","SI-4G-SSL-PREM","Aw Bug Fix","3"],["00227789"," ","SI-GT-CGC16-SSL","Aw Bug Fix","3"]"
but this is not the problem my problem is that when I call the html page I can see that up to the connection to the database and db selection is fine but the query is not actually issued.
Here's my code, you'll see I've done some changes from the original one, i.e I had to remove SQL_CALC_FOUND_ROWSfrom the SELECT as the server would return
"Invalid column name 'SQL_CALC_FOUND_ROWS"
[code]
root@ubuntu:/var/www/salvo/dataTables-1.5/mytest# cat test1.php
<?php
/* mssql connection */
$myServer = "xxxxx";
$myUser = "xxxxx";
$myPass = "xxxxx";
$myDB = "xxxxx";
$connection = mssql_pconnect( $myServer, $myUser, $myPass ) or
die( 'Could not open connection to server' );
mssql_select_db( $myDB, $connection) or
die( 'Could not select database '. $gaSql['db'] );
// die( 'Could not select database '. $myDB );
/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mssql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mssql_real_escape_string( $_GET['iDisplayLength'] );
}
/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i
[/code]
......
This discussion has been closed.
Replies
[code]
root@ubuntu:/var/www/salvo/dataTables-1.5/mytest# cat stest.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
DataTables example
@import "../media/css/demo_page.css";
@import "../media/css/demo_table.css";
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./test1.php"
} );
} );
DataTables server-side processing example
Live example
Rendering engine
Browser
Platform(s)
Engine version
CSS grade
Loading data from server
Rendering engine
Browser
Platform(s)
Engine version
CSS grade
[/code]
Thanks,
Salvo
http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx
[code]
declare @startrow int
declare @endrow int
set @startRow = 40
set @EndRow = 70
select
MasterRowNums.*
from
(
select
m.nameLast, m.nameFirst, m.lahmanID,
ROW_NUMBER() over (order by m.nameLast, m.nameFirst, m.lahmanID) as RowNum
from
[master] m
)
MasterRowNums
where
RowNum between @startRow and @endRow
order by
nameLast, NameFirst, lahmanID
[/code]
I got silent for a while and am now resuming this thread as I'm going to to work on this again.
Allan and all, I'd like you to share your view on my code.
Any thought and fresh ideas will surely help me moving forward.
Cheers,
Salvo
Did you take account of the information given by brianbolton in the post above (i.e. there is no LIMIT). There are a few other things that need to be taken account of as well - FOUND_ROWS for example. As I say, I'm no MSSQL expert, so it might be best to ask in an MSSQL centric forum.
Regards,
Allan
thank you
Although you're right about the differences between the 2; at this I've got the php working and retrieving the data in a json format when run on its own; however when started by the html page; the connection to the MSSQL will be started but the query not completed: that's why I don't think it's a ms-sql issue but suspect I might just be overlooking something.
The html page will just hang with "Loading data from server" .
Have you come across this before or have any fresh ideas?
Thanks,
Salvo
it's my program using ok, use with asp + sql2000
use store procedure to paging results, it's very good and ok
try it!
@Salvo: It does sound more like an SQL issue if the query is not returning or throwing an error. I'd check that the script is running okay, and make use of the information provided by sueboy above!
Regards,
Allan