ColdFusion ORM Supported Databases with a Custom Dialect

The following databases should be supported as long as you provide the custom dialect using the fully qualified class name.

RDBMS ORM Dialect
Oracle (any version) org.hibernate.dialect.OracleDialect
Oracle 11g org.hibernate.dialect.Oracle10gDialect
Microsoft SQL Server 2000 org.hibernate.dialect.SQLServerDialect
Microsoft SQL Server 2005 org.hibernate.dialect.SQLServer2005Dialect
Microsoft SQL Server 2008 org.hibernate.dialect.SQLServer2008Dialect
SAP DB org.hibernate.dialect.SAPDBDialect
Informix org.hibernate.dialect.InformixDialect
Hypersonic SQL org.hibernate.dialect.HSQLDialect
H2 Database org.hibernate.dialect.H2Dialect
Ingres org.hibernate.dialect.IngresDialect
Progress org.hibernate.dialect.ProgressDialect
Mckoi SQL org.hibernate.dialect.MckoiDialect
Interbase org.hibernate.dialect.InterbaseDialect
Pointbase org.hibernate.dialect.PointbaseDialect
FrontBase org.hibernate.dialect.FrontbaseDialect
Firebird org.hibernate.dialect.FirebirdDialect

However, ColdFusion ORM is not without its challenges

While having CF-ORM support all database platforms is terrific; using CF-ORM may lock you into using a particular ColdFusion version. CF-ORM functionality changes depending upon the version of ColdFusion. CF-ORM on ColdFusion 9 is quite a bit different than CF-ORM on ColdFusion 2016. So, while you may be supporting a wide variety of database platforms, you may be stuck on a particular ColdFusion version. There are also differences between CF-ORM between ColdFusion, and its open-source sister, Lucee.

Also, CF-ORM has a slew of other challenges. Error reporting is confusing. Since CF-ORM is built upon Hibernate, it does not have the error messages that we typically expect out of a ColdFusion product. Error messages are not always available and are often confusing. The documentation is sparse, and at times misleading. Take for example the cfproperty documentation on the Adobe site. Most of the properties are not documented at all. Two-thirds of the description columns are blank.

Additionally, if you dig around and find documentation for a certain feature, it may not work at all! For example, I wanted to eliminate the database constraint for some of the database columns using the constrained="false" argument. Adobe's own cfproperty documentation mentioned the constrained property, but gave no description. Finally, I was able to find some documentation on the constrained property on another site, however, no matter what I did, I could not get the argument to work. I also went to the ORM channel on cfslack to ask for help, and no one knew how to get it to work.

Finally, another issue is that just like ColdFusion UI, CF-ORM is tied to Adobe ColdFusion and you're limited in what changes you can make. You'll have to wait until a new ColdFusion version to come out before you can upgrade to a new version of Hibernate.

ColdFusion ORM alternatives

While I have not used them yet myself, I have heard good things about Quick and cbOrm. Quick is written in ColdFusion and it handles errors much better than CF-ORM. cbOrm is an extension of CF-ORM, and Ortus has added a lot of functionality and flexibility to the underlying Hibernate engine that CF-ORM uses. I won't cover either Quick or cbOrm here, but they are both worth recommending if you want to avoid CF-ORM, especially if you're already using the ColdBox framework.

Since my project requires that I support as many database platforms possible, and I don't want to include another library, such as ColdBox, I am using CF-ORM and will show you some of the solutions that I used to overcome common CF-ORM pitfalls.

In the next article, we'll jump in and look at the code....