Bug #1934 prepareStatement dies silently when encountering Statement.RETURN_GENERATED_KEY
Submitted: 24 Nov 2003 10:28 Modified: 25 Nov 2003 7:39
Reporter: Ralf Hauser Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1. nightly OS:Linux (RH9)
Assigned to: Mark Matthews CPU Architecture:Any

[24 Nov 2003 10:28] Ralf Hauser
Description:

	public void initialize()
		throws IOException, SAXException, DatabaseException {
		try {
			log.debug("starting db initialize");
			connRead = rds.getConnection();
			connWrite = wds.getConnection();
//... many other prepared statements
	   log.debug("before Statement.RETURN_GENERATED_KEYS");
           insAttachmentStmt =
	     connWrite.prepareStatement("INSERT INTO " + TBL_ATTACHMENT
	        +" (file_size, "
		+ " file_name, "
		+ " content_type, "
		+ " file_data_blob, "
		+ " create_id, "
		+ " create_date) "
		+ " VALUES (?,?,?,AES_ENCRYPT(?,?),?,?);",
		Statement.RETURN_GENERATED_KEYS);
	  log.debug("after Statement.RETURN_GENERATED_KEYS");
//   ... many other prepared statements
		} catch (SQLException e) {
			log.error(e.getMessage());
			throw new DatabaseException(
				"SQLException: "
					+ e.getMessage()
					+ " SQLState: "
					+ e.getSQLState()
					+ " VendorError: "
					+ e.getErrorCode());
		}
	} // end initialize()

How to repeat:
every time, I get until the "before Statement.RETURN_GENERATED_KEYS" in my catalina.out, but I never see the "after Statement.RETURN_GENERATED_KEYS"

Suggested fix:
at least throw an exception if something is wrong and don't continue as if all were all right.

Or am I doing something wrong?
[24 Nov 2003 15:14] Mark Matthews
Seems like you're doing something wrong (you don't state what server version you're using, but it shouldn't really matter). 

I don't see how the code could even 'proceed as if everything is all right', given that _after_ prepareStatement() is called, you are returned to your code.

The following testcase with 3.1 nightly works as expected, printing both 'before' and 'after' the prepareStatement() call:

 public void testBug1934() throws Exception {
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
    		this.stmt.executeUpdate("CREATE TABLE testBug1934 (field1 INT)");
    		
    		System.out.println("Before prepareStatement()");
    		
    		this.conn.prepareStatement("INSERT INTO testBug1934 VALUES (?)", java.sql.Statement.RETURN_GENERATED_KEYS);
    		
    		System.out.println("After prepareStatement()");
    		
    	} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
    	}
    }
[25 Nov 2003 2:33] Ralf Hauser
Mark, thanks, I had to adapt your test case as follows:
			Statement stmt =
                 connWrite.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
			java.sql.ResultSet.CONCUR_UPDATABLE);
			try {
			stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
			stmt.executeUpdate(
				"CREATE TABLE testBug1934 (field1 INT)");
			log.debug("Before prepareStatement()");
			connWrite.prepareStatement(
				"INSERT INTO testBug1934 VALUES (?)",
				java.sql.Statement.RETURN_GENERATED_KEYS);
			log.debug("After prepareStatement()");
		} finally {
			stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
		}

Same effect  :(
One error I noticed and fixed was that I imported com.mysql.jdbc.Statement instead of java.sql.Statement, but this unfortunately didn't fix it.

Hmm, so I am giving up on statement-granularity, but
     rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
seems to work, with connection granularity only, though
[25 Nov 2003 7:39] Mark Matthews
It sounds like there is something definitely wrong with your environment or your code, because the behavior you are describing shouldn't be able to happen :(

What happens if you step through the code with a debugger?
[25 Nov 2003 9:46] Ralf Hauser
thx for the moral support ;) 
It may take a few weeks, but I'll not give up and once I master the remote debugging you are teaching me in http://bugs.mysql.com/bug.php?id=1658, I guess I'll be able to get that here solved too!
Therefore, pls keep this bug open for while.
[4 Dec 2003 3:22] Ralf Hauser
After moving to struts1.1 and simultaneously,
- replacing driverClassName "org.gjt.mm.mysql.Driver" with "com.mysql.jdbc.Driver"
- moving from GenericDataSource to org.apache.commons.dbcp.BasicDataSource
The problem no longer occurs.
[4 Dec 2003 3:40] Ralf Hauser
and oviously, also here got the nightly jar of early Dec03