Hibernate count request on Sybase ASE 15

[Thomas]

Hi all

I'm facing a SqlGrammarException on my app when I execute a count()
request on my model object, while delete(), find() and save() works
all very well.
When enabling the jpa.debugSQL option, I see that Hibernate has
generated a request with incorrect request for Transact SQL syntax
(I'm requesting a Sybase ASE Db).

select count((systempara0_.interface, systempara0_.name,
systempara0_.origine)) as col_0_0_ from system_parameters systempara0_

The syntax error is in the count content, Transact SQL doesn't allow
to have more than one column in the statement. If a remove the commas
and set only one column inside the count, the request is OK.

I really don't know how to fix this, does someone have an idea ?

--Tom

Note : As my model has multiple primary key, Hibernate put all of them
in the count statement.
Note2 : jpa.dialect is configured to
jpa.dialect=org.hibernate.dialect.SybaseASE15Dialect, and Hibernate
implements a method called supportsTupleCount() in the Dialect classes
but it seems to be in a more recent version than the Hibernat embedded
in Play 1.1.0 [1]

[1] http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/dialect/Dialect.html#supportsTupleCounts()

[Guillaume Bort]

Please ask on the hibernate google group, I'm sure you'll get more
help about this problem.

> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>
>

--
Guillaume Bort, http://guillaume.bort.fr

For anything work-related, use g...@zenexity.fr; for everything else,
write guillau...@gmail.com

[GrailsDeveloper]

See http://stackoverflow.com/questions/4971001/incorrect-sql-request-on-count-method-with-play-sybase-hibernate-when-multiple-pk

> > [1]http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/dialect/Di...()

>
> > --
> > You received this message because you are subscribed to the Google Groups "play-framework" group.
> > To post to this group, send email to play-fr...@googlegroups.com.
> > To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> > For more options, visit this group athttp://groups.google.com/group/play-framework?hl=en.
>
> --

> Guillaume Bort,http://guillaume.bort.fr

>
> For anything work-related, use g...@zenexity.fr; for everything else,

> write guillaume.b...@gmail.com

[Tom]

Hi all

I've posted this issue in the hibernate forum but nobody answer me. [1]

By the way I'm still with my problem : Hibernate generate an incorrect sql count() request for Sybase ASE, for my Model objet that has multiple primary keys.

Can somebody have a look to this gist [2], I put my application.conf, 2 models - as there is 2 ways to declare multiple pk, I implemented both, one with the @idClass (SystemParameter.java) and the other with @embeddable (SystemParameter2.java). There is also the 2 junit that both fails on the count request.

Thanks in advance for your help

--Tom

[1] https://forum.hibernate.org/viewtopic.php?f=1&t=1009542

[2] https://gist.github.com/833439/

[Manuel Bernhardt]

Hi Tom,

I suggest you write the count query by hand, using JPA.em().createNativeQuery().

[Tom]

Ok I will try, but the first time I noticed this issue was when using the CRUD module, which performs some count queries too. 

I don't think writing my own count query with nativeQuery will fix also count query in CRUD module too isn't it ?

--Tom

[Manuel Bernhardt]

> I don't think writing my own count query with nativeQuery will fix also
> count query in CRUD module too isn't it ?

Indeed, that most likely won't work.

One idea in that case would be to use your own superclass for models
that extends the play Model class, and then to write an Enhancer that
will replace the default count() method added by the play JPA
enhancer. Have a look at play.db.jpa.JPAEnhancer, I think you should
be able to redefine (remove + add) the count() method for subclasses
of your own class. One thing I am not sure about though is in which
order the Enhancers will be called but with a bit of luck the built-in
enhancers are applied before other Enhancers.