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.

The WinPHP challenge come come to an end and I'm fairly happy with how SuccesSQL has come. I wish I had realised exactly how little time I had in May though. Somehow I managed to forget that we had three family birthdays, a Christening and a weekend holiday to fit in during the month...

I already knew that PHP worked great on Windows and SuccesSQL again proves that PHP is truly cross-platform. The areas that I wanted to explore and learn a little about were running PHP on IIS and using the SqlSrv database connector.

I learnt a lot about setting up PHP in Windows. It's amazing how much easier it was the second time when I installed it on the Leaseweb server. The Web Platform Installer is very good as long as you remember to stop the IIS service before running it! It's also very odd that the installer doesn't install PEAR or PDO_ODBC. I'm very glad that it uses the exact same installer as available at windows.php.net, as it makes correcting these oversights trivial.

Fortunately, Juozas Kaziukenas also wanted to connect to SQL Server, so we collaborated on a Zend Framework Database adapter for SqlSrv on codepex at http://zfmssql.codeplex.com/. Please download and exercise that too as all bug reports (and patches) will be gratefully received!

Like Juokaz, I also really enjoyed the community aspect of this competition too. Twitter was alive with #winphp tweets and it felt as co-operative rather than competitive as we helped each other overcome obstacles.

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.

The WinPHP challenge comes to an end around now, so I've put up a demo installation of SuccesSQL (v0.01!) at http://test.successql.org. The test site takes a lot longer to display the first page than version running in my VM and I have no idea why!

The source code is released under the New BSD license and is available from my subversion repository.

I expect that this code isn't as secure as it should be and isn't especially well tested either. As such, I highly recommend that you don't use it on important databases or on a public facing web server!

Obligatory screen shot:

I've still got lots to do though, but there's enough done that I'm using it for work related stuff when I need to look something up, but can't be bothered to wait for the SQL Server Management tool to get around to starting up. In that sense, it's already a success!

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 finally found some more time to work on SuccesSQL and can now display table structure information:

Zend_Db_Table provides all this information directly which is quite useful, however the intended use-case for Zend_Db_Table is that you extend Zend_Db_Table_Abstract for each table that you want to interact with. Obviously, SuccesSQL doesn't know in advance the names of the tables, so I created a stub class that allowed me to instantiate a Zend_Db_Table dynamically:

class SSQL_Db_Table extends Zend_Db_Table_Abstract
{
}

Then, for any given table, I can instantiate like this:

$table = new SSQL_Db_Table(array('name'=>$tableName));

The metadata about the table is stored in a protected member variable called _metadata. As this is protected, it's not accessible, outside of the class, so I created an accessor method to allow me to get at the data:

class SSQL_Db_Table extends Zend_Db_Table_Abstract
{

    public function getMetadata()
    {
        if (empty($this->_metadata)) {
            $this->_setupMetadata();
        }
        return $this->_metadata;
    }

}

Interestingly, Zend_Db_Table_Abstract lazily loads the _metadata variable and so I had to check if it had been loaded and if not, set it up. The data returned is an multi-dimensional array, with one sub-array per field. A dump looks like this:

array
  'id' => 
    array
      'SCHEMA_NAME' => null
      'TABLE_NAME' => string 'albums' (length=6)
      'COLUMN_NAME' => string 'id' (length=2)
      'COLUMN_POSITION' => int 1
      'DATA_TYPE' => string 'int' (length=3)
      'DEFAULT' => null
      'NULLABLE' => boolean false
      'LENGTH' => int 4
      'SCALE' => int 0
      'PRECISION' => int 10
      'UNSIGNED' => null
      'PRIMARY' => boolean true
      'PRIMARY_POSITION' => int 1
      'IDENTITY' => boolean true
  'artist' => 
    array
      'SCHEMA_NAME' => null
      [etc]

From then on, it's just a case of display formatting.

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 need to get a list of databases from SQL Server for the currently logged in user. Initially, I found a built in function sp_databases. This looked promising, until I discovered that it didn't work for my user. I'm pretty sure that it's related to permissions. The user just has db_owner on a specific database and nothing else. (Mainly as I don't know how to set anything else!)

A bit of googling provided enabled me to develop this SQL statement:

SELECT name, collation_name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

This works, but gives me a list of all (non-system) databases, but doesn't tell me which ones I have permission to actually access.

I'm sure there's a better way, but I discovered that if I try to use a database, then I get an error message back, so a simple foreach loop solved my problem:

$validDatabases = array();
foreach($databases as $name) {
    $sql = "USE [$name]";
    try {
        $this->_db->query($sql);
    } catch (Exception $e) {
        // failed to access the database - ignore it
        continue;
    }
    $validDatabases[] = $name;
}

Now... this is inelegant. Surely there's a better way?!

On the plus side, it works ;)

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'm now at the stage where I'm writing code and came across an error:

Message: 2 [Microsoft][SQL Native Client]
    Named Pipes Provider: Could not open a connection to SQL Server [2]. 

That seemed a little odd, so I fired up the Microsoft SQL Server Management Studio and logged in with the credentials that I'm using in the web app. It let me log in, so I went to click on the Databases item in the Object Explorer and got this message:

Any ideas? All I've found when googling is suggestions to reinstall...