Having dabbled in ColdFusion ORM last year and not quite convinced myself to adopt it as standard practice, I've decided to give it a second chance by converting another existing app, namely the one used to generate this blog.

Since my initial experimentation I've had the benefit of reading John Whish's superb Guide to ColdFusion ORM, and am feeling more confident that the complexities will be less daunting this time around.

A short way into the project, however, I've already had to deal with a roadblock.

This app differs from the previous in that it uses a separate database for user — i.e. "blog author" — data. This allows me to have a single login for several internal applications.

No problem, I thought, the CF9.0.1 update added support for multiple data sources, so all I need do is use the datasource attribute when mapping the component.

<cfcomponent name="Author" persistent="true" datasource="central" table="users">

Each Post has an Author so I went ahead and defined that relationship in Post.cfc:

<cfcomponent name="Post" persistent="true" table="posts">
<cfproperty name="author" fieldtype="many-to-one" cfc="Author" fkcolumn="authorID">
...
</cfcomponent>

So far so good. But when I ran the app, I got the following error:

An association from the table users refers to an unmapped class.

I'd definitely mapped Author.cfc to users, what gives? After some inconclusive Googling, I found the answer staring out at me from the Adobe ORM docs:

Since a Hibernate configuration uses a single data source, all related CFCs (using ORM relationships) must have the same data source.

So it seems you can use multiple data sources in CF ORM, but only if the entities are unrelated to each other.

Before the 9.0.1 updater, though, I recall Terry Ryan mentioning a workaround for multiple datasources and re-reading this made wonder if using the schema attribute instead of the newer datasource might help.

<cfcomponent name="Author" persistent="true" schema="central" table="users">

No joy:

Table users defined for cfc model.Author does not exist in schema central.

But back on Terry's blog post, a commenter helpfully pointed out that another attribute, catalog, might be required to reference the table correctly depending on the database product in use. I'm using MySQL 5.0 and have frankly always been a little confused by the terms "catalog" and "schema" in the MySQL context ("schema" to me suggests the data model or design - perhaps someone can set me straight), but it had to be worth a shot:

<cfcomponent name="Author" persistent="true" catalog="central" table="users">

It worked! My Posts and Authors are now related despite being in different databases... or catalogs? Schemas? Whatever.