I have problems getting the example program 'Auction' to run successfully using the 'ant eg' command. It succeeds in creating and populating the tables in the MySQL database. But it fails to query the table.
Here is the java code from the example:



Here is the output from the console with my comments:
[java] 12:27:13,140 DEBUG SessionImpl:959 - find: from AuctionItem item left join fetch item.bids bid left join fetch bid.bidder order by item.en
ds desc
[java] 12:27:13,140 DEBUG QueryParameters:212 - named parameters: {}
[java] 12:27:13,171 DEBUG QueryTranslatorImpl:170 - compiling query
[java] 12:27:13,203 DEBUG QueryTranslatorImpl:227 - HQL: from org.hibernate.auction.AuctionItem item left join fetch item.bids bid left join fetc
h bid.bidder order by item.ends desc

The HQL query seems to be matching the code.

[java] 12:27:13,203 DEBUG QueryTranslatorImpl:228 - SQL: select auctionite0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.descr
iption as descript2_0_0_, auctionite0_.ends as ends0_0_, auctionite0_.condition as condition0_0_, auctionite0_.seller as seller0_0_, auctionite0_.succ
essfulBid as successf6_0_0_, bids1_.amount as amount1_1_, bids1_.`datetime` as y4_1_1_, bids1_.bidder as bidder1_1_, bids1_.item as item1_1_, bids1_.i
sBuyNow as isBuyNow1_, user2_.userName as userName2_2_, user2_.`password` as y3_2_2_, user2_.email as email2_2_, user2_.firstName as firstName2_2_, us
er2_.`initial` as y6_2_2_, user2_.lastName as lastName2_2_, 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 limit ?

The translated SQL query contains the unexpected '?'

[java] 12:27:13,203 DEBUG AbstractBatcher:252 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[java] 12:27:13,203 DEBUG SQL:290 - select auctionite0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.description as descript2_0
_0_, auctionite0_.ends as ends0_0_, auctionite0_.condition as condition0_0_, auctionite0_.seller as seller0_0_, auctionite0_.successfulBid as successf
6_0_0_, bids1_.amount as amount1_1_, bids1_.`datetime` as y4_1_1_, bids1_.bidder as bidder1_1_, bids1_.item as item1_1_, bids1_.isBuyNow as isBuyNow1_
, user2_.userName as userName2_2_, user2_.`password` as y3_2_2_, user2_.email as email2_2_, user2_.firstName as firstName2_2_, user2_.`initial` as y6_
2_2_, user2_.lastName as lastName2_2_, bids1_.item as item__, bids1_.id as id__ from AuctionItem auctionite0_ left outer join Bid bids1_ on auctionite
0_.id=bids1_.item left outer join AuctionUser user2_ on bids1_.bidder=user2_.id order by auctionite0_.ends desc limit ?
[java] Hibernate: select auctionite0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.description as descript2_0_0_, auctionite0_.
ends as ends0_0_, auctionite0_.condition as condition0_0_, auctionite0_.seller as seller0_0_, auctionite0_.successfulBid as successf6_0_0_, bids1_.amo
unt as amount1_1_, bids1_.`datetime` as y4_1_1_, bids1_.bidder as bidder1_1_, bids1_.item as item1_1_, bids1_.isBuyNow as isBuyNow1_, user2_.userName
as userName2_2_, user2_.`password` as y3_2_2_, user2_.email as email2_2_, user2_.firstName as firstName2_2_, user2_.`initial` as y6_2_2_, user2_.lastN
ame as lastName2_2_, 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 limit ?
[java] 12:27:13,203 DEBUG AbstractBatcher:341 - preparing statement
[java] 12:27:13,203 DEBUG JDBCExceptionReporter:49 - SQL Exception
[java] java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near '?' at line 1

The following comes from the MySQL driver trace. Note that it is complaining about the '?'
[java] Query being prepared when exception was thrown:

[java] select auctionite0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.description as descript2_0_0_, auctionite0_.ends as end
s0_0_, auctionite0_.condition as condition0_0_, auctionite0_.seller as seller0_0_, auctionite0_.successfulBid as successf6_0_0_, bids1_.amount as amou
nt1_1_, bids1_.`datetime` as y4_1_1_, bids1_.bidder as bidder1_1_, bids1_.item as item1_1_, bids1_.isBuyNow as isBuyNow1_, user2_.userName as userName
2_2_, user2_.`password` as y3_2_2_, user2_.email as email2_2_, user2_.firstName as firstName2_2_, user2_.`initial` as y6_2_2_, user2_.lastName as last
Name2_2_, 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 limit ?
[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(ServerPreparedStatement.java:1485)
[java] at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:151)
[java] at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1309)
[java] at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1281)
[java] at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:349)
[java] at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:300)
[java] at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:81)
[java] at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:978)
[java] at org.hibernate.loader.Loader.doQuery(Loader.java:346)
[java] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:193)
[java] at org.hibernate.loader.Loader.doList(Loader.java:1303)
[java] at org.hibernate.loader.Loader.list(Loader.java:1286)
[java] at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872)
[java] at org.hibernate.impl.SessionImpl.find(SessionImpl.java:973)
[java] at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84)
[java] at org.hibernate.auction.Main.viewAllAuctionsSlow(Main.java:87)
[java] at org.hibernate.auction.Main.main(Main.java:367)
[java] 12:27:13,203 WARN JDBCExceptionReporter:57 - SQL Error: 1064, SQLState: 42000
[java] 12:27:13,218 ERROR JDBCExceptionReporter:58 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '?' at line 1


Hibernate version: 3.0 beta 1 but happened with 2.1.7 too

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

Commenting out the 'setMaxResults' made the '?' problem disappear. I appreciate any insights from folks who faced this problem before.

This is the code that worked:


Thanks
Jagadeesh.

using the correct dialect?

I tried all the MySQL dialects that are in the default hibernate.properties file with both the mysql driver flavors specified. I tried 6 combinations and everyone of them gave the same error.

I tried hibernate 2.1 too, but with one combination. I had the same problem.

This is one combination among the 6:

#hibernate.dialect org.hibernate.dialect.MySQLDialect
hibernate.dialect org.hibernate.dialect.MySQLInnoDBDialect
#hibernate.dialect org.hibernate.dialect.MySQLMyISAMDialect
#hibernate.connection.driver_class org.gjt.mm.mysql.Driver
hibernate.connection.driver_class com.mysql.jdbc.Driver
hibernate.connection.url jdbc:mysql:///jktest?logger=com.mysql.jdbc.log.StandardLogger&profileSQL=true&dumpQueriesOnException=true&pedantic=true
hibernate.connection.username jk
hibernate.connection.password xxxx

Did you find the answer to this? I am having the same issue. It didn't happen with previous versions of mysql but with 4.1, I have this problem as well. Thanks.

Joe

This is a MySQL Bug. See http://opensource.atlassian.com/project ... se/HB-1347

Thank you very much Michael

Joe