Wow, that was quick.

Hey, no wise cracks on this blog title.

Oh wait, that wasn’t any better.

This afternoon I’m writing about a quick problem I had with SQL and colours. Visual Basic 6 is notorious for storing colours, you can only store them as an integral value – a 64 bit integer (a whole number for you non-techies.)

Obviously, some critter decided it would be a wise idea to store the value in the database in it’s obsolete form. I say obsolete, because even Microsoft have moved to Hexadecimal string values as a default value for WPF colour values and brushes.

So, what’s the problem?

We have reports that are required by clients, on these reports a colour is displayed that should be shown from the database. The problem being, however, is that the data type that we store the value as (int in SQL) actually means that the report can’t show this format. Instead, what has happened is that extra fields have been added to the database for customers.

This is bad.

Not only does it create a problem for continuous integration (software updates and database schemas) – but it also results in non-maintainable data in the database. What happens when the customer changes the colours, or adds a new one. This new “special hex string field that someone thought was a good idea” immediately becomes wrong. And if there’s one thing worse than non-maintainable data, it’s wrong non-maintainable data.

Why is this bad I hear you say

This is one of the most common problems with software development. When you create a new entity that no one else knows about, it means that it will break. Software breaking = bad. The value will be wrong, the user experience will be terrible (Fancy that, some text saying the colour is Yellow when in-fact the colour is something totally different and irrelevant. This confuses users and only makes your software look bad.

The golden answer.

OK – OK, I hear you, you’re a developer you already know all the nuances of the trade, tell me how to god’damn fix it.

Fair enough. One of the nicest features I’ve found for problems like this is a little thing known as “User Defined Functions” (MSDN Link). You can create one of these to execute a function on a specific value in your database. Ah, I hear you say, let’s convert the value to a HEX string in the function, then we don’t need to add new columns, all the existing applications continue to work as expected. Everyone wins.

The code looks a little like this:

CREATE FUNCTION ToHexColor(@value int)
    RETURNS varchar(50)
AS
BEGIN
   DECLARE @result varchar(50)
   SET @result = [master].dbo.fn_varbintohexstr(@value)
   SET @result = '#' + UPPER(RIGHT(@result, 6))
   RETURN @result
END

This snippet uses a built-in function “fn_varbintohexstr” that actually does most of the heavy lifting for us. This converts our integer value to HEX as so: 0x00ffffff – which is good but not quite there. The 0x signifies the value is a Hexadecimal value, so that can go. Second, HTML does not support alpha blending in it’s colour representations. So we crop these off, add a hash and the database value is now directly bindable.

Lovely.

The moral of this story, check with your developers before adding new fields, and always remember the simplest, easiest solution to maintain is always the best.

Abiento!

Advertisements

myFirstBlogPost.publish()

Being tradition I’m going to use my first blog post as a bit of an introduction about myself, what I do, and where I’m going.

So my name’s James, I’m 23 as of writing this blog post. (Yeah, I know I’m a youngster, and I might not know what I’m talking about) However, unlike most my age I’m mature enough to listen and take in advice if I’m wrong about something so please feel free to correct me. The sole purpose for my blog is education, to make being a great software developer an easy and achievable task!

I work in Leeds, a city in England at a software house that produces management and data capture software for the print & media industry. I’m the youngest developer at the company, but (without bigging myself up too much) I’m probably one of the most competent there. (Hey, when it comes to your job confidence is good!) We’ve got a few skilled people here, and we consistently work as a team and produce some of the best products availiable in our industry, breaking bounds and introducing new technologies to make our customers more effective.

That being said, my particular areas that I specialise at my work are:

  1. VB6 development (You may say “Yawn” or you may say “WTF, you’re a little too young for VB6?”) I’ll start by saying you’re right, on both counts. Visual Basic 6 is tedious and slow. But what do you expect for a product that was release when I was 9. I know this stuff, because I learnt Visual Basic 5 (Yep, even older) when I was at Junior School. (Forgive the UK/US school grades, whatever grade you were when you were 10)
  2. VB.NET Development. This ones a lot more up to date. I work with most versions, starting at 2.0, through to 4.5 (the latest one).
  3. WPF Development. This has been a personal passion of mine, ever since I first laid eyes on the technology in use (those lovely looking Windows messenger UIs when they first came out) I’ve wanted to duplicate it.
  4. ASP development. Oh, yeah, did I say I was old skool? I’ve got dozens of projects written using classic ASP, and ASP WebForms. One of my aims over the next few years it to work on more MVC webs, and make use of the benefits of a loosely coupled architecture brings.
  5. SQL Server. Lucky you. I’ll be providing some resources on SQL Server, (specifically Microsoft SQL Server) – and Azure. (I’ll also touch on some No-SQL alternatives)
  6. ORM wrappers. (For those of you who’re unsure, that stands for Object Relational Mapper)
  7. And the rest that I don’t have time to go into more detail today; JavaScript, HTML, CSS, XML/XSL, PLC, Micro framework, Win Mobile, and Windows 8 development.

Anyway, I could ramble all day, but I think that’ll do for my first post.

I hope you enjoy the deliciousness of my blog, as much as I enjoy sharing the information.

Until next time!