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!
abbottdev 