In the second post of the series “Documentum problems and how to fix them”, where we describe problems our team has encountered when implementing Documentum for customers and share how to fix them, we’ll look at using diacritic characters in case insensitive xCP searches and Postgres.
Problem: Using diacritic characters in search
One of our testers found that searching on a search term containing a diacritic character did not work in xCP: the system did not return any results while clearly there were documents matching the criteria entered by the tester.
Solution: Change locale support settings in the database
Activating DFC-tracing revealed the actual query that was being run. As it turns out, OpenText has implemented a case-insensitive search in xCP using uppercase: the database converts the values in the search column to uppercase and xCP uses java to turn the search term entered by the user into uppercase, e.g. étienne becomes ÉTIENNE. This makes sense and works on an Oracle database, but not on a Postgres database.
The behavior of the UPPER function in Postgres turns out to depend on the LC_COLLATE database setting. The LC_COLLATE setting determines how the database handles locale specific data, such as alphabets, sorting, number formatting, etc.
See for example:
Since Java translates the criteria entered by the user as ÉTIENNE, obviously, the UPPER function behavior with LC_COLLATE set to "C" (which is the default) will not match.
To fix this behavior, we needed to take the following steps in order for the search function to work:
- Change the LC_COLLATE setting to Dutch_Netherlands.1252
- Set the PGCLIENTENCODING windows environment variable to UTF8.
- Use the PostgreSQL ANSI (x64) odbc datasource instead of the PostgreSQL Unicode (x64) datasource.