I am working on a small personal project, using it, in part, as a learning experience and giving Test Driven Development a try. I decided at the beginning of the project to use ORM for my data persistence. Over the weekend I ran into an interesting problem that, when I finally found the reason, was one I should have picked up a lot sooner.
The project is a basic personal budgeting application. I decided to create my objects and their relationships and let ORM create the database tables, columns and foreign keys. This took a bit of time, but all the database items got created without issue.
One object, the Budget object, has two properties named Year and Month - to specify what month and year the budget represents. I also have an Account object that can have many Budgets. One thing I wanted to do was sort the Budget relationship by Year and Month so that when I called Account.getBudgets() the most recent budgets would be on top.
This should have been easy considering the orderby attribute of the ORM property. However, when I added the following:
orderby = "Year desc, Month desc"
I was getting an error from Hibernate stating that the order-by property could not be parsed.
Well, Google was not much help. I was able to find pages about this error message, but not anything related to ColdFusion. After clicking around a few links, it finally hit me what the issue was. Year and Month must be reserved words in MySQL.
It took one more Google search to figure out how to escape reserved words in MySQL. For those who do not know, it the ` character (its the key to the left of the 1 key). By changing the orderby to:
orderby = "`Year` desc, `Month` desc"
I was able to get past the error and have the Budgets sorted correctly. This should have been something I thought of sooner, but I think I was thrown off the scent by the fact that the columns were created without issue. That will not happen to me again.
I should note that this fix might not work in other database engines as they use different characters to 'escape reserved words. However, since, for now, this will not run on anything but MySQL, I can continue along writing my unit/integration tests