Hibernate version: 2.1.1
Mysql 4.0.20:

Hi there,

I have code that executes without problems using HQL to find a list of objects with a certain "name" using the LIKE operator (see example and SQL below). However, the moment the "name" contains an apostrophe (') it does not return any values. What is the correct way to search for or use apostrophe's or quotes as I could not find anything in the help documents. Why would it work in all other circumstances but not this one?

Any help or a point in the right direction will be appreciated.

Marius Botha

HQL:
From com.workpool.directory.Entry entry
where (entry.name like "%Nigeria's%" or entry.refNo like "%Nigeria's%")
and (entry.personal="No" or entry.author.id=3) order by entry.name

The generated SQL (show_sql=true):
select entry0_.id as x0_0_ from entry entry0_ where
((entry0_.name like "%Nigeria 's%" or entry.refNo like "%Nigeria' s%" ))
and
((entry0_.personal="No" )or(entry0_.author_id=3 ))
order by entry0_.name

Error:
java.sql.SQLException: General error, message from server: "Unknown table 'entry' in where clause"

Full stack trace of any exception that occurs:
java.sql.SQLException: General error, message from server: "Unknown table 'entry' in where clause"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java
:304)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.hql.QueryTranslator.iterate(QueryTranslator.java:860)
at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1608)
at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1581)
at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1573)
at net.sf.hibernate.jca.JCASessionImpl.iterate(JCASessionImpl.java:194)
at com.workpool.hibernate.HibernateSession.find(HibernateSession.java:3

I did the following and that seem to have resolved the problem. It now works never mind what I type in.

e.g.:
String query = "From com.workpool.directory.Entry entry where (entry.name like :searchFor or entry.refNo like :searchFor)"
Query q = HibernateSession.getInstance().getSession().createQuery(query);
q.setString("searchFor", "%"+searchFor+"%");

Getting the correct result now.