I had a comment on my blog yesterday about ORM’s restrictions on multiple datasources. Yes, ORM in ColdFusion 9 is restricted to one datasource per application. However this does not mean you are restricted to one database, schema or tablespace.
A datasource is just an abstraction for database connection details. It takes a server, a port, user credentials, a default database and other information. On the database side, just because a database is defaulted doesn’t mean that you are limited to just that database. In SQL you can append schema namespaces to the front of a table:
SELECT *
FROM NotMyDefaultApplication..users
This can also be accomplished in ORM components by specifying the schema attribute on the component. This will allow you to access a table outside the default database.
So if you have the need to access multiple databases/tablespaces/schema on one database server you absolutely can with the proper credentials and the schema attribute.
tpryan.blog 
I’d post a quick sample (yes, I know it’s just an argument, but…)
LikeLike
Hmmm, interesting. I’ll have to chew on this and see if it will meet my needs. But wouldn’t you agree that this is more of a work-around, and it would be more preferable to have ORM support for multiple datasources? Just getting your opinion.
Thanks.
LikeLike
SELECT *
FROM NotMyDefaultApplication..users
Two dots?? Or Typo?
LikeLike
I think Joshua is correct in that this is just a workaround, and probably an unintentional one at that.
So one can do this?
component persistent=”true” table=”SomeOtherDB.artists”
{
}
It helps somewhat, but isn’t a great solution as it also eliminates the abstraction granted by using the datasource as an alias for the database. Often one uses a datasource that may point to a development database (ac=accounting_dev), and then when ready change the CF datasource to point it to the “working” database (ac=accounting). Using the workaround one would need to go in and do a global search and replace on every component prior to putting them into production. Not good.
Actually, this also fails for a couple of other reasons. First, as you mention, a datasource specs a server, a port, user credentials, and so on.
So what if the other database required different credentials for access? A relatively common occurrence, is it not?
Second, and worse, what if the required database is on an entirely different server? As mentioned earlier, a client I work for has a member database, a content database, and a database used for logging sessions and page requests. All are hit for each page request, and for performance reasons the logging database is in fact on a separate server.
In which case, saying Members.users and Logging.pages doesn’t work, as each is on a different server and as such requires it’s own connection.
Just how hard would it have been to allow this?
component persistent=”true” table=”artists” datasource=”workgroup”
{
}
You could easily have spawned a distinct handler for each datasource named and referenced during a given request.
As I said before, I was really looking forward to using the new ORM features in CF9. Unfortunately, it seems that it’s been hobbled to the point where one would need two distinct ORM solutions in one’s toolbox. The CF9 version for relatively simple environments and circumstances, and something more robust to cover everything else.
I’d say that I’d be looking forward to this being corrected in CF X, but given Adobe’s past history in such matters, it’s entirely likely that they’d regard “complex” environments as being Enterprise-only…
LikeLike
Subscribing.
LikeLike
Mark Mandel was saying that this is mostly due to the inner workings of Hibernate and made sense if you understood Hibernate, I don’t, so feel free to explain further 🙂
LikeLike
I was wondering if you actually got this working, or that this is just in theory.
As I have tried it myself, but did not get it too work.
LikeLike
In case anyone was having issues getting Terry’s ‘work-around’ to work you need to have the following define for each Entity that is pointed at a different Database. And it will not work cross server as was mentioned in a previous comment.
catalog=”My_Database”
schema=”dbo”
table=”My_Table”
selects from My_Database.dbo.My_Table
LikeLike
@brian two dots is correct.
You can query from one database to another database on the same server (or linked server)
eg.
select * from database1.schema.table
inner join database2.schema.table on (…)
If the schema is the default, eg. dbo, then it can be left out.
eg.
select * from database..table
I’m referring to SQL Server here, but most databases support the same syntax anyway
LikeLike
I can’t understand how Adobe can say,”ColdFusion (MX 6.x, 7, and 8) applications should seamlessly migrate to ColdFusion 9.”
Not so!
I have several applications in CF 8, that have several datasources. ON different servers.
I bought CF 9 but it is of no use to me. Obviously!
LikeLike
Whoah nelly. CF9 will support your multiple datasources on multiple servers just fine. And your non-ORM CF8 app shouldn’t have any issues.
LikeLike
Well this is not what we discovered. We had to go back to 8.
Queries that would work when in a template by themselves were OK. But when in a template with another using a different datasource, they were not. So whoa Neddy!
LikeLike
Thanks for the response, Josh. Kate contacted me as well and I’m working with her. She is not working with ORM in her code and I’ve informed her that as such, Terry’s post here does not apply to her.
LikeLike
Thanks Josh (both Josh’es!) I am happy going back to CF 8 and think I’ll not try further to solve the problem right now. I was wondering though – the only other difference I can think of is the port number I used. I used the same number across the client and the server but a different one when I moved to CF 9. Surely that couldn’t matter?
LikeLike
This doesn’t seem to flexible if the schema is dependent on the user logging in. Say we abstract NPPI data into a table for each clients under a schema. Say clients.dbo.table1(has non-nppi client data co-mingled), clients.terry.table1, and clients.mike.table1. I would have to create a table1 model cfc for each client it seems.
LikeLike
AJ Michels, does the solution work for related entities? i.e., can related entities span across multiple datasources?
LikeLike