As you know, I have been a LAMP developer for years. I simply love all the things that make LAMP amazing – Apache, PHP, MySQL and Linux. Last year some fellow developers were trying to convince me to move away from Apache and switch to nginx but they failed. I am that type of developer who always try to use the most used technologies. The reason for that is because they are usually the most stable.
Recently, I have been asked to develop a PHP application that runs on Windows Server’s IIS and uses Microsoft SQL server as a RDMS. As you might have expected this has been a new experience for me and it has been rather bad.
The reason why I am doing this post is to try and help other developer with PHP/MySQL background switch better. So lets get started!
1. Installing
Best way to install PHP on IIS7 is to use the Microsoft Platform Installer. It provides a Microsoft-style wizard with next-next steps. After you complete the install, restart IIS (World Wide Web publishing service). The wizard also installs a third party PHP manager for IIS Management Console, which is basically a graphic front end for the PHP.ini file’s variables which we are used to editing ourselves in Nano 🙂
After you install it, it will come with a sqlsrv driver which lets you connect to MS SQL server. Now this is the first thing that got me confused. Last time I looked into PHP extensions for SQL server, it was called “mssql”. I was also looking on the PDO installation page at php.net and it was called “mssql” there too. Later on I found out that Microsoft have made their own PHP driver for SQL server and they have called it sqlsrv. Now sqlsrv is much better than mssql (something like mysql and mysqli).
2. PDO
Now the next thing that will get you confused is creating a new PDO object. It seems that Microsoft have made their own syntax which is different from the standard syntax we have in PHP. Have a look at the MSDN page for PDO as you might have noticed the syntax is a little different:
MySQL:
$DBH = new PDO(“mysql:host=$host;dbname=$dbname”, $user, $pass);
MS SqlSrv:
$DBH = new new PDO(“sqlsrv:Server=$host;Database=$dbname”, $user, $pass);
So in the DSN it’s not “host”, but it’s called “Server” and it’s not “dbname” but it’s “Database”. Microsoft inventing their own things again without following the standard (why am I not surprised?).
3. SQL Syntax
Now the next thing you might notice is the different syntax from MySQL. For example, in MySQL we surround the column names (attributes) with grave accents (`) but in MSSQL we suround them with square brackets ([]). In addition to that there is a “dbo” before every table. Have a look at the queries below to see the difference:
MySQL:
SELECT `field1`, `field2`, `field3` FROM `tablename`;
MS SqlSrv:
SELECT [field1], [field2], [field3] FROM [dbo].[tablename];
4. Order By with DISTINCT
In MySQL we can order by anything we like and we don’t have to select it even if we use DISTINCT. In MS SQL, if you are using DISTINCT, you have to select the field which you are ordering by. Jeff from sqlteam has more about this here.
In addition to that you can not have it inside sub-queries. That’s very stupid. What if I don’t want the RDMS to return the field I am ordering by? Has anyone at Microsoft thought of that scenario?
5. Limit
There is no such thing as LIMIT in MSSQL. A possible work around I thought of was a sub-query together with MSSQL’s row_number(). See below:
MySQL:
SELECT `id`, `name` FROM `table` LIMIT 0,50;
MS SqlSrv:
SELECT [sq].[id], [sq].[name] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [t].[id]) AS “rn” [t].[id] AS “id”, [t].[name] AS “name” FROM [dbo].[table] t) sq WHERE [sq].[rn] BETWEEN 1 AND 50;
I dont need to say how lame that is.
UDPATE: In addition to this, you cannot use “ORDER BY” inside subqueries. That makes it impossible to select the data you might want.
UPDATE 2: A possible solution is to store the IDs of the rows you want inside variables and do an “WHERE [id] BETWEEN @minId AND @maxId”.
6. Import/Export
I think I discovered a bug in the Import/Export data wizard for SQL server where the columns were not mapping to fields as they were supposed to when importing from CSV. Have a look at the comment I made at the Oscar Valles post.
7. WHERE statement
You know how we are used to mixing whatever we like in the WHERE clause in MySQL? Well, you cant do that in MSSQL..
SQL query ( SELECT [id] FROM [dbo].[table] WHERE [field1] = ? AND [field2] = ? ) failed. SQLSRV PDO execution caught an exception with message “SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The data types text and nvarchar are incompatible in the equal to operator.”
The most stupid thing
I cant decide which one is the most stupid and badly designed feature because there are so many to choose from..
However my favourite one is the design of the SQL Server Management Studio (SSMS). For example, if you want to edit the values in a table (from a GUI) you need to right click on the table and select “Edit top 200 rows”. After that you get a window that lets you edit the values. First stupid thing here is what happens if I want to edit something which is not in the top 200 rows? I can’t.. The second one is why am I getting all the 200 if I only want to edit 1 of them? And the third one is the instant commitment. As soon as you click out of something it gets saved into the database. You don’t have the option to “Cancel” those changes or to commit (save) them if you like. It all happens as soon as you click out of something – chances of making a mistake and messing up your database are higher than ever. Another hard thing to do in SSMS is browsing records – you can only select the top 1000 and not having the LIMIT statement makes it even harder to go to the next set of rows. Perhaps Microsoft should learn from PhpMyAdmin’s navigation where you have pagination for the records and each page is 30 rows. After that you have an edit option for each individual row where you have the option to cancel or save. You would think that in the 21st century the management software of an RDMS should let you change any record you like without having to issue an UPDATE query yourself.
And talking of UPDATE queries – if we say you have a column which has a length of 5 chars and then you try and update the value for that column in one of the rows with a simple update query:
udpate [dbo].[users]
SET [email] = ‘myLongEmailUserName@myLongDomainName.com’
The error you will get is not something stating that the data is too long but it will be something suggesting you have a syntax error:
Line 1: Incorrect syntax near ‘.’.
Even after you change the design of the table, you will still get this. You need to open a “new query” window and re-run it there.
Well that’s it for now. I will be adding to this post as I go.