I am facing an odd problem with my application. The application was developed and tested using HSQLDB and worked great. When I built the WAR file and deployed it to the server, one specific bit of code (which is important for the application, of course) failed.
Code
def assessment = Assessment.findByPostingAndAssessor(posting, judge)
Error:
Caused by: java.sql.SQLException: No value specified for parameter 2 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2214) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2138) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1853) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1808) at org.hibernate.loader.Loader.doQuery(Loader.java:697) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228)
I looked at google and found this pretty old blog post
http://blog.flurdy.com/2008/09/no-value-specified-for-parameter-when.html
which refers to this problem and says that it was caused by a bad version of sleep mode. But this is three years; Now I am using grails 1.3.7 with the 1.3.7 sleep plugin. Also, I couldn't figure out where to find the maven specifications that are published on the blog so that I can edit them.
And none of my other applications demonstrate this problem. I tried using the search engine as above, and tried to manually create a criteria query, but both of them made the same mistake. None of the values passed to the method are null.
I really really need this to work as soon as possible, so I wonder if anyone has any ideas on what to do next.
For the record, you can see the full stack trace here: http://grails.1312388.n4.nabble.com/file/n3787337/hibernate-exception.txt
Thanks,
Gene
EDITED 9/2/2011:
Here is the hibernate / SQL log that is causing the error:
11/09/02 17:56:15 DEBUG hibernate.SQL: select this_.id as id22_0_, this_.version as version22_0_, this_.assessor_id as assessor3_22_0_, this_.comment as comment22_0_, this_.date_created as date5_22_0_, this_.last_updated as last6_22_0_, this_.value as value22_0_ from assessment this_ where this_.id=? and this_.assessor_id=? 11/09/02 17:56:15 TRACE type.LongType: binding '2' to parameter: 1 11/09/02 17:56:15 ERROR util.JDBCExceptionReporter: No value specified for parameter 2 11/09/02 17:56:15 ERROR docusearch.UiController: Could not save assessment org.hibernate.exception.SQLGrammarException: could not execute query
This is followed by a stack trace.
EDITED 3/9/2011:
Here are the classes:
package com.fxpal.docusearch import com.sun.org.apache.xalan.internal.xsltc.cmdline.getopt.IllegalArgumentException; class Assessment { Posting posting AssessmentValue value Searcher assessor static belongsTo = [posting: Posting, assessor: Searcher] static indexes = { posting() assessor() } static constraints = { posting(nullable: false) value() assessor(nullable: true) } static judge(Posting posting, Searcher judge, String value) { if (judge == null) throw new IllegalArgumentException("missing judge value"); // error occurs here def assessment = Assessment.findByPostingAndAssessor(posting, judge) def judgment = AssessmentValue.fromString(value) if (judgment && !assessment) assessment = new Assessment( posting: posting, assessor: judge ) if (!judgment && assessment) { assessment.delete(flush:true) assessment = null } else { assessment.value = judgment assessment.save(flush:true) } return assessment } } package com.fxpal.docusearch class Posting { Document document int rank double score Assessment assessment static mapping = { cache true } static belongsTo = [document: Document, query: Query] static constraints = { document(nullable: false) rank() score() assessment(nullable: true) } } package com.fxpal.docusearch import com.fxpal.authentication.User class Searcher Extends User { String name String email String color static constraints = { name(blank:false, unique: true, maxSize:255) email(nullable: true) color(blank: false) } static mapping = { tablePerHierarchy true } public String displayName() { return name ?: username } } package com.fxpal.authentication // This is the generic class generated by the Spring Security plugin class User { String username String password = 'n/a' boolean enabled = true boolean accountExpired = false boolean accountLocked = false boolean passwordExpired = false static constraints = { username blank: false, unique: true password blank: false } static mapping = { password column: '`password`' } Set<Role> getAuthorities() { UserRole.findAllByUser(this).collect { it.role } as Set } }
Do you think the hierarchical relationship between Searcher and User is part of the problem?
Again, I must emphasize that this code works when I run it as a starting application with a database in memory.
EDITED 3/9/2011:
Here is an example illustrating the problem. It grails run-app fine like grails run-app (in memory), but doesn't work when you use "grails prod run-app" (with MySQL db).
http://grails.1312388.n4.nabble.com/file/n3788449/test.zip
EDITED 9/4/2011: I asked this question to the Grails Nabble group as well, and Daniel Henrique Alves Lima identified a potential problem with my code layout. (See his post on Nabble). The problem is that I encoded:
static belongsTo = [posting: Posting, assessor: Searcher]
in my Assessment class, which led to the behavior described above. When I changed the announcement to
static belongsTo = [assessor: Searcher]
The code findBy..() worked correctly. I still think that there is a hidden error in the code hiding the parameters - this is not the best way to report errors :-)), but at least my program now saves the data!
Thanks to everyone who contributed to the solution!