[DERBY-4921] Statement.executeUpdate(String sql, String[] columnNames) throws ERROR X0X0F.S exception with EmbeddedDriver

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Invalid
• Affects Version/s: 10.6.2.1
• Component/s: JDBC
• Urgency: Urgent
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Jarek Przygódzki
• Created: 25/Nov/10 20:08
• Updated: 04/Feb/13 14:48
• Resolved: 22/Dec/11 02:40

Description

Statement.executeUpdate(insertSql, int[] columnIndexes) and Statement/executeUpdate(insertSql,Statement.RETURN_GENERATED_KEYS) does work, Statement.executeUpdate(String sql, String[] columnNames) doesn't.

Test program

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class GetGeneratedKeysTest {
static String createTableSql = "CREATE TABLE tbl (id integer primary key generated always as identity, name varchar(200))";
static String insertSql = "INSERT INTO tbl(name) values('value')";
static String driver = "org.apache.derby.jdbc.EmbeddedDriver";

static String[] idColName =

{ "id" }

;

public static void main(String[] args) throws Exception {

Class.forName(driver);
Connection conn = DriverManager
.getConnection("jdbc:derby:testDb;create=true");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs;
stmt.executeUpdate(createTableSql);
stmt.executeUpdate(insertSql, idColName);
rs = stmt.getGeneratedKeys();
if (rs.next())

{ int id = rs.getInt(1); }

conn.commit();
}
}

Result

Exception in thread "main" java.sql.SQLException: Table 'TBL' does not have an auto-generated column named 'id'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2269)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1321)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:246)
at GetGeneratedKeysTest.main(GetGeneratedKeysTest.java:23)
Caused by: java.sql.SQLException: Table 'TBL' does not have an auto-generated column named 'id'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
... 9 more
Caused by: ERROR X0X0F: Table 'TBL' does not have an auto-generated column named 'id'.
at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:303)
at org.apache.derby.impl.sql.execute.InsertResultSet.verifyAutoGeneratedColumnsNames(InsertResultSet.java:689)
at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:419)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232)
... 3 more

Issue Links

• is related to: DERBY-4015 Regression in Statement.getGeneratedKeys()
• is related to: DERBY-2653 Expose existing auto-generated key functionality through more JDBC APIs in Derby Client.