[CONJ-234] ava.sql.SQLException: getGeneratedKeys error. Statement.RETURN_GENERATED_KEYS must be precised on connection.prepareStatement(String sql, int autoGeneratedKeys) or statement.executeUpdate(String sql, int autoGeneratedKeys) Created: 2015-12-10  Updated: 2015-12-31  Resolved: 2015-12-31

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 1.3.0, 1.3.2, 1.3.1, 1.3.3
Fix Version/s: 1.3.0

Type: Bug Priority: Major
Reporter: Frank S Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None


 Description   

Consider following testcase:

 @Test
    public void test() throws ClassNotFoundException, SQLException {
        Class.forName("org.mariadb.jdbc.Driver");
        try (Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost/test", "root", "")) {
            try {
                connection.prepareStatement("CREATE TABLE table1(id int NOT NULL AUTO_INCREMENT, value varchar(34), PRIMARY KEY(id))").execute();
 
//                try (PreparedStatement statement = connection.prepareStatement("INSERT INTO table1(value) VALUES (?)",Statement.RETURN_GENERATED_KEYS)){
                try (PreparedStatement statement = connection.prepareStatement("INSERT INTO table1(value) VALUES (?)")){
                    statement.setString(1, "test");
                    ResultSet generatedKeys = statement.getGeneratedKeys();
                    while (generatedKeys.next())
                    Assert.assertNotNull(generatedKeys.getInt("id"));
                };
                try (PreparedStatement ps = connection.prepareStatement("select * from table1")) {
                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        Assert.assertEquals("test", rs.getString("value"));
                    }
                }
            } finally {
                connection.prepareStatement("drop table table1").execute();
            }
        }
    }

This runs fine on all versions befor 1.3.0. At version 1.3.0 following exception is thrown:

java.sql.SQLException: getGeneratedKeys error. Statement.RETURN_GENERATED_KEYS must be precised on connection.prepareStatement(String sql, int autoGeneratedKeys) or statement.executeUpdate(String sql, int autoGeneratedKeys)
 
	at org.mariadb.jdbc.MariaDbStatement.getGeneratedKeys(MariaDbStatement.java:800)
	at TestMaria.test(TestMaria.java:23)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runners.Suite.runChild(Suite.java:128)
	at org.junit.runners.Suite.runChild(Suite.java:27)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

The only way to fix ist to run the prepareStatement with the argument RETURN_GENERATED_KEYS (just as the commented line).
As we want to upgrade from 1.2.X to 1.3.3, we do not want to fix all the prepared statements which use an auto inc in our code base.



 Comments   
Comment by Diego Dupin [ 2015-12-15 ]

Current implementation follow JDBC rules and permit to use less memory (avoid to parse and load in memory those informations).

For transition, an option must have been added to facilitate transition, but, as every option add complexity and above all take process time for each query, this option will be marked as deprecated so to avoid it used, and will have a end of life in a few years.

Comment by Diego Dupin [ 2015-12-31 ]

Documentation option was missing : alwaysAutoGeneratedKeys


JDBC permit to retrieve previous insert id's by the Statement.getGeneratedKeys() command.
By default id's are not retrieved. When executing query, additional parameters Statement.NO_GENERATED_KEYS or Statement.RETURN_GENERATED_KEYS indicate if id's must be retrieved, like in command Statement.executeUpdate(String sql, int autoGeneratedKeys).

When option alwaysAutoGeneratedKeys is set to true, insert id's will be retrieved even if no autoGeneratedKeys parameter or Statement.NO_GENERATED_KEYS is set.
Default: false. Since 1.3.0

Generated at Thu Feb 08 03:14:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.