Using Hibernate 2.1.2 against Oracle 9. It's a generic problem so I haven't included mappings yet...

I want to construct a like clause in OQL to retrieve rows where the value in a field starts with the percent character. In JDBC, I would write this:

select * from table_name where field_name like '&%%' {escape '&'}

So I tried this in OQL:

select b1 from b1 in class test.Blah where b1.name like '&%%' {escape '&'}

But this gets translated by Hibernate into this:

select b1.id as x0_0_ from djones.blah b1 where (b1.name like '&%%' {escape '&' } )

This is not a valid JDBC SQL statement.

I think the problem may be related to a bug with named SQL queries on JIRA:

http://opensource.atlassian.com/project ... wse/HB-898

But thought I'd ask here if there is a different way to formulate this particular LIKE query in OQL.

Thanks in advance,

DJ

That is not a bug. Nowhere in the Hibernate documentation does it say that you can use JDBC escapes in HQL.

Thanks for the quick response. (Sorry - mixed up OQL and HQL in original message but you got my gist!)

I can't figure out from the docs how to formulate the "begins with percent" like clause in HQL. Is there a way?

this works fine for me... (oracle)

Thanks, but that didn't work for me...

Here are the Oracle statements to create my trivial test table and insert some values:

create table blah (name varchar2(255) not null);
insert into blah values ('% profit');
insert into blah values ('% yield');
insert into blah values ('diff');
commit;

In my test app, I want to pull out just the rows beginning with the percent character, but SQL assumes that a percent in a like clause is a multi-character wildcard unless it is escaped. I can do this in SQL using the 'escape' keyword (in Oracle anyway):

select name from blah where name like '&%%' escape '&';

...but I'm using Hibernate and need to do this with HQL.

Any more ideas?

DJ

Sorry. I finally understand the problem... have not had to handle this situation. I wonder is session.createSQLQuery could help get around the problem...