Bad Escaping for LIKE in MySQL

• Key: OPENJPA-80
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Unassigned
• Reporter: Jakob Braeuchi
• Component/s: query
• Fix Version/s: 0.9.7

Environment

openJPA 0.9.7
MySQL 5.0.15

Description

the following query :

em.createQuery("select k from KategorieGruppe k where k.bezeichnung like ?1 order by k.bezeichnung asc");

generates an sql-statement with a bad escaping character '\' for MySQL :

SELECT t0.id, t0.bezeichnung FROM ekv2kategoriegruppe t0 WHERE (t0.bezeichnung LIKE ? ESCAPE '\') ORDER BY t0.bezeichnung ASC

the correct escaping would be '\\'.

Comments

[Jakob Braeuchi]

the variable searchStringEscape must be correctly set in org.apache.openjpa.jdbc.sql.MySQLDictionary:

...
 searchStringEscape = "\\\\";
...

[Marc Prud'hommeaux]

Note that you should be able to work around the issue for the time being by setting the property "openjpa.jdbc.DBDictionary" to "searchStringEscape=\\" in your persistence.xml file or in the properties you use to create the EntityManagerFactory.