Yesterday afternoon, I was working on the code for my CFUnited presentation and I ran into some issues with Derby when trying to use ColdFusion 9 ORM. (Yes, I said CFunited. Yes, my presentation will be that awesome. Yes, it means I need to start coding for it now.) I have come to use Derby a lot, especially for presentations. It makes it easier to package up all my files, and I know that anyone running ColdFusion 8 or ColdFusion 9 can run the databases without issue.
I already had a Derby data base created with tables and columns, which I had created using Aqua Data Studio (a fantastic database tool – and free if you develop open source software). When I tried to hook ORM up to the tables, I kept getting errors that the tables did not exist, however, I noticed that in the error messages the table names were all shown as upper case.
I figured this was a case sensitivity issue, so I renamed the tables, tested again and *BAM*……more errors. This time, the errors referred to missing columns in the query. Realizing the case sensitivity issue most likely extended to the column names as well, I renamed all the columns in all the tables, tested again and *BAM*…it worked! (please keep in mind that these revelations did not come to me quickly, but I figured I would spare you the details)
This kind of threw me for a bit because I knew that I had used ORM with Derby databases before and it worked fine, without any issues. I then realized that, in those other instances, I had let ORM create the database tables and columns for me, rather than starting with the database first. I quickly took a look at those databases using the RDS Data View in ColdFusion Builder and saw that the table names and column names were all uppercase.
The issue, for me, was that when creating the tables and columns in Aqua Data Studio, it allowed me to use names with lower- or mixed-case. I am not sure if it a JDBC driver issue where the table names and columns are passed as uppercase, or if it is an issue with Derby, but this one sure got me…and it got me good. So, be careful if you are using Derby with ORM and are creating the tables yourself using a database management tool like Aqua Data Studio. Or, if possible, model your objects first, then let ORM handle creating/updating the database for you.
If you see any Alagadians at cf.Objective() 2010, don’t forget to ask what this TaskForce stuff is all about!
DougHughes.net 
Comments on: "Derby and ORM and Case Sensitivity, Oh, My!" (1)
Yay Scott!
We can’t wait to see your presentation at CFUnited!!!
Good luck with Derby and the rest of your preparations!!
LikeLike