Software Development in North Wales

Windows Media Player saves playlists in an XML compliant format called WPL. This XSLT script will convert a WPL file into an HTML file that you can print (or copy and paste into a document):

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!-- rename 'smil' element as 'html' (and ignore any attributes) -->
<xsl:template match="smil">
<xsl:element name="html">
<xsl:apply-templates/>
</xsl:element>
</xsl:template>
<!-- include the playlist title as an h1 element in the body -->
<xsl:template match="body">
<xsl:element name="body">
<xsl:element name="h1"><xsl:value-of select="../head/title"/></xsl:element>
<xsl:apply-templates/>
</xsl:element>
</xsl:template>
<!-- rename 'seq' element as 'ol' (numbered list) (and ignore any attributes) -->
<xsl:template match="seq">
<xsl:element name="ol">
<xsl:apply-templates/>
</xsl:element>
</xsl:template>
<!-- rename 'media' element as 'li' (list item) -->
<xsl:template match="media">
<xsl:element name="li">
<xsl:value-of select="@src"/>
</xsl:element>
</xsl:template>
<!-- this template captures and ignores any elements we want to discard -->
<xsl:template match="author | meta | @tid">
<!-- ignore these elements -->
</xsl:template>
<!-- this is the 'identity' template, which copies any other content -->
<xsl:template match="@*|*">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>

Save the above file as 'wpl2html.xslt' and you can then use this DOS batch file to execute it:

@ECHO OFF
REM Check there is a parameter
IF "%1" == "" GOTO MISSING_PARAMETERS
:START
SET file=%1
IF NOT EXIST %file%.wpl GOTO MISSING_FILE
:MAIN
ECHO Creating HTML...
java -jar ../resources/saxon8.jar %file%.wpl wpl2html.xslt >%file%.html
ECHO Done
GOTO END
:MISSING_PARAMETERS
ECHO Usage: "go myplaylist" to process "myplaylist.wpl" and create "myplaylist.html"
GOTO END
:MISSING_FILE
ECHO Error: '%file%.wpl' not found
GOTO END
:END
@ECHO ON


Labels: playlist, wpl, xslt

My pet hate is entering events in my calendar - it's so tediously manual and error prone. So here's a way of generating a calendar file that can be imported into any calendar that supports the vCal standard (in my case, FirstClass, but in your case, probably MS Outlook).

Confession time: I often use spreadsheets to quickly generate chunks of code. Tedious though it may be to devise formulae to generate text, it's easy to re-use. There are plenty of alternatives, but if you've got Excel or similar taking up valuable disk space, I reckon you might as well use it. So here's an Excel spreadsheet that lets you enter (or paste in) a list of dates, as a first step to generating a vCal file that you can import into your Calendar:



The data you enter goes in columns A to G (Date, What, Where, Start Time, End Time , Comments, and Available [Y or N = whether or not you are free to accept other appointments that clash with this one]).

