CODEWITHSUNDEEP
sqlgrailsgrails-orm
Johansensen
Johansensen

Reputation: 401

GORM query produces database error (grails 1.1.1)

(this is cross-posted from grailsforum.co.uk)

Hi all, I'm relatively new to Grails (and Spring, Hibernate, etc) so please forgive me if this is a noob question. Googling has got me nowhere, but it's rather a difficult problem to search for.

When I run a particular GORM query (in grails 1.1.1), it normally works fine, but in the case where there are no records in the database that match, instead of returning nothing I get an exception bubbling right up from the database driver level. I had wondered if this was an issue with the particular flavour of Oracle my local dev database is running, but this also occurs when running on the in-memory JDBC database, and on the remote dev server which has a database equivalent to the ones our production servers use. It appears, as far as I can tell, that somewhere down the line the query is actually resolving into invalid SQL.

The query is on the domain class CourseTerm, which represents a semester at a university. This class defines starting date and ending date as properties. My query aims to select the set of all course terms active on a given date (there may be multiple as they can overlap), as below:

def currentCourseTerms(date = new Date()) {
     return CourseTerm.findAllByCourseStartDateLessThanAndCourseFinishDateGreaterThan(date, date)
}

As I say, this works perfectly if there are any course terms which match the query. But in the case where no terms are current, the request fails and I get the following output:

Error 500: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Servlet: grails
URI: /ltrp/grails/scheduleableCourse/list.dispatch
Exception Message: ORA-00936: missing expression 
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query 
Class: ScheduleableCourseController

On my workstation where I get the log output, I also see this:

2012-03-23 15:04:18,664 [13262636@qtp-21448718-2] ERROR util.JDBCExceptionReporter  - ORA-00936: missing expression

My research and general intuition tells me that this is probably a bug in Grails or Spring, and that the solution will be to update to a newer version of Grails. This is definitely on the roadmap as I'm not a fan of running out-of-date webapps at the best of times, but my recent attempt to upgrade to the latest 1.x release was not a success, and I don't have time to fight with it right now.

I'm working around the problem by catching the exception when the query is called, but there are enough random errors showing up in our logfiles already without adding more, and I would like to get this functioning properly if at all possible. If anyone can advise me of a solution or more elegant workaround, or at least inform me if this is a known issue, I would appreciate it greatly.

Upvotes: 1

Views: 883

Answers (1)

cprsd
cprsd

Reputation: 473

You can check the SQL that is getting generated by turning on SQL logging as described here. Take that query and execute on the db and see if it works. That should tell you if the sql generated by hibernate is in error or if you're missing something. Alternatively, you can write your own named query and use a BETWEEN clause and see if that works with all the cases.

Upvotes: 1

Related Questions