Here's some of the relevant code:
This bit is in the constructor, and the prepared statement insertNew is an instance variable. This bit never throws an exception.
****************************************************************************
con = DriverManager.getConnection(url);
// create a prepared statement to do inserts
//
insertNew = con.prepareStatement("INSERT INTO " + this.table + "(iteration, email, client_time, status, client_count, minA, maxA,minB, maxB, minX,maxX, minY, maxY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
****************************************************************************
The next bit throws an exception at the insertNew.execute call. But not always
****************************************************************************
public void insert(BealNetObject bno, BigInteger masterIteration,
String status) throws ServletException{
try {
insertNew.setString(1,masterIteration.toString());
insertNew.setString(2,bno.getUsername());
insertNew.setString(3,Long.toString(bno.getStartTimeAsLong()));
insertNew.setString(4,status);
insertNew.setString(5,Long.toString(bno.getCount()));
IterativePoint min = bno.getMin();
IterativePoint max = bno.getMax();
insertNew.setString(6,Long.toString(min.a));
insertNew.setString(7, Long.toString(max.a));
insertNew.setString(8, Long.toString(min.b));
insertNew.setString(9, Long.toString(max.b));
insertNew.setString(10,Integer.toString(min.x));
insertNew.setString(11, Integer.toString(max.x));
insertNew.setString(12, Integer.toString(min.y));
insertNew.setString(13, Integer.toString(max.y));
insertNew.execute();
insertNew.clearParameters();
} catch (SQLException sql) {
throw new ServletException(sql);
}
****************************************************************************
here's the relevant part of the stack trace:
****************************************************************************
----- Root Cause -----
java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
at arthur.beal.net.BealDAO.insert(BealDAO.java:155)
****************************************************************************
Now the exception is only thrown when a very important bit of code is executed before the prepared statement is even created. Here is the code that leads up to that, but it is a bit lengthy, so I'll try to explain it. In the constructor, I try to make a table, but not if it's already made, so I issue a CREATE statement regardless, knowing that I'll get an exception if the table is already there. So if it is, I put some code in the catch block, to get some info about the table. Then I open a new connection and create the prepared statement to use for the life of this object...and it throws the exception if the table already existed, although the rest of the code runs fine.
Here's the code to create the database and/or get some info about the table
****************************************************************************
try {
// open a connection
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url);
Statement s = con.createStatement();
// create table
try {
s.execute("CREATE TABLE " + this.table + " (iteration NUMERIC PRIMARY KEY,email CHAR(40), client_time NUMERIC,status CHAR(20),client_count NUMERIC,minA NUMERIC,maxA NUMERIC,minB NUMERIC,maxB NUMERIC,minX INTEGER,maxX INTEGER,minY INTEGER,maxY INTEGER)");
con.close();
// we used the first connection to create the table, and for some reason we need to close it
// to make the table persistent.
// since we're just starting the computation, we set the current point at the lowest setting
currentPoint = info.getAbsoluteMin();
currentIteration = BigInteger.ONE;
// we're going to use a row in the database to store the state of the master iterator.
// that row will be row 0, so the actual iteration value will be stored in client_count
// and the values for a,b,x and y will be in the 'max' fields. Here we create that entry
con = DriverManager.getConnection(url);
s = con.createStatement();
IterativePoint p = info.getAbsoluteMin();
s.execute("INSERT INTO " + this.table + " (iteration, client_count, maxA,maxB,maxX,maxY) VALUES (0,1," + p.a + "," + p.b + "," + p.x + ","+ p.y +")");
con.close();
System.out.println("The table was successfully created.");
} catch (SQLException sql) {
// If we get it here, it probably means that the table already exists.
// So, we pull the state of the iterator out of the database
con = DriverManager.getConnection(url);
s = con.createStatement();
ResultSet rs = s.executeQuery("SELECT iteration,client_count,maxA,maxB,maxX,maxY FROM " +this.table+" WHERE iteration = 0");
rs.next();
currentIteration = rs.getBigDecimal("client_count").toBigInteger();
currentPoint = new IterativePoint();
currentPoint.a = rs.getLong("maxA");
currentPoint.b = rs.getLong("maxB");
currentPoint.x = rs.getInt("maxX");
currentPoint.y = rs.getInt("maxY");
con.close();
}
con = DriverManager.getConnection(url);
// create a prepared statement to do inserts
//
insertNew = con.prepareStatement("INSERT INTO " + this.table + "(iteration, email, client_time, status, client_count, minA, maxA,minB, maxB, minX,maxX, minY, maxY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
} catch (Exception sql) {
System.out.println(sql.toString());
}