magpiebrain

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.