Data Driver

Blog archive

PHP Meets SQL Server -- Converting MySQL Queries

As an addendum to last week's post about a recent SQL Server camp held for PHP developers, Microsoft's Brian Swan has published some new nuts-and-bolts details for converting MySQL queries.

Swan, who blogs on the "At the Intersection of PHP and Microsoft" page, said he learned the technique at the JumpIn! camp, where Microsoft hosted some 17 prominent PHP developers in order to share ideas and educate them about the process of supporting SQL Server and SQL Azure in their PHP applications.

He said it was "an excellent two-way learning experience" and one of the things he learned was that the SQL Server Migration Assistant for MySQL 1.0 (SSMA) can convert specific database queries in addition to its main job of converting databases.

Swan explains the relatively straightforward process by using SSMA to convert the MySQL query:

SELECT post_title, post_date FROM wp_posts ORDER BY post_date LIMIT 5 OFFSET 5;

to the SQL Server query:

SELECT TOP (5) ssma$sub1.post_title, ssma$sub1.post_date
FROM
  (
    SELECT wp_posts.post_title, wp_posts.post_date, ROW_NUMBER() OVER (
      ORDER BY wp_posts.post_date) AS ssma$rownum
    FROM dbo.wp_posts
   ) AS ssma$sub1
WHERE ssma$sub1.[ssma$rownum] > 5
ORDER BY ssma$sub1.[ssma$rownum]
GO

(Whew! If I'm a noob evaluating the two on the basis of query simplicity, it's pretty clear which direction I'd lean.)

Swan noted that all queries won't translate successfully (if they contain functions specific to MySQL, for example), but even if it doesn't work, it can provide clues to help write the translated query yourself.

Do you have experience in supporting SQL Server in PHP apps? Is MySQL really that much simpler than SQL Server? Please share your thoughts here or drop me a line.

Posted by David Ramel on 12/08/2010 at 1:15 PM


comments powered by Disqus

Featured

  • Customize Your Own Audio Test Cues in Visual Studio 2019 v16.9 Preview 3

    Yes, developers can be alerted to a failed test with a fart sound.

  • Progress Touts New Third-Party Blazor UI Components

    Third-party dev tool specialist Progress announced an update to its .NET-centric offerings, touting new controls for Blazor, Microsoft's red-hot project for creating web apps with C#.

  • Entity Framework Core 6: What Developers Want

    Microsoft outlined its plan for Entity Framework Core 6, which in November will take its place as the data access component of the landmark .NET 6, a long-term support (LTS) release that will mark Microsoft's transition from the Windows-only .NET Framework to an open source, cross-platform umbrella offering of all things .NET.

  • AWS Open Sources .NET Porting Assistant GUI

    After previously open sourcing components of its Porting Assistant for .NET, Amazon Web Services open sourced the tool's GUI.

Upcoming Events