A couple of days ago, Steve Bryant suggested that today would be “How I Got Started in ColdFusion day”. He claims every story he heard so far is interesting, so why not share mine? Here goes.

It was late 2005 when I applied for a job of Programmer at a company called Netbasics. Back then I used PHP as my server side tool to create dynamic websites, and had zero experience with ColdFusion. But I always wanted to be versatile in my knowledge and was promised that ColdFusion has a short learning curve. I started in Febuary 2006 and found that the learning curve is indeed quite short, meaning I was able to do some nice stuff within a couple of days.

As I learned more about ColdFusion, I noticed how different it is from other languages that I know. The syntax obviously, but also the way objects behave. Some of those characteristics I liked (cfquery, brilliant!), but some I really had to get used to (indexes start at 1, what’s that all about?). Working more and more in ColdFusion, I made it into a second nature and I really like it for being a great tool to develop dynamic web applications.

Besides that, I also find the community to be quite friendly and helpful. Ask a question on Twitter and you’ll usually get a response within the hour. There are a couple of people blogging about their experiences and providing useful tips and tricks which come in handy. This is especially valuable since the community isn’t really scattered: it appears as if everyone is on the same level. Sure, we all have our differences in approach, but we all understand and support each other. That I don’t find in any other community around a programming language (maybe jQuery, but that’s still pretty scattered as well).

I am still a nerd however, which makes me love the more programmatic syntax as well. Therefore I will probably never stop using PHP as a tool for my own little projects, even though cfscript has come a long way, especially with the latest versions of ColdFusion. But ColdFusion is simply brilliant, and I can’t wait to see where this technology is in, let’s say, five years.

What I think would really boost the usage of ColdFusion though, is the release of a ColdFusion Community Edition Server: free for non-profit or with less features than the current versions. The reason why starting programmers start with languages like PHP, is because those languages are easy to adopt and give a clear vision – cost wise – of what to expect once you want to bring your website online. For example, Apache, PHP, and MySQL is a really popular combination with people who start programming. And if you know one language, you are less likely to apply for a job that requires another, even when it clearly states you are free to learn it once you got the job. (I should really make a seperate post about this.)

All in all I’m really happy that I have gotten the chance to work with ColdFusion and predict its future will be even brighter than it is now (also thanks to integrated technologies like Apache Solr and the about to be adopted jQuery).

If you’re creating a comma-seperated list of values which get grouped, and you’re using the built-in XML functionality to do it, you’re in for an unpleasant surprise. This happened to me, although I do believe I should have foreseen this. The problem is that when you dynamically create XML, special characters in Textnodes get escaped – and rightly so ofcourse. I didn’t think of it when implementing this solution, but found out by accident.

The problem

Let me show you an example. Suppose you have two tables: “tags” with two columns “id” and “name”, and “tags_in_posts” with two columns “tgs_id” and “pts_id”. (Obviously there is a third table, but that one isn’t important for this example.) What we want to get is a comma-seperated list of categories per post. We could run the following query:

SELECT       tip.pts_id POST_ID
           , RTRIM(
               XMLAGG(
                 XMLELEMENT(E, tgs.name || ', ')
               ).EXTRACT('//text()')
             , ', ') TAGS
FROM       tags tgs
INNER JOIN tags_in_posts tip
GROUP BY   tip.pts_id

Sample output:

+---------+----------------------------------+
| POST_ID | TAGS                             |
+---------+----------------------------------+
|       1 | twitter & facebook, DB's |
|       2 | boeing, b787                     |
|       3 | moon, tele, space                |
+---------+----------------------------------+

The XML functions create a nice comma-seperated list of all tags for each post, with the RTRIM() function removing the last comma. This works great, or? What? If a tag would contain a special character, like & or ‘, things get weird. Instead of “twitter & facebook, DB’s” you would get “twitter & facebook, DB's”. This is ofcourse correct behaviour, but it might be unexpected.

The fix

The fix lies in another (pretty cool) function in Oracle: DBMS_XMLGEN.CONVERT(). This function takes two arguments (subject and direction) and is able to escape or unescape special characters in a string. When put in our original query, it now looks like this:

SELECT       tip.pts_id POST_ID
           , DBMS_XMLGEN.CONVERT(
               RTRIM(
                 XMLAGG(
                   XMLELEMENT(E, tgs.name || ', ')
                 ).EXTRACT('//text()')
               , ', ')
             , 1) TAGS -- "1" means "UNESCAPE"
