magpiebrain

Specifying a program name in Oracle JDBC connections

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 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));
  }
} 

Specifying program name with C3P0

When configuring this value using the 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.

This entry was posted on Tuesday, May 9th, 2006 at 9:07 pm and is filed under Java, howto. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.