Thursday, August 23, 2012

Sample Application for Switching Application Module Data Sources

I was preparing proof of concept for such scenario, where ADF application was working with multiple data sources. Requirement was to be able to select data source on runtime, before login. We had several DB users, different DB security applied to each of them - separate data source was defined. Different data source per business site, not really per business user - this would be too much. I would like to post and describe prepared sample application. Data Source runtime switch is implemented based on concept described in Jobinesh blog - Modifying the Application Module's JDBC DataSource at run time.

Download sample application - UserDataADFBCSample_v3.zip. This sample is based on application from my previous posts, it is enhanced with Data Source selection from login page.

Data Source runtime switch is implemented basically in two steps:

1. In the Model there is custom class that implements EnvInfoProvider. Specifically one method is overriden - getInfo(String, Object). This method reads data source name from session scope (its where we set it during login) and puts it into AM configuration:


2. Each Application Module must be set with proper configuration for jbo.envinfoprovider property. This property must point to our custom EnvInfoProvider class (see above):


Default Data Source name defined for Application Module is still important, ADF BC will use it in case if no custom Data Source name was provided:


I'm getting list of Data Sources from static VO:


This VO is exposed through Application Module Data Control. Keep in mind, even VO is static - AM still we be initialized on VO access. This means AM will establish connection to database, at that time when we will load static VO data. Custom Data Source name will not be set yet - default Data Source name will be used. Instance of static VO with list of Data Sources:


UI list with Data Sources can be created by drag and drop of DataSourcesView1 instance from Data Control into JSF page. Select Single Selection -> ADF Select One Choice option:


Choose display attribute - press OK and choice list with Data Source names will be generated for you:


Once choice list was created, go to Login page definition and create Name attribute binding (based on same iterator as Data Source choice list):


This will allow us to access selected Data Source name from Login bean - doLogin() method:


Here how it looks on runtime - user selects Data Source from the list:


User logs in into application - Application Module configuration is initialized with Hr DB data source and data is rendered:


From the log we can see that after login, session cookie was changed. This happens because there are two Web sessions created always - before and after login (its how it works by default in ADF). This allows us to set custom Data Source name after login action is done. Remember - before login, Application Module is initialized with default Data Source:


Hr Test DB - is non existing Data Source, I'm using it only for test purpose -  application should fail, to prove Application Module is using Data Source selected during login:


Indeed application breaks:


From the log we can see that Application Module was trying to connect with jdbc/HrTest Data Source - correct as expected:


52 comments:

Unknown said...

Hi , God Bless You for this nice article .
In My Case I need to :
1 - Get DEPT data from one MSSQL datasource and create DEPT Entity Object .
2 - Get EMP data from another MSSQL datasource and create EMP Entity Object.
3 – Create a new View Object base on these two Entity Objects and create a connection between them.
Dear Andrejus Baranovskis
Your solution is good for switching between two datasources at runtime but I need to work simultaneously with two datasource at runtime .
In php or .net we could create two different classes for connecting two different datasources at runtime and working with them concarrently. We could create relations in record sets or datatables.How we can overcome this challenge in Oracle ADF ?

Best Regards
Amir Husain Meysami
From : Fedosi Squre , Tehran , IRAN , ASIA

Andrej Baranovskij said...

Hi,

May be this post will help you - http://andrejusb.blogspot.com/2012/07/lov-implementation-with-adf-bc-service.html

Andrejus

Siva Sankar said...

Hi Andrejus,

I have some requirement,but I have four sachems like DEV,INT,TEST and PROD like that,
when the login time i will select schema name its not a datasource.how can we handle this situation based on the selected username and env(schema) how to dynamically connect the particular schema.how to bind from the single UI to access based on the user and env(schema selected).

Ganesh Parthasarathi said...

Hi, to run the sample you offered, which jsf implemention is required.

When i opened the project in jdev, got compilation errors for faceCTx.

Then added jsf-api-2.0.4.jar.

But deployment fails with "Caused By: java.lang.IllegalStateException: Application was not properly initialized at startup, could not find Factory: javax.faces.context.FacesContextFactory".

I'm using jdev, 11.1.1.6 along with integrated server.


I tried to create my own project like yours, but the url ""/adfAuthentication?success_url=/faces/main"", gives 404 error. Not sure what is causing the issue. I just hard coaded user to default weblogic/weblogic1. Authentication does not throw any errors.

Andrej Baranovskij said...

Hi,

You can run this sample app with ADF 11g R2. If you want to run it with ADF 11g R1 - you should copy paste web.xml and weblogic-application.xml content from another ADF 11g R1 application. Also change all Facelet files to be JSPX.

Andrejus

Ganesh Parthasarathi said...

Hi, i tried with Jdev R2. And getting following exception with integrated server.

