Chris McGrath’s Blog

Just another Developer’s Blog

SQL Generators

leave a comment »

SQL Generators are known for how many brackets they love to put in. So many in fact it made a simple query hard to understand.

Like most professional developers I don’t spend too much time using generators, I much prefer to write the query myself. And I generally stay away from DataSets completely. But today I had to paste my SQL into a query in a DataSet, and I noticed how Microsoft had tried to optimise the Builder so when it reformatted my SQL it didn’t have so many brackets.

The problem was it removed the useful brackets and added useless ones.

Here’s what I entered…

(x1 = x2 AND y1 = y2) OR (n1 = n2 AND m1 = m2)

And here’s what it optimised it to…

(x1 = x2) AND (y1 = y2) OR (n1 = n2) AND (m1 = m2)

How completely stupid! The main reason anyone adds brackets in SQL is to make the order of operations absolutely clear. Yet it removes those ones and adds sets around single statements.

The query might be correct but a human has trouble – not everyone knows the order of operations with booleans.

Advertisements

Written by Chris McGrath

August 31, 2009 at 4:16 pm

Posted in Blog

Tagged with ,

New Domain.

leave a comment »

I’ve finally decided to buy a proper domain. So my site is now at http://cpmcgrath.com. Still setting everything up but this site is now obsolete.

Written by Chris McGrath

August 13, 2009 at 4:56 pm

Posted in Blog

Another cool thing about chrome

leave a comment »

Chrome is a great example of how an user interface should be done. There’s so many small things about it which are so useful. My current favourite is the ability to resize text areas (aka multi-line textboxes).

They put a subtle gripper on the corner which you can grab and increase the size.

I can’t believe how many sites provide you with ridiculously small text areas. They ask for a detailed description then give you 3 lines with enough space for maybe 40 words. Of course a scroll bar appears but they’re annoying with only 3 lines.

I hope all other browsers copy this function, it is a brilliant idea.

Written by Chris McGrath

July 29, 2009 at 2:13 pm

Posted in Blog

Tagged with , ,

The worst thing about Batch files

leave a comment »

Date manipulation. So very painful. Not only do you have to do the annoying string manipulation dates are made worse by something else – stupid formats. I’m Australian and I believe in the 2 logical formats for dates day-month-year and year-month-day. It makes sense – smallest unit to largest unit or largest to smallest. But then there’s the American format of month-day-year. Seriously how does this make any sense to anyone? why not we change general numbers to act like this, 842 can become 824 (hundreds-ones-tens).

Anyway, it’s this fact which makes it really annoying. The batch date doesn’t allow you to enter the format you want it outputted as. So if I extract the values to a day, month, year variable then put it on a computer which doesn’t have the same format it doesn’t work.

Luckily we now have Powershell which allows us to manipulate objects instead of strings. Thank you for logic.

Written by Chris McGrath

July 28, 2009 at 12:59 pm

Posted in Blog

Tagged with

Application Data

leave a comment »

I’ve never really got Window’s AppData concept. Local, LocalLow and Roaming. What the hell do they mean. Admittedly, I haven’t bothered researching it much, but to me how it should be handled is quite clear.

Some background…

I’m a big believer of 2 partitioned systems – the system partition and the data partition.

The System partition holds the windows files and program files, while the data partition obviously holds data including the desktop and documents folder. The most important part of the system partition is that it should be treated as volitle – I should be able to format it at any time and not lose any data.

This makes it great for create an image of the drive – if something goes wrong, I can restore it and have a fresh install without losing any data.

There is one problem with this and that’s the AppData.

From the name AppData you’d think that it should belong on the data partition. And definitely some does belong there, but it is used for so many other thing that could potentially be causing the problem you want to restore your image to get rid of. As such we have to leave it on the system partition.

And how it should be done…

AppData should be treated to support this distinction between Applications and Data. So AppData should be divided into…

  • Volatile App Data
  • Non-Volatile App Data

Volatile App Data would remain on the system partition and Non-Volatile on the Data Partition.

A great example is a web browser. A web browser has temporary internet files since they are temporary they are volatile. But they also have bookmarks which the user will want to keep. Also I want to keep a history of where I have been so that should be non-volatile.

This system is very clear what the meanings are. It is also an important step in what I consider a critical issue – the standardisation of the 2 partition system.

Written by Chris McGrath

July 26, 2009 at 12:21 pm

Posted in Blog

Tagged with , ,

Grids suck

leave a comment »

I’ve heard a lot of people ask, “What is the benefit in moving to WPF? I’m just making a plain line of business app I don’t need any special effects.”  Well I think that using WPF there is one thing that it really makes stand out – Grid suck.

I know all you winform enthusiasts out there are saying, “Have you seen the DataGridView? I can create a list in seconds.” Well I’ve got news for you DataGridViews suck too.

I’m not talking about from the programming point of view, you’re right nothing compares to the DataGridView for quickly showing data. The problem is from the user experience point of view, it’s ugly and anything more than three columns is really hard to use.

So here is the big problem with WinForms, it’s hard to create “ViewAll” screens without using a DataGridView, as such you don’t bother with anything else. So if you want a reason to change to WPF, you no longer need the DataGridView.

Instead of Grids WPF focuses strongly of List Items. You provide a template for one item – and it’s as customisable as you like, any control can go in it, you can use multiple lines – and it repeats that template for every item in the list.

Written by Chris McGrath

July 18, 2009 at 6:03 pm

Posted in Blog

Tagged with , , ,

A really sneaky SQL join problem

leave a comment »

I was creating a query the other day and it wasn’t returning the amount of rows I was expecting. I did quickly figure out what was causing me but how to solve it stumped me. Luckily a co-worker who was much more experienced with SQL knew how to fix it. But it’s a case that those of us who don’t live and breath SQL probably don’t know about and it’s so easy to get this wrong all developers should be aware of it.

Here’s the problem…

You have a person table and a phone number table. A phone can be work or home. In the person table there is John, Michael, Chris and Dan. John has neither number entered. Michael has both entered, Chris just has work number entered and Dan has home number entered.

You want to display a list of every Person and if they have their work number entered, to grab that as well, else null.

Easy right? How about giving it a go?

Did you come up with this…

select Person.*, Phone.Number
from Person left outer join Phone on person.id = phone.personid
where Phone.Type = ‘w’ or Phone.Type is null

That was easy, the left outer join stops the person row from disappearing if no phone number exists and we get rid of the home numbers through the where – we are also careful not to remove the items without a phone number with a “OR IS NULL” check.

Well you’re wrong. If you were to run it only 3 of the 4 people would show up. Can you think why? Here’s a clue, the person would be Dan.

If you still haven’t got it, the Dan rows that exist before the where is just when type = ‘h’. It doesn’t have a null value their like John has. So the where then says, “is it w? is it null? well get rid of it.”

But how do we fix this? It doesn’t look like we can, our check is happening too late we want to filter the phone table and then outer join it. Not possible? Must require some complex subquery? Well actually it doesn’t…

select Person.*, Phone.Number
from Person left outer join Phone on person.id = phone.personid
AND Phone.Type = ‘w’

That’s it. We move the check inside the join.

It’s quite a nice solution for it really. I think as long as you are aware of this you won’t have any problem but I do wonder how many bugs are out there exist because this wasn’t taken into consideration.

Written by Chris McGrath

July 17, 2009 at 9:10 am

Posted in Blog

Tagged with ,