MySQL Bugs: #13668: RETURN_GENERATED_KEYS does not work, if insert is within a stored procedure.

Bug #13668	RETURN_GENERATED_KEYS does not work, if insert is within a stored procedure.
Submitted:	30 Sep 2005 19:18	Modified:	11 Nov 2009 2:35
Reporter:	k self	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.2.0-alpha	OS:	Windows (Windows-XP)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
When calling a stored procedure that contains a single insert statement that inserts a single record into a table that has an AUTO_INCREMENT column, the newly inserted value is NOT returned when using the getGeneratedKeys() method.

How to repeat:
1. Create a table that contains an AUTO_INCREMENT column.
2. write a simple sp that inserts a record into this table.
3. Call the sp using the 'RETURN_GENERATED_KEYS' value in the prepareStatement function.
4. Attempt to obtain the new insert ID value using the getGeneratedKey() function.

The resultset is empty.

1)

create table A(a int primary key auto_increment,b int);

2)

delimiter //
create procedure AA()
begin
  insert into A(b) values(100);
end
//

3)
PreparedStatement stmt = conn.prepareStatement("call AA();",Statement.RETURN_GENERATED_KEYS);

4)
ResultSet lrs = stmt.stmt.getGeneratedKeys();

The lrs resultset is empty.

Suggested fix:
No work around can be found to obtain the generated key from the sp call.

Thanks for the bug report. I was able to reproduce the bug.

Connected to 5.0.14-rc-debug
START
E
Time: 0,344
There was 1 error:
1) testPound13668(Bug13668)java.lang.NullPointerException
        at com.mysql.jdbc.Statement.getGeneratedKeysInternal(Statement.java:347)
        at com.mysql.jdbc.Statement.getGeneratedKeys(Statement.java:329)
        at com.mysql.jdbc.PreparedStatement.getGeneratedKeys(PreparedStatement.java:663)
        at Bug13668.testPound13668(Bug13668.java:24)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at Bug13668.main(Bug13668.java:34)

FAILURES!!!
Tests run: 1,  Failures: 0,  Errors: 1

Test case is attached.

Test case

Attachment: Bug13668.java (text/java), 941 bytes.

The JDBC API itself doesn't seem to support this behavior.

The specification requires that you use Connection.prepareCall() to call stored procedures. There is no variant of prepareCall() that takes a parameter to allow a client to retrieve generated keys.

Even if this weren't the case, until the server supports the prepare of the "call" statement, and returns generated auto increment values, it can't be implemented from a client in a robust way.

As a workaround, either return SELECT LAST_INSERT_ID() as an output parameter, or issue it as a query after executing the stored procedure.

Is it possible to add a function to allow you to set the returnGeneratedKeys() result set from a select statement within a stored procedure? If this function existed, then the stored procedure could return the newly created record ID, and work correctly with the RETURN_GENERATED_KEYS call.

No, it is not possible, as the JDBC driver has no "insight" into what functions exist, and functions can only return result sets or output parameters.

The only feasible workaround is to use an output parameter or return result set to get this information back to the JDBC driver. 

As stated earlier, there is no functionality provided by the JDBC specification to allow generated keys to be returned for CallableStatements.

the following code produces the same NullPointerException

ps = connection.prepareStatement(INSERT_QUERY, Statement.RETURN_GENERATED_KEYS);
ps.executeBatch();
ps.getGeneratedKeys();

Here is the trace 
ava.lang.NullPointerException
	at com.mysql.jdbc.Statement.getGeneratedKeysInternal(Statement.java:347)
	at com.mysql.jdbc.Statement.getGeneratedKeys(Statement.java:329)
	at com.mysql.jdbc.PreparedStatement.getGeneratedKeys(PreparedStatement.java:663)
	at newsleecher.workers.jdbc.SaveNewGroupsWorker.save(SaveNewGroupsWorker.java:85)
	at newsleecher.workers.jdbc.SaveNewGroupsWorker.access$000(SaveNewGroupsWorker.java:20)
	at newsleecher.workers.jdbc.SaveNewGroupsWorker$1.consumeBuffer(SaveNewGroupsWorker.java:51)
	at newsleecher.util.QueueConsumer.consumeQueue(QueueConsumer.java:53)
	at newsleecher.workers.jdbc.SaveNewGroupsWorker._runJdbc(SaveNewGroupsWorker.java:58)
	at newsleecher.workers.jdbc.AbstractJDBCNewsLeecherWorker._run(AbstractJDBCNewsLeecherWorker.java:44)
	at newsleecher.workers.AbstractWorker.run(AbstractWorker.java:74)
	at newsleecher.ui.dispatch.Dispatcher.run(Dispatcher.java:50)
	at java.lang.Thread.run(Thread.java:595)

It should make sense to return an int[0]

It appears your bug is a totally different issue, so please post a new bug with a repeatable testcase.