BugDroid777 BugDroid777 - 10 months ago 84
JSON Question

Problems with charset in a json with data from a mssql database

I got the following script that returns me the data of a mssql databse, it works fine however special caracters like "ç" "á" "ã", etc is displayed in a weird way.

From what i saw, as it is a mssql this can be a pain.. didnt find any exact response.

I tried to add this line in order to fix:

  • ini_set('mssql.charset', 'UTF-8');

  • "Database"=>"programaplo","UID"=>"--","PWD"=>"--","CharacterSet"=>"UTF-8");

  • header('Content-type: application/json; Charset=UTF-8');

The script

ini_set('mssql.charset', 'UTF-8');

$serverName = "mssql3.gear.host";

/* Get UID and PWD from application-specific files. */
$connectionInfo = array( "Database"=>"programaplo", "UID"=>"--", "PWD"=>"--","CharacterSet"=>"UTF-8");
$conn = sqlsrv_connect($serverName, $connectionInfo);

if( $conn ) {
echo "Connection established.<br />";
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));

$tsql = "SELECT * FROM Obras";
$stmt = sqlsrv_query($conn, $tsql);

if( $stmt === false ) {
echo "Error in executing query.</br>";
die( print_r( sqlsrv_errors(), true));

echo "Query: sucesso \n";

$json = array();

while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
$json[] = $row;

header('Content-type: application/json; Charset=UTF-8');
echo json_encode($json);

sqlsrv_close($conn); //Close the connnectiokn first


The result:

{"Id":2,"NomeObra":"Super Olimpia","idCliente":"Associa\u00e7\u00e3o Super Olimpia","DataPLevantamento":"4 de agosto de 2016","DataRLevantamento":"4 de agosto de 2016","Estado":"Obra conclu\u00edda ","DataRMateriais":"6 de setembro de 2016","DataInicioObra":"18 de setembro de 2016","DataConclusao":"20 de outubro de 2016","DataVestoria":"18 de setembro de 2016","Obs":"","Prompor":"Gas Fenosa","Levantpor":"Ploran\/Paulo","executpor":"Ploran"}

The issue:


Answer Source

Associa\u00e7\u00e3o is the official way to encode binary (or non-ASCII) characters in JSON. Every Json parser [can|should] understand this notation.

See : JSON and escaping characters