Glorp/DB2 – Tip: Sorting case insensitive


If you are working with Glorp and DB2 and use #orderBy: you may have found out that the results of ordering by CHAR or VARCHAR columns may end up strange. DB2 assumes that a Small letter is less than a capitalized one. “Hans” is lower than “HANK”.

In my case this came obvious in a query that was issued using GLORP like this:

^self dbSession readManyOf: Person orderBy: #lastname

This may or may not be surprising to you, it is not in line with the kind of sorting you’d expect in a telephone book or Lexikon (if you’re old enough to remember, otherwise this post is probably useless for you 😉 ).

I was wondering for a moment whether this is Smalltalk’s or DB2’s fault. So I tried evaluating these two expressions:

#('Hans' 'HANK') asSortedCollection --> SortedCollection('HANK' 'Hans' )
#('Hans' 'Hank') asSortedCollection -->SortedCollection('Hank' 'Hans' )

So Smalltalk’S sorting fits with my expectations and DB2 is to blame.

It turns out this is a common problem in the SQL world, as you can readon Stack Exchange. So what is needed is a way to change the SQL statement that gets created by Glorp in order to get the “correct” results. So first we need to know what the SQL should look like. There are a few options, but these two seem to be the most commonly used ones:

ORDER BY LASTNAME COLLATE NOCASE

ORDER BY UPPER(LASTNAME)

There may be differences in execution speed, but for me the more important question was how to change my Glorp Query to produce one of these two variants.

It turns out it is easy to achieve:

^self dbSession readManyOf: Person orderBy: [:f| f lastname asUppercase]

 

Advertisements