Microsoft is announcing an important interoperability milestone: the release of the Microsoft Drivers for PHP for SQL Server 2.0!!
The major highlight of this release is the addition of the PDO_SQLSRV driver, which adds support for PHP Data Objects (PDO). The PHP community has signaled that PDO is the future as it removes data access complexity in PHP applications by enabling developers to focus their efforts on the applications themselves rather than database-specific code. Providing the PDO_SQLSRV driver enables popular PHP applications to use the PDO data access “style” to interoperate with Microsoft’s SQL Server database and make it easier for PHP developers to take advantage of SQL Server's proven track record and to leverage features such as SQL Server's Reporting Services and Business Intelligence capabilities. In addition to accessing SQL Server, both drivers (SQLSRV and PDO_SQLSRV) also enable PHP developers to easily connect to and use Microsoft's cloud database offering, SQL Azure, and enjoy the benefits of a reliable and scalable relational database in the cloud, as well as functionality like exposing OData feeds.
New architecture
While the major focus of this release was the PDO_SQLSRV driver, we took this opportunity to re-architect our code create a core functional layer so that we can offer the same functionality and consistency in both drivers (SQLSRV and PDO_SQLSRV). This new architecture enables us to add new features easily to both drivers.
PHP developers are now free to select the driver of their choice, using either the native SQLSRV API (SQLSRV driver) or the PDO API (PDO_SQLSRV driver) for accessing SQL Server or SQL Azure. The following code snippets provide an illustration of a simple task (query and list products from AdventureWorks sample database) using each driver:
SQLSRV driver:
<?php $serverName = "(local)\sqlexpress"; $connectionOptions = array( "Database"=>"AdventureWorks" ); /* Connect to SQL Server using Windows Authentication. */ $conn = sqlsrv_connect( $serverName, $connectionOptions ); /* Get products by querying against the product name.*/ $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product"; /* Execute the query. */ $getProducts = sqlsrv_query( $conn, $tsql ); /* Loop thru recordset and display each record. */ while( $row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC ) ) { print_r( $row ); } /* Free the statement and connection resource. */ sqlsrv_free_stmt( $getProducts ); sqlsrv_close( $conn ); ?>
PDO_SQLSRV driver:
<?php $serverName = "(local)\sqlexpress"; /* Connect to SQL Server using Windows Authentication. */ $conn = new PDO( “sqlsrv:server=$serverName;Database=AdventureWorks" ); /* Get products by querying against the product name.*/ $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product"; /* Execute the query. */ $getProducts = $conn->query( $tsql ); /* Loop thru recordset and display each record. */ while( $row = $getProducts->fetch( PDO::FETCH_ASSOC ) ) { print_r( $row ); } /* Free the statement and connection resource. */ $getProducts = NULL; $conn = NULL; ?>
Community Engagement
We’d like to think our engagement with the PHP community has been among our best and has helped achieve this high level of interoperability with Microsoft’s SQL Server. We listened to comments from the PHP community including core contributors to PHP, many core contributors from several popular PHP applications (such as Drupal, Doctrine, xPDO), several software vendors, and individual developers. We worked hard to meet the intent of PDO (simplifying data access) in our design for PDO_SQLSRV. We worked through several challenges some of which are outlined in a blog by my colleague, Brian Swan. Once again, we heard from our partners and community with the primary goal of delivering a driver that best meets interoperability needs of PHP developers. The team received and responded to many comments in our blog, online forum, at conferences and other events, from participants on the CodePlex project site, through conference calls, and even twitter. We’d like to thank everyone for their effort and look forward to continued community engagement.
Functionality highlights
Documentation and download
The Microsoft Drivers for PHP for SQL Server 2.0 release is available for download on the MSDN Download Center as well as the Web Platform Installer. We encourage you to download it and explore the programming guides, the API documentation, and the two example applications in the .chm file. We will continue to offer our v1.1 release along with our new v2.0 release on our MSDN Download Center for a few months. There is also the ability to access all documentation on MSDN, including the example applications.
Open source code
We have published the source code for both drivers on our CodePlex project site. At this time we are releasing the source code under the Apache 2.0 license. Microsoft supports only the Microsoft signed versions of the drivers.
Feedback and bug reports
We thank community members for their comments. We will continue to be responsive to feedback on this blog, our MSDN forum, as well as the established SQL Server product feedback mechanism. In addition to reporting problems on our forum, the forum is a great resource for looking up issues reported by others and finding the solution that worked. Please send feedback our way as we continue our interoperability work to give developers choices when it comes to our platform.
Thanks.
Ashay Chaudhary Program Manager, SQL Connectivity – PHP
Perhaps I missed something but, in your code samples are you using SSPI for the security context? or how do you set specifically the kind of security that u're going to use?
Referring to the documentation for both the constructor for PDO [msdn.microsoft.com/.../ff628159(v=SQL.90).aspx] and SQLSRV interface [msdn.microsoft.com/.../cc296161(v=SQL.90).aspx].
For PDO_SQLSRV, We can see that if you wish to use SQL Authentication (e.g. NOT Windows Authentication via SSPI) then you must provide the username and password arguments, i.e. PDO::__construct($dsn [,$username [,$password [,$driver_options ]]] ). Not providing these arguments, or passing NULL for both $username and $password (if you wish to set $driver_options) will result in SSPI negotiation.
For SQLSRV we see the parameters are sqlsrv_connect( string $serverName [, array $connectionInfo]), where the username for SQL Authentication would be specified as UID and PWD in the $connectionInfo associative array, e.g. $connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>"AdventureWorks"); If UID and PWD are not specified, then SSPI negotation is assumed.
Hope that helps and also that everyone is having fun with our latest release!
Casey's answer is correct. I've also clarified it in the code comments.
This looks like a really well-done PDO implementation - thanks all.
PHP for SQL Server Version 2.0 PDO LastInsertId and fetching scope_identity in sqlsrv not works. Both have bugs.
<?php
$serverName = "192.168.1.4";
$db_user = "user";
$db_pw = "password"
$db_name = "sampledb";
try
{
$pdo_con = new PDO( "sqlsrv:server=$serverName ; Database=$db_name", "$db_user", "$db_pw");
$pdo_con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch(Exception $e)
die( print_r( $e->getMessage() ) );
$tsql = "INSERT INTO [sampledb].[dbo].[tbUsers] (UserId,NameSurname) VALUES (newId(),?)";
$NameSurname = "John Doe";
$params = array(&$NameSurname);
try{
$insert_user = $pdo_con->prepare($tsql);
$insert_user->execute($params);
$lastRow = $pdo_con->lastInsertId();
echo $lastRow;
?>
Everything works perfect. But i cant get lastInsertId() i think it not works correctly.
There is a note on msdn.microsoft.com/.../ff628155%28SQL.90%29.aspx
Returns the identifier for the row most recently inserted into a table in the database. The table must have an IDENTITY NOT NULL column.
My UserId coulmn is (PK,uniqueidentifier,not null) i think there is no problem in my table.
It not worked for sqlsrv driver too.
$db_pw = "password";
$connectionInfo = array("UID" => "$db_user", "PWD" => "$db_pw", "Database"=>"$db_name");
$ms_con = sqlsrv_connect( $serverName, $connectionInfo);
if( $ms_con === false )
echo "Could not connect.\n";
die( print_r( sqlsrv_errors(), true));
$tsql = "INSERT INTO [sampledb].[dbo].[tbUsers] (UserId,NameSurname) VALUES (newId(),?); SELECT SCOPE_IDENTITY() AS NewUserId";
$stmt = sqlsrv_prepare( $ms_con, $tsql, $params);
if( $stmt === false )
die( FormatErrors( sqlsrv_errors() ) );
if( sqlsrv_execute( $stmt) === false)
/*Skip the open (first) result set (rows affected). */
$next_result = sqlsrv_next_result($stmt);
if( $next_result === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
// Fetch the next result set.
if( sqlsrv_fetch($stmt) === false)
// Get the first field - the identity from INSERT.
$insert_id = sqlsrv_get_field($stmt, 0);
print($insert_id);
This version doesnt works too.
Both have bugs i think.
Ok i finf at last.
Key point is uniqueidentifier. You cant get last insert id if your column is not Identity. For uniqueidentifier you have to first $query="select newId() as last_id";
$inf = assoc($query);
$last_id = $inf[last_id];
after insert $query = "insert into table (user_id,name_surname) values (?,?)";
$params = array(&$last_id,&$name_surname);
pdo::prepare($query);
pdo::execute($params);
logic is first select newId then insert this to table.
I just started using sql server with the odbc driver last week.. now I have this. I easily switched over to using it. Thanks for the driver!
I was tasked with setting up an intranet for our company that serves 2000+ employees. I am using Windows Server 2008 and IIS 7.5. I would like to have SQL Server 2.0 on there as well. I would use Windows Authentication for the intranet. Also, I would have to open an extranet up for department heads and directors to access information while off-site. I love programming in PHP, but over here they are all Microsoft and Windows, which made it difficult for me to PHP. But with this new driver, I think it will work great! So excited! Back to my point. I'm setting this intranet up any recommendations or suggestions to get started?
banana/chris - glad to make you guys happy!
nodzend - i'll move your support issue to our forum where there is better opportunity for others to chime in. that said, we'll look into this too.
PHPq - what you have described should work, you just need to be a little more diligent when it comes to using Windows Authentication in web application scenarios. We suggest browsing thru our blog for useful tips on various issues, as well as blogs.msdn.com/brian_swan for more tips. For starters, you could just use the example applications and attempt to deploy them as you would your custom application. we'll be happy to support you when you run into any issues - i suggest you use our forum to post them.
Hi,
We have been trying to get wordpress working on IIS7 with SQL Server but just hitting a brick wall. PHP is functioning OK but the WP config creation step fails with unable to connect to database. We have run a FRT and it just shows an HTTP 500 error with FastCgiModule - no more info provided in trace.
We added our own wp-config.php file with the correct connection values for our SQL Server database and then tried hitting the Wordpress site. It spins for about a minute before returning the same unable to connect to DB error. The FRT log shows the same 500 error in fast CGI and with no additional info provided.
We have no idea what the error is as there is so little info being provided in the request trace. Can anyone provide some pointers about how we can troubleshoot this?
Thanks,
Jason
Just trying to get a solid framework for MDX queries in PHP over here.