WombatNation

A few months ago, the instrument cluster on my Audi TT started behaving very oddly for the first twenty seconds after I started the car in the morning. As time passed, twenty seconds became thirty, forty, … and then up to two minutes before returning to normal behavior. The colder the weather, the longer it lasted.

So, exactly what do I mean by oddly? Well, the needles on the temperature and fuel gauges would slam back and forth wildly. The tach and speedometer gauges would jump from zero to very high RPMs or speeds, respectively, then fall back to zero. Diagnostic lights would come on and go off, with their accompanying beeps. See for yourself.

It got so bad that finally the instrument cluster stopped talking to the immobilizer. This caused the immobilizer to think the car was being stolen, so it kept killing the engine when I tried to start it.

At that point, I had to have the instrument cluster replaced. Although this is a common problem with TTs, Audi has yet to issue a recall in the US.

And, no, the mice had nothing to do with this.

The C3P0 database connection pooling library has worked very well for me, but I recently ran into a problem when I wanted to log the SQL that was being generated for a PreparedStatement if an insert failed. Then, if database problems caused inserts to fail beyond an automated retry period, I could easily harvest the SQL statements from the log file and retry them later. Also, if a data problem caused the inserts to fail, I could harvest the statements from the log, use a script to adjust the data and then retry them.

C3P0 wraps the JDBC driver’s PreparedStatement class with its C3P0ProxyStatement. Unfortunately, it doesn’t override toString() to return something useful. So, you just get the default of a class name and the hash code for the instance. Not helpful.

Unfortunately, there is nothing in the API that gives you an obvious means of getting at the real PreparedStatement, which I can see in the debugger inside a private variable named inner. I knew that the rawStatementOperation() method might be the key, but it wasn’t obvious. However, a bit of googling turned up a solution in the unit test code for C3P0.

Unit tests are awesome. Not just for testing your code, but also for providing example code.

So, here’s some example code. First, I created a simple table in the test database of a local MySQL install.

create table rs (a int);

And here’s some code that connects to the database, sets up a prepared statement and then extracts the SQL from the prepared statement with the parameters bound:

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
import com.mchange.v2.c3p0.C3P0ProxyStatement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
 
public class C3P0Demo {
 
  public static void main(String[] args) throws Exception {
 
    // Get a connection
    String url = "jdbc:mysql://localhost:3306/test";
    ComboPooledDataSource ds = new ComboPooledDataSource();
    ds.setDriverClass("com.mysql.jdbc.Driver");
    ds.setJdbcUrl(url);
    ds.setUser("myuser");
    ds.setPassword("mypassword");
    Connection conn = ds.getConnection();
 
    // Prepare the PreparedStatement
    PreparedStatement ps = conn.prepareStatement("insert into rs values (?)");
    ps.setInt(1, 10);
 
    // Extract the SQL
    String sql = "";
    try {
      C3P0ProxyStatement c3p0Stmt = (C3P0ProxyStatement) ps;
      Method toStringMethod = Object.class.getMethod("toString", new Class[] {});
      Object toStr = c3p0Stmt.rawStatementOperation(toStringMethod,
          C3P0ProxyStatement.RAW_STATEMENT, new Object[] {});
      if (sql instanceof String) {
        sql = (String) toStr;
        sql = sql.substring(sql.indexOf('-') + 1).trim() + ";";
        System.out.println(sql);
      }
    } catch (SQLException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (SecurityException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (NoSuchMethodException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (IllegalArgumentException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (IllegalAccessException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (InvocationTargetException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    }
  }
}

Inside the C3P0ProxyStatement is a com.mysql.jdbc.ServerPreparedStatement. For my example, ServerPreparedStatement.toString() returns:

com.mysql.jdbc.ServerPreparedStatement[1] - insert into rs values (10)