JSON data from server could not be parsed. JSON formatting error

plica2006

Hi,

I am using Datatables and server side processing with php connecting to MS SQL Server 2008... its not working because when I try to start up the php page I get: JSON data from server could not be parsed. JSON formatting error. The table headers are there but no rows of data and the "Processing" Div is stuck on the screen. The page looks the same viewed in both IE 9 and Google Chrome 24. I submitted my table to the Datatable debugger and my six digit code is: afubev

Here are some more details of my environment:
jquery 1.8.2
Datatables 1.9.4
TableTools 2.1.5

MS SQL Server 2008 R2
Microsoft SQL Server Management Studio 10.50.4000.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5466
Operating System 6.1.7601

IIS Express 8.0
PHP 5.4.12 32-bit Non Thread Safe
Netbeans IDE 7.2.1
IE 9
Google Chrome 24.0

I've googled and tried so many things for hours. In Google Chrome I go into Developer Tools and Network then XHR and click on the Response Tab to view the JSON sent back from the web server. I copy and paste that JSON into JSONLint and it tells me it is valid. I have no idea what could be going wrong, any help greatly appreciated!

This is the php file I use to return a JSON object... I blanked out my DB connection properties. I got this php file from datatables.net...

[code]
<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "lv.Artikel.OID";

/* DB table to use */
$sTable = "lv.Artikel";

/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "";

/*
* 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(
'OID',
'Name',
'EAN',
'RundePackung',
'relativeMinimalStock',
'minimalStock'
);

/*
* Joins.
* Replace the $sJoins empty string below with your JOIN clause(s) if you have any.
*/
//$sJoins = "";

/*
* ODBC connection
*/
$connectionInfo = array("UID" => $gaSql['user'], "PWD" => $gaSql['password'], "Database"=>$gaSql['db'],"ReturnDatesAsStrings"=>true);
$gaSql['link'] = sqlsrv_connect( $gaSql['server'], $connectionInfo);
if( $gaSql['link'] )
{
echo "Connection established.\n";
}
else
{
echo "Connection could not be established.\n";
die( print_r( sqlsrv_errors(), true));
}
$params = array();
$options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET );

/*
* Ordering.
* Replace the $sOrder empty string below with your ORDER BY clause if you have one.
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) ) {
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
$row = array();
for ( $i=0 ; $i
[/code]

allan

http://debug.datatables.net/afubev

Click on the 'Tables' tab and then 'Server interaction'. The 'Last JSON from server' field shows that it is not valid JSON. The string "Connection established. " is prefixed making it invalid.

Allan

plica2006

Hi Allan, you're an absolute legend! I must have been blind not to see the "Connection established". It was in line 41 above of the php code. I've really enjoyed using Datatables and TableTools too.. so useful.

allan

Easy done. Good to hear it is working well for you now :-)

Allan

bhaskarmca06

This is bhaskar php programmer. i have tested below code working fine in php with mssql sever.

<?php
$aColumns = array( 'CompanyName', 'CompanyLocation', 'ContactPerson');

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

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

/* Database connection information */
$gaSql['user'] = "dbuser";
$gaSql['password'] = "dbpasswordl";
$gaSql['db'] = "dbname";
$gaSql['server'] = "168.168.0.1";


/*if(isset($_GET['iDisplayLength']) && $_GET['iDisplayLength'] != '-1')
$limit = $_GET['iDisplayLength'];
else
$limit = 50;

if(isset($_GET['iDisplayStart']))
$top = $_GET['iDisplayStart'];
else
$top = 0;*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = " row > " .$_GET['iDisplayStart']." and row <= ".
($_GET['iDisplayLength'] + $_GET['iDisplayStart']);
} else {
$sLimit = "row > 1 and row <= 100";
}

$gaSql['link'] = mssql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] );
mssql_select_db( $gaSql['db'], $gaSql['link'] );

/* mysql_real_escape_string */
function mssql_escape($data)
{
if(is_numeric($data))
return $data;
$unpacked = unpack('H*hex', $data);
return '0x' . $unpacked['hex'];
}

/* 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