magpiebrain

Sam Newman's site, a Consultant at ThoughtWorks

When connecting to a database from Java, it’s very handy to tag your connections. When tracking down performance issues and monitoring at a database levels, being able to seperate out your program’s connections (which could come from a variety of machines). Most database drivers allow you to specify a program name when creating your connection.

With SqlServer, you can specify a program name “on the query string(The MS SQL Server Driver – connection properties)”:http://edocs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html#1074599 of your JDBC connection. However with Oracle it’s not quite as simple. I spent ages trying to track down how to do this with Oracle, but it seems that where Oracle is concerned useful documentation lies behind expensive consultants or registration screens. Eventually resident database guru (thanks Jason) sent me a snippet of code which does exactly that, and I’m blogging it here for prosperity (and for consumption by the Google spider).

This code opens a connection with the name Test. By querying the database’s @v$session@ dictionary view we can see each connection from our application (by the way, good luck searching
Google using a $ in a search term). @v$session@ exposes a variety of information – including the program name. The final lines of the code prints out the program name of every session currently connected to the database, helping confirm that the snippets code has worked.

class SetProgram
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    java.util.Properties props = new java.util.Properties();
    props.put("v$session.program", "Test");

    // Connect to the database
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:thin:user/tiger@localhost:1521:xe",
				   props);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the PROGRAM field from the V$SESSION table
    ResultSet rset = stmt.executeQuery ("select program from v$session");

    // Iterate through the result
    while (rset.next ())
      System.out.println (rset.getString (1));
  }
}

h3. Specifying program name with C3P0

When configuring this value using the “C3P0(C3P0 – Java database connection pool)”:http://sourceforge.net/projects/c3p0 connection pool, if you want to specify properties like @v$session.program@, you cannot configure login and password using the normal @setPasword@ or @setUser@ methods as it gets its knickers in a twist. Instead you’ll have to place those in the properties object which you pass to the connection pool using the @setProperties@ method on @ComboPooledDataSource@.

This is worth noting, as where @setUser@ and @setPassword@ will be JDBC driver agnostic, the properties bundle passed in isn’t – or more correctly, the properites themselves aren’t.

About these ads

7 Responses to “Specifying a program name in Oracle JDBC connections”

  1. Sam Newman

    Justas – a little tip. Saying “It doesn’t work” without supplying any addtional information is unlikely to get you much help :-)

    I’ll give it a go anyway – are you using a connection pool? Is it possible you’re using a Database driver that is mangling your properties object? Can you get your driver to output the properties it is using to confirm it is correctly getting set?

    Specifiyng v$session.program is something natively supported in Oracle – if it isn’t working for you, the problem is either going to be an (unlikely) bug in Oracle, or else a bug in the method you’re using to connect to the database.

    Reply
  2. Norbert

    For JDBC OCI the program is java.exe when java runs on windows. With JDBC OCI driver, program cannot be overriden. With JDBC Thin, the code above works, since the property is not overriden, with JDBC OCI it does not work and you always get V$SESSION.PROGRAM=’java.exe’.
    This is based on testing with 10.2.0.3.0 Oracle JDBC driver.

    Norbert Debes

    Reply
  3. Willi Firulais

    Realy cool code snippet.
    Do you have something when using a connection pool?
    Is it possible to set some v$session infos when using connection pool?
    Thx a lot in advance, Willi

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Basic HTML is allowed. Your email address will not be published.

Subscribe to this comment feed via RSS

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: