PreparedStatement and NullPointerException

[Eugene Stanley]

The following is the ejbCreate() method of an entity bean with BMP. (I'm running this code on WLS6.1 with a MySQL DB v.3.23.):
public AddPK ejbCreate(String carAddId, ...) throws CreateException {
PreparedStatement pStmt = null;
Connection conn = null;
String sql_INS = "INSERT INTO test.CarAdd VALUES(?, ?, ?)";
try {
conn = getConnection();
pStmt = conn.prepareStatement(sql_INS);
//...
pStmt.setString(1, carAddId);
//...
//executeUpdate()...
return new AddPK(carAddId);
}
catch (Exception e) {
throw new CreateException(e.toString());
}
finally {
try { if (pStmt != null) pStmt.close(); }
catch (Exception e) {}
try { if (conn != null) conn.close(); }
catch (Exception e) {}
}
....................................
The problem is the "conn.prepareStatement(sql_INS)" part: I just can't seem to get what's wrong with the sql_INS query?! A NullPointerException is thrown from this clause (it seems...), cuz the program control doesn't seem to get past "pStmt = conn.prepareStatement(sql_INS)"
The connection is perfectly ok (I've tried with getMetaData and so forth).
Is there anyone that kindly could explain what's going on?!?

[Eugene Stanley]

A remark on the above code: This following shows how a DB connection is obtained. Note that I do NOT employ a DataSource on the WLS. (It came to mind that the explanation could be somwhere there within...could it help to employ a DataSource?)
public Connection getConnection() throws Exception {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost/test/?user=add_admin");
} catch(SQLException sqex) {
System.out.println(sqex);
}
catch(Exception e) {
}
finally {
try {
if (conn != null) conn.close();
} catch (Exception ex) {
System.out.println("Error closing database");
}
}
return conn;
}

[Jeanne Boyarsky]

Does your getConnection() method throw an exception if it fails in getting a connection or just return null? If it returns null, that would explain the behavior you are getting.

[Lu Battist]

In the code shown, you've got 3 parameters but I only see you setting one of them. This would cause a problem.

[Dana Hanna]

Your getConnection() code will return null if it gets an error opening the connection, and will close any connection that it ever opens based on the finally block. The finally block will ALWAYS execute, whether an error occurred or not.

[Eugene Stanley]

FYI. Establishing the connection through a DataSource fixed the problem, see the code snippet below. I think it might be the WLS pooling mechanism that played me a trick earlier on. But if someone could express in a sentence exactly what's going in, that would be cool... ;-)
public Connection getConnection() throws Exception {
try {
Context ctx = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("mysqlds");
return ds.getConnection();
}
//...
}