Connecting to MS SQL Server with PHP for JSON Example

Following on from a previous post about coercing MSSQL Server 2012 to produce JSON, here is a short php script to deliver said JSON to a JavaScript function (use it in the callback).


<?php
$serverName = "(local)";
$uid = "myUserId";
$pwd = "myPassword";
$connectionInfo = array( "UID"=>$uid,
                         "PWD"=>$pwd,
                         "Database"=>"myTestDatabase");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}

/* Query SQL Server  */
$tsql = "EXEC SerializeJSON 'SELECT TOP 10 * FROM myTestTable'";

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

/* Retrieve and display the results of the query. */
$row = sqlsrv_fetch_array($stmt);
echo $row[0]."</br>";

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);

?>

As it turns out, this method doesn’t seem to need a named instance specified (?!). Can’t explain that.

Setting up Remote Access to SQL Server 2012 is a royal PITA. Daniel_Walsenbach seems to have a good explanation here from way back in 2007. Stack Overflow has too many threads on this but would suggest starting hereMicrosoft’s  documentation on their php drivers is… dense, to put it politely.  PHP.net’s own documentation seems to have more user contributed questions than working examples. 

Still, this seems to work for me and hopefully for you too.

EDIT: TSQL to grant remote access to a MS SQL Server table.


USE myTableName ;
GO
EXEC sp_configure 'remote access', 0 ;
GO
RECONFIGURE ;
GO

Advertisements

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