Hi there,
Does someone know about an issue with hibernate 3.x and Postgres > 8.2 ?
We currently use Postgres 8.2.x and hibernate 3.2 within glassfish v2ur2.
We plan to migrate to an earlier machine with a little refresh of all of that.
That means Postgres 8.4.1, hibernate 3.3.2, glassfish v2.1.1
For now, i just run our application in this new environment as it.
Here is the trouble
Code:
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:637)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:107)
at hmd003.metier.ClientServiceBean.getClientForIhm(ClientServiceBean.java:292)
... number of lines
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2235)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
... number of lines
Caused by: [color=#800000]org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer[/color]
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 5163
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
... number of lines
A first look at postgres mailing list lets me know that
* postgres is more strict with the SQL syntax.
* Hibernate would SQL generate code with string parameter (setString) even for int type.
In fact i can get this kind of error in pure SQL.
If I run the query (where 'num' is an int) :
Code:
select * from client_client where num = '950000'::varchar
Then Postgres 8.2 returns the expected result ; But Postgres 8.4 shoots me with
Code:
ERROR: operator does not exist: integer = character varying
LINE 1: select * from client_client where num = '950000'::varchar
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
That lets me believe hibernate postgreSQLDialect could generate a dirty SQL for 8.4.
Could someone tell me if there is an issue or maybe I'm wrong ?
Thanks you for reading this long post :)
Here come more details about this case, if needed.
the query was
Code:
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public Client getClientForIhm(int idClient) {
Query query = em.createQuery(
"Select distinct c " +
"from Client c " +
"left join fetch c.listeTelecom_telecoms tc left join fetch tc.refNatureTelecom " +
"left join fetch c.listeAdresse_adresses " +
"left join fetch c.dernierCatalogueEnvoye " +
"left join fetch c.idCodeAnalytique_contactOrigine " +
"left join fetch c.langage " +
"left join fetch c.marque " +
"where c.num = :idClient");
query.setParameter("idClient", idClient);
Client client = (Client) query.getSingleResult();
//... more code
The entity looks like
Code:
@Entity @org.hibernate.annotations.Entity (dynamicInsert=true, dynamicUpdate=true)
@Table (name="client_client")
@Proxy @BatchSize (size=10)
public class Client implements Serializable {
private static final long serialVersionUID = 1L;
@Id @GeneratedValue (strategy=GenerationType.SEQUENCE, generator="client_num_seq")
@SequenceGenerator (name="client_num_seq", sequenceName="client_num_seq", allocationSize = 1, initialValue=1)
private Integer num;
// ... more code