Accessing Windows Azure SQL Database From PHP
<?php
$host = "database.windows.net";
$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, }
le="color:#0000ff;"> "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 SQL Database data server to connect to. This takes the form of servername.database.windows.net. In the example, I’m using a virtual server provisioned in the SDS pre-production domain database.windows.net |
|
Driver |
ODBC driver to use: |
|
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, SQL Database 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
Trust the certificate returned by the data service – this is currently required due to implementation restrictions that will be relaxed before we release
|
|
|
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.
You see the patterns used to access SDS are identical to those you use to access SQL Server.