Problem using white space in the People form field

Flowable Engine
1

The People Type form field does not return results when using white space.
Ex: When typing “Test” the user defaul “Test Administrator” is returned, but when typing (the first and second name) "Test " or “Test A” is not returned the user name “Test Administrator”, according to the images:

NOTE: The Group form field works correctly.

2

The ‘issue’ here is not the white space but the fact that the actual query that is executed is a like on the first name or on the last name.
‘Test’ is the first name; ‘Administrator’ the lastname. ‘Test A’ doesn’t match both.

Regards,

Yvo

3

Yvo,
Is it possible to change the search to be performed in the concatenation of the first_ and last_ fields? Because on a large user base there are many homonyms being necessary to enter at least the first and second names.

Best regards.

4

Hi,

this is currently not on the roadmap.
One of the reasons is that creating a solution for this specific request will involve database specific implementations.

If you have other possible solutions. Please let us know.

Regards,

Yvo

5

Yvo,

When you say “involve database specific implementations” are you saying that each database uses a different notation for Concatenation? Ex: PostgreSQL = “||”, MySQL = “Concat”, SQL Server = “+”.
I believe it can have three possible paths:

  1. Create a condition to check which database and create distinct SQLs for each database.
  2. Use the properties file of each database to create a variable that defines a concatenation operator.
  3. Create a View that returns a FullName field with the concatenation of First and Last name. The creation of the view should be added to the Bank creation script.
  4. Create a concatenation function in each data structure creation SQL script

Which option do you think is the best option?

6

The Flowable engine uses MyBatis as thin layer between the raw sql and the logic. Option 2) is probably the way to go, as MyBatis has a mechanism built-on to switch based on database type and/or use a property.

7

Looking at the latest Flowable version 6 MySQL Jenkins build, you will find a single test in error.

The error message is

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' '  RES.LAST_) like '%erm%')          order by RES.ID_ asc     LIMIT 2147483647' at line 5 ### The error may exist in org/flowable/idm/db/mapping/entity/User.xml ### The error may involve org.flowable.idm.engine.impl.persistence.entity.UserEntityImpl.selectUserByQueryCriteria-Inline ### The error occurred while setting parameters ### SQL: select RES.*            from ACT_ID_USER RES             WHERE  ((RES.FIRST_  ' '  RES.LAST_) like ?)          order by RES.ID_ asc     LIMIT ? OFFSET ? ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' '  RES.LAST_) like '%erm%')          order by RES.ID_ asc     LIMIT 2147483647' at line 5

Reading the post from @robsoncardoso.ti it would appear that the correct property to set for conconcatenta for MySQL is “Concat”. But when examining the mysql.properties file the value is sent to blank:

concatOpeator=

This was done as part of PR 405.

I don’t have a MySQL database handy to test out this supposition.

8

Looks like @yvo has a better solution in PR 430.