ResultSet "column not found" with c3p0

Question

I have a query made to a db and the result is obtained through a java.sql.ResultSet, because this query is dynamic the number of columns returned could be 5 or 7, in the past with the same code it was generated a “column not found exception” and was contained in the following catch:

try{
    sTemp = this.rsResults.getString("col3");
   }catch(Exception e){}

But now with the same try and catch (the only difference is that now i’m using combopooldatasource and their connection), I get two exceptions that do not fall in the catch.

How could I improve this, is there a better way to check if column exists? Does c3p0 have to mandatory test the connection based on a (SQLState: S0022) column not found error?

Error n1 - in the com.mchange.v2.c3p0.impl.NewProxyResultSet.getString qlUtils.toSQLException() - Attempted to convert SQLException to SQLException. Leaving it alone. [SQLState: S0022; errorCode: 0]
java.sql.SQLException: Column 'col3' not found.

Error n2 -  DefaultConnectionTester.statusOnException() - Testing a Connection in response to an Exception:
java.sql.SQLException: Column 'col3' not found.

ps: the driver used is the same org.gjt.mm.mysql.Driver

score 1 · Answer 1 · answered Jun 20 '13 at 10:33

1

c3p0 internally tests Connections on any kind of Exception, but the Exception from this test is not thrown or visible to client code. You are only seeing it because you are logging c3p0 output at DEBUG levels. c3p0 stuff should be logged at INFO for normal use. If you log at DEBUG-ish levels, you'll see all kinds of alarming messages and stack traces.

answered Jun 20 '13 at 10:33

Steve Waldman

13,689
1
35
45

yes it is true, but if i set the logging to INFO, the connection test will still occur? if yes then the cost of raising the test plus the stack for the exception will still be high, and that's what i want to avoid – AndreFonseca Jun 20 '13 at 10:57
yes, the test will still occur. c3p0 presumes Exceptions indicate exceptional conditions, and always tests after Exceptions to determine whether or not the Connection is suitable for re-use in the pool or should be marked for destruction upon close. connection tests needn't be very costly, if you set preferredTestQuery or automaticTestTable properties. the default test is often slow, however. – Steve Waldman Jun 20 '13 at 14:09
Ok thank you Steve. I think that the cost of the Exception (col not found) is what bother's me the most, i'm making some test's with original code (try&catch with SQLException column not found) and with modified code (checking if columns exists before ResultSet.getString()) all with and without c3p0, i will post the results here when done. – AndreFonseca Jun 20 '13 at 14:26

Ravi K Thapliyal · Accepted Answer · 2013-06-19T19:44:04.760

0

Inspect the ResultSetMetaData

ResultSet rs = stmt.executeQuery("SELECT * FROM your_table");

ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

// test the number of columns returned
if (numberOfColumns == 5) {
    //
} else {
    // numberOfColumns = 7
}

// or, test the column names
if ("col3".equals(rsmd.getColumnName(3)) {
    // col3 exists
}

EDIT :
If you don't want to make modifications to your source code and just want your current approach to work with c3p0 as well; just catch Throwable. (it does make me shudder though :)

try {
    sTemp = this.rsResults.getString("col3");
} catch (Throwable t) {
    // Both, Exceptions and Errors are ignored now
}

edited Jun 19 '13 at 19:44

answered Jun 19 '13 at 17:10

Ravi K Thapliyal

51,095
9
76
89

thank you for the response, the ResultSetMetaData if fine for checking if col3 exists but col3 changes it's position in the select statment. The getString method of ResultSet is just perfect, because if column does not exists returns an exception as decribed in http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#getString(java.lang.String), my problem is with this particular exception when using c3p0. – AndreFonseca Jun 19 '13 at 17:34
I don't like either the Throwable, because i don't want to log anything and to have to deal with any errors and exception's that can arise besides the column not found. I will make changes to the source code, it's the best way. – AndreFonseca Jun 20 '13 at 08:30

score 0 · Answer 3 · edited May 23 '17 at 12:29

I've made the following (rough) tests just to illustrate the cost of raising an exception vs checking if columns exist in the RecordSet, in my particular case.

The number of columns to check was 169.

Code 1

try
{
 sTemp = this.rsResults.getString("col3");
}catch(Exception e){}
try
{
 sTemp = this.rsResults.getString("col4");
}catch(Exception e){}

...

try
{
 sTemp = this.rsResults.getString("col169");
}catch(Exception e){}

Code 2 with function hasColumn [question]:How can I determine if the column name exist in the ResultSet?

ResultSetMetaData rsmd = null;
try {
     rsmd = this.rsResults.getMetaData();
} catch (SQLException e1) {
 e1.printStackTrace();
}

try{
 if (rsmd != null && hasColumn(rsmd,"col3"))
  sTemp = this.rsResults.getString("col3");
}catch(Exception e){}

try{
 if (rsmd != null && hasColumn(rsmd,"col4"))
  sTemp = this.rsResults.getString("col4");
}catch(Exception e){}

...

try{
 if (rsmd != null && hasColumn(rsmd,"col169"))
  sTemp = this.rsResults.getString("col169");
}catch(Exception e){}

Results without c3p0

           code 1   code 2
query nº1   75ms     36ms
query nº2   40ms     43ms
query nº3   227ms    46ms
query nº4   262ms    18ms

Results with c3p0 loglevel at INFO

           code 1   code 2
query nº1   519ms     45ms
query nº2   358ms     28ms
query nº3   2348ms    9ms
query nº4   3243ms    12ms

As a conclusion the cost of raising an exception just to check if columns exists (besides of being bad practice) is high in both cases and especially if using c3p0.

ResultSet "column not found" with c3p0

3 Answers3