Case insensitive variable queries

Camunda 7 Topics Discussion & Questions
1

Is there any way of querying variables in HistoricProcessInstanceQuery using case insensitive like (SQL ilike)?

Right now, I can only see HistoricProcessInstanceQuery.variableValueLike(), which is translated to SQL case sensitive likes.

1 Like
2

Hi Clessio Cunha Mendes,

It is set to be case sensitive on database level.
You can change it on database level too

For example in the case of mysql database
Tables are set as CHARSET=utf8 COLLATE utf8_bin

You can change collation as follow
http://dev.mysql.com/doc/refman/5.7/en/charset-column.html

1 Like
3

Thanks hassang.

Changing collation doesn’t seem to be an option on my postgresql database, hosted on a cloud service.

So, I think this should be a feature request to have case insensitive query options on Camunda. Or maybe extending HistoricProcessInstanceQuery to make them available myself.

Is there a simple way to extend the query implementation to add custom functions like that?

2 Likes
4

I also am using the Camunda PostgreSQL distribution and I also miss this feature.
I do not want to filter in the java code afterwards as I’m using pagination and this makes it much harder than it should be.

Is it perhaps possible to alter the ‘text_’ datatype from text to citext?

5

I was able to change the datatype of table act_hi_varinst column text_ to citext using PostgreSQL.
You only have to install or activate the extension with following command and change the datatype

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

The camunda internal query matches are case insensitive now for that given column.

-Note that this implementation has a slight performance penalty towards querying.

1 Like
6

Hey Nico,

Thanks for sharing your findings. Elaborating on the question from your second last post: You can surely do these things. We won’t fix bugs related to that, though. Users can submit pull requests that we consider to merge as long as they don’t break the supported environments/database schemas.

Cheers,
Thorben

7

Hello! There were changes in question about using case insensitive? Is it still changing on database level?

8