How to get Grails to use a reserved word with Microsoft SQL

If you’re building a Grails application using a Microsoft SQL database, and you want to have a property on an object that’s a reserved word in MSSQL, like RULE, you’ll probably end up with a SQL syntax error because Grails, Gorm and Hibernate don’t know what words are reserved in MSSQL.

You can just use another name. Does the property need to be called MyObject.rule? You might want to call it something more specific anyway. Problem avoided!

But if you want to keep the name, or if you can’t change it in the DB (say, because you’re connecting to a legacy database), you can get it to work!

Use a custom mapping. Grails lets you map a domain object’s properties to a column that doesn’t fit the usual camelCase → snake_case pattern, and you can use the same mechanism to add the delimiters that MSSQL needs to use the reserved word as a column name. Problem solved!

static mapping = {
    rule column: '[rule]'

Now Gorm/Hibernate will use the right delimiters on the back-end, and you can keep accessing myObject.rule, MyObject.findByRule() and so on.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.