JSON from MS SQL Server with PHP Example

Someone much smarter than me, pointed out that my previous solution would hit a size limit (max cell width). This stored proc returns the JSON stream as a single table cell (albeit a big one). So their alternative solution is to do the JSON conversion on the webserver with PHP’s json_encode().

It works nicely.

EDIT: Don’t forget the UTF-8 encoding or you’ll experience random NULLs in your JSON.


<?php
/* Set Connection Credentials */
$serverName="(local)";
$uid = "";
$pwd = "";
$connectionInfo = array( "UID"=>$uid,
                         "PWD"=>$pwd,
                         "Database"=>"TEST",
                         "CharacterSet"=>"UTF-8");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false ) {
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}

/* TSQL Query */
$tsql = "SELECT TOP 100 * FROM AtxJSON";
$stmt = sqlsrv_query( $conn, $tsql);

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

/* Process results */
$json = array();

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

/* Run the tabular results through json_encode() */
/* And ensure numbers don't get cast to trings */
echo json_encode($json,<code> JSON_NUMERIC_CHECK</code>);
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);

?>

Advertisements

3 thoughts on “JSON from MS SQL Server with PHP Example

  1. I got a problem at “$conn = sqlsrv_connect( $serverName, $connectionInfo);”.

    Error:
    Fatal error: Uncaught Error: Call to undefined function sqlsrv_connect().

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s