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]

 

Instantiations releases VA Smalltalk Version 8.6.3

While it is no secret that Instantiations is working on a new 64-bits VM and Image for its VA Smalltalk system, innovation in all kinds of Software packages that can be connected to VAST doesn’t stop and wait for it to be done.

So Instantiations just announced the immediate availability of VA Smalltalk 8.6.3, a version that mainly ships upgraded class libraries for communicating with the outside world. Here’s a list of what’s new in VA Smalltalk 8.6.3:

• Communications
o IPV6 Support

• Middleware
  o SQLite 3
• Security

o OpenSSL 1.1 Compatibility

o Cryptographic Support

• Native Interface Support Enhancements o Long Datatype Support
• Installation
  o Headless Windows Installero Solaris PKG Installer

  o Headless Importer

• Updated OS Platformso Ubuntu 16.10
  o Fedora 24
  o Red Hat Enterprise 7
• Documentation
  o Updated Migration Guide

So you can clearly tell that Instantiations aims to make VA Smalltalk play nicely on current operating systems and to be a first-class citizen of today’s communication and security standards while we all are waiting for the “big 64 bits bang”.

The “little” enhancements in the installation process have life of users easier over the last few releases and still continue to do so.

If you want to learn more about what’s new in this release and what’s to come in the near future and what the status of the new 64 bit VM is, you should definitely take a look at these slides from John’s talk at FAST in Argentina in November 2016.

Where to download?

A free time-unlimited and fully functional evaluation copy of  VA Smalltalk can be downloaded from the company’s website. Registered users with a current support contract can get the software free of charge and also download the unlock files at this location.

If you want to buy VA Smalltalk or need assistance in your project

If you’d like to learn more about VA Smalltalk or need help im migrating from an older (maybe as old as 6.x from IBM) version of VisualAge Smalltalk, feel free to contact us at objektfabrik for the German-speaking market ot Instantiations directly from all other areas on the planet.