I was writing a very simple Grails application and ran into a problem when I accidentally used a SQL keyword as a property name. This post documents what happened, and how I (pretty easily) fixed it.
To illustrate the issue, consider a trivial Grails application called “messaging
” with a single class called Message
.
class Message {
String from
String to
String text
}
This is supposed to represent a simple email message, with fields to represent the sender, the receiver, and the text of the message itself. It seemed quite logical at the time to use the words from
, to
, and text
for the fields, but that leads to problems.
I added a MessageController
with dynamic scaffolding (i.e., def scaffold = Message
) and started the server. When I accessed the MessageController
link, however, I got
org.hibernate.exception.SQLGrammarException: could not execute query
In order to see the actual problem, I modified my DataSource.groovy
file to add “loggingSql = true
” in the dataSource
section. After restarting the server, in the console window I saw that the exception was caused by a SQL command generated by the scaffolded list
method in MessageController
:
Hibernate:
select
this_.id as id0_0_,
this_.version as version0_0_,
this_.from as from0_0_,
this_.text as text0_0_,
this_.to as to0_0_
from
message this_ limit ?
This statement looks fine, and in fact there’s nothing wrong with it. I couldn’t see the problem until I switched to MySQL so that I could browse the database independently.
(Switching to another database is covered many places in the Grails literature. In short, it means adding the JDBC driver to the messaging/lib
directory, creating the messaging database in MySQL, and changing the driverClassName
, url
, username
, and password
settings in DataSource.groovy
.)
When I did that and checked the database with the MySQL client, I found the problem (or at least a symptom of it):
mysql> show tables;
Empty set (0.00 sec)
In other words, the problem was that the message
table didn’t exist. Somehow the SQL used to generate the table in the first place didn’t work.
Logging the SQL as I did wasn’t sufficient to show me the CREATE TABLE statement. If, however, I go into Config.groovy
and change the value of log4j.logger.org.hibernate
to debug
, I see in the resulting console:
[1125] hbm2ddl.SchemaExport
create table message (
id bigint not null auto_increment,
version bigint not null,
from varchar(255) not null,
text varchar(255) not null,
to varchar(255) not null,
primary key (id)
)
followed immediately by
[1125] hbm2ddl.SchemaExport Unsuccessful: create table message ...
[1125] hbm2ddl.SchemaExport You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from varchar(255) not null, text varchar(255) not null, to varchar(255) not null' at line 1
The problem is that the word “from
” is a SQL keyword. MySQL got upset when I tried to use it in the create table
statement, as shown above.
How can I fix this? I could change the name of the from
property, to, say, sender
. Since Grails 1.0, however, GORM now has the ability to do custom ORM mappings, which feels like a cleaner way to solve the problem. Therefore, I added the following closure to my class:
static mapping = {
from column: 'sender'
}
Now the generated create statement is:
[1157] hbm2ddl.SchemaExport
create table message (
id bigint not null auto_increment,
version bigint not null,
sender varchar(255) not null,
text varchar(255) not null,
to varchar(255) not null,
primary key (id)
)
and the new error is
[1157] hbm2ddl.SchemaExport You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to varchar(255) not null, primary key (id))' at line 1
So apparently the word “to
” is also a problem. I therefore modified the mapping
closure to include it as well:
static mapping = {
from column: 'sender'
to column: 'receiver'
}
Now it all works as it should. The lesson appears to be either that I should keep a list of SQL keywords handy, or simply that the custom ORM DSL is a Good Thing(TM), and so are the logging settings. 🙂
Leave a Reply