Greetings,
I'm having trouble with a query that uses MySQL variables and assigns the result of a query to the variable. MySQL uses the ":=" operator as a variable assignment operator, and unfortunately it seems like it's being treated as a named query parameter, because of the ":" in the query.
The query looks like this:
Code:
<sql-query name="com.mycompany.core.DentalFamilyMember.customerIds">
<return alias="dental" class="com.mycompany.core.DentalFamilyMember"/>
<![CDATA[
select {dental.*} from Customer customer INNER JOIN DentalFamilyMember dental ON (customer.id=dental.customerId and customer.businessId=dental.businessId and customer.businessId = ? and customer.optedIn=?) where (@a:=(DATE_FORMAT(dental.nextAppointmentDate,'%Y-%m-%d') < ? or dental.nextAppointmentDate is null) and (DATE_FORMAT(dental.lastVisit,'%Y-%m-%d') <= ? and dental.reminderSent=?)) or ( @a = 1 and DATE_FORMAT(dental.reminderSentDate,'%Y-%m-%d') <= ? and
dental.reminderSent=?) order by dental.lastVisit desc;
]]>
</sql-query>
When I run the query, I get a Hibernate JDBCException, caused by a MySQL SQLException:
Code:
Caused by: java.sql.SQLException: No value specified for parameter 8
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.jav
a:2201)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.jav
a:2154)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
1452)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3764)
Is there a way I could perhaps escape the variable assignment operator in the query so that Hibernate would not treat it as a named parameter? I looked through the sources, and I couldn't find a place where such an escape would be handled.
Thank you!
Hibernate version: 2.1.2
Mapping documents:
Code between sessionFactory.openSession() and session.close():
Full stack trace of any exception that occurs:
Name and version of the database you are using: MySQL 4.0.17
The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
Login
FAQ
Search