PreparedStatement not throwing error when it should

[Micky Johnson]

Given the following code...

		
		String insertStatement = "DELETE FROM UserSession WHERE UserFK = ?; " +
						     "DELETE FROM UserSession WHERE SessionID = ?; " +
						     "INSERT INTO UserSession (SessionID, UserFK) " + 
						     "VALUES ( ? , ? );";
		
    	PreparedStatement prepStmt = null;
		
        try 
        {	
    		// Prepare Statement 	
        	prepStmt = connection.prepareStatement(insertStatement);
    		prepStmt.setInt(1, userPk);
    		prepStmt.setString(2, sessionId);
    		prepStmt.setString(3, sessionId);
    		prepStmt.setInt(4, userPk);
    		
    		// Execute statement
    		prepStmt.execute();
		}
        catch(Exception ex) 
        {
        	log.error(ex);
        	throw ex;
        }
        finally // Close SQL related resources 
        {
        	
        	if (prepStmt != null)
        	{
        		try
        		{
        			prepStmt.close();
        		}
        		catch (SQLException ex)
        		{
        			// Do nothing
        		}
        	}    	
        }		

...and assuming I have the table structure...

UserSession (
	SessionID varchar(75) NOT NULL,
	UserFK] int NOT NULL,
	LastLogin datetime NOT NULL) /* Note: I have intentionally not set a default for LastLogin to create the scenario */

....I would expect to get an error because I am trying to insert a record into a table without a value for LastLogin. However I do not get an error and therefore experience unexpected behavior in my application. The same SQL executed in a Query Analyzer returns an error.

Can anyone explain why this is not working as expected?

Thanks.

[Paul Clapham]

How is it working as not expected, then? Does it insert a record? If so then what is the value of the LastLogin column of that record?

[Micky Johnson]

No record is inserted into the UserSession table. I stepped through the code and after "prepStmt.execute();" the debugger skips directly to the "finally" block. Given that I know a SQL error has occurred it should have been caught in the "exception" block but for some reason it is not.

[Micky Johnson]

It seems that if you have multiple SQL statements you should use a PreparedStatement for each one instead of trying to bundle them all into one PreparedStatement (or Statement for that matter). If you do this and use transactions you should be all set. To use transactions within the Java code set autoCommit(false) on the Connection instance and then commit or rollback depending on the outcome of the execution of the PreparedStatements (don't forget to set autoCommit(true) when you are finished).

See http://download.oracle.com/javase/tutorial/jdbc/basics/transactions.html for details.