Hibernate gurus,

I have just recently downloaded and extracted hibernate-2.1.7c.zip and have attempted to follow the "roadmap" directions located at http://www.hibernate.org/152.html - "Getting Started".

I have tried the "roadmap" using both McKoi1.0.3 and now MySQL4.1 and neither has completed successfully out of the box.

From what I can tell from the generated SQL listed below, there is a trailing "limit ?" that is messing everything up. It appears to be added because of the following highlighted code taken from eg/org/hibernate/auction/Main.java:

List auctions = s.createQuery(
"select new AuctionInfo( item.id, item.description, item.ends, max(bid.amount) ) "
+ "from AuctionItem item "
+ "left join item.bids bid "
+ "group by item.id, item.description, item.ends "
+ "order by item.ends desc"
)
.setMaxResults(100)
.list();

When I comment out the setMaxResults() method all is well.

Here are the other gory details in case they are necessary . . .

Hibernate version: 2.1.7c

Mapping documents:

from hibernate.properties:

## MySQL

hibernate.dialect net.sf.hibernate.dialect.MySQLDialect
#hibernate.connection.driver_class org.gjt.mm.mysql.Driver
hibernate.connection.driver_class com.mysql.jdbc.Driver
hibernate.connection.url jdbc:mysql:///test
hibernate.connection.username <my username>
hibernate.connection.password <my password>

Code between sessionFactory.openSession() and session.close():

from eg/org/hibernate/auction/Main.java:

Session s = factory.openSession();
Transaction tx=null;
try {
s.setFlushMode(FlushMode.NEVER); //entirely optional!!
tx = s.beginTransaction();

List auctions = s.createQuery(
"from AuctionItem item "
+ "left join fetch item.bids bid left join fetch bid.bidder "
+ "order by item.ends desc"
)
.setMaxResults(100)
.list();

Iterator iter = new HashSet(auctions).iterator();
while ( iter.hasNext() ) {
AuctionItem auction = (AuctionItem) iter.next();
System.out.println(
"Auction: " + auction.getId() + " - " + auction.getDescription() +
", ends: " + auction.getEnds() +
", bids: " + auction.getBids()
);
}
System.out.println();

tx.commit();
}
catch (Exception e) {
if (tx!=null) tx.rollback();
throw e;
}
finally {
s.close();
}


Full stack trace of any exception that occurs:

[java] 23:17:20,796 WARN JDBCExceptionReporter:57 - SQL Error: 1064, SQLSt
ate: 42000
[java] 23:17:20,796 ERROR JDBCExceptionReporter:58 - You have an error in y
our SQL syntax; check the manual that corresponds to your MySQL server version f
or the right syntax to use near '?' at line 1
[java] 23:17:20,796 WARN JDBCExceptionReporter:57 - SQL Error: 1064, SQLSt
ate: 42000
[java] 23:17:20,806 ERROR JDBCExceptionReporter:58 - You have an error in y
our SQL syntax; check the manual that corresponds to your MySQL server version f
or the right syntax to use near '?' at line 1
[java] net.sf.hibernate.exception.SQLGrammarException: Could not execute qu
ery
[java] at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCo
deConverter.java:69)
[java] at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCEx
ceptionHelper.java:30)
[java] at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:41
10)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1556)

[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[java] at org.hibernate.auction.Main.viewAllAuctionsSlow(Main.java:86)
[java] at org.hibernate.auction.Main.main(Main.java:366)
[java] Caused by: java.sql.SQLException: You have an error in your SQL synt
ax; check the manual that corresponds to your MySQL server version for the right
syntax to use near '?' at line 1
[java] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851)
[java] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
[java] at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPr
eparedStatement.java:1485)
[java] at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedS
tatement.java:151)
[java] at com.mysql.jdbc.Connection.prepareStatement(Connection.java:13
09)
[java] at com.mysql.jdbc.Connection.prepareStatement(Connection.java:12
81)
[java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(Batche
rImpl.java:260)
[java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(Batche
rImpl.java:235)
[java] at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(Batch
erImpl.java:66)
[java] at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.j
ava:779)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCol
lections(Loader.java:133)
[java] at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.jav
a:854)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1553)

[java] ... 3 more
[java] Exception in thread "main"


BUILD FAILED
C:\JAB\Work\hibernate-2.1\build.xml:509: Java returned: 1

Name and version of the database you are using: MySQL Server 4.1

The generated SQL (show_sql=true):

[java] Hibernate: select auctionite0_.id as id0_, bids1_.id as id1_, user2_
.id as id2_, auctionite0_.description as descript2_0_, auctionite0_.ends as ends
0_, auctionite0_.condition as condition0_, auctionite0_.seller as seller0_, auct
ionite0_.successfulBid as successf6_0_, bids1_.isBuyNow as isBuyNow1_, bids1_.am
ount as amount1_, bids1_.datetime as datetime1_, bids1_.bidder as bidder1_, bids
1_.item as item1_, user2_.userName as userName2_, user2_.`password` as y3_2_, us
er2_.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_.it
em left outer join AuctionUser user2_ on bids1_.bidder=user2_.id order by aucti
onite0_.ends desc limit ?

Debug level Hibernate log excerpt: info

>>>> Is there some configuration parameter that needs to be set in order to make this work properly?

Thanks!
JAB

See this thread.

thanks!

thanks!