Multiple Datasources CMP

[nono_garcia]

Hi:

We have a problem to select diferents datasources working with CMP, it's posible???. Next messages was posted Oct 31, 2001 and the problem is the same:


We are using multiple data sources in our application. We work with a couple of different databases in one location. When using CMP, how do you specify what datasource to use? We want to pull queries using CMP from one datasource, and then stick them in another database using a different datasource. In some cases the same one entity bean is used in two (or more) different tables, so that the entity may need to specify what datasource it needs to be used with. How is this done?

Thank you.

[benwalstrum]

Each entity EJB can be associated with a specific datasource, or also the entire EJB deployment can be set up to work with a particular datasource. To do this, put the following lines into your jbosscmp-jdbc.xml:

...
<jbosscmp-jdbc>

java:/OracleDS
<datasource-mapping>Oracle</datasource-mapping>

<enterprise-beans>
...

Obviously you will need to substitue the values of datasource and datasource-mapping (found in standardjbosscmp-jdbc.xml) with the appropriate values for your datasource. This will set up your entire deployment to use that datasource. You can also specify a datasource for each individual EJB in the jbosscmp-jdbc.xml as shown below:

...

<ejb-name>MyEJB</ejb-name>
java:/OracleDS
<datasource-mapping>Oracle</datasource-mapping>
<table-name>MY_TABLE</table-name>
...

Hope this helps.

Ben

[nono_garcia]

Thanks, but it not solve my problem.

I need that the same EJB-CMP will work with diferents Datasources, and my application on runtime execution will change the Datasource.

For example: We have EJB-CMP called 'Customer'. If user 'scott' login my application, JBoss assign Datasource ScottDS to the user, and all EJB-CMP connect this Datasource. Is user 'john' login, JBoss assign Datasource JohnDS.

I need that user 'scott' is loged, EJB-CMP 'Customer' use Datasource ScottDS, and simultaneous if user 'john0 is loged, another instance of EJB-CMP 'Customer' will use Datasource JohnDS

It's posible? Is context related with the problem?

Thanks

[benwalstrum]

From what I understand (and this is not a completely definitive answer) there would be no way to do what you are looking for as you have described it. The problem is that when entity EJBs are deployed they are associated at that time with a particular datasource, and will always be with that datasource. I have never heard of a way to change the datasource at runtime.

The only way that I know of to get around your problem is to deploy your EJBs twice with separate JNDI names. That is, you can deploy 'Customer' with JNDI name 'ejb/scott/Customer' and also with 'ejb/john/Customer'. When the user logs in you will know what code to use, so when you look up the entities you can do something like "ejb/" + username + "/Customer" to look up the correct EJB.

I know that this solution is not necessarily the easiest, but from all I've ever read, dynamic binding of EJBs to datasources is simply not possible.

Ben

[mr.m]

Unfortunately, it appears that JBoss doesn't allow CMP beans to programatically set the bean datasource. I think this is a limitation of EJB and no other application server allows it either. The datasource is bound when CMP beans are first loaded. You can however, create multiple deployments of the same beans pointing at different data sources. In the deployment jar, the jbosscmp-jdbc.xml should be modified to point at an alternate data source configuration, and the jboss.xml JNDI names should be changed to avoid conflicts.

For example, the jboss.xml:


<ejb-name>MasterSymbol</ejb-name>
<local-jndi-name>tree/MasterSymbolLocalHome</local-jndi-name>


Becomes:


<ejb-name>MasterSymbol</ejb-name>
<local-jndi-name>tree/MasterSymbolLocalHome2</local-jndi-name>