So, unless the rownumber() function is provided by the user, this statement will get a syntax error:
select * from ( select rownumber() over(order by auctionite0_.ends desc) as rownumber_, ...
Also the "over(order by auctionite0_.ends desc)" syntax isn't supported by Derby. Derby doesn't have a built-in rownumber() function -- built-in functions are summarized at http://incubator.apache.org/derby/manuals/reference/sqlj57.html (Reference Manual -> SQL Language Reference -> Built-In Functions).
Unless the rownumber() function is provided by the user, this statement will get a syntax error:
select * from ( select rownumber() over(order by auctionite0_.ends desc) as rownumber_, ...
Also the "over(order by auctionite0_.ends desc)" syntax isn't supported by Derby.
Has anyone else encountered trouble running the Hibernate examples? Other list traffic cites success with hibernate -- see http://article.gmane.org/gmane.comp.apache.db.derby.user/1000 -- so it might be helpful for us to build a list of the examples that work and the ones that don't.
If hibernate/derby users will post specifics about what does and doesn't work, I'll volunteer to organize that info and put it on the derby web site.
-jean
dju dju (JIRA) wrote:
Hibernate bad support ---------------------
Key: DERBY-179
URL: http://issues.apache.org/jira/browse/DERBY-179
Project: Derby
Type: Bug
Components: SQL Versions: 10.0.2.1 Environment: SUN JDK 1.4
Hibernate 2.1.8
Reporter: dju dju
Priority: Blocker
When trying to use Derby with the Hibernate basic example (auction system - ant eg) I get the following error . I have being using the Derby Dialect posted at http://opensource.atlassian.com/projects/hibernate/browse/HB-1224
Here is the error message: =============================================================================== [java] Hibernate: select * from ( select rownumber() over(order by auctionite0_.ends desc) as rownumber_, auctionite0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.description as descript2_0_, auctionite0_.ends as ends0_, auctionite0_.condition as condition0_, auctionite0_.seller as seller0_, auctionite0_.successfulBid as successf6_0_, bids1_.isBuyNow as isBuyNow1_, bids1_.amount as amount1_, bids1_.datetime as datetime1_, bids1_.bidder as bidder1_, bids1_.item as item1_, user2_.userName as userName2_, user2_."password" as y3_2_, user2_.email as email2_, user2_.firstName as firstName2_, user2_."initial" as y6_2_, user2_.lastName as lastName2_, bids1_.item as item__, bids1_.id as id__ from AuctionItem auctionite0_ left outer join Bid bids1_ on auctionite0_.id=bids1_.item left outer join AuctionUser user2_ on bids1_.bidder=user2_.id order by auctionite0_.ends desc ) as temp_ where rownumber_ <= ? [java] 15:16:29,959 WARN JDBCExceptionReporter:57 - SQL Error: -1, SQLState: 42X01 [java] 15:16:29,959 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -1, SQLSTATE: 42X01, SQLERRMC: Encount ered "(" at line 1, column 40�42X01 [java] 15:16:29,990 WARN JDBCExceptionReporter:57 - SQL Error: -1, SQLState: 42X01 [java] 15:16:29,990 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -1, SQLSTATE: 42X01, SQLERRMC: Encount ered "(" at line 1, column 40�42X01 [java] net.sf.hibernate.exception.SQLGrammarException: Could not execute query [java] at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:58) [java] at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29) [java] at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131) [java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1557) [java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49) [java] at org.hibernate.auction.Main.viewAllAuctionsSlow(Main.java:86) [java] at org.hibernate.auction.Main.main(Main.java:366)
Can you help with this?
