Example of multiple datasources for ORM in 9.0.1

July 14, 2010

ColdFusion 9.0.1 brings multiple datasources support for ORM. Here is how to set it up.

In this example, we are going to use both the cfartgallery and cfbookclub datasources. In the Application.cfc we will make the cfartgallery the default datasource. Optionally we are going to set the dbcreate rules differently for the two datasources. (We could also do this for the schema, catalog, dialect and sqlscript settings.)

component

{

this.name="multi";

this.ormEnabled=true;

this.datasource="cfartgallery";

this.ormsettings = { dbcreate={ cfartgallery="update",

cfbookclub="none"} };

}

From cfartgallery we will use the Art table as an object. As cfartgallery is the default datasource, this is set up as usual:

component persistent="true"

{

property name="artID" fieldtype="id" ;

property name="artistID";

property name="artname";

property name="description";

property name="prize";

property name="largeimage";

property name="mediaID";

property name="issold";

property name="samColumn";

}

For any tables from the cfbookclub datasource there is an additional attribute "datasource" for the component:

component persistent="true" datasource="cfbookclub"

{

property name="bookID" fieldtype="id";

property name="authorID";

property name="title";

property name="bookdescription";

property name="isspotlight";

}

We can then get the data and dump it like so:

books = entityLoad("BOOKS", {}, {maxResults=2});

writeDump(books);

art = entityLoad("ART", {}, {maxResults=2});

writeDump(art);

Pretty straighforward and pretty cool stuff.

4 Comments

Great post! This is exactly what I needed. I am having one related problem. Datasource A is MS SQL Server and datasource B is Sybase SQLAnywhere. I'm getting the error from dsn B... Unable to determine Dialect to use [name=SQL Anywhere, majorVersion=11]; user must register resolver or explicitly set 'hibernate.dialect' It sounds easy, just define the dialect... But where? And why would I need to when Sybase SQLAnywhere should auto detect.

By: Jamie 11/15/2011 12:32 PM

Ah! Nevermind, I got it, have to set it at the application level in ormsettings, exactly as you post states. Thanks again!

By: Jamie 11/15/2011 12:36 PM

This was a great find! I am getting an error message back whenever i try to use the additional datasource (in your case cfbookclub). I am trying to use "ormExecuteQuery". I have checked the spelling and when I swap out the datasources it seems only the primary one works correctly with "ormExecuteQuery" both can use "entityLoad" with no issues. Any ideas? Error Messages: Error while executing the Hibernate query. org.hibernate.hql.ast.QuerySyntaxException: tblSQM_Final is not mapped [from tblSQM_Final where actiontakenstatus = 'open' and lifecycle in (SOM,na)]

By: Scot hershman 02/01/2013 10:47 AM

When I use this method I can only use entityLoad on the second datasource. I can't use HQL, I get a "not mapped" error message.

By: Scot hershman 02/04/2013 10:07 AM

Some comments have been lost over the years due to moving hosts.

More blog entries that I have written.

Code coloring by PRISM.

Sam Farmer

Example of multiple datasources for ORM in 9.0.1

4 Comments

More