Custom Query with LIKE operator works well on studio but not when deployed

Q&A
1

Hi all,

I'm having some trouble with a custom query with LIKE operator when I deploy the BDM to portal. Here the details.

  • I use Bonita studio 7.9.4 community on my PC with standard configuration and h2 database. 
  • I've deployed a Tomcat Server with Bonita webapp which use Postgres sql server as database engine

All works well when I develop a process on studio and deploy it on the Bonita server. The only difference I see is that when I define a BD object that contain a TEXT attribute, in the h2 database the attribute content is fully displayed, instead on postgres database the attribute content is replaced by a long number. Here I imagine that the real text is stored somewhere in another table where the long number is the ID of the row containing the data (still don't know where). Really not a problem because when I access data with REST API, the data is correctly read from database. Until now. But.

If I define a custom query that has LIKE operator on such TEXT attribute, I've problem when I deploy such query. For example the custom query is:

SELECT s 
FROM Segnalazione s 
WHERE s.registro LIKE :utente
AND NOT (s.utenteInserimento= :segnalatore OR s.destinatario= :segnalatore)
ORDER BY s.dataInserimento DESC

registro is the TEXT attribute of Segnalazione. Parameters utente and segnalatore are java.lang.String

When I call the REST API from an application page on the local webserver on h2 database:

../API/bdm/businessData/com.company.model.Segnalazione?q=findByCoinvolto&p=0&c=100&f=utente=%25antonio.bianchi%25&f=segnalatore=antonio.bianchi

The API give me the correct JSON object that correspond to such query. Instead when I deploy the BDM and the application page on the remote Bonita Server, when I call the same API, the server reply me:

{"exception":"class org.bonitasoft.engine.exception.BonitaRuntimeException","message":"USERNAME=massimiliano.donno | org.bonitasoft.engine.commons.exceptions.SRetryableException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet"}

If I see server logs I can see:

...  javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet ...
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet ...
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: text ~~ bigint
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 1287

It seems that when I query the BDM with REST API, the engine knows well how to retrieve data in TEXT fields, but when I use the LIKE operators on such TEXT fields, the engine forget that the field contain a long that is an id that has to be used to retrieve the data somewhere in the database. 

I'm forgetting something or I'm doing something wrong here? There is something I can do to bypass the problem?

Thank You all.

2

Hi Massimiliano,

It looks like the behaviour come from the fact that the underlying database engine is different (H2 in local server, opposed to PostgreSQL on remote server).

From what I've found on the Internet it could be related to the fact that the driver PostgreSQL is not able to convert a Java long to SQL Text without an explicit conversion (while H2 is able to do it on its own): Stackoverflow: operator-does-not-exist-text-bigint (it is a pretty old post, but it is still a valid point to look at anyway):

When you created your custom query in Bonita, what type did you give to the :utente query parameter?

Make sure it is of type java.lang.String and that you make an explicit conversion when calling your query (i.e. call with a value like "123" and not 123).

Hope this helps,

Captain Bonita

3

Hi Nicolas,

The variable type of :utente is already java.lang.String. It seems that the LIKE operator don't work with TEXT field. Instead work with string field. I see on underlying database that the same field is represented differently. While the H2 database store the TEXT field in the same table, the postgres database store a sort of code, that surely address a row where the true text field is stored, but I don't know where is stored. And seems that while the normal query can extract this value, the LIKE query is not able to "resolve" this address. Isn't it?

4

Hi,

Oh right! I missed that. If you are using TEXT (clob) instead of String (varchar), then depending on the database the storage might be different (as explained in Bonita Studio). But then I do not know if the like operator works on your PostgreSQL version on a clob column.

What use cases brought you to use the TEXT data type and perform a LIKE operation on it? I would be interested to know, so please do not hesitate to share. :-)

If it is an option I would suggest not to use the TEXT data type but use String (varchar) instead as it may have a significant impact on database server (storage & cache). I would also suggest to limit the usage of LIKE operator as again it might result in slow queries and affect the user experience.

Captain Bonita

5

Hi Nicolas,

The field I'm using as TEXT field is quite similar to a log, that contain activity of each user that has interaction with the business object. This field born only to contain log data (eg. 21/03/2020 - John Smith: requested data... ). I just was trying to check if a suggestion of a colleague was feasible: "Is possible to check all users involved in the process?". So my idea was to use a LIKE query on log field for specific username just to see if that user had been involved with that object. In term of process is not a problem, maybe I can store involved users in different fashion over a different field if this is really useful to check involved users. I was just curious about why that is a problem on database type and work perfectly on other database type, in order to be more skilled on designing process with Bonita. Today I still ignore where TEXT content ir really stored on a postgres database, I've searched everywhere (also on google).