Friday, May 11, 2012

Working on one of our Grails apps I got "could not execute query; SQL [select contrac0_.contract_hrs as col_0_0_ from contract contrac0_ ]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query". I got the exception while running an integration test against the Service class executing the above query.
Farther down the stack there was also "Caused by: java.sql.SQLException: Table not found in statement [select top ? contrac1_.contract_hrs as col_0_0_ from contract contract0_]"

We are using Oracle and the specific domain class had a field defined as Double, but it also had 'sqlType' for the field specified as 'NUMBER(7,2)' to match up with the DDL for the table and to get around the dbCreate set to validate in our DataSource.groovy.

class Contract {
      Double contract_hrs

    static mapping ={
        contract_hrs column: "contract_hours", sqlType: "NUMBER(7,2)"

To take care of the test, we had to take out the sqlType out.

Fixing the test this way though, made the application not run. At application startup we started getting an error message saying something along the lines of "expected double, but found integer" for the the contract_hours field(column).

The way to get around that was to change the dbCreate in DataSource.groovy from "validate" to "none".

Hope this helps someone!