java.lang.IllegalStateException: was not present on this page; tag oracle.adfinternal.view.faces.unified.taglib.UnifiedDocumentTag@76ee18encountered without an being processed.
at org.apache.myfaces.trinidad.webapp.UIXComponentELTag.setProperties(UIXComponentELTag.java:82)
at javax.faces.webapp.UIComponentClassicTagBase.findComponent(UIComponentClassicTagBase.java:690)
at javax.faces.webapp.UIComponentClassicTagBase.doStartTag(UIComponentClassicTagBase.java:1311)
at org.apache.myfaces.trinidad.webapp.UIXComponentELTag.doStartTag(UIXComponentELTag.java:67)
at oracle.adfinternal.view.faces.unified.taglib.UnifiedDocumentTag.doStartTag(UnifiedDocumentTag.java:50)
at jsp_servlet.__login_jspx._jspService(__login_jspx.java:99)

Andrej Baranovskij said...

It works on my machine, something wrong on your environment.

Andrejus

Ganesh Parthasarathi said...

i just created a new jspx page and tried it was working. Then just changed the url to point to login.jspx, it started to work.. i did not change anything..

Anyway the functionality that i wanted got it working. Thanks a lot..

Andrej Baranovskij said...

Very cool - I call this ADF magic :)

Andrejus

Don Kleppinger said...

I don't understand how this works with AM pooling. An AM is configured for a datasource. If you change the datasource on an AM and it gets checked back into the pool and then gets passivated when another user gets it who had selected a different datasource at login will the AM reconfigure each time its' checked out of the pool.

Using more than one datasource is as simple as configuring one app module for one datasource and another app module for a different datasource which we do in our application. But I don't understand how switching the datasource on the fly like you described will work with AM pooling.

Andrej Baranovskij said...

Having different AM's per data source is quite primitive, often new data source can be created - we are not going to create new application module and redeploy app each time.

Your concern is false. This approach is stress tested with AM activation/passivation. When AM is checked back from the pool - then Data Source is reapplied again.

Andrejus

Don Kleppinger said...

When you have a page that displays data from two different databases you have to use a separate app module. You can't query two different databases from the same app module. Unless you created use a database view in the database itself that used a external link to the external database.

Andrej Baranovskij said...

You describe different use case - accessing different DB's from the same session. Use case presented in this blog is different - different database access for all AM across entire session.

Andrejus

Anonymous said...

Hi !!!
I tried to create WebService on ADF BC
with 10 diffrent(IP) Oracle DB with same functionality for each of them. I use APP module for WS. How I can switch him for correct IP

Thanks for Your help

Anonymous said...

Hi,
Nice Post.
To enhace this more further. How can we get list of JDBC Data sources available in weblogic in the ADF Application.
Thanks
Syed

Andrej Baranovskij said...

By using WebLogic API this should be possible or by accessing MBean...

Andrejus

Vikas Kukreti said...

hi Andre can we get the same database connection wizard on our page ,i mean dynamic database for our fusion application at runtime we change the connection and it will reflect the changes at the back end or I would like to know what happens when we changes database connection in application sources which file get affected ,how it changes and the flow of the database connection .

Andrej Baranovskij said...

Hi,

Are you using Oracle Fusion Applications? Or you have custom made ADF application?

Andrejus

Vikas Kukreti said...

hi Andrejus, I have created two ADF fusion web applications one is using oracle Database connection with EO VO ,Am and other one is using Sql Server connection.
Both databases containing the same table name and structure in them,
And now I have created a third ADF fusion web application which is a main application in which i haven't created any adf business component,in that main application i have created one jspx page and i have added jars of previous two applications in main application through dynamic region i have dragged their task flows in my main application page as a link.
according to our databases i also created the DataSources in weblogic which we are using in our application.
when i make my main application run then the oracle connection TF link is working but when i clicked on Task Flow link which contains SQL server connection the table i have used in the page fragment of that task Flow .("Doesn't throwing any error or exception ")but is not displaying records and message coming "Access Denied".
Suggest a Solution .........

Vikas Kukreti said...

Is that possible to access different databases in single Fusion web Application ,in the above mentioned.
If yes, then how?
If Not in the way i am trying then suggest me the way by which I can access dual databases from same application

CountZero said...

There’s one thing worth noticing. If you create your entities with New Entity Object wizard instead of Business Components from Tables - schema object prop of Entity gets prefixed with database schema name. You get HR.COUNTRIES instead of only COUNTRIES and it propagates down when creating (or updating) view objects. And you can’t have two db schemas sharing name on one db server – switching DS in that case will give you query errors. Otherwise great article, very helpful. Keep up good work!

Andrej Baranovskij said...

Thanks for your update. Makes sense, developers should make sure EO's universal naming (without schema prefix).

Andrejus

Есөн-Эрдэнэ said...
This comment has been removed by the author.
Unknown said...

Hi Andrejus. This posting is great. But could the data source be switched whilst in the application i.e: after login has happened.

Regards
Logan Applesamy

Andrej Baranovskij said...

Hi Logan,

Yes, this should be possible - but I don't have ready sample app to share. I would need to implement it and test.

Regards,
Andrejus

Smita said...

HI

I have a question :

