MySQL Bugs: #117641: Documented example for fetching auto-increment value from updatable ResultSets throws error

Bug #117641	Documented example for fetching auto-increment value from updatable ResultSets throws error
Submitted:	7 Mar 14:08	Modified:	27 Nov 17:57
Reporter:	Christopher Schultz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	9.2.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Auto increment field, Connector J, UpdatableResultSet

Description:
This example no longer works with Connector/J 9.1 and 9.2:

https://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-last-insert-id.html#connec...

Using the same code with Connector/J 5.1 works, all tests running against the same backend database version.

How to repeat:
Run the example code.

Error is:

Exception in thread "main" java.sql.SQLException: Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114)
	at com.mysql.cj.jdbc.result.ResultSetImpl.last(ResultSetImpl.java:1788)
	at com.mysql.cj.jdbc.result.UpdatableResultSet.last(UpdatableResultSet.java:815)

Suggested fix:
Unsure. One thing would be to modify the documentation. If you change the ResultSet type from TYPE_FORWARD_ONLY to TYPE_SCROLL_INSENSITIVE then the code runs well.

I haven't found a way to get the generated keys from the statement using JDBC APIs without throwing some error. It doesn't appear to be possible to request both Statement.RETURN_GENERATED_KEYS and also ResultSet.CONCUR_UPDATABLE.

So the caller's only options are to use ResultSet.TYPE_SCROLL_[IN]SENSITIVE or to use a separate query for "SELECT LAST_INSERT_ID()".

Hello Christopher Schultz,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

Documentation updated as requested.
Thank you for the bug report.