FROM       tags tgs
INNER JOIN tags_in_posts tip
GROUP BY   tip.pts_id

The query now returns a result as expected:

+---------+--------------------------+
| POST_ID | TAGS                     |
+---------+--------------------------+
|       1 | twitter & facebook, DB's |
|       2 | boeing, b787             |
|       3 | moon, tele, space        |
+---------+--------------------------+

Happy querying!

Recently I was struggling to find a function to get MD5 to work in Oracle, like the MD5() function in MySQL. After some searching I found out that there is no direct way to calculate an MD5 checksum in Oracle. Therefore I created the function below.

CREATE OR REPLACE FUNCTION USERINFO.md5hash (str IN VARCHAR2)
	RETURN VARCHAR2
	IS v_checksum VARCHAR2(32);
 
	BEGIN
		v_checksum := LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW( sys.dbms_obfuscation_toolkit.md5(input_string => str) ) ) );
		RETURN v_checksum;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
			NULL;
		WHEN OTHERS THEN
			-- Consider logging the error and then re-raise
			RAISE;
	END md5hash;
/

What this basically does is use the sys.dbms_obfuscation_toolkit.md5 function’s raw byte array, convert it to a hexadecimal string and convert that string to all lowercase characters. This way you can easily convert strings to MD5.

For example:

SELECT md5hash('foo')
FROM tbl

This returns acbd18db4cc2f85cedef654fccc4a4d8, the MD5 result we were expecting.

Feel free to use it!

I’m happy to announce a new project, called “What’s my Wheaton?“, of which the first version will be released very soon! The past couple of days I’ve been working late to finalize functionality and test every bit of it, new project, called “What’s my Wheaton?”. The essence of the project is to find out what your Wheaton score is.

Now, this probably isn’t common knowledge (yet!), so here’s the definition (source: Wikipedia):

The Wheaton is a measurement of Twitter followers relative to celebrity Wil Wheaton. The measurement was standardized when Wil Wheaton achieved half a million Twitter followers, with the effect that Wil Wheaton now has 3.4 Wheatons himself. As few Twitter users have millions of followers, the milliwheaton (500 followers) is more commonly used.

“So, what’s the plan?” you might wonder. Well, What’s my Wheaton will allow you to quickly calculate your Wheaton score by simply providing your Twitter username. So why not find out what your Wheaton score is, and that of your friends?

The functionality will expand over time, but for now it will remain basic.

The project will be launched in a couple of days, so stay tuned! For more information, check whatsmywheaton.com.

Not so long ago, PHP introduced a new DateTime object. This object makes it easier to calculate a date and (re)format it, or make calculations with it. Apart from that, one might say it’s a lot cleaner, code-wise (if you’re an object-oriented type of programmer, like myself).

The object is supported by a second object, DateTimeZone. This object is used for creating a new timezone instance to be used with a DateTime instance.

To fully understand it, here’s an example of starting off with the current timestamp in the UTC (GMT) timezone, and switching the timezone to different ones, with the same timestamp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# Create a new DateTime instance, with the current UTC (GMT) timezone.
$dt = new DateTime('now', new DateTimeZone('UTC'));
echo $dt->format('D F jS Y, H:i:s');
# Outputs: Sun August 15th 2010, 16:21:19

# Switch the timezone to a local timezone.
$dt->setTimeZone(new DateTimeZone('Europe/Amsterdam'));
echo $dt->format('D F jS Y, H:i:s');
# Outputs: Sun August 15th 2010, 18:21:19

# Switch to a timezone in Australia.
$dt->setTimeZone(new DateTimeZone('Australia/Canberra'));
echo $dt->format('D F jS Y, H:i:s');
# Outputs: Mon August 16th 2010, 02:21:19

Now, imagine a UTC timestamp being saved in a database table and users are able to set their own timezone setting, in order to have all dates and times displayed relative to their own current time. To show you how easy this now is, take a look at the following code:

1
2
3
4
5
6
7

# We pretend to have two objects, a user and a post.
# The user has a timezone setting, the post has a datetime timestamp.
$dt = new DateTime($post->date_time, new DateTimeZone($user->date_time_zone));
 
# That's all there's to it! Now you can display it any way you like.
# Maybe in a format also stored in a user's settings:
echo $dt->format($user->date_time_format);

As you can see, wether you want to switch between date and timezones or you just want a generic way to display date and time information, DateTime and DateTimeZone are your object oriented solution.