Bug #72270 SQLException Generated keys not requested on INSERT

• Submitted: 8 Apr 2014 7:08
• Modified: 8 Apr 2014 10:01
• Reporter: Nicolas De Amicis
• Status: Not a Bug
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 5.1.30
• OS: Any
• CPU Architecture: Any

[8 Apr 2014 7:08] Nicolas De Amicis

Description:
When I insert with a PreparedStatement or Statement and I read the generated key by the mysql database, I have a SQLException Generated keys not requested, but I have specified the Statement.RETURN_GENERATED_KEYS parameter.

I have tested with connector/J 5.1.23 and 5.1.30 and Fedora Linux 64 bits and Windows Server 2012 64 bits. It's the same error. I have JDK 1.7u51 64 bits on both OS.

Here the output of my test program:
=====  Database info =====
DatabaseProductName: MySQL
DatabaseProductVersion: 5.1.51-log
DatabaseMajorVersion: 5
DatabaseMinorVersion: 1
=====  Driver info =====
DriverName: MySQL Connector Java
DriverVersion: mysql-connector-java-5.1.30 ( Revision: [e-mail]-20140310090514-8xt1yoht5ksg2e7c )
DriverMajorVersion: 5
DriverMinorVersion: 1
=====  JDBC/DB attributes =====
Supports getGeneratedKeys(): true
Command successfully executed
java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
	at com.mysql.jdbc.StatementImpl.getGeneratedKeys(StatementImpl.java:1971)
	at TestInsert.test(TestInsert.java:33)
	at TestInsert.main(TestInsert.java:15)

How to repeat:
Run this program with a connector/J in the classpath:

public class TestInsert
{
	private Connection conn;
	
	public static void main(String[] args)
	{
		new TestInsert().test();
	}
	
	public void test()
	{
		Statement stmt = null;
		ResultSet rs = null;
		int newId = 0;
		try
		{
			openConnection();
			stmt = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE,
					Statement.RETURN_GENERATED_KEYS);

			stmt.executeUpdate("INSERT INTO ..."); // TODO to change
			rs = stmt.getGeneratedKeys();
			if(rs != null)
			{
				if(rs.first())
				{
					newId = rs.getInt(1);
					System.out.println("NewID=" + newId);
				}
			}
			rs.close();
			stmt.close();
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
		finally
		{
			closeConnection();
		}
	}
	
	private void openConnection() throws SQLException
	{
		try
		{
			conn = DriverManager.getConnection("jdbc:mysql://xxx:3306/yyy", "uuu", "ppp"); // TODO to change
			DatabaseMetaData dbmd = conn.getMetaData();
			System.out.println("=====  Database info ====="); 
			System.out.println("DatabaseProductName: " + dbmd.getDatabaseProductName() ); 
			System.out.println("DatabaseProductVersion: " + dbmd.getDatabaseProductVersion() ); 
			System.out.println("DatabaseMajorVersion: " + dbmd.getDatabaseMajorVersion() ); 
			System.out.println("DatabaseMinorVersion: " + dbmd.getDatabaseMinorVersion() ); 
			System.out.println("=====  Driver info ====="); 
			System.out.println("DriverName: " + dbmd.getDriverName() ); 
			System.out.println("DriverVersion: " + dbmd.getDriverVersion() ); 
			System.out.println("DriverMajorVersion: " + dbmd.getDriverMajorVersion() ); 
			System.out.println("DriverMinorVersion: " + dbmd.getDriverMinorVersion() ); 
			System.out.println("=====  JDBC/DB attributes ====="); 
			System.out.print("Supports getGeneratedKeys(): "); 
			if (dbmd.supportsGetGeneratedKeys() )
			{
				System.out.println("true");
			}
			else
			{
				System.out.println("false");
			}
			System.out.println("Command successfully executed");
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	
	private void closeConnection()
	{
		try
		{
			if(conn != null)
			{
				conn.close();
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
}

[8 Apr 2014 9:59] Filipe Silva

Hi Nicolas,

In the method Connection#createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability), the third argument is used to determine cursor holdability which is not related to generated keys.

In order to be able to retrieve generated keys you must use either Statement# 	execute(String sql, int autoGeneratedKeys) or Statement#executeUpdate(String sql, int autoGeneratedKeys).
Alternatively you may also get generated keys using Prepared Statements if constructed with the method Connection#prepareStatement(String sql, int autoGeneratedKeys).

Regards,