'Hello World' using AJAX

“Asynchronous Javascript And XML” is a mouthful for a web technology that’s in wide use these days. Simply put, it lets a web page download more content without reloading the page, and relies on a magic Javascript object called an XmlHttpRequest to do the work of requesting more data in the background, so it can fill in things on the page.

So, fiddling with AJAX, let’s do a little Hello Worlding:

Here’s a button, that when clicked, calls the “startRequest()” function in some javascript behind the entry:

And here’s a div:

<div id="ajaxtest">
<em>(although you can't see it as a separate div,
this text will be replaced)</em></div>
(although you can’t see it as a separate div, this text will be replaced)

Go on, watch the div text above, and click the button.

Here’s the javascript:

var xhr;
if (window.XMLHttpRequest){
  xhr = new XMLHttpRequest();
else if (window.ActiveXObject){ // in case it's IE
  xhr = new ActiveXObject("Microsoft.XMLHTTP");
// set up the callback
xhr.onreadystatechange = function(){
  myDiv = document.getElementById("ajaxtest");
  if(xhr.readyState  == 4) {
  if(xhr.status  == 200){
   myDiv.style.background = "green";
   myDiv.innerHTML =  xhr.responseText;
  } else {
   myDiv.innerHTML = "Error: " + xhr.status;
function startRequest(){
  xhr.open('GET', '/ajaxtest.php', true);

Firstly, the code sets up a variable xhr, which contains the magical object. This is the XmlHttpRequest object, and is responsible for communicating from the live Javascript to the back end server.

The request object is first set up at the script’s execution, but the only thing we do with it is tell it what to do when its “ready state” changes; that is, when the request shifts from not initialised, to set up, to having been sent, to being in process, to completion.

These states are numbered 0-4 respectively. Hold that thought; we’ll use it later. Suffice to say that, on the script’s execution, we now have an otherwise anonymous function that will be called when the ready state changes.

Now, in order for something interesting to happen, we click the button. That kicks off startRequest(), which in turn sets up a asynchronous request to ajaxtest.php, and submits it with no parameters (that’s the null). Asynchronous means we don’t sit around waiting for a response, and that’s why we need the readystatechange callback function.

So, when something interesting happens to the xhr object, the ready state changes (e.g. when we get a response back after sending our request). The browser then executes the anonymous function: first, the function checks that the request is complete – remember Ready State 4 above? don’t forget the ready state changes several times per request. We only want to read the response text once, at completion. Once the function has checked the request has been successfully completed, it then uses the xhr’s response-text to fill in the div called “ajaxtest”.

Et voila, we have “Hello World” in AJAX.


SQL Server Compatibility Levels – to Upgrade or Not to Upgrade?

When you upgrade from one SQL Server type to another (most of my experience has been with 2000 -> 2005), one of the options available is to stick with the existing compatibility level, rather than upgrade.

SQL Server 2000 defaults to “Compatibility Level 80”, with other options including 65, 70. These represent the features and compatibility options for version 8.0 (SQL Server 2000), 6.5 and 7.0 respectively. In other words, it’s possible to coerce a database into thinking it’s running with features consistent with an earlier release of SQL Server.

When upgrading to 2005, it’s possible to retain compatibility level 80, but the recommended route is to switch to 90. But should you?

There are two ways to look at this:

* If you want to change your code (or install some collaborating SQL2005/2008 servers) in the future, your compatibility mode will affect that decision
* If you haven’t run the Upgrade Adviser, you may not be able to upgrade; compatibility level 90 disables certain syntaxes and features that were available in 80.

So, when upgrading, you don’t have to increase your compatibility level, but then you’ve to consider your future with the server too; you might be happy to run on the latest SQL Server instance, but with old-school database settings and syntaxes. On the other hand, collaboration with other servers and applications, or maintenance by future programmers and DBAs, will probably require the switch to a higher compatibility level.

Note: it’s entirely possible to have several databases with different compatibility levels on a single instance.

Tricks with ORDER BY

The ORDER BY clause in SQL Server is a bit more flexible than many people give it credit for.

In a training course I’m giving at the moment, we’ve just gone over a few of the lesser known uses, along with the widely used ones:

  • The obvious: ordering by a column name
    ORDER BY advance
  • Less obvious: ordering by an expression on a column
    ORDER BY id % 2 -- even IDs first, then odd
  • Ordering by multiple fields
    ORDER BY id % 2 desc, price
           -- odd orders first by even/odd IDs, then by prices within each
  • Ordering by visible column position
    SELECT id, customerid, price * quantity as "gross revenue"
    FROM orders
    ORDER BY 2
  • Ordering by column aliases
    SELECT id, customerid, price * quantity as "gross revenue"
    FROM orders
    ORDER BY "gross revenue"

The last one is particularly interesting, because column aliases can’t be used in other places e.g. in the where clause or from clause.

Installing Java Applications via "Setup" Utilities

As with many applications, you may wish to install a Java program with a setup utility that installs the files and sets up appropriate shortcuts. However, there are some subtleties with Java application installation that require some extra thought.

Java applications can only be run on a machine with a JRE (Java Runtime Environment), so any setup utility won’t run quite the same as other application setup files; you won’t be able to give someone your setup file and expect it to run correctly, unless they already have the appropriate JRE.

It is still possible to install Java applications using standard Windows Installer utilities; some tools allow you to bundle (or download and install) a suitable JRE as part of the Windows Installer process, resulting in the application’s files being installed along with any dependencies.

It’s even possible to install Java applications as a service in Windows, using additional wrapper utilities.

However, in Java, the usual way to do the “setup routine” is typically going to result in a “jar” file. This way, you can launch the program by using a shortcut that launches the JVM and points it to the appropriate jar file. From the user’s point of view, this is indistinguishable from any other shortcut, but of course it requires that the files and JRE have already been installed.

Who's Afraid of the Big Bad Bignum?

Short one this time.

If you’ve a bignum in Perl, it might be a bit intimidating. For example, how on Earth would you get the third last digit from an enormous number? Convert it to a string first? would that even work? Mod 1000?

Fortunately, bignums are transparently available; once you’ve got one in a variable, you treat it just like any other Perl scalar, so this will work:

$digit = substr($bignum, -3, 1);

Neat, eh?

Choosing Between Database Platforms: MS SQL and MySQL, with a twist

Another StackOverflow question: Given a site using PHP and VB.NET, and a choice between Microsoft SQL Server and MySQL, which coupling would be easier to maintain?

VB.NET and Microsoft SQL Server are an obvious marriage, as are PHP and MySQL, but as both development languages are in use, we are faced with choosing between two odd combinations:

  • VB.NET and MySQL
  • or PHP and MS SQL

My response? I’d go with MySQL (possibly controversially!), although SQL Server is by far the superior platform, all things considered. If you’re comparing them as similar alternatives, you’re probably not going to use any of the features of MS SQL that make it the better platform, and so it’s not worth the extra hassle.

In summary, here’s why:

  • PHP’s support for MySQL is second to none (given the following caveat)
  • PHP’s support for SQL Server is suboptimal; Microsoft provide a PHP driver, and there are other techniques, but PHP’s simply not quite as database-agnostic as VB.NET
  • VB.NET, although it loves SQL Server, will happily talk to any OLEDB provider (e.g. an ODBC connection) with no problems whatsoever, and MySQL’s ODBC support is pretty mature.

How to do application support: Padre

I accidentally stumbled across Padre, a Perl-native IDE, the other day, and although I wasn’t hugely impressed with its featureset, I was massively impressed with one small aspect. It’s still fairly new, and although it’s only a year or so old, it already has quite a few necessary features.

I wouldn’t compare it to Eclipse or any other relatively mature IDE, but I was massively impressed with one idea, that of user support.

The screenshots page almost casually refers to the “Live Support” option. Yes, they have Live Support. I’m sure it doesn’t come with a guarantee of instant access to the developers, but for an open source project, it’s pure genius. The developers and enthusiastic early-adopters probably hang out in the IRC channel anyway, even without any payment or expectation of payment. Tying this to the users, via a freely available web-based IRC client, is a remarkably clever use of existing technologies and the developer culture.

I’ve been to application-specific IRC channels before, and they tend to be polarised to either rather elitist “are you a developer? no? then buzz off” attitudes, or the opposite, policed by folk who like answering easy questions, and are therefore lording it over their own fiefdoms, kicking curious lurkers who want to pick up the accumulated wisdom by osmosis.

Padre is the first example I’ve seen where, rather than expecting the curious (or desperate) users to get onto IRC (IRC? what’s that?) themselves, the turn it into a clickable “Live Support” option, and bring the users to them with little or no difficulty.

I wish more applications did this.

Running a command on a remote server with SQL Server

Another StackOverflow question: How do I run an OS command on a remote server, from within SQL Server? I answered as follows.

Something like rsh might do – have your local SQL instance invoke xp_cmdshell with an rsh command (or a batch file), connecting to the remote server and executing the command.

If you have SQL on the remote machine, it’d be far easier; call a stored procedure on that machine from the local machine, and let that stored procedure do the work (might need fiddling with proxy accounts and “execute as”).

A third option would be to create a .NET stored procedure, and do your socket programming or remote invocation from there – it runs in-process, so you get similar same performance and security benefits you’d get from writing it in T-SQL rather than hopping out to a cmdshell with all the mess that entails.

The rsh solution won the day.

Having said that, the rsh solution is not tied to SQL Server — rsh can be invoked from anything capable of running shell commands locally, even a batch file, or a Perl script.

Rsh is a feature Microsoft added to Windows a few versions ago, although a variety of it has been available in resource kits since NT 4.0. In simplest terms, it provides a remote command line, not unlike telnetting into a Unix server, only you get the Windows CMD prompt. Rsh allows the invoker to invoke a single command remotely, using command-line options, and without the need to write any further server-side code. This makes it suited to the quick-and-dirty route taken by the recipient of the above answer.

Data Warehousing: To Primary Key or not to Primary Key?

Constraints are a necessary part of the tug-and-thrust of database design, but once in a while, when designing a data warehouse, someone comes up with the question “Do I need to map those constraints to my DW?” The simple answer is “Yes”, but it’s also a somewhat naive answer that doesn’t take into account the needs of a DW.

For your primary key, consider using a surrogate or alternate key; you’ll need to cater for slowly changing dimensions, e.g. if you’re doing a report over the last 5 years on average sales per married/unmarried salesperson, you’ll want to register the fact that someone was unmarried for 2 years, then married for the last 3. This means your data warehouse will have two dimension-table rows for the same person. Following the OLTP structure for that will be hard, to say the least. Note that it’s not enough to create a composite key on the original primary key and e.g. marital status — the same person may also have other fields that change occasionally, such as address (zipcode), department, job title, or even gender!

As for other constraints such as default value or check constraints, we need to consider how a DW is populated, and what the arrival of data to the DW means. The DW is invariably populated from an OLTP (relational) system, the same system that typically has all the original constraints. Ideally, this means the data is already clean by the time the DW gets it. It also means that the performance hit required by checking these constraints in writing to the database has already been taken. And OLTP system is typically optimised for writes and reads, particularly the large tables that change frequently and are the subject of most of the storage in a DW.

On the other hand, Data Warehouses are massively optimised for reads (assuming you’re populating as a batch, with reasonably high latency), and constraints don’t really factor in read operations. You can typically get around any constraint issues with your DW populating job, if they haven’t already been dealt with in the OLTP system, and deal with nulls etc. at the analysis/reporting tools if necessary. It’s far more important to make sure default values fit with your conceptual data model, and don’t introduce issues at the DW client tools.

SQL: Why nulls slow down some queries

Someone asked a question on StackOverflow today about why null values slow down certain queries. This is roughly what I answered.

The main issue with null values and performance is to do with forward lookups.

If you insert a row into a table, with null values, it’s placed in the natural page that it belongs to. Any query looking for that record will find it in the appropriate place. Easy so far….

…but let’s say the page fills up, and now that row is cuddled in amongst the other rows. Still going well…

…until the row is updated, and the null value now contains something. The row’s size has increased beyond the space available to it, so the DB engine has to do something about it.

The fastest thing for the server to do is to move the row off that page into another, and to replace the row’s entry with a forward pointer. Unfortunately, this requires an extra lookup when a query is performed: one to find the natural location of the row, and one to find its current location.

So, the short answer to the question of performance is yes, making those fields non-nullable will help search performance. This is especially true if it often happens that the null fields in records you search on are updated to non-null.

Of course, there are other penalties (notably I/O, although to a tiny extent index depth) associated with larger datasets, and then you have application issues with disallowing nulls in fields that conceptually require them, but hey, that’s another problem!