Using the Zend Framework and the PDO_SQLSRV Driver
A couple of months ago, Rob Allen pointed out in a blog post that no Zend Framework PDO adapter existed for SQL Server. But, he also noted that it would be easy to write one…so he did: https://github.com/akrabat/Akrabat/blob/master/zf1/Akrabat/Db/Adapter/Pdo/Sqlsrv.php. Rob also mentioned that it would be very easy to use his adapter with the Zend Framework, but I wondered just how easy – that’s what I’ll investigate in this post. (Cut to the chase: it is very easy.)
I’m a newbie when it comes to the Zend Framework, so I turned to Rob for advice about getting started. He pointed me to a tutorial he wrote, which you can download here: http://akrabat.com/zend-framework-tutorial/. I highly recommend this tutorial for getting started with the Zend Framework. Not only will you be up and running at the end of it, you will have a better understanding of the MVC (Model-View-Controller) architectural pattern. In fact, I found the tutorial so good, that it would be a waste of time for me to attempt to improve upon it. However, I undertook the tutorial with the idea of getting things set up on Windows/IIS/SQL Server (using the adapter I mentioned above). The tutorial covers how to use ZF on Windows, but it uses Apache and MySQL, so I’ll cover the “diffs” for working through the tutorial with the aim of getting things running on IIS and SQL Server. I’ll simply call out the sections of Rob’s tutorial that don’t apply or where I had to modify the tutorial. With that in mind, this post will make the most sense if you also have Rob’s tutorial open and can refer back and forth between the two.
Before I get started, here is my relevant system information: Windows 7, IIS 7.5, SQL Server 2008 R2 Express, Zend Framework 1.11.1.
Tutorial assumptions
Because I’ll be using IIS, the Apache-related assumptions in this section do not apply. However, you do need to make sure that you have the IIS URL Rewrite module installed, which you can download here: http://www.iis.net/download/URLRewrite. Later, I’ll show how you can use the URL Rewrite module to import Apache rewrite rules to IIS.
Setting up Zend_Tool (Zend_Tool for Windows)
Only one comment on the instructions in this section: I didn’t need to reboot after modifying my PATH environment variable.
When using the Zend_Tool in subsequent sections, do so from a command prompt that is running with administrator privileges.
Getting our application off the ground
I found it easiest to create the example application (zf-tutorial) in the c:\inetpub\wwwroot directory (the default root directory for IIS).
In this section, you are instructed to copy the library/Zend/ directory from your downloaded archive to your project’s zf-tutorial/library/ directory. This is also a good time to download Rob’s PDO adapter for SQL Server (from here) and copy the zf1/Akrabat/ directory to your project’s zf-tutorial/library/ directory. (So your project’s zf-tutorial/library/ directory should now have two sub-directories: Zend and Akrabat.)
Setting up the Controller
In this section you will test the Zend Framework installation. Before you do so, you need to import the Apache rewrite rules to IIS. Here’s how to do that:
1. The URL Rewrite Module does not support the –s or –l flags for Apache rewrite rules. To work around this, open the .htaccess file and change the rewrite condition containing –s to –f (which the module does support) and remove the rewrite condition containing –l. The Apache rewrite rules should now look like this:
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} -f [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^.*$ - [NC,L]
RewriteRule ^.*$ index.php [NC,L]
In practice, these rules should work as well as the original rules.
2. Open IIS Manager, navigate to your project’s root directory, and open the URL Rewrite Module (double click the icon like the one below).
3. Click Import Rules…
4. Navigate to the project’s .htaccess file:
5. Click Import.
6. Finally, click Apply in the Actions pane:
You should now be able to successfully test your Zend Framework installation.
Database Configuration
Instead of adding the lines to the application.ini file that are suggested in this section, add the following:
resources.db.adapter = "Pdo_SqlSrv"
resources.db.params.adapterNamespace = "Akrabat_Db_Adapter"
resources.db.params.host = "ServerName\SQLEXPRESS"
resources.db.params.username = UserName
resources.db.params.password = Password
resources.db.params.dbname = DatabaseName
Create the database table
Instead of using the CREATE TABLE statement (written for MySQL), use the following statement (written for SQL Server):
CREATE TABLE [dbo].[albums](
[id] [int] IDENTITY(1,1) NOT NULL,
[artist] [varchar](100) NOT NULL,
[title] [varchar](100) NOT NULL,
CONSTRAINT [PK_albums] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
(Note that the data insertion script in the tutorial works just fine.)
That’s it! If you follow the rest of the tutorial, you should have an example application using the Zend Framework on Windows/IIS/SQL Server (using Rob’s PDO adapter). And, to boot, you should understand a lot more about the MVC architectural pattern.
Thanks.
-Brian