Accessing SDS From PHP
Hi folks, this is Nigel Ellis and I’m an architect on the SQL Services team. I’ve just returned from MIX2009 where I announced our new relational service in my What's New with SQL Data Services talk. You can watch the full video and download the slides from here.
During my talk, I demonstrated running an application on Windows Azure using PHP to interact with a database hosted in our new relational SQL data service. I used the unified ODBC support built in to PHP to interact with SDS. I’ve included a sample code snippet below which uses an SDS server server.data.dev.mscds.com:
<?php
$host = "server.data.dev.mscds.com";
$dbname = "database";
$dbuser = "user@server";
$dbpwd = "password";
$driver = "{SQL Server Native Client 10.0}";
// Build connection string
$dsn="Driver=$driver;Server=$host;Database=$dbname;Encrypt=true;TrustServerCertificate=true";
if (!($conn = @odbc_connect($dsn, $dbuser, $dbpwd))) {
die("Connection error: " . odbc_errormsg());
}
// Got a connection, run simple query
if ($qh = @odbc_exec($conn, "SELECT A, B FROM myTable")) {
// Dump query result
$rows = 0;
while ( $row = @odbc_fetch_object($qh) ) {
echo("$rows: $row->A $row->B\r\n");
$rows++;
}
@odbc_free_result($qh);
}
else {
// Error running query
echo("Query error: " . odbc_errormsg($conn));
}
// Free the connection
@odbc_close($conn);
?>
In this example, I’m using the SQL Server native ODBC driver which ships out of band from the operating system. Other driver choices may be used as outlined in the table below.
|
Item |
Comments |
|
Server |
Name of the virtual SDS data server to connect to. This takes the form of servername.domain_name. In the example, I’m using a virtual server provisioned in the SDS pre-production domain data.dev.mscds.com |
|
Driver |
ODBC driver to use:
{SQL Server} – Legacy SQL Server driver
{SQL Native Client} – SQL 2005 Native Client
{SQL Server Native Client 10.0} – SQL 2008 Native Client |
|
Database |
Name of the target database to connect to. If left unspecified the connection will default to the master database. |
|
Encrypt |
Force use of encryption over TDS – if left unspecified, SDS will force encryption |
|
TrustServerCertificate |
Trust the certificate returned by the data service – this is currently required due to implementation restrictions that will be relaxed before we release |
|
UID |
Username of the user – form should be user@server. Can be specified in the connection string or as argument to odbc_connect() |
|
PWD |
Password of the user account. Can be specified in the connection string or as argument to odbc_connect() |
For those familiar with ODBC you’ll note this pattern is also used to connect to a regular SQL Server instance. The only change in the code is the name of the server, use of encryption and the use of SQL standard security (username and password).
In addition to the ODBC driver patterns, there is an open source Native PHP driver for SQL Server. This driver is a native client library built over the SQL 2005 Native Client. Today the driver requires the target SQL server support MARS. This prevents the driver from working with SQL 7.0, 2000 and SQL Data Services as MARS is not supported. The good news is that we’re working with the native driver folks to expose MARS as a configuration option; this support is already provided by the underlying ODBC driver. This change allows the use of the PHP Native driver with SDS without requiring SDS to support MARS.
In the end, you’ll see the patterns used to access SDS are identical to those you use to access SQL Server.
Nigel.
