Re: hibernate nativequery and uuid

[Andrew <archa(at)pacific(dot)net(dot)au>]

Oh, I see what you mean.  Use EJBQL on a view.  That would probably 
work.  Have to get going, so will try that when I get back in several 
hours.  I'll let you know how I go.

Andrew wrote:
> I have given it consideration, but haven't tried it as I have 
> concluded that I would still have the same issue.  The problem occurs 
> with the client attempting to apply a UUID value to filter the result 
> set.  So I would still have a datatype mismatch if I were to use a view.
>
> I am confident that I can get it to work if I were to remove Hibernate 
> and make a direct JDBC call, as I have tested that approach 
> previously.  That is a fall back position.  But I would like if I can 
> to keep to a single approach with my DB access for this application if 
> I can.
>
> The suggestion was appreciated though.
>
>
>
> A.M. wrote:
> > On Jul 31, 2008, at 5:57 PM, Andrew wrote:
> >
> > > I'm currently using JPA with Hibernate as my ORM and have been able 
> > > to convince hibernate to play nicely with the Postgresql UUID.  Most 
> > > of my queries have been in EJBQL using the JPA entity manager's 
> > > createQuery.  However when I try to do a UNION, JPA only returned 
> > > the results of the first query, and ignored the other UNION queries, 
> > > which is in line with what I have read online, in that JPA does not 
> > > support UNIONS.
> > > So I'm currently attempting to go via a createNativeQuery call.  The 
> > > keys on the target tables are PostgreSQL UUID data types.  When 
> > > doing so I get the following error:
> > >
> > > org.postgresql.util.PSQLException: ERROR: operator does not exist: 
> > > uuid = character varying
> > >
> > > The only relevant thing I have been able to find relating to it is 
> > > http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which 
> > > suggests adding a ::uuid cast to the parameter.
> > >
> > > However, when doing that, hibernate thinks that it is a named 
> > > parameter and complains.
> > > org.hibernate.QueryException: Not all named parameters have been set
> > >
> > > Hibernate's functionality to declare an escape character for a LIKE 
> > > clause does not apply to this.  But in attempting to escape the 
> > > colons, getting various other hibernate parsing errors, so my 
> > > attempts down this path has not been of help.
> > >
> > > Has anyone else run into this issue and been able to resolve it?
> > >
> > > I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the 
> > > Postgresql 8.3-603 JDBC4 driver.
> >
> > Could you use a view to hide the UNION?
> >
> > Cheers,
> > M
> >
> > No virus found in this incoming message.
> > Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database: 
> > 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM