Hibernate version: 3.1beta6 + Annotations (EJB3)

Name and version of the database you are using: MySQL 5.0.15

The generated SQL (show_sql=true):
insert into Checkentry (check, ctime, mtime, sortindex, text, viewtime, id) ...

A sample error for this statement:
ERROR 1064 (42000): 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 'check, ctime, mtime, sortindex, texts, viewtime, id)

I have faced some problems with reserved words for column names. I have seen several postings which suggest to avoid these names.

But I have seen as well that the queries are totally unquoted in the Hibernate batcher. As I understand it is quite impossible to face all reserved name problems for all DBMs, but would it possible as a future feature to quote the column/table names? Or is even this quoting so different for every DBMS?

Regards
Thomas

you have always been able to quote table and column names. read the refeerence manual about quoting via backticks.

I am relatively new to Hibernate with annotations and I have spent way too many hours trying to identify what was wrong with my hibernate annotations, before discovering that renaming my columns away from reserved words was all that was required.

Why it was necessary to work so hard to find the cause of this error is not clear to me. But what is clear to me is that it is inevitable that every transactional purchasing system created with hibernate will at some point have a column or table named "order" which could cause this error if the developer did not realize that now SQL terms are reserved within the Java namespace.

I would argue that to require Java developers to protect their code by changing their column names so that they don't overlap with the SQL reserved word namespace is absurd. This is why namespaces were created in the first place. It seems to me that this is a clear design flaw in Hibernate and Hibernate with Annotations.

Furthermore, given the number of possible reserved words with multiple relevant meanings (like 'order') and the poor error messages that result from accidentally using a reserved word in a column or table, I think that fixing this would save developers a tremendous amount of unnecessary debugging time.


I agree with the previous writer that the best option would be to:
1) automatically quote column names in all cases

If that is not an option, than I would strongly recommend that:
1) This issue be made prominent in the documentation with a list of possible reserved words
2) That the relevant error messages be either improved or cataloged.

Laura