다음을 통해 공유

Microsoft Drivers for PHP for SQL Server 2.0 released!!

  • 아티클

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.

Microsoft Drivers for PHP for SQL Server

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

  1. A fully functional PDO_SQLSRV driver - all API defined by PDO are implemented, even those marked EXPERIMENTAL by PDO. For clarity, all custom attributes or constants in PDO_SQLSRV start with "PDO::SQLSRV_".
  2. The SQLSRV driver rebuilt with the re-architected code, and several critical bug fixes.
  3. The Connection Options are almost identical for both drivers (exceptions only when the option did not apply to PDO_SQLSRV). However, the Connection Options need to be specified in the connection string of PDO_SQLSRV.
  4. All errors are now encoded per the setting of the CharacterSet connection option in SQLSRV, and the setting of the PDO::SQLSRV_ATTR_ENCODING attribute in PDO_SQLSRV.
  5. PDO does not define an OBJECT data type, so all DATETIMEs from SQL Server are always returned as strings by PDO_SQLSRV. The default for SQLSRV continues to be PHP's DateTime object and can be modified using the ReturnDatesAsStrings in the Connection Options array.
  6. The custom attribute PDO::SQLSRV_ATTR_DIRECT_QUERY offers many benefits and additional flexibility for developers:
    1. It enables a more performant use of the PDO::query() API
    2. It enables the use of SQL Server's temporary tables feature by multiple queries
    3. It offers the ability to bind parameters not traditionally offered by PDO::query()
  7. In PDO_SQLSRV, a developer has the additional flexibility to encode data at per connection, and per column.
  8. PDO offers the ability to define IN and IN_OUT parameters, in addition PDO_SQLSRV offers the capability to define OUT parameters very easily.
  9. Developers can set the query timeout using the PDO::SQLSRV_ATTR_QUERY_TIMEOUT per connection and per statement, and exploit it to provide a better user experience in their applications.
  10. Both drivers, SQLSRV & PDO_SQLSRV, require SQL Server Native Access Client 2008 R2 which offers an improved experience when connecting to SQL Azure as well as SQL Server 2005 and later.
  11. The example application for SQLSRV updated to demonstrate new features in our SQLSRV driver in our v1.1 release, and the same application is also ported to demonstrate the same functionality using PDO_SQLSRV.

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

Comments

  • Anonymous
    August 05, 2010
    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?

  • Anonymous
    August 05, 2010
    The comment has been removed

  • Anonymous
    August 05, 2010
    Casey's answer is correct. I've also clarified it in the code comments.

  • Anonymous
    August 08, 2010
    The comment has been removed

  • Anonymous
    August 08, 2010
    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);            }            catch(Exception $e)            {                die( print_r( $e->getMessage() ) );            }            $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. <?php $serverName = "192.168.1.4"; $db_user     = "user"; $db_pw         = "password"; $db_name     = "sampledb"; $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";            $params = array(&$NameSurname);            $stmt = sqlsrv_prepare( $ms_con, $tsql, $params);            if( $stmt === false )            {                die( FormatErrors( sqlsrv_errors() ) );            }            if( sqlsrv_execute( $stmt) === false)            {                die( FormatErrors( sqlsrv_errors() ) );            }            /*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)            { die( FormatErrors( sqlsrv_errors() ) ); }            // 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.

  • Anonymous
    August 08, 2010
    The comment has been removed

  • Anonymous
    August 08, 2010
    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!

  • Anonymous
    August 09, 2010
    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?

  • Anonymous
    August 09, 2010
    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.

  • Anonymous
    September 26, 2010
    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

  • Anonymous
    October 08, 2010
    Just trying to get a solid framework for MDX queries in PHP over here.