Rob Allen's DevNotes

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

To get my Zend Framework Tutorial working with my IIS7 & MS SQL server set up, I needed two things:

1. A Sqlsrv adapter for Zend Framework.
2. A replacement Apache's mod_rewrite module.

I've already covered the Sqlsrv adapter, so let's look at rewriting requests with IIS7's URL Rewrite module. As with everything in IIS, you get at this tool via the IIS Manager GUI. Be aware though that the Url Rewrite icon is available at server level and at the website level.

You need the website level which is accessed under the "Sites" section which is under the "Server name" section. In my case it is called "Default Web Site". You now click on "Import Rules" on the right hand action list and get this screen:

Simply paste in these rules:

RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^.*$ /zf-tutorial/public/index.php [NC,L]

and press "Apply"

Note that this rules doesn't match the official ZF rules, but it performs essentially the same functionality and generates a much clearer rule within the IIS URL Rewrite manager.

Also, you need to put the full path to the index.php file for it to work. It's much easier to create a new website within the IIS manager and point it at the public directory.

My Zend Framework tutorial website is now working though, so it's full steam ahead on the actual application.

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

I haven't managed to get much time on the challenge this week as I had hoped. Both my sons' birthday parties have taken place along with one son's birthday (the other's is tomorrow).

It turns out that fellow challenger Juozas Kaziukenas also needs a connection between Zend Framework and SQL Server, so we have joined forces to create App_Db_Adapter_SqlSrv and associated classes. Juozas started a project on codeplex at http://zfmssql.codeplex.com/ and has enabled me to commit to the project too.

Juozas committed an initial implementation and so far I've updated the _connect() method to allow for all the parameters supported by sqlserv_connect().

My current application.ini looks like this:

resources.db.adapter = SQLSRV
resources.db.params.adapterNamespace = "App_Db_Adapter"
resources.db.params.host = "RKAWIN2008\SQLEXPRESS"
resources.db.params.username = "rob"
resources.db.params.password = "123456"
resources.db.params.dbname = zf-tutorial
resources.db.params.driver_options.ConnectionPooling = 1
resources.db.params.driver_options.Encrypt = 0
resources.db.params.driver_options.TransactionIsolation = SQLSRV_TXN_READ_COMMITTED

The adapter correctly converts the SQLSRV_TXN_READ_COMMITTED string from the ini file into the constant value required by the sqlsrv_connect() function using the very useful constant() function.

I had a slight hiccup with detection of the table's primary key. Microsoft's documentation is quit clear once you have found what you are looking for, so that was easily fixed.

At this point the adapter is working for my ZF tutorial, though more work needs to be done on it.

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

It's time to connect PHP to MS SQL Server. To make life simple, I decided to take my Zend Framework tutorial and get that going.

I created the database and albums table using the SQL Server Management Studio and populated with a couple of rows.

Using The SqlSrv API Reference, I started by with a simple test PHP script:

$connectionInfo = array(
    'Database'=>'zf-tutorial',
    'UID'=>'rob',
    'PWD'=>'123456',
);
$conn = sqlsrv_connect('RKAWIN2008\SQLEXPRESS', $connectionInfo);

if ($conn === false) {
    echo "<p&gtConnection could not be established.</p&gt <pre>";
    die(print_r(sqlsrv_errors(), true));
}

// Tidy up
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

which gave me the error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

A quick google around suggested that I needed to install the SQL Server Native Client, so I did :) This worked!

It seems a little odd that the Web Platform Installer let me install PHP and MSSQL Server Express, but didn't do the native client at the same time!

Once you have a connection, it gets easy! The code to retrieve data from the database and iterate over it is:

$sql = "SELECT * FROM albums";
$stmt = sqlsrv_query($conn, $sql);
if ($stmt === false) {
     echo "<p>Error in query preparation/execution.</p>";
     die(print_r(sqlsrv_errors(), true));
}

// Retrieve each row as an associative array and display the results.
while ($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
      echo $row['artist'] . ': ' . $row['album'] . "<br/>n";
}

which gives the output:

The next step is to connect and use a Zend_Db_Adapter, but there isn't one yet. I've also discovered that you can't use the SQL Server driver for PHP with PDO, so I'll be creating an App_Db_Adapter_Sqlsrv which, assuming it works, I'll propose for addition to Zend Framework.

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

I thought I'd spend a little time sorting out how to connect to MSSQL server using PHP on my Windows 2008 box. I now have my system set up in VMWare Fusion and use SMB to map the wwwroot directory to my OS X environment so I can use my familiar IDE. I am also using Safari for OS X to view the webpages having set up a local entry in my /etc/hosts file.

I quickly noticed that if I made an error, then I'd get back a generic IIS error in the browser:

However, if I viewed this site in IE on the VM using http://localhost as the address, then I could see the actual error.

The solution to this is in the ISS Manager program. Select the server in the left hand tree and then choose the Error Pages option in the main pane. Choosing Edit Feature Settings... in the right hand pane gives access to this window:

I changed to Details errors and then started seeing the real error in Safari. Obviously, on a live server, you'd probably want to use the default option of Details errors for local requests and custom error pages for remote requests. It would be tidier if I could set the 192.169.0.x IP range as "local" though.

Getting the MSSQL server connection working is the subject of another post now :)

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

So.. I've decided on a name: SuccesSQL! I can almost hear the groan from here... What can I say? The name appealed to me :)

The plan is to write a web based management interface for MS SQL Server databases with a focus on inspecting the data and importing/exporting it. Essentially, I want to be able to inspect the schema and query data without having to go to all the hassle of firing up a VM, connecting to a VPN, starting the SQL Server management application, connecting to the SQL Server, drilling down though five levels of tree on the left just to find out if how many chars a given varchar has been assigned.

I'm intending to write SuccesSql so that it operates quickly with the minimum of fuss.

The plan

The application will be written using Zend Framework 1.8. The Zend_Db component doesn't have a good adapter for MSSQL, so the first job will be to write one.

The main goals are:

• Easy to use UI
• Inspect database schema
• A data search functionality (global would be nice too)
• Ability to run arbitrary SQL statements against the database

Going forward, additional functionality that would be useful is:

• Export a database table / query result-set to a CSV file
• Eexport a database to a single text file
• Table administration: add, update and delete
• Inspection and administration of indexes
• Management of users

The next step is to understand the SQL Server Driver for PHP and compare it to using ODBC directly.