Column K contains the vCal formatted events - just copy the contents of this column (minus the heading), paste it into Notepad (or a similar text editor), and (this is important) do a global replace to remove any double quotes (Excel adds these around each item, but we don't want them). Then save the file as something.vcs, and import it into your calendar - hey presto!

Here's the spreadsheet (save this as vCal-helper.xml and it will open in Excel when you double-click on it):

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Version>11.6360</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>1560</WindowHeight>
<WindowWidth>7515</WindowWidth>
<WindowTopX>360</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Font ss:FontName="Arial Narrow" x:Family="Swiss"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s22">
<Font ss:FontName="Arial Narrow" x:Family="Swiss"/>
<NumberFormat/>
</Style>
</Styles>
<Worksheet ss:Name="Events">
<Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="6" x:FullColumns="1"
x:FullRows="1" ss:StyleID="s21">
<Column ss:StyleID="s21" ss:Width="45.75"/>
<Column ss:StyleID="s21" ss:Width="30"/>
<Column ss:StyleID="s21" ss:Width="33"/>
<Column ss:StyleID="s21" ss:Width="45"/>
<Column ss:StyleID="s21" ss:Width="41.25"/>
<Column ss:StyleID="s21" ss:Width="47.25"/>
<Column ss:StyleID="s21" ss:Width="45.75"/>
<Column ss:StyleID="s21" ss:Width="77.25" ss:Span="1"/>
<Column ss:Index="10" ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="93"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0"/>
<Row>
<Cell><Data ss:Type="String">Date</Data></Cell>
<Cell><Data ss:Type="String">What</Data></Cell>
<Cell><Data ss:Type="String">Where</Data></Cell>
<Cell><Data ss:Type="String">Start Time</Data></Cell>
<Cell><Data ss:Type="String">End Time</Data></Cell>
<Cell><Data ss:Type="String">Comments</Data></Cell>
<Cell><Data ss:Type="String">Available?</Data></Cell>
<Cell><Data ss:Type="String">Start Time (vCal)</Data></Cell>
<Cell><Data ss:Type="String">End Time (vCal)</Data></Cell>
<Cell><Data ss:Type="String">vCal prep</Data></Cell>
<Cell><Data ss:Type="String">vCal item</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">LEAVE THIS ROW EMPTY</Data></Cell>
<Cell ss:Index="10"><Data ss:Type="String">BEGIN:VCALENDAR|VERSION:1.0</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">BEGIN:VCALENDAR VERSION:1.0</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">39056</Data></Cell>
<Cell><Data ss:Type="String">Work</Data></Cell>
<Cell><Data ss:Type="String">Chester</Data></Cell>
<Cell><Data ss:Type="String">0900</Data></Cell>
<Cell><Data ss:Type="String">1730</Data></Cell>
<Cell ss:Index="7"><Data ss:Type="String">N</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061205T090000</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061205T173000</Data></Cell>
<Cell
ss:Formula="=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('vCal Template'!R1C1,"%end%",Events!RC9),"%where%",Events!RC3),"%start%",Events!RC8),"%what%",Events!RC2),"%comments%",Events!RC6),"%available%",IF(Events!RC7="N","0","10"))"><Data
ss:Type="String">BEGIN:VEVENT|CLASS:PUBLIC|DTEND:20061205T173000Z|LOCATION;ENCODING=QUOTED-PRINTABLE:Chester|PRIORITY:0|DTSTART:20061205T090000Z|SUMMARY;ENCODING=QUOTED-PRINTABLE:Work|X-COLOR:4291624704|X-SHOWTIME:0|DESCRIPTION;ENCODING=QUOTED-PRINTABLE:|END:VEVENT</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">BEGIN:VEVENT CLASS:PUBLIC DTEND:20061205T173000Z LOCATION;ENCODING=QUOTED-PRINTABLE:Chester PRIORITY:0 DTSTART:20061205T090000Z SUMMARY;ENCODING=QUOTED-PRINTABLE:Work X-COLOR:4291624704 X-SHOWTIME:0 DESCRIPTION;ENCODING=QUOTED-PRINTABLE: END:VEVENT</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">39059</Data></Cell>
<Cell><Data ss:Type="String">Day off</Data></Cell>
<Cell ss:Index="7"><Data ss:Type="String">Y</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061208T000000</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061208T000000</Data></Cell>
<Cell
ss:Formula="=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('vCal Template'!R1C1,"%end%",Events!RC9),"%where%",Events!RC3),"%start%",Events!RC8),"%what%",Events!RC2),"%comments%",Events!RC6),"%available%",IF(Events!RC7="N","0","10"))"><Data
ss:Type="String">BEGIN:VEVENT|CLASS:PUBLIC|DTEND:20061208T000000Z|LOCATION;ENCODING=QUOTED-PRINTABLE:|PRIORITY:0|DTSTART:20061208T000000Z|SUMMARY;ENCODING=QUOTED-PRINTABLE:Day off|X-COLOR:4291624704|X-SHOWTIME:10|DESCRIPTION;ENCODING=QUOTED-PRINTABLE:|END:VEVENT</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">BEGIN:VEVENT CLASS:PUBLIC DTEND:20061208T000000Z LOCATION;ENCODING=QUOTED-PRINTABLE: PRIORITY:0 DTSTART:20061208T000000Z SUMMARY;ENCODING=QUOTED-PRINTABLE:Day off X-COLOR:4291624704 X-SHOWTIME:10 DESCRIPTION;ENCODING=QUOTED-PRINTABLE: END:VEVENT</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">39062</Data></Cell>
<Cell><Data ss:Type="String">Work</Data></Cell>
<Cell><Data ss:Type="String">Bangor</Data></Cell>
<Cell><Data ss:Type="String">0900</Data></Cell>
<Cell><Data ss:Type="String">1730</Data></Cell>
<Cell ss:Index="7"><Data ss:Type="String">N</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061211T090000</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061211T173000</Data></Cell>
<Cell
ss:Formula="=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('vCal Template'!R1C1,"%end%",Events!RC9),"%where%",Events!RC3),"%start%",Events!RC8),"%what%",Events!RC2),"%comments%",Events!RC6),"%available%",IF(Events!RC7="N","0","10"))"><Data
ss:Type="String">BEGIN:VEVENT|CLASS:PUBLIC|DTEND:20061211T173000Z|LOCATION;ENCODING=QUOTED-PRINTABLE:Bangor|PRIORITY:0|DTSTART:20061211T090000Z|SUMMARY;ENCODING=QUOTED-PRINTABLE:Work|X-COLOR:4291624704|X-SHOWTIME:0|DESCRIPTION;ENCODING=QUOTED-PRINTABLE:|END:VEVENT</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">BEGIN:VEVENT CLASS:PUBLIC DTEND:20061211T173000Z LOCATION;ENCODING=QUOTED-PRINTABLE:Bangor PRIORITY:0 DTSTART:20061211T090000Z SUMMARY;ENCODING=QUOTED-PRINTABLE:Work X-COLOR:4291624704 X-SHOWTIME:0 DESCRIPTION;ENCODING=QUOTED-PRINTABLE: END:VEVENT</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">LEAVE THIS ROW EMPTY</Data></Cell>
<Cell ss:Index="10"><Data ss:Type="String">END:VCALENDAR</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">END:VCALENDAR</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="vCal Template">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:StyleID="s21">
<Row>
<Cell><Data ss:Type="String">BEGIN:VEVENT|CLASS:PUBLIC|DTEND:%end%Z|LOCATION;ENCODING=QUOTED-PRINTABLE:%where%|PRIORITY:0|DTSTART:%start%Z|SUMMARY;ENCODING=QUOTED-PRINTABLE:%what%|X-COLOR:4291624704|X-SHOWTIME:%available%|DESCRIPTION;ENCODING=QUOTED-PRINTABLE:%comments%|END:VEVENT</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>


A friend asked me if there was a way (on a Windows XP PC) to capture a sequence of images (e.g. every 5 minutes for a couple of hours) from a webcam on the Internet. An image from a webcam embedded in a web page is no different from any static image (unless it's wrapped inside a Flash or Java container), so a single capture is usually as simple as right-clicking on the image and choosing "Save Image As...". However, for the automated capture of a sequence of images something else is needed.

There are probably cheap shareware tools out there that can do this (there is no shortage of apps that can do the reverse, capturing images from an attached camera and publishing them to the web), but I hate installing new cruft on my PC for simple jobs, and since I'd recently installed cURL (a command line tool for downloading files with a URL) I thought I'd figure out a scripted solution. The installer for the Windows XP version of cURL can be found here (versions for other operating systems are here).

Once cURL is installed, we can invoke it from any folder on our system provided we add it to a Windows environment variable called "Path" (instructions on how to change environment variables can be found here). In my case, cURL is installed to "C:\Program Files\curl-7.15.3" so I appended ";C:\Program Files\curl-7.15.3" to the end of my Path. You can check it's working by opening a DOS shell (Start > Run > cmd) and entering curl at the prompt - you should get a message saying curl: try 'curl --help' or 'curl --manual' for more information
If you get 'curl' is not recognized as an internal or external command, operable program or batch file then cURL is not installed, or the path to it is not in your Path variable.

We can now invoke cURL from a batch file and capture an image: with Notepad you can create a file called "capture.bat" (if you put the name in quotes when saving from Notepad, ".txt" won't get appended to the end of the filename). The file only needs one line:

curl -o test.jpg http://path/to/image/file

The -o test.jpg tells cURL to create an output file called test.jpg (in the current folder), and the remainder of the line is the URL of the image file - you can find this out by right-clicking on the image in your browser and choosing "Copy Image Location" (Firefox) or "Properties" (Internet Explorer). You can run your batch file by double-clicking on it.

After this, I started experimenting with DOS commands to loop for a specified time and rename each captured image file to something time-based and unique, however I soon discovered that this is not as easy as it sounds - I'm sure it is possible with some serious DOS trickery (such as is described here), but I gave up - it was too arcane and kludgy for me.

Any Linux/UNIX enthusiast will tell you that the bash shell is ideal for this kind of scripting. Fortunately, Windows users can also run bash scripts - we just need to install the amazing cygwin (a Linux-like environment for Windows) - click here to install it. Installation instructions for Windows XP can be found here.

Once cygwin is installed, browse to your home directory (which will have the same name as your Windows username - mine is "c:\cygwin\home\Rich Edwards"). Create a new file called "capture" (if you're using Notepad, remember the quotes around the name when saving to prevent Notepad from adding the ".txt") and enter the following script:

#!/bin/bash

# The following three lines are parameters you can change
duration=300
interval=30
url=http://www.ogwen-rescue.org.uk/oggi_cam/bethesdacam.jpg

# Record the start time (this will give us
# the number of seconds elapsed since 01/01/1970)
time_start=$(date +%s)

# Some output
echo
echo Every $interval seconds for the next $duration seconds
echo this script will capture an image from $url
echo

# Start an infinite loop
while [ 1 ]
do

# The time now (number of secs elapsed since 01/01/1970))
time_now=$(date +%s)

# Construct the filename (this will be unique provided
# our interval >= 1 second)
filename=$time_now.$image_format

# Grab the image and store it in the file
echo Creating $filename
curl -o $filename $url

# If the elapsed time is greater than the specified
# duration exit the loop
if [ $(($time_now - $time_start)) -ge $duration ]
then
echo
echo 'Time duration exceeded - exiting!'
break
fi
echo

# Pause for the specified interval
sleep $interval

done

This script will capture an image from Ogwen Mountain Rescue's webcam (donations) every 30 seconds for 5 minutes, but you can customise it for any duration, interval and/or URL. If cURL returns an error but you are sure your URL is correct, have a look at the cURL help (type curl --help) for how to use additional parameters; for example, there is a -A switch that lets you specify additional info that lets cURL pretend to be a web browser asking for the resource (some webservers are configured to prohibit requests which do not appear to come from a browser).

Now to run the script: start Cygwin from the "cygwin.bat" file in your cygwin folder (e.g. "c:\cygwin\cygwin.bat"). This will start a Cygwin shell that looks much like a DOS shell (but fortunately it's way more functional!). Enter pwd (print working directory) to see your current location relative to the cygwin folder ("/home/Rich Edwards" in my case). Now enter chmod +x capture - this changes the "mode" of the capture script to "executable" (this only needs to be done once).

Now all that's needed is to execute the script with ./capture every time we want to capture images from the specified webcam. The preceding dot and slash tells cygwin that the script is in the current folder (otherwise it will search the Windows Path and not find it).

If you've followed me this far then you now also have the benefit of an amazingly useful scripting environment! There are lots of useful intros to bash - see here for a tutorial for absolute beginners. Happy scripting!

Nano and I wrote this procedure nearly a year ago, but I needed it today so I've resurrected it, fixed a minor collation-related bug, and thought I'd release it to the public (via this blog and also here in my snippets library). It generates a 'cross-tab' (or 'pivot table') result set from (almost) any query, even a stored procedure, and optionally allows you to specify a column sort order.

The procedure makes extensive use dynamic SQL and hence 'sp_executesql'. Because the scope of temporary tables does not extend outside of an sp_executesql transaction, I've had to use global temporary tables, which means that this procedure will become a bottleneck if used too frequently by multiple sessions. That said, we've been using it in production for over a year now for an app that has relatively few users, and it works a treat.

This stored procedure is provided 'as is' and without any warranty - I've only tested it on a relatively small number of queries. If you want help to build it into a production app, please get in touch (email rich at semantise dot com).

CREATE PROCEDURE [dbo].[genericCrosstab]

-- @tableSpec
-- A comma delimited list of columns with datatypes (and null constraints, if required)
-- e.g. 'col1 INT, col2 VARCHAR(255) NULL'
-- N.B. You only need provide this list if the source of the data is a stored procedure
@tableSpec NVARCHAR(4000) = '',

-- @sqlSelect
-- The SELECT clause (or 'EXEC %stored_procedure%' statement) for the input data
-- e.g. 'SELECT a.au_id, a.au_fname, a.au_lname, a.phone, LEFT(t.title, 10) AS [title], LEFT(s.stor_name, 10) AS [store], sales.qty'
-- e.g. 'EXEC dbo.myStoredProcedure %param1%, %param2%'
@sqlSelect NVARCHAR(512),

-- @sqlFromWhere
-- The FROM and WHERE clauses for the input data (if a SELECT statement)
-- e.g. 'FROM authors a, titleauthor ta, titles t, stores s, sales WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.title_id = sales.title_id AND s.stor_id = sales.stor_id'
-- N.B. Leave this blank if the source of the data is a stored procedure
@sqlFromWhere NVARCHAR(512) = '',

-- @pivotExpr
-- The column name or expression to use as the pivot (specifies which values are the column headings for the crosstabbed data)
-- e.g. 'store' or '''Store: '' + [store]'
@pivotExpr NVARCHAR(255),

-- @valueExpr
-- A column name or expression to use as the values in the crosstabbed data
-- e.g. 'qty'
@valueExpr NVARCHAR(512),

-- @function
-- The aggregation function to use to combine values in the crosstabbed data
-- e.g. 'MIN'
-- N.B. If this parameter is left blank, the procedure will count up occurences and create additional column headings as necessary, e.g. '[X], [Y], [Y (2)], [Z]' if there are two occurences for label 'Y'
@function NVARCHAR(20) = '',

-- @groupBy
-- The column list to group by (these columns will appear to the left of the crosstabbed data)
-- e.g. 'au_id, au_fname, au_lname, phone'
@groupBy NVARCHAR(512),

-- @sortLookup
-- A table or view which can be queried to provide a sort order for the columns in the crosstabbed data
-- e.g. 'days_of_the_week'
-- N.B. The specified table or view must have 'label' and 'sort_order' columns
-- N.B. If column order is irrelevant, leave this parameter blank
-- N.B. Feature: if @sortLookup is given as '*', the columns are placed in ascending alphabetical order
@sortLookup NVARCHAR(40) = '',

-- @collation
-- The collation sequence
-- e.g. 'Latin1_general_CI_AS'
@collation NVARCHAR(40) = 'Latin1_general_CI_AS',

-- @verbose
-- Set to 1 if you want debug messages
@verbose BIT = 0

AS
BEGIN

-- Check that the mandatory parameters are not empty strings
IF LEN(RTRIM(ISNULL(@sqlSelect, ''))) = 0
BEGIN
RAISERROR ('The @sqlSelect parameter cannot be an empty string.', 16, 1)
ROLLBACK TRANSACTION
END

IF LEN(RTRIM(ISNULL(@pivotExpr, ''))) = 0
BEGIN
RAISERROR ('The @pivotExpr parameter cannot be an empty string.', 16, 1)
ROLLBACK TRANSACTION
END

IF LEN(RTRIM(ISNULL(@valueExpr, ''))) = 0
BEGIN
RAISERROR ('The @valueExpr parameter cannot be an empty string.', 16, 1)
ROLLBACK TRANSACTION
END

IF LEN(RTRIM(ISNULL(@groupBy, ''))) = 0
BEGIN
RAISERROR ('The @groupBy parameter cannot be an empty string.', 16, 1)
ROLLBACK TRANSACTION
END

-- Local variables
DECLARE @i INTEGER
DECLARE @sql NVARCHAR(4000)
DECLARE @sqlX NVARCHAR(4000)
DECLARE @col NVARCHAR(4000)
DECLARE @pivot NVARCHAR(4000)
DECLARE @indx NVARCHAR(10)
DECLARE @cols NVARCHAR(4000)
DECLARE @where NVARCHAR(4000)
DECLARE @update NVARCHAR(4000)
DECLARE @value NVARCHAR(532)
DECLARE @select NVARCHAR(4000)

-- Drop the global temporary tables we will use (if they already exist)
-- N.B. we have to use global temp tables, as local temp tables are dropped at the end of a batch (so are not accessible outside of the sp_executesql context)
-- This syntax is a bit long winded because I couldn't make the substitution syntax for sp_executesql work (for this example only! - a bug in T-SQL?)
SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
EXEC sp_executesql @sqlX

IF @verbose = 1 SELECT 'Global temporary tables dropped (if present).' AS [Message]

-- * The real work starts here

-- Store the input dataset
IF LEN(RTRIM(@tableSpec)) = 0
BEGIN
-- Create and populate the temporary table in one step
SELECT @sql = @sqlSelect + ' INTO ##input ' + @sqlFromWhere
IF @verbose = 1 SELECT @sql AS [Create and populate the temporary table in one step]
EXEC sp_executesql @sql
END
ELSE
BEGIN
-- Create the temporary table first (the only method that can be used if the data is coming from a stored proc)
SELECT @sql = 'CREATE TABLE ##input (' + @tableSpec + ')'
IF @verbose = 1 SELECT @sql AS [Create the temporary table]
EXEC sp_executesql @sql
-- Populate it
SELECT @sql = 'INSERT INTO ##input ' + @sqlSelect + ' ' + @sqlFromWhere
IF @verbose = 1 SELECT @sql AS [Populate the temporary table]
EXEC sp_executesql @sql
END
IF @verbose = 1 SELECT * FROM ##input

-- Does the query we want to cross-tab already have a column called 'crossTabRowID'? This is a reserved column name!
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.id = c.id AND o.name = '##input' AND c.name = 'crossTabRowID')
BEGIN
RAISERROR ('The query passed to the [genericCrosstab] procedure uses a reserved column name (''crossTabRowID'').', 16, 1)
ROLLBACK TRANSACTION
END

-- Add an identity column (we need a 'row ID')
ALTER TABLE ##input ADD crossTabRowID NUMERIC(9, 0) IDENTITY NOT NULL

-- Generate the column list
SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##colList FROM ##input WHERE 1 = 0'
IF @verbose = 1 SELECT @sql AS [Generate the column list]
EXEC sp_executesql @sql
IF @verbose = 1 SELECT * FROM ##colList

-- If no aggregation function is specified, we have to check first that there are no duplicates, and - if there are - we need to handle them
IF RTRIM(ISNULL(@function, '')) = ''
BEGIN
SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], COUNT(1) AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
END
ELSE
BEGIN
SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], 1 AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
END
IF @verbose = 1 SELECT @sql AS [Create the ##grpCount table]
EXEC sp_executesql @sql
IF @verbose = 1 SELECT * FROM ##grpCount

-- Create a temporary table that will act as a lookup (containing all of the non-pivot / non-group columns names)
SELECT [pivot], [count] AS [index], [pivot] AS [column_name] INTO ##lookup FROM ##grpCount WHERE 1 = 0

-- Build the results table; one row per group
SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##results FROM ##grpCount GROUP BY ' + @groupBy
IF @verbose = 1 SELECT @sql AS [Create the ##results table]
EXEC sp_executesql @sql
IF @verbose = 1 SELECT * FROM ##results

-- Build the column list, taking into account duplicate occurences of pivotal values
DECLARE xcursor CURSOR FOR SELECT [pivot], MAX([count]) FROM ##grpCount GROUP BY [pivot]
OPEN xcursor

FETCH NEXT FROM xcursor INTO @pivot, @indx

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @i = 1

-- Loop over indx
WHILE @i <= @indx
BEGIN

-- Build the column list
SELECT @col = CASE @i WHEN 1 THEN @pivot ELSE @pivot + ' (' + CAST(@i AS VARCHAR(10)) + ')' END
INSERT INTO ##lookup VALUES (@pivot, @i, @col)
SELECT @col = '[' + @col + '] NVARCHAR(255) NULL'
SELECT @cols = ISNULL(@cols + ', ', '') + @col

-- Add the column to the results table
SELECT @sql = 'ALTER TABLE ##results ADD ' + @col
IF @verbose = 1 SELECT @sql AS [Add column to the ##results table]
EXEC sp_executesql @sql

-- Continue
SELECT @i = @i + 1

END

FETCH NEXT FROM xcursor INTO @pivot, @indx

END

CLOSE xcursor
DEALLOCATE xcursor

IF @verbose = 1 SELECT * FROM ##lookup
IF @verbose = 1 SELECT * FROM ##results

-- Loop over the column list (using the syscolumns table in the temp database) to build the WHERE clause
DECLARE xcursor CURSOR FOR SELECT DISTINCT c.[colid], c.[name] FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.[id] = c.[id] AND o.[name] = '##colList' ORDER BY c.[colid]
OPEN xcursor

FETCH NEXT FROM xcursor INTO @indx, @col

WHILE @@FETCH_STATUS = 0
BEGIN

-- Build the WHERE clause
SELECT @where = ISNULL(@where + ' AND ', '') + 'ISNULL(t1.[' + @col + '], '''') = ISNULL(t3.[' + @col + '], '''')'

FETCH NEXT FROM xcursor INTO @indx, @col

END

CLOSE xcursor
DEALLOCATE xcursor

IF @verbose = 1 SELECT @where AS [WHERE clause]

-- Create a temp table that will help us build the UPDATE statements to set the values in the pivot table, and the SELECT statement (with columns in preferred order) afterwards
IF RTRIM(ISNULL(@sortLookup, '')) = ''
BEGIN

-- If no sorting table was specified, just select all columns
SELECT @select = '*'

-- Sort alphabetically
SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5 WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY c.[name], t5.[index]'

END
ELSE
BEGIN

-- The 'group by' column(s) always come(s) first
SELECT @select = @groupBy

-- Sort in proscribed order
SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot], l.[sort_order] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5, ' + @sortLookup + ' l WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND t5.[pivot] COLLATE ' + @collation + ' *= l.[label] COLLATE ' + @collation + ' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY l.[sort_order], c.[name], t5.[index]'

END

IF @verbose = 1 SELECT @sql AS [Build the ##temp table]
EXEC sp_executesql @sql
IF @verbose = 1 SELECT * FROM ##temp

-- The value expression
SELECT @value = @valueExpr

-- If a function has been specified
IF RTRIM(ISNULL(@function, '')) != ''
BEGIN
SELECT @value = @function + '(' + @value + ')'
END

DECLARE xcursor CURSOR FOR SELECT [index], [name], [pivot] FROM ##temp
OPEN xcursor

FETCH NEXT FROM xcursor INTO @indx, @col, @pivot

WHILE @@FETCH_STATUS = 0
BEGIN

-- Build the SELECT expression
IF @select != '*' SELECT @select = @select + ', ' + @col

-- Create the SET clause of the UPDATE sql
IF RTRIM(ISNULL(@function, '')) = ''
BEGIN
-- No function specified
SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID = (SELECT MIN(t0.crossTabRowID) FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + ') + (' + CAST(@indx AS VARCHAR(3)) + ' - 1))'
END
ELSE
BEGIN
-- Function specified
SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID IN (SELECT t0.crossTabRowID FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + '))'
END
SELECT @sql = 'UPDATE ##results SET ' + @update + ' FROM ##results t3'
IF @verbose = 1 SELECT @sql AS [Create the SET clause of the UPDATE sql]
EXEC sp_executesql @sql

FETCH NEXT FROM xcursor INTO @indx, @col, @pivot

END

CLOSE xcursor
DEALLOCATE xcursor

-- Return the results
SELECT @sql = 'SELECT ' + @select + ' FROM ##results'
IF @verbose = 1 SELECT @sql AS [Create the SELECT statement that will return the results]
EXEC sp_executesql @sql

-- Tidy up: drop the global temporary tables
SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
EXEC sp_executesql @sqlX
SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
EXEC sp_executesql @sqlX

END
GO

The following script creates two tables (one of test data, the other to specify a sort order) and runs the genericCrosstab procedure to generate a report.

IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE [id] = object_id(N'[dbo].[testTable]')
AND OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
DROP TABLE [dbo].[testTable]
END
GO

CREATE TABLE testTable
([who] VARCHAR(10) NOT NULL,
[when] DATETIME NOT NULL,
[what] VARCHAR(255) NOT NULL,
[qty] INT NOT NULL)
GO

DELETE FROM testTable
GO

INSERT INTO testTable VALUES ('Fred', '2005-APR-01', 'Coding', 5)
INSERT INTO testTable VALUES ('Jo', '2005-APR-01', 'Coding', 1)
INSERT INTO testTable VALUES ('Jo', '2005-APR-01', 'Testing', 7)
INSERT INTO testTable VALUES ('Fred', '2005-APR-01', 'Coding', 3)
INSERT INTO testTable VALUES ('Tim', '2005-APR-01', 'Coding', 4)
INSERT INTO testTable VALUES ('Fred', '2005-APR-02', 'Coding', 8)
INSERT INTO testTable VALUES ('Jo', '2005-APR-02', 'Coding', 2)
INSERT INTO testTable VALUES ('Jo', '2005-APR-02', 'Testing', 6)
INSERT INTO testTable VALUES ('Tim', '2005-APR-02', 'Coding', 4)
GO

SELECT * FROM testTable
GO

EXEC [dbo].[genericCrosstab]
@sqlSelect = 'SELECT *',
@sqlFromWhere = 'FROM testTable',
@pivotExpr = 'what',
@valueExpr = 'qty',
@function = 'SUM',
@groupBy = 'who'
GO

-- Extending the example to proscribe column order...

IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE [id] = object_id(N'[dbo].[testSorter]')
AND OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
DROP TABLE [dbo].[testSorter]
END
GO

CREATE TABLE testSorter
([label] VARCHAR(255) NOT NULL,
[sort_order] INT NOT NULL)
GO

DELETE FROM testSorter
GO

INSERT INTO testSorter VALUES ('Testing', 1)
INSERT INTO testSorter VALUES ('Coding', 2)
GO

SELECT * FROM testSorter
GO

EXEC [dbo].[genericCrosstab]
@sqlSelect = 'SELECT *',
@sqlFromWhere = 'FROM testTable',
@pivotExpr = 'what',
@valueExpr = 'qty',
@function = 'SUM',
@groupBy = 'who',
@sortLookup = 'testSorter',
@verbose = 0
GO

You can change @verbose to 1 to see what is going on.

I'm still evaluating Ruby on Rails as a plaform for future web apps. While I like the idea of the ActiveRecord and auto-generation of web forms from database tables, I agree totally with Jacec's post on ZDNet about the benefits of stored procedures:

In MS SQL parlance, a stored procedure is something that resides on the server, NOT in an application's code. These server-side stored procedure generate an execution plan ONCE and store it, leading to future performance gains and efficiency, freeing the CPU for other tasks, etc.

The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:

They allow modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.

They allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

They can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

They can be used as a security mechanism.
Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

So, that said, how to call a stored procedure from Rails? Rahoul Baruah's Made of Stone blog has the answer (and solves a gotcha):

I know this is not "the Rails way", but I've got an urgent deadline and the stuff that the stored procedure does is 1) well tested and 2) very complex.

So I need to call it, to get this stuff done in time.

Afterwards I will wrap a unit test around it (that's a story for another time) so I can reimplement it in pure Rails, but for that to happen as it should, I also need to get the webservice stuff working (so the logic currently tied up in the stored procedure is accessible to the outside world) and rewrite parts of the desktop application to call the webservice instead of the proc. So a bit of a way off yet.

Anyway, to call a stored procedure in Rails I used the following:

sql = 'declare @result int \n'
sql += 'declare @some_value uniqueidentifier \n'
sql += 'exec @result = my_stored_procedure @input1 = 'hello', @input2 = 'world', @output = @some_value output \n'
sql += 'select @result\n' # change this to select @some_value if you want to access the output parameter
result = connection.select_value(sql)

You could probably change the last line of the SQL to select @result, @some_value and use select_all to grab the results if you need to.

Anyway, this is the equivalent of what I would do in Query Analyser - declare some variables for storing stuff, call the proc (using @result to store the return value) and then select the values I am interested in to take a look at them.

However, sometimes it worked and sometimes I would get a 'cannot perform this object when the dataset is closed' - looking at the stack trace, it appeared to be coming from the ADO Recordset itself.

Why? There seemed to be no reason for it, until my colleague Nick had a revelation. ADO allows a single Recordset object to contain multiple Recordsets (excellent class naming there Bill). When you send a compound statement via Query Analyser, it splits the actual recordsetsinto the "data pane" and various messages into the "messages pane". However, in ADO the messages sometimes get sent back as a recordset. Therefore, Rails was trying to read a "recordset" containing "2 row(s) affected" when the actual data it was interested was in the "next" recordset. As I wasn't sure if I could get at the underlying Recordset, and I didn't really want to, I was a bit stumped. But Nick came to the rescue again - adding the line "set nocount on" as the very first statement, before the declares, seemed to solve the problem. Basically, we were telling SQL Server to suppress the messages and it seems to work.

Of course, there will be some configuration somewhere, when it goes live, that will screw it up - so I need to start copying that proc into Rails as soon as.

In a later post, Rahoul provides a link to an opensource Ruby module that wraps this functionality up.

There's more about calling stored procs on the Rails Wiki.

One feature of XSLT that I haven't used much is keys. This posting on Simon Woodside's blog describes how to use xsl:key as an index for large XML files:

xsl:key pre-creates an index on the table, based on whatever attributes or nodes you choose. For example, if your xml looks like:

<foo>
<bar name="X">...</bar>
<bar name="Y">...</bar>
...
</foo>

and you need to select '/foo/bar[@name='X']', doing so directly is cheap if your XML is small. But if it's big you should create a key, especially if you're doing selections of that type frequently. So before your templates you need:

<xsl:key name="bar_by_name" match="/foo/bar" use="@name"/>

Then when you need a piece of data you use 'key('bar_by_name','X')'.

You can even make up a concatenated index, if you need a multi-part search. i.e.:
<xsl:key name="foo" match="/foo/bar" use="concat(@name,'-',@type)"/>
and then select 'key('foo',concat($foo_name,'-',$foo_type))'. Just make sure that the string you're using to separate the search elements isn't valid as part of the content of the elements.

Proper application of xsl:key can be very useful. One of our translations is taking flat database dumps with approximately 10,000 nodes and converting them to a structured format, based on the structure of our database, with approximately 145,000 nodes. Using xsltproc (which is slower than Saxon, but more widely available) that translation takes 30-40 seconds. Without keys it was taking over 10 minutes.

Incidentally, Simon's blogging engine (opensource) was created by him using the opensource AXKit XML content management framework and is powered largely by XSLT transforming XML content files.

Just found this simple example for sending email (via SMTP) without using any extra libraries:

//This works without any extra libraries!
//Code adapted from http://www.javaworld.com/javaworld/javatips/jw-javatip36-p3.html
//and Craig Morrall

String sender = '';
String reciever = '';
String subject = 'This message is coming from Processing';
String message = 'Mail Message';
String mailServer = 'postbox.gold.ac.uk';

//strange java thing to get a correct carriage return
String carriageReturn = System.getProperty('line.separator');

void setup()
{
sendMessage();
}

void sendMessage()
{
println('Trying...');
try {

//connect to the mail server
Socket socket = new Socket(mailServer, 25);

//create an in and out connection
DataOutputStream out = new DataOutputStream(socket.getOutputStream());
DataInputStream in = new DataInputStream(socket.getInputStream());

//ask your machine what it's really called
String hostname = InetAddress.getLocalHost().getHostName();
println('HOST: '+hostname);

//converse with the mail server
readIn(in);
sendEmail(out, in, 'HELO ' + hostname + carriageReturn);
readIn(in);
sendEmail(out, in, 'RSET' + carriageReturn);
readIn(in);
//tell the mail server your email
sendEmail(out, in, 'MAIL FROM: ' + sender + carriageReturn);
readIn(in);
//tell the mail server where you want to send the message
sendEmail(out, in, 'RCPT TO: ' + reciever + carriageReturn);
readIn(in);
//start the message body
sendEmail(out, in, 'DATA' + carriageReturn);
readIn(in);
sendEmail(out, in, 'To:' + reciever + carriageReturn);
sendEmail(out, in, 'From:' + sender + carriageReturn);
sendEmail(out, in, 'Subject: ' + subject + carriageReturn);
sendEmail(out, in, message);

//close the message body
sendEmail(out, in, carriageReturn + "." + carriageReturn);
//end of message body

//end the conversation
readIn(in);
sendEmail(out, in, "QUIT" + carriageReturn);
readIn(in);

//close the connections
in.close();
out.close();

println("Message '" + subject + "' sent to '" + reciever + "'");

}//end of try statement
catch (UnknownHostException e)
{
println("Unknown Host Exception: " + e);
}//end of catch statement
catch(IOException e)
{
println("Send failure: " + e);
}//end of catch statement

}//end of method sendMessage

void sendEmail(DataOutputStream out, DataInputStream in, String stringHolder)
throws IOException
{

if(stringHolder != null)
{
out.writeBytes(stringHolder);
println(stringHolder);
}//end of if statement

}//end of method sendEmail

void readIn(DataInputStream in)
throws IOException
{
String record;

if ((record = in.readLine()) != null)
{
println("Message from host: "+record);
}//end of if statement

}//end of method readIn

Check out these awesome samples of round corners in web pages without using images - all with a little CSS, some Javascript and vanilla HTML: Nifty Corners Cube

While I'm thinking about it, here are some arguments for CSS-type layouts (with no tables or any of that other cruft):

• They look great!
• They are backwards compatible with old browsers (which will just display a plain vanilla HTML type layout)
• They are compatible with screen readers
• They are compatible with mobile devices, WAP phones (remember them?), PDAs etc
  
• Less cruft means smaller page loads (since there are usually far fewer tags, especially if there is only one CSS file for the whole site which will be cached by the user's browser)
• Accessibility - users can switch off the styling to see just the content
• Ease of maintenance of the content

... which reminds me - here's an essential extension for Firefox: WebDeveloper. WebDeveloper has loads of useful features, including the ability to edit the CSS styling of a page on the fly (great for changing colours, sizes etc until it's exactly how you want it).

Not many organisations realise that the UK's Disability Discrimination Act (DDA) now extends to websites - it will probably take a few high profile legal actions before anyone really takes notice. However, regardless of the legality of it, the practices that make your website more accessible to the visually impaired will also make it more accessible for everyone else.

So, how to do it? A good place to start is Mark Pilgrim's fantastic step-by-step guide at http://diveintoaccessibility.org/ (browse online or download a PDF). There's highly readable info on doc types, alt tags (all the stuff you'd expect), but also on the need to have an accessibility statement, and support for Access Keys (a completely new area for me - ironic considering my love of keyboard shortcuts!) - most browsers support keybard shortcuts to access specific pages (or anchors); on Windows, you can press ALT + an access key; on Macintosh, you can press Control + an access key.

I've just bought the 'beta' PDFs of two new Pragmatic Programmer titles: 'Pragmatic AJAX' and 'Rails Recipes' (because they're in beta, they're not available as hard copy yet, and I'll get updates as the title develops - nice idea). In the AJAX book, chapter 1 (in describing Google Maps, which arguably kickstarted the current AJAX / Web2.0 boom) has a quote from Glenn Vanderburg that I want to remember for my forthcoming presentation on Web20: "Technically it’s easy, but the conception of this kind of interface is the really amazing part, just having the idea and then realizing that it could be done. So many things are simple once you’ve seen that they’re possible."