SQL: WHEN CASE NULL Fails

Imagine you want to find all the null values in a column in a database table (SQL Server).

x
1
2
NULL
4
5

Here is the SQL that performs the task as required:
[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
The result he expected was:

x result
1 no
2 no
NULL yes
4 no
5 no

But that isn’t what he got. His result was like this:

x result
1 no
2 no
NULL no
4 no
5 no

Curiously, if you run this:
[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN 1 THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
You do get this:

x result
1 yes
2 no
NULL no
4 no
5 no

So, why didn’t the original work?

  • Because NULL means unknown
  • Because NULL does not equal NULL
  • Because NULL is just weird, weird, weird

Anyway, here is the SQL that gives the expected answer:
[sourcecode language=”sql”]
SELECT x,
CASE
WHEN x IS NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]

Problem Starting Application Using VBScript?

Today, someone asked me about a problem he had running an application from VBScript. Here is the text of my reply:
Adding the parameters to the end of the .exe name should work. Often, however, the problem is spaces / quotes in the command line. The following recipe should deal with all such problems.

Write out the command you want to execute in full, e.g.
C:Program FilesJavaThingyJava.exe -silent -otherParameters:”value” -finalParameter

If there is a space in the path to the executable, put this bit in quotes.

“C:Program FilesJavaThingyJava.exe” -silent -otherParameters:”value” -finalParameter

Then, double all quotes in the whole line

“”C:Program FilesJavaThingyJava.exe”” -silent -otherParameters:””value”” -finalParameter

Put another set of quotes around the whole thing, and add the WshShell.Run gubbins:

WshShell.Run “””C:Program FilesJavaThingyJava.exe”” -silent -otherParameters:””value”” -finalParameter”, 1

Useful Web Development Plugins for IE

Info on a couple of IE plugins that I find useful:
First, I couldn’t live without the Internet Explorer Developer Toolbar. It is a collection of useful little tool for all kinds of web page development, free from Microsoft. Great for digging into the workings (or failings) of a web page. Follow the link for more information:
http://www.microsoft.com/downloads/details.aspx?familyid=e59c3964-672d-4511-bb3e-2d5e1db91038&displaylang=en
Second, the Web Development Helper useful. It was written by independent developer,Nikhil Kothari, and is also free. Not as useful as the Microsoft one, but still handy to have around. Its strength is its ability to log the traffic between the browser and the server: good if you want to know what a form is sending to the server, or what a server is sending back.
http://projects.nikhilk.net/Projects/WebDevHelper.aspx

SQL Server: Case Sensitive Query

Someone at work sent this out to the team.
From LW:

Dear All, you might find this bit of code quite useful.  I was trying to find data within a table where the data was like lower case letters e.g a, b as opposed to A, B.  TSQL was not differentiating between lower and upper case so the following line forced it to do this substring(hradminblockcode,1,1) COLLATE SQL_Latin1_General_CP1_CS_AS

I used it in the following query

SELECT hrrefno FROM hrrepairmaster WHERE substring(hradminblockcode,1,1) COLLATE SQL_Latin1_General_CP1_CS_AS�
IN  (‘a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’,’i’,’j’,’k’,’l’,’m’,’n’,’o’,’p’,’q’,’r’,’s’,’t’,’u’,’v’,’w’,’x’,’y’,’z’)

That might be useful. There is more information here: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

GridView Buttons not Working?

In my experience, the most common reason for Buttons in a GridView not working is that data is being bound to the GridView during the postback.

In other words, the code looks something like this:

When it should look like this:

Information for Laptop Users?

I got the details of this story first-hand from the people involved. I’m reproducing it here because I think it highlights some of the things that can go wrong when an IT department tries to communicate with clients about technical things.

The Story

In July 2006 an email was sent out to everyone in the company. It was entitled, “IMPORTANT INFORMATION FOR LAPTOP USERS”.
The email read as follows:

The following information is for anyone who uses a laptop computer.
Please note that it is very important if you use a laptop, to ensure that you regularly use it from the battery power supply as opposed to plugging it into the mains electricity. This is because the battery needs to be used in order for it to recharge fully and maintain its lifecycle. The average life expectancy of any laptop battery is only 2 to 3 years.
The following is the most effective way of ensuring you get the best performance from your laptop battery.
1. Use your laptop from the battery power supply until you receive a message saying that you should change your power outlet.
(This message indicates that the battery has run down to approximately 10% capacity)
2. Swap to mains power for about 4 hours after which time the battery will have recharged to full capacity.
3. Use the laptop from the battery power supply again and continue with the above cycle of events.
If you have any queries regarding the above, please contact the ICT Helpdesk for advice.

Something about this advice simply didn’t ring true to one of the employees of the company. So, he did his research and sent this reply to IT:

Out of curiosity, I did a web search for more information about prolonging battery life. I was surprised to discover a number of web sites whose advice conflicts with that given below, and little on the web that supports it.
Specifically, there seems to be a consensus that:

  • Letting Lithium Ion (or “Li-Ion”) batteries discharge completely will do them harm.
  • Li-In batteries should be kept cool.
  • Li-In batteries do not suffer from “memory effect”, unlike some older battery technologies.

The main disadvantage to Li-Ion batteries is that they lose about 10% of their usable capacity each year due to chemical breakdown within the cells. There is nothing that can be done to prevent this condition.
Several sites suggests that you can maximize the useful life of your Li-Ion batteries by keeping them on the charger during use (e.g. laptop computer) or keeping them on the charger when not in use. This is because Li-Ion battery life is dependent on the number of discharge cycles, anything you can do to minimize the number of discharge cycles will, in effect, increase the battery’s useful life.
Also, several sites suggested that, because Li-Ion batteries are “smart” batteries, they cannot be overcharged.
As my laptop has a Li-Ion battery, the above suggests that it would be better to keep it plugged in and charged; or, better still, removed from the laptop altogether as I use it in the office most of the time anyway. I am concerned, however, that this conflicts with the advice given.

To this, the employee received a reply that basically said:

I have forwarded your comments on to our hardware team…

And that was it. The employee never received any followup, nor was the direcive recinded by the IT department.
So, you can imagine what is going on in that company to this day. Everyone in the company who has a laptop engages in this ritual where they unplug the thing and wait until just before there is no power left. Then they grub around on the floor, frantically trying to plug in their laptop before their work vanishes. Four hours later (do they set an Outlook reminder to make sure they don’t miss the occasion?) they unplug and wait for their battery to all-but expire. Then they do it all again. And again.
The company employee concerned said that he believes this ritual achieves the following:

  • It stresses the power connector on the back of the laptop, risking hardware failure and the resultant loss of productivity
  • It stresses the battery in the laptop, so reducing the life of their equipment
  • It stresses the people who undertake the ritual.
  • People risk losing work if they forget to plug back in before they nip out to the loo or to a meeting.
  • It wastes company time.

Learning Points

  • Technical things are not always quite as simple as they seem
  • Get your facts right before sending out a global email.
  • Be prepared for responses to global emails.
  • Be prepared to back up or back down. Either:
    • Defend your position, or
    • Be honest and let people know if you got it wrong
  • Listen to your users. They often know better than you do.

 

Cloning Windows Installations: The HAL Issue

For a long time, we had no problem installing Windows on new PCs by simply cloning the hard drive of another machine new machine. One black day, however, disaster struck.

We had about 200 Windows 2000 PCs. When new machines arrived we would simply copy on our standard image and then add the applications that were specific to the new machine. Of coures, we had taken the precaution of performing a SYSPREP on the original image – the result of which is that the PC will re-enumerate its hardware and load the appropriate drivers when it re-boots. This include fairly basic drivers for hard drive controllers etc. At the same time, a new network ID and various other properties could be configured.

The first hint of problems was that the system clock on a new batch of PCs kept resetting to “wierd” times every time the machines were rebooted. It took days to narrow the problem down to its cause.

It turned out that our original build had been on a PC that the Microsoft installer identified as a Standard PC. The new batch of PCs were different. They were ACPI Uniprocessor.

Unfortunately, there is a low-level set of drivers that is not touched by SYSPREP. These are known as the HAL (Hardware Abstraction Layer). Unfortunately, a change of HAL from Standard to ACPI is extremely difficult to accomplish (we never actually succeeded – I doubt if anyone has). Apparently, it is possible to switch between Uniprocessor ACPI and Multiprocessor ACPI, but that is about it.

The problem with switching between Standard and ACPI HALs is that, not only are the HAL dll different, but the entire structure of the HKLM/Hardware branch of the registry is different, too. IIRC, there are also various other support dlls that need to be changed.

Some notes on the problem were found here:

Sadly, none of these solutions ever worked for us. We had no option but to rebuild our standard image from the ground up. I guess someone might come up with a better solution. If you do, you have my admiration. I ‘d love to hear from you.