Reserved Words as Grails Domain Objects

Post by Matt Nohr

I recently ran into a SQL exception creating a simple domain class called “Group” that looked like this:

class Group {
    static constratints = {}
}

When I ran my application I got this not-exactly-helpful error:

| Compiling 1 source files....
| Running Grails application
| Error 2012-08-14 09:30:01,325 [pool-5-thread-1] ERROR hbm2ddl.SchemaExport - Unsuccessful: create table group (id bigint generated by default as identity, version bigint not null, primary key (id))
| Error 2012-08-14 09:30:01,325 [pool-5-thread-1] ERROR hbm2ddl.SchemaExport - Syntax error in SQL statement "CREATE TABLE GROUP[*] (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, VERSION BIGINT NOT NULL, PRIMARY KEY (ID)) "; expected "identifier"; SQL statement:
create table group (id bigint generated by default as identity, version bigint not null, primary key (id)) [42001-164]
| Server running. Browse to http://localhost:8080/test


I should say that once I figured out the problem, then the error message did make sense. The problem turned out to be that I was trying to use a reserved word for the H2 database, in this case “Group”. And you cannot create a table with the same name as a keyword.

Luckily, this is pretty easy to fix and still use the same domain class. Just add a “static mapping” block so your class looks like this:

class Group {
    static constratints = {}
    static mapping = { table 'my_group' }
}

This will vary based on your database implementation. For H2, here are the list of keywords you cannot use (from H2 documentation):

CROSS, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DISTINCT, EXCEPT, EXISTS, FALSE, FOR, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, IS, JOIN, LIKE, LIMIT, MINUS, NATURAL, NOT, NULL, ON, ORDER, PRIMARY, ROWNUM, SELECT, SYSDATE, SYSTIME, SYSTIMESTAMP, TODAY, TRUE, UNION, UNIQUE, WHERE

This entry was posted in Software Development and tagged , , , . Bookmark the permalink.

Related Posts:

3 Responses to Reserved Words as Grails Domain Objects

  1. Same thing with ‘User’ and PostgreSQL. This one bit me when doing a quick test of the Spring Security plugin with the suggested defaults.

  2. Dan Vega says:

    You can usually escape reserved words using a backtick, this seemed to work here.

    static mapping = {
    table ‘`Group`’
    }

    The generated SQL will look something like this.

    insert into “Group” (id, version, name) values (null, ?, ?)

  3. Dan Vega says:

    I included the SQL form my list method but I am sure you knew what I meant :D Here is what the create statement looks like.

    create table “Group” (id bigint generated by default as identity, version bigint not null, name varchar(255) not null, primary key (id));

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>