If you are modernising Oracle Forms system, high chance you need to rely on DB proxy connection. Read more about it in my previous post for ADF 11g - Extending Application Module for ADF BC Proxy User DB Connection. It works in the same way for ADF 12c, but there is issue related to handling DB error, when DB proxy connection is on. DB error is propagated to ADF but is being substituted by save point error (as result - user would not see original error from DB). It seems like related to JDBC driver in 12c. The workaround is to override ADF SQL builder class and disable save point error propagation (there might be better ways to workaround it).
Proxy connection is established from prepareSession method in generic AM Impl class:
If I would change salary value to negative and save data - DB constraint error would fire (negative not allowed). Unfortunately, end user would not see that error - he gets message about failed save point:
Workaround - we can disable save point error propagation. Override SQL Builder class and add try/catch block in rollbackToSavepoint method. If error happens, do nothing:
You must register SQL Builder class with AM. Add jbo.SQLBuilderClass property in bc4j.xcfg, pointing to the class:
You should be able to see DB errors after this change is applied:
However, there is one drawback of this workaround to keep in mind. When data is posted to DB, ADF executes lock statement. If update fails, normally ADF would execute rollback to save point and lock will be removed. But not in the case of DB proxy, now rollback to save point is failing - this means lock will stay:
If user would fix data and try to save again - lock error will be returned:
Error during lock:
To bypass lock issue, you should enable DB pooling for AM instance. In this case, after each request DB connection will be returned back to the pool and lock will be released automatically:
Download sample application - AMExtendApp_v3.zip.
Proxy connection is established from prepareSession method in generic AM Impl class:
If I would change salary value to negative and save data - DB constraint error would fire (negative not allowed). Unfortunately, end user would not see that error - he gets message about failed save point:
Workaround - we can disable save point error propagation. Override SQL Builder class and add try/catch block in rollbackToSavepoint method. If error happens, do nothing:
You must register SQL Builder class with AM. Add jbo.SQLBuilderClass property in bc4j.xcfg, pointing to the class:
You should be able to see DB errors after this change is applied:
However, there is one drawback of this workaround to keep in mind. When data is posted to DB, ADF executes lock statement. If update fails, normally ADF would execute rollback to save point and lock will be removed. But not in the case of DB proxy, now rollback to save point is failing - this means lock will stay:
If user would fix data and try to save again - lock error will be returned:
Error during lock:
To bypass lock issue, you should enable DB pooling for AM instance. In this case, after each request DB connection will be returned back to the pool and lock will be released automatically:
Download sample application - AMExtendApp_v3.zip.
We found another fix for this issue - setting Auto Commit = false for Proxy DB connection solves the problem.
ReplyDeleteconn.setAutoCommit(false);
Regards,
Andrejus
Hi Andrejus,
ReplyDeleteDid you put this conn.setAutoCommit(false) in beforeDisconnect? of AppModuleImpl.
I am doing a POC before doing a full implementation by using database proxyUser.
From your experience i would like to ask is there any complexity in implementing this as a security feature ? compared to other security model such as dynamic JDBC credentials.
Thanks in Advance, Arora