It’s been stated that a weakness of CF9 ORM is that it only allows one ORM datasource. You can use other datasources via cfquery. It’s worth understanding why this is so. It certainly wasn’t left out arbitrarily.
In a nutshell, default CF9 ORM only allows one datasource because it wraps each request in a transaction. Generally speaking, this is a good idea, but it’s a characteristic of standard database transactions that each is tied to a single database connection. This is generally true, not just specific to ColdFusion datasources. It’s also why cftransaction only allows queries from one datasource within the transaction body.
I gave myself a couple of escape hatches in the preceding paragraph – I said “default” CF9 ORM and “standard” database transactions. Does that mean non-default CF9 ORM with non-standard transactions does allow multiple datasources? Why, yes it does! But be aware that this is a non-standard usage model. Not only do you need to delve into the underlying Hibernate configuration to make it work, many scenarios are not even standard practice within the Hibernate world.
I’m not going to write a how-to on CF9 ORM with multiple datasources, but I will provide some pointers to how this might be done.
- Delve into the underlying Hibernate and turn off the transactions. Non-transactional code is fairly common in plain CFML apps. This sort of thing is a large part of why “scripting” language developers are regarded as cowboys by “serious” (read Java) developers, but it can be a valid solution in some circumstances.
- Use XA datasources with a JTA transaction manager for true distributed transactions. There’s a succinct example for this in Java here. This is common practice in the J2EE world, but pretty outre for CF. For a start, you’ll have to find XA datasources for your database and replace ColdFusion’s built-in datasources. You may also need to configure your database server to participate in distributed transactions.
- Roll your own transaction manager in Java and plug it into Hibernate. There are any number of people out there in the Java world with strange use cases attempting just this. Google “hibernate multiple datasources” for some interesting and somewhat desperate reading. Or not 🙂
- Use CF9 ORM in its default transactional mode for your main datasource with non-transactional access (e.g. cfquery) to secondary datasources. You don’t have to completely give up on data integrity, though. Schedule a task to check data integrity and issue compensating transactions where necessary.
There are some other possibilities, but that should be enough to get my basic points across. Firstly, this isn’t really a CF9 or even a Hibernate issue. And secondly, you really only have a choice between either relaxing your transactional guarantees (options 1 and 4), or doing a lot more work (options 2 and 3).