Suppose there are 2 jsff's in a taskFlow. Is is possible for the different jsff's to use different data sources ?

Thanks,
Smita

Andrej Baranovskij said...

Yes, definitely.

Andrejus

Devrim Bahar said...

How? :)
Thank you for this great article. I tried your code in jdeveloper 12.0.3 but I cannot manage second DC although I created one in weblogic server. Is there any specific configuration to do for latest version of jdeveloper?
Best,
Devrim

Andrej Baranovskij said...

I will need to test it with 12c.

Andrejus

Devrim Bahar said...

Thank you Andrejus, I would be pleased if you could.

Regards,
Devrim

Unknown said...

Hi Andrejus

We upgraded to 12c as well. Switching datasources worked well in 11g. But there seems to be issues in 12c. We had to change the Shared property on the pageflows to Isolated. This works for the individual screen. But the menu refuses to get refreshed based on the new datasource.

So, Yes, I am also waiting for the outcome of your 12c test.

Regards
Logan

Andrej Baranovskij said...

You should be careful switching to TF Isolated, it will consume more resources - each TF will use its own AM instance.

I think next week I will have time to test this, I will post outcome on the blog.

Andrejus

Devrim Bahar said...

Hello Andrejus,

Much obliged to hear that. I am looking forward the test results.
I hope it works fine in 12c also.

Regards
Devrim

Andrej Baranovskij said...

Hi,

I did a quick test and it doesn't work on 12c, could be multiple reasons and several fixes could be tested. I will need to spend more time on this, but right now pretty busy with my work. I will look at it later.

Regards,
Andrejus

Devrim Bahar said...

Hello,

Thank yoı for your help. I hope there could be a way to run it on 12c. I am excited and waiting for good news :)

Devrim Bahar said...

Hello Andrejus,

Did you have a chance to look at the case?

Best regards,
Devrim

Andrej Baranovskij said...

Not yet. Too busy with projects and customer work. Also preparing new blog articles.

Regards,
Andrejus

Devrim Bahar said...

Hello Andrejus, did you look at it? Any chance? :)

Best,
Devrim

Andrej Baranovskij said...

No, when I will look into it and have solution - I will post it. Too busy with projects and preparation for OOW session and ongoing topics for blogs.

Regards,
Andrejus

Devrim Bahar said...

Hello Andrejus, how are you? :)

Andrej Baranovskij said...

I'm very good. Busy with work in the projects :)

Devrim Bahar said...

Hello Andrejus,

I hope you are doing well and you were happy with OOW :)

Somehow I managed to do what I asked you before by using Steve Muench's famous example/approach.

But I have another issue which is not directly related to this case : I created a simple ADF application which uses a mysql database. I deployed it on internal WL app. server and it can connect the mysql database and shows a table data.

But when I modify and prepare the same app and deploy on glassfish, it behaves strange and display different data from another mysql database's table. Both databases have the same schemas and tables but different data.

For WL version I have used standard JNDI Name for a Datasource (e.g. java:comp/env/jdbc/YourConnectionDS) in app modules configuration and for GF version I create pool and connection (e.g. jdbc/YourConnectionDS) for the same database. But GF version still uses local database which run on GF system. Do you have any idea or time?

I would be happy if you can help me :(

Regards,
Devrim

Devrim Bahar said...

Hello Andrejus, did you have a chance to look at 12C version of your project? I will be glad if you have time.. :)
Best regards

Andrej Baranovskij said...

Hi,

No, I wasnt looking into it.

Regards,
Andrejus

Muddasar Amin said...

Dear Andrejus,

Thank you so much for such helpful post. The link provided to download sample application is not workking, could you please make it available or it will be a great favor, if you will send me the application on my email: muddasaramin70@gmail.com.

A bundle of thanks
Mudi

Muddasar Amin said...

Dear Andrejus,

I'm sorry, but I am not able to find the application from the link also. Please help!

Thanks
Mudi

Andrej Baranovskij said...

It is there, just go by date to page Nr 7 - https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/jdevsamples/UserDataADFBCSample_v3.zip

Andrejus

Devrim Bahar said...

Dear Andrejus,

Does this version work with 12c? Please say yes! :)

Regards,
Devrim

Andrej Baranovskij said...

Never tested with 12c. But if you ask, I guess it doesnt work? :)

Andrejus

Muddasar Amin said...

Hi, I have tried it in 12.1.3.0.0. and its working for me, but with the addition of 1 more step:

Go to AppModule Configuration and double click on AppModuleLocal
and Uncheck the "Enable Application Module Pooling"

For further assistance, please see the community thread.
https://community.oracle.com/thread/3984767

Andrej Baranovskij said...

You should never uncheck "Enable Application Module Pooling". This will force application to work without AM pool and will do passivations/activations on each request. This is not acceptable in any ways.

Andrejus

Sudhee Orsu said...

Andrejus ,

I have followed your blog and executed your project in 12.2.1.0.0 version . But somehow I could not able to switch DB instance . Could you please suggest .