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