autogeneratedjdbc30.java

derby database source code.good for you.

				return;
			}
			s.execute("delete from t11");

			//Test14 - insert select with columnNames[] array
			System.out.println("Test14 - insert select with columnNames[] array");
			String colNames[] = new String[1];
      colNames[0] = "C11";
			s.execute("insert into t11(c11) select c21 from t21", colNames);
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
			dumpRS(s.getGeneratedKeys());

			System.out.println("Test14ps - insert select with columnNames[] array");
			ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			// BUG 4836 Hey, actually fetch a generated column!!!!!!!!!!!!!
			colNames[0] = "C12";
			s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
			dumpRS(s.getGeneratedKeys());
			ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			//Verify data in the table
			if(count(con,s) != 12) {
				System.out.println("Test failed");
				return;
			}
			s.execute("delete from t11");

			//Test15 - insert select with columnNames[] array with duplicate column names
			System.out.println("Test15 - insert select with columnNames[] array with duplicate column names");
			colNames = new String[2];
      colNames[0] = "C11";
      colNames[1] = "C11";
			s.execute("insert into t11(c11) select c21 from t21", colNames);
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
			dumpRS(s.getGeneratedKeys());

			System.out.println("Test15ps - insert select with columnNames[] array with duplicate column names");
			ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			//Verify data in the table
			if(count(con,s) != 8) {
				System.out.println("Test failed");
				return;
			}
			s.execute("delete from t11");

			//Test16 - insert select with columnNames[] array with invalid column name
			colNames = new String[1];
			System.out.println("Test16 - insert select with columnNames[] array with invalid column name");
      colNames[0] = "C13";
			try {
				s.execute("insert into t11(c11) select c21 from t21", colNames);
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}

			System.out.println("Test16ps - insert select with columnNames[] array with invalid column name");
			try {
				ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
				ps.execute();
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				dumpRS(ps.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				ps.executeUpdate();
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				dumpRS(ps.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}

			//Verify data in the table
			if(count(con,s) != 0) {
				System.out.println("Test failed");
				return;
			}
			s.execute("delete from t11");

			//Test17 - insert select with null columnIndexes[] internally switches to Statement.NO_GENERATED_KEYS
			System.out.println("Test17 - insert select with null columnIndexes[] internally switches to Statement.NO_GENERATED_KEYS");
			colPositions = null;
			s.execute("insert into t11(c11) select c21 from t21", colPositions);
			try {
				dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
			try {
				dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}

			System.out.println("Test17ps - insert select with null columnIndexes[] internally switches to Statement.NO_GENERATED_KEYS");
			ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
			ps.execute();
			try {
				dumpRS(ps.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			ps.executeUpdate();
			try {
				dumpRS(ps.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}

			//Verify data in the table
			if(count(con,s) != 8) {
				System.out.println("Test failed");
				return;
			}
			s.execute("delete from t11");

			//Test18 - insert select with null columnNames[] internally switches to Statement.NO_GENERATED_KEYS
			System.out.println("Test18 - insert select with null columnNames[] internally switches to Statement.NO_GENERATED_KEYS");
			colNames = null;
			s.execute("insert into t11(c11) select c21 from t21", colNames);
			try {
				dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
			try {
				dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}

			System.out.println("Test18ps - insert select with null columnNames[] internally switches to Statement.NO_GENERATED_KEYS");
			ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
			ps.execute();
			try {
				dumpRS(ps.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			ps.executeUpdate();
			try {
				dumpRS(ps.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}

			//Verify data in the table
			if(count(con,s) != 8) {
				System.out.println("Test failed");
				return;
			}
			s.execute("delete from t11");

			//Test19a - insert values with column position order which doesn't match column positions in the actual table
			//The column positions correspond to columns in the table and not the columns in the insert statement
			System.out.println("Test19a - insert values with column position order which doesn't match column positions in the actual table");
			colPositions = new int[1];
			colPositions[0] = 1;
			s.execute("insert into t21(c22,c21) values('true', 23)", colPositions);
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("insert into t21(c22,c21) values('true', 23)", colPositions);
			dumpRS(s.getGeneratedKeys());

			//Test19aps - insert values with column position order which doesn't match column positions in the actual table
			System.out.println("Test19aps - insert values with column position order which doesn't match column positions in the actual table");
			ps = con.prepareStatement("insert into t21(c22,c21) values('true', 23)", colPositions);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			//Verify data in the table
			dumpRS(s.executeQuery("select count(*) from t21"));
			s.execute("delete from t11");
	}

	private static void positiveTests(Connection conn) throws SQLException
	{
			System.out.println("Test 1 - request for generated keys resultset on a brand new statement with no sql executed on it yet");
			System.out.println("We will get a resultset with no rows because it is a non-insert sql");
			Statement s = conn.createStatement();
			dumpRS(s.getGeneratedKeys());

			System.out.println("Test2 - request for generated keys on a statement which does select from a table ie a non-insert sql");
			s.execute("select * from t11", Statement.RETURN_GENERATED_KEYS);
			System.out.println("We will get a resultset with no rows because it is a non-insert sql");
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
			PreparedStatement ps = conn.prepareStatement("select * from t11", Statement.RETURN_GENERATED_KEYS);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 3 - insert multiple rows into a table with autogenerated key and request generated keys resultset");
			System.out.println(" We will get a row with NULL value because this insert sql inserted more than one row and ");
			System.out.println(" there was no prior one-row insert into a table with autogenerated key");
			s.execute("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
			s.executeUpdate("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
			ps = conn.prepareStatement("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 4 - request for generated keys after doing an insert into a table with no auto generated keys");
			System.out.println(" And there has been no one-row insert into a table with auto-generated keys yet.");
			s.execute("insert into t21 values(23, 'true')", Statement.RETURN_GENERATED_KEYS);
			System.out.println("We should get a resultset with one row of NULL value from getGeneratedKeys");
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
			s.executeUpdate("insert into t21 values(24, 'true')", Statement.RETURN_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
			ps = conn.prepareStatement("insert into t21 values(25, 'true')", Statement.RETURN_GENERATED_KEYS);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
			ps = conn.prepareStatement("insert into t21 values(26, 'true')", Statement.RETURN_GENERATED_KEYS);
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 5a - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys");
			s.execute("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS);
			System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value");
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
			s.executeUpdate("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
			ps = conn.prepareStatement("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 5b - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys");
			s.execute("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS);
			System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value");
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
			s.executeUpdate("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
			ps = conn.prepareStatement("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 5c - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys");
			s.execute("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS);
			System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value");
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
			s.executeUpdate("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
			ps = conn.prepareStatement("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 6 - request for generated keys after doing a one-row insert into a table with auto generated keys");
			s.execute("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS);
			System.out.println("We should get a resultset with one row of non-NULL value");
			dumpRS(s.getGeneratedKeys());