wrong SQL generated on Oracle when using like

[kws]

I have a Person with multiple addresses. When searching a Person living in a street starting with SomeValue_, the query becomes:
select *
from PERSON person
join ADDRESS address
on address.PERSON = person.ID
where address.STREET like 'SomeValue\_%'

(note the backslash)

Apparently, the backslash character is a default escape character for most databases. Oracle, however does not have this, so the query should read:
select *
from PERSON person
join ADDRESS address
on address.PERSON = person.ID
where address.STREET like 'SomeValue\_%' escape '\'

IMHO the OracleTemplates class should override the STARTS_WITH, ENDS_WITH and STRING_CONTAINS operations like this:
templates.put(Ops.STARTS_WITH, templateFactory.create("{0} like {1%} escape '\\'"));
templates.put(Ops.ENDS_WITH, templateFactory.create("{0} like {%1} escape '\\'"));
templates.put(Ops.STRING_CONTAINS, templateFactory.create("{0} like {%1%} escape '\\'"));

The same applies for the like - ignoreCase templates.

It should also be possible to configure this escape character. Right now, this is hardcoded in the StringEscape class. IMHO this should be provided by the appropriate SqlTemplates implementation.

Thanks.

[Timo Westkämper (timo-westkamper)]

I fixed the Oracle like templates in SVN trunk. I am still trying to figure out the best way to make the escape character configurable.

[Timo Westkämper (timo-westkamper)]

The escape char is now configurable in all SQLTemplates subclasses :

e.g.

new OracleTemplates('!', false)

[Timo Westkämper (timo-westkamper)]

Released in 2.0.7