Thursday, March 8, 2012

Extending Application Module for ADF BC Proxy User DB Connection

In this post I will describe how to retrieve database connection for ADF BC proxy user connection. I will provide best practice as well, how to implement Common AM and extend your local AM with common functionality. In the next post will test how ADF BC proxy user approach works when database connection pooling is enabled. We are using proxy user connection approach, when there is requirement to login into database with Web user. Such connection can be established through JDBC Data Source proxy user:


Its often happens to see database connection for proxy user is retrieved from dummy prepared statement, as described on this blog- we are bypassing ADF BC offered functionality in this way:


However, there is another way - we can use standard ADF BC methods and retrieve database connection from ADF BC transaction factory. How to implement it in generic way? Download sample application - AMExtendApp.zip, I will describe how this sample is implemented.

I have two Model projects - one contains common classes together with common AM, another consumes common Model and contains actual Model implementation:


Common Model extends ADF BC database transaction factory:


Database transaction implementation class provides public method to retrieve database connection:


Common Model AM Implementation class contains overriden prepareSession method, where we are getting DB connection from transaction factory and opening proxy connection for current Web user. This is main method, where proxy connection is established:


Common Model AM Implementation class is declared to be Base Class for CommonModel AM:


This is visible from AM Component Class:


Make sure to set transaction factory for Common Model AM - TransactionFactory property in AM configuration:


We switch now to our local AM, from Model project. Select this AM to extend from CommonModule - it will inherit custom base classes with generic prepareSession method:


Thats all, no need to change anything for local AM - it will invoke prepareSession from common AM automatically. Just double check you have set JDBC Data Source for AM connection:


JDBC Data Source connects as APPUSER:


Web user connects as HR (same as DB schema name):


HR schema data is retrieved successfully through proxy APPUSER:


Select statement is executed for Employees table from HR schema:


When passivation/activation happens, we can see that PS_TXN table is created in APPUSER schema (as expected in proxy user schema):


One more hint, if you get DB transaction cast error from prepareSession:


Make sure to set custom transaction factory class for local AM as well. When extending AM from common AM, only custom classes and VO instances are retrieved, but not AM properties:


8 comments:

Unknown said...

Hi, Andrejus, thanks you for your interesting blog.
Can you explain me, why this your app works well on weblogic, but cant work with glassfish?
I try this app https://blogs.oracle.com/imc/entry/how_to_use_database_proxy - and it works well on glassfish, but your app get me this error:

[#|2015-10-26T16:38:06.510-0300|WARNING|glassfish3.1.2|javax.enterprise.system.container.web.com.sun.enterprise.web|_ThreadID=79;_ThreadName=Thread-2;|StandardWrapperValve[Faces Servlet]: PWC1406: Servlet.service() for servlet Faces Servlet threw exception
JBO-29114 ADFContext is not setup to process messages for this exception. Use the exception stack trace and error code to investigate the root cause of this exception. Root cause error code is JBO-30003. Error message parameters are {0=com.redsamurai.model.HrModuleLocal}
java.lang.ClassCastException: com.sun.gjc.spi.jdbc40.ConnectionHolder40 cannot be cast to oracle.jdbc.OracleConnection
at com.redsamurai.common.model.utils.CustomTransactionImpl.getConnection(CustomTransactionImpl.java:14)
at com.redsamurai.common.model.utils.CustomCommonModuleImpl.prepareSession(CustomCommonModuleImpl.java:38)
at oracle.jbo.server.ApplicationModuleImpl.prepareSession(ApplicationModuleImpl.java:7088)
at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolMessage(ApplicationPoolMessageHandler.java:170)
at oracle.jbo.server.ApplicationModuleImpl.doPoolMessage(ApplicationModuleImpl.java:9542)
at oracle.jbo.common.ampool.ApplicationPoolImpl.sendPoolMessage(ApplicationPoolImpl.java:4526)
at oracle.jbo.common.ampool.ApplicationPoolImpl.prepareApplicationModule(ApplicationPoolImpl.java:2453)
at oracle.jbo.common.ampool.ApplicationPoolImpl.doCheckout(ApplicationPoolImpl.java:2263)
at oracle.jbo.common.ampool.ApplicationPoolImpl.useApplicationModule(ApplicationPoolImpl.java:3162)
at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:590)
at oracle.jbo.http.HttpSessionCookieImpl.useApplicationModule(HttpSessionCookieImpl.java:224)
at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:523)
at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:518)
at oracle.adf.model.bc4j.DCJboDataControl.initializeApplicationModule(DCJboDataControl.java:637)

Andrej Baranovskij said...

You need to use Oracle JDBC driver on Glassfish.

Regards,
Andrejus

Muthu said...

I am extending an application module but when I try to access any view object in the base AM i am getting oracle.jbo.InvalidObjAccessException: JBO-25036: An invalid object operation was invoked on type View Object with name XxxxxVO. here is my scenario, I have a AM(BaseAM) where I have all my VO's and EOs. I added another ChildAM extends BaseAM. I created this because I need a XA data source for my AM(i will commit to db as well as publish to JMS). I am using findViewObject to execute queries from my REST service. When I create an AM instance for BaseAM it works fine but for the same VO if I try creating an AM instance for ChildAM I get the above error. When I tried getViewObjectNames() on the VO the BaseAM instance returned all the VO names as array where as the ChildAM instance returns nothing. Any way to over come this ?

Anonymous said...

Hi Andrejus,

Can you upload again the zip file please? thanks

Andrej Baranovskij said...

You can download it from Google archive, search by date - https://code.google.com/archive/p/jdevsamples/downloads

Andrejus

Anonymous said...

Thank you very much Andrejus for helping, I did search of AMExtendApp.zip and didn't get any result, by the way I'm migrating a Froms application that uses Forms built-in security logging with db users accounts.

Andrej Baranovskij said...

It is there, use this URL https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/jdevsamples/AMExtendApp.zip

Ok, let me know if any further help will be required by email.

Andrejus

Anonymous said...

Hi again,

Just sent you an email, and also can I combine the proxy user connection with a custom ADF login form?

Regards,