In the ASP.NET frontend, we only need to replace the ADO.NET driver from MySQL to SQL Server driver and it is all done. In the PHP CMS, however, we will need to do some adaptation since PHP did not have a unified API for database driver.
Lucky enough, it is not much work as there is a driver layer in the CMS. There were only three functions we need to change: query, fetch, total which corresponding to mysql_query, mysql_fetch_arry, and mysql_num_rows in MySQL driver and sqlsrv_query, sqlsrv_fetch_array, and sqlsrv_num_rows in SQL Server.
The major differences can be summarizes as follows:
- Getting last inserted record identity
- Scalar functions
- Aggregation functions
- Escaping strings
1. Getting last inserted record identity
MySQL PHP driver provides mysql_insert_id() to get the auto-increment column value of the last record inserted. There is no equivalent function in SQL Server PHP Driver. However, it is no more than just querying "@@IDENTITY" in SQL Server.
While doing paging in MySQL only takes "LIMIT n OFFSET skip", SQL Server will need
SELECT * FROM ( SELECT TOP n * FROM ( SELECT TOP z columns -- (where z = n + skip) FROM table_name ORDER BY key ASC ) AS a ORDER BY key DESC ) AS b ORDER BY key ASC
3. Scalar functions
MySQL uses NOW() to get the current timestamp. SQL Server uses GETDATE().
MySQL provides RAND() so we could do random order. SQL Server provides NEWID(). Please note that NEWID() generate a GUID so the ordering is random. It does not mean NEWID() is same as RAND() in MySQL.
4. Aggregation functions
MySQL provides many convenient aggregation functions like GROUP_CONCAT(). The queries have to be rewritten for SQL Server or we need to create the corresponding user defined aggregation functions.
5. Escaping strings
Most developers use \' to escape single quote. In fact, both MySQL and SQL Server use two consecutive single quotes to escape a single quote. MySQL just accepting the other formats.
Up to this moment, we discussing the data layer (if you have a data layer in PHP). There is no code change in the business layer yet. This is also an example that how we could modularize a program if we partition layers clearly regardless which platform we are using.