executing Stored Procedure, JSON, MSSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
core000
Forum Newbie
Posts: 4
Joined: Thu Aug 13, 2009 9:34 am

executing Stored Procedure, JSON, MSSQL

Post by core000 »

I'm using PHP to access SQL Server Stored Procedure. However, the

characters are not returning correctly using JSON.

Database value
--------------

_ResourceGuid
'341EEB0B-9D71-4805-8380-000C53BAE950'

ResourceGuid
'341EEB0B-9D71-4805-8380-000C53BAE950'

status
'Active'


PHP Returned JSON array
-----------------------

[{"_ResourceGuid":"\u000b","Resourceguid":"\u000b","status":"Active",".....


Code below:
-------------------

Code: Select all

<?php 
 
$myServer = "server"; 
$myUser = "user";
$myPass = "passw";
$myDB = "mydatabase";
 
$conn = mssql_connect($myServer, $myUser, $myPass, $myDB); 
 
 
 
$sql ="EXEC spassetreport"; 
 
/*$result = mssql_query("SET ANSI_NULLS ON") or
die(mssql_get_last_message());*/
$result = mssql_query("SET ANSI_WARNINGS ON") or
die(mssql_get_last_message());
 
$data = mssql_query( $sql, $conn);    
 
 
$result = array();    
 
do { 
    while ($row = mssql_fetch_object($data)){ 
        $result[] = $row;    
    } 
}while ( mssql_next_result($data) ); 
 
echo json_encode($result);
 
mssql_close($conn); 
?>

Thanks
Brian Swan
Forum Newbie
Posts: 17
Joined: Thu Jan 14, 2010 11:56 am

Re: executing Stored Procedure, JSON, MSSQL

Post by Brian Swan »

Have you tried encoding the data as UTF-8? I think the json_encode function only works with UTF-8 encoded data: http://us.php.net/manual/en/function.json-encode.php

-Brian
core000
Forum Newbie
Posts: 4
Joined: Thu Aug 13, 2009 9:34 am

Re: executing Stored Procedure, JSON, MSSQL

Post by core000 »

I've tried the JSON encode, but it does not work.

is this the syntax?

echo json_encode(utf8_encode($result));

Also using print_r($result); gets me:

Array ( [0] => stdClass Object ( [_ResourceGuid] => ë4qHƒ€ SºéP [Resourceguid] => ë4qHƒ€ SºéP [status] => Active
Brian Swan
Forum Newbie
Posts: 17
Joined: Thu Jan 14, 2010 11:56 am

Re: executing Stored Procedure, JSON, MSSQL

Post by Brian Swan »

Hmmm. Well, utf8 encoding clearly wasn't the problem.

I tried to repro this with the sqlsrv driver (which is available here: http://www.microsoft.com/downloads/deta ... 52214caad9). When I execute this code...

Code: Select all

$conn = sqlsrv_connect($server, $connOptions);
$sql = 'select * from GUID_Table';
$stmt = sqlsrv_query($conn, $sql);
$result = array();
while($row = sqlsrv_fetch_object($stmt))
{
                array_push($result, $row);
}
 
echo json_encode($result);
...I don't see any corruption of the GUID field in my table. Is it possible that the mssql driver is doing something strange?

I don't know how far into development you are, but you might try the sqlsrv driver?

Hope that helps.
-Brian
Post Reply