Solved

Example code in JDBC using "application context" in Oracle

Posted on 2004-08-17
9
1,675 Views
Last Modified: 2008-01-09
Hi

I am looking for a sample JDBC code in Oracle 8i to use "application context" to pass certain client information. I could find many examples using SYS_CONTEXT and implementing security policies in PL/SQL, but could not find one using JDBC.

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#8113
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759

-Srinivas
0
Comment
Question by:sbolisetty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11821975
look at the package dbms_application_info

I have frequently used this to set application context values.  Example (from Oracle Applications):

BEGIN
      dbms_application_info.set_client_info(33);
END;


This is just a session-specific value.


0
 

Author Comment

by:sbolisetty
ID: 11822237
From a PL/SQL block that is how it can be done. From a JDBC connection - especially using connection pool with in an application server - how do I go about passing this information from application server's session back to the database?

-Srinivas
0
 
LVL 7

Accepted Solution

by:
bvanderveen earned 315 total points
ID: 11822530
Use a CallableStatement to call dbms_application_info.  Use set_client to set the value, get_client to check it.  With a Connection named conn:
 
   CallableStatement pCall = null;

   try{
         Class.forName("oracle.jdbc.driver.OracleDriver");
          conn = DriverManager.getConnection("jdbc:oracle:thin:@dbserver:1521:ORCL","scott","tiger");
         
          pCall = conn.prepareCall("{call dbms_application_info(?)}");
          pCall.setInt(1,33);  //set the value of the parameter above...
          pCall.execute();
    } catch(SQLException e){
         System.out.println("You have done something wrong");
    }

 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sbolisetty
ID: 11822960
This seem to be just the session-id. Is there any way to pass other information? For example, in my application I need to pass at least two fields - User who is Logged in and Module-Name the user is working on. This is needed to enforce security policies as dictated by the existing oracle structures.

-Srinivas
0
 

Author Comment

by:sbolisetty
ID: 11823245
Looked up on the DB_Application_Info a bit. The set_module and set_client_info procedures should be good enough for me to pass this information to the oracle backend. From the looks of it, this needs to be done before every call to the database. Is this the way Applicatio Context is used in Oracle? Is there any better way to do this from Java Application Server point of view? Thanks for your help.

-Srinivas
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11823411
These are session-based.  You don't need to set before each call to the db, just per session.  When you open a jdbc connection, you have a session.  Set application-level info there, and it will persist throughout the session.  Next connection, they will need to be re-established.

If you are in an environment (HTML, for instance) where the connection is not persistent, you need to stash the values in the page, and use them each time you connect.  Use the get functions to test the value during development.

One other "gotcha"  if you use connection pooling, users may be sharing these values.  Not good, so test around this, or see if your app can skip the pooling.
0
 

Author Comment

by:sbolisetty
ID: 11823579
Do you know of any easier way to approach this problem in the case of HTML-Application using connection-pooling? We do all this and trying to attack this problem from this one application perspective. Thank you.

-Srinivas
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11823698
Simple way: Hold the value you want in a hidden field on your page and reset it each time.  

Are you using JSP, PLSQL server pages, ?  9iAS, WebShere?

You might look at the session object, or use a temporary table to hold values as well.  Don't know your architechture, so don't know.  If using a framework such as JSP, Struts, etc.  look at the documentation on the session object.
0
 

Author Comment

by:sbolisetty
ID: 11824222
It is not the problem with session object. We do keep session object based information and caching on the app server itself. But we have this need to send the user and module names to the oracle backend which will filter out the data based on the security policies set inside the database. The solution above may work if we simply call the dbms_application_info stored procedures before doing any thing with in the connection object. That is, we can get the connection from the pool - then call these two stored procedures and then continue to do what we need to in the application. But this seems a bit cludge and wondering if and how this is solved by others. Thanks.

-Srinivas
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question