db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Philip Ogren (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-4921) Statement.executeUpdate(String sql, String[] columnNames) throws ERROR X0X0F.S exception with EmbeddedDriver
Date Fri, 01 Feb 2013 22:54:13 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13569197#comment-13569197
] 

Philip Ogren commented on DERBY-4921:
-------------------------------------

I prepared some code that demonstrates the behavior that I ran on the latest driver (10.9.1.0).
 I don't see away to attach it so I will just paste it here.  Hopefully, it will render ok.

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

/**
 * to run testClientDriver first start the Derby server with something like:
 *  java -jar derbyrun.jar server start
 *  
 *  to run testEmbeddedDriver change the local variable 'location' to some directory that
exists on your system.
 */

public class DerbyTest {

	public static final String CREATE_TABLE_SQL = "create table test_table (id int generated
always as identity primary key, name varchar(100))"; 
	public static final String INSERT_SQL = "insert into test_table (name) values (?)";

	public static void testClientDriver() throws Exception {
		String driver = "org.apache.derby.jdbc.ClientDriver";
		Class.forName(driver).newInstance();
		Connection connection = DriverManager.getConnection("jdbc:derby://localhost:1527/testdb;create=true");
		connection.prepareStatement("drop table test_table").execute();
        connection.prepareStatement(CREATE_TABLE_SQL).execute();
		for(String columnName : new String[] {"ID", "id"}) {
			PreparedStatement statement = connection.prepareStatement(INSERT_SQL, new String[] { columnName});
			statement.setString(1, "my name");
			statement.executeUpdate();
			ResultSet results = statement.getGeneratedKeys();
			results.next();
			int id = results.getInt(1);
			System.out.println(id);
		}
	}

	public static void testEmbeddedDriver() throws Exception {
		String driver = "org.apache.derby.jdbc.EmbeddedDriver";
		Class.forName(driver).newInstance();
		String location = "D:\\temp";
		Connection connection = DriverManager.getConnection("jdbc:derby:"+location+"\\testdb;create=true");
		connection.prepareStatement("drop table test_table").execute();
                connection.prepareStatement(CREATE_TABLE_SQL).execute();
		for(String columnName : new String[] {"ID", "id"}) {
			PreparedStatement statement = connection.prepareStatement(INSERT_SQL, new String[] { columnName});
			statement.setString(1, "my name");
			statement.executeUpdate();
			ResultSet results = statement.getGeneratedKeys();
			results.next();
			int id = results.getInt(1);
			System.out.println("column name="+columnName+": "+id);
		}
	}

	public static void main(String[] args) throws Exception {
		testClientDriver();
		testEmbeddedDriver();
	}
}

                
> Statement.executeUpdate(String sql, String[] columnNames)  throws ERROR  X0X0F.S exception
with EmbeddedDriver
> --------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4921
>                 URL: https://issues.apache.org/jira/browse/DERBY-4921
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.6.2.1
>            Reporter: Jarek Przygódzki
>
> 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

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message