Cake PHP With MSSQL Server 2005 2008

I just came across a requirement where the customer wants to use Cake PHP with an SQL Server, not an Mssql server, because of the huge database. There are lots of lots of examples of CakePHP with MySQL but MS Sql server is not that common. There were hundreds of stored procedures and tables, many custom data types etc. in SQL server.

It took a while for me to run CakePHP with an SQL server. There are very few examples of this. So, I thought to share my experience with others.

You Cannot Use Ubuntu Or Any Non-Windows OS If You Want To Use MS SQL With Cake.

I assume that you have the latest WAMP installed or apache/Php is already installed.

First I used latest CakePHP 2.2 with Sql server 2005, but it should work with latest Sql server versions also.

1. Download the installer from https://www.microsoft.com/en-us/download/details.aspx?id=20098

2. Install it at some place and then copy the following two files into binphpphp5.4.16ext of WAMP installed folder. Otherwise, put then under normal phpext folder if you are not using WAMP

php_pdo_sqlsrv_54_ts.dll

php_sqlsrv_54_ts.dll

“ts” means thread safe. If your app is not thread safe then copy php_sqlsrv_54_nts.dll and php_pdo_sqlsrv_54_nts.dll files.

3. Once copied, write following two lines in your php.ini where all the extensions are defined.

extension=php_sqlsrv_54_ts.dll

extension=php_pdo_sqlsrv_54_ts.dll

If you are using WAMP then you need to change in both php.ini files at following location, where 5.4.16 is the version of Php and 2.4.4 is the version of apache.

binphpphp5.4.16

binapacheApache2.4.4bin

4. Restart WAMP (Apache if not using WAMP) so that updated files are used.

5. Open the notepad, write the following line and save it as info.php (or any other name with Php as extension)

phpinfo();

6. Now, go to https://localhost/info.php URL and you will get a large page in browser with lots of info.

7. If you search for PDO drivers, sqlsrv support and pdo_sqlsrv on this page, you should see sqlsrv in enabled.

8. If you can point 7 entries on the page, it means Good News, your WAMP is setup for MS Sql server.

9. Move on to CakePHP. First thing first is the data source. Your data source should be correct if cake has to work with the database. It’s in database.php file in appConfig folder. If there is no database.php then either rename or copy database.php.default to database.php

10. Your data source should be like following for MS Sql Server.

11. Save the file and your cake should work now with SQL server.

12. To test it, create a model and controller, in that controller says
$result = $this->ControllerName->query(“select * from tablename”);
print_r($result);

If you run it in browser, you should see all the data from table.

Things need to take care while working with cake and MS Sql Server. I will update this list as and when I come across with new issues.

1. You need to careful. Every table should have a primary key otherwise update () of cake will not work because for update () in Sql server, cake first do select and then update.