MDM search is case sensitive while using an RDBMS db

[TalendSolutionExpert]

Overview

MDM's webUI search is case insensitive using xmldb (eXist/Qizx), but it is case sensitive using RDBMS db.

Environment

MDM 5.2.1 using RDBMS db.

Symptoms/Description

Starting with 5.2.1, Talend MDM supports RDBMS db. The webUI search is case insensitive using xmldb (eXist/Qizx), but it is case sensitive using RDBMS db.

Resolution

• For a MySQL db, refer to Case Sensitivity in String Searches on the MySQL site.

• For an Oracle db, you can create a logon trigger such as:

  create or replace
  TRIGGER SYSTEM.SET_NLS_ONLOGON
  AFTER LOGON
  ON DATABASE
  DECLARE
      cmmd1 VARCHAR2(100);
      cmmd2 VARCHAR2(100);
  BEGIN
      cmmd1:='ALTER SESSION SET NLS_SORT=BINARY_CI';
      cmmd2:='ALTER SESSION SET NLS_COMP=LINGUISTIC';
       
       EXECUTE IMMEDIATE cmmd1;
       EXECUTE IMMEDIATE cmmd2;
      
  END SET_NLS_ONLOGON;

  Note: From 5.3.0 , there is a minor feature about the issue, refer to TMDM-5352 - Add an option to make CONTAINS operator case insensitive - CLOSED.