autogeneratedjdbc30.java

derby database source code.good for you.

			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) values (99)", 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) values (99)", 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 7 - Now try again inserting multiple rows into a table with autogenerated key and request generated keys resultset");
			System.out.println(" This time we will get a row of non-NULL value because there has been a prior one-row insert into table with auto-generated 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 8 - create a new statement and request for generated keys on it after doing an insert into ");
			System.out.println(" a table with no auto generated keys");
			Statement s1 = conn.createStatement();
			s1.execute("insert into t21 values(27, 'true')", Statement.RETURN_GENERATED_KEYS);
			System.out.println("We should get a resultset with one row of non-NULL value");
			dumpRS(s1.getGeneratedKeys());
			System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
			s1.executeUpdate("insert into t21 values(28, 'true')", Statement.RETURN_GENERATED_KEYS);
			dumpRS(s1.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(29, '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(30, 'true')", Statement.RETURN_GENERATED_KEYS);
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 9 - request for generated keys on a statement which does a update ");
			s.execute("update t11 set c11=1", Statement.RETURN_GENERATED_KEYS);
			System.out.println("We should get a resultset with no rows from getGeneratedKeys because we executed a non-insert sql");
			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("update t11 set c11=1", 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("update t11 set c11=1", 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 10 - request for generated keys on a statement which does a delete ");
			s.execute("delete from t11", Statement.RETURN_GENERATED_KEYS);
			System.out.println("We should get a resultset with no rows from getGeneratedKeys because we executed a non-insert sql");
			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("delete from t11", 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("delete from t11", 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 11 - do a commit and request for generated keys on a statement which does insert into a table with ");
			System.out.println(" no auto generated keys (previous transaction had a one-row insert on a table with auto-generated keys)");
			conn.commit();
			s.execute("insert into t21 values(31, 'true')", Statement.RETURN_GENERATED_KEYS);
			System.out.println("expected to see resultset with one row of NULL value but instead get one row of non-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(32, '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(33, '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(34, 'true')", Statement.RETURN_GENERATED_KEYS);
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 12 - do a rollback and request for generated keys on a statement which does insert into a table with ");
			System.out.println(" no auto generated keys (previous transaction had a one-row insert on a table with auto-generated keys)");
			conn.rollback();
			s.execute("insert into t21 values(35, 'true')", Statement.RETURN_GENERATED_KEYS);
			System.out.println("had expected to see resultset with one row of NULL value but instead get one row of non-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(36, '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(37, '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(38, 'true')", Statement.RETURN_GENERATED_KEYS);
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 13 - try savepoint rollback and see what happens to auto generated keys resultset");
			Savepoint savepoint1;
			System.out.println("Inside the savepoint unit, issue a one-row insert into a table with auto generated keys");
			savepoint1 = conn.setSavepoint();
			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());
			System.out.println("Now rollback the savepoint unit, and make sure that autogenerated keys resultset still holds on to ");
			System.out.println(" value that got set inside the rolled back savepoint unit");
			conn.rollback(savepoint1);
			s.execute("insert into t21 values(39, 'true')", Statement.RETURN_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());

			System.out.println("Test 14 - Look at metadata of a getGeneratedKeys resultset");
			s = conn.createStatement();
			s.execute("insert into t31(c31) values (99)", Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = s.getGeneratedKeys();
			ResultSetMetaData rsmd = rs.getMetaData();
			System.out.println("The resultset will have one column only");
			System.out.println("Found " + rsmd.getColumnCount() + " column in the resultset");
			System.out.println("Type of the column is " + rsmd.getColumnTypeName(1));
			System.out.println("Precision of the column is " + rsmd.getPrecision(1));
			System.out.println("Scale of the column is " + rsmd.getScale(1));
			dumpRS(rs);

			System.out.println("Test 15 - Can not see the auto generated keys if insert is with NO_GENERATED_KEYS");
			s = conn.createStatement();
			s.execute("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			ps = conn.prepareStatement("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 16 - Can not see the auto generated keys if insert is done jdbc 2.0 way ie with no generated key feature");
			s.execute("insert into t31(c31) values (99)");
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("insert into t31(c31) values (99)");
			dumpRS(s.getGeneratedKeys());
			ps = conn.prepareStatement("insert into t31(c31) values (99)");
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 17 - non-insert with NO_GENERATED_KEYS");
			s = conn.createStatement();
			s.execute("update t31 set c31=98", Statement.NO_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("update t31 set c31=98", Statement.NO_GENERATED_KEYS);
			dumpRS(s.getGeneratedKeys());
			ps = conn.prepareStatement("update t31 set c31=98", Statement.NO_GENERATED_KEYS);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			System.out.println("Test 18 - non-insert is done jdbc 2.0 way ie with no generated key feature");
			s.execute("delete from t31");
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("delete from t31");
			dumpRS(s.getGeneratedKeys());
			ps = conn.prepareStatement("delete from t31");
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());
  }

	public static void negativeTests(Connection con) throws SQLException
	{
			Statement s = con.createStatement();
			PreparedStatement ps;
			//Test21 - insert select with columnIndexes[] array
			System.out.println("Test21 - insert select with columnIndexes[] array should fail");
			int colPositions[] = new int[1];
      colPositions[0] = 1;
			try {
				System.out.println("Try passing array with Statement.execute");
				s.execute("insert into t11(c11) select c21 from t21", colPositions);
				System.out.println("ERROR: shouldn't be able to pass array with Statement.execute");

			} catch (SQLException e) {
				if ((e.getMessage() != null &&
					 e.getMessage().indexOf("Driver not capable") >= 0)
					|| (e.getSQLState() != null &&
						(e.getSQLState().startsWith("0A"))))
					System.out.println("PASS - expected exception - Feature not implemented");
				else System.out.println("Unexpected FAILURE at " +e);

			}
			try {
				System.out.println("Try passing array with Statement.executeUpdate");
				s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
				System.out.println("ERROR: shouldn't be able to pass array with Statement.executeUpdate");
			} catch (SQLException e) {
				if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
					dumpExpectedSQLExceptions(e);
				else
					JDBCDisplayUtil.ShowSQLException(System.out,e);
			}

			System.out.println("Test21ps - insert select with columnIndexes[] array should fail");
			try {
				System.out.println("Try passing array with Connection.prepareStatement");
				ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
				System.out.println("ERROR: shouldn't be able to pass array with Connection.prepareStatement");
			} catch (SQLException e) {
				if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
					dumpExpectedSQLExceptions(e);
				else
					JDBCDisplayUtil.ShowSQLException(System.out,e);
			}

			//Test22 - insert select with columnNames[] array
			System.out.println("Test22 - insert select with columnNames[] array should fail");
			String colNames[] = new String[1];
      colNames[0] = "C11";
			try {
				System.out.println("Try passing array with Statement.execute");
				s.execute("insert into t11(c11) select c21 from t21", colNames);
				System.out.println("ERROR: shouldn't be able to pass array with Statement.execute");
			} catch (SQLException e) {
				if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
					dumpExpectedSQLExceptions(e);
				else
					JDBCDisplayUtil.ShowSQLException(System.out,e);
			}
			try {
				System.out.println("Try passing array with Statement.executeUpdate");
				s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
				System.out.println("ERROR: shouldn't be able to pass array with Statement.executeUpdate");
			} catch (SQLException e) {
				if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
					dumpExpectedSQLExceptions(e);
				else
					JDBCDisplayUtil.ShowSQLException(System.out,e);
			}

			System.out.println("Test22ps - insert select with columnNames[] array should fail");
			try {
				System.out.println("Try passing array with Connection.prepareStatement");
				ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
				System.out.println("ERROR: shouldn't be able to pass array with Connection.prepareStatement");
			} catch (SQLException e) {
				if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
					dumpExpectedSQLExceptions(e);
				else
					JDBCDisplayUtil.ShowSQLException(System.out,e);
			}
	}

	public static void doTest1920(Statement s, Connection con) throws SQLException
	{
			//Test19 - bug 4838 no auto generated key resultset generated for INSERT with
			//generated keys if server-side methods are invoked.
			//Adding the tests to try server side methods which toggle the auto generated flag
			//and make sure we don't loose the client side setting in the process
			System.out.println("Test19 - fix the no auto generated key resultset generated for INSERT with " +
			"generated keys if server-side methods are invoked");
			s.execute("CREATE FUNCTION MMWNI() RETURNS VARCHAR(20) LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.jdbcapi.autoGeneratedJdbc30.MyMethodWithNoInsert' PARAMETER STYLE JAVA READS SQL DATA");
			con.commit();
			s.execute("insert into t21 values(40,MMWNI())", Statement.RETURN_GENERATED_KEYS);
			System.out.println("Back to client side looking for auto generated keys");
			dumpRS(s.getGeneratedKeys());

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

			s.execute("DROP FUNCTION MMWNI");
			con.commit();
			s.close();

			// Test 20 - BUG 4837 garbage collection of the generated key result sets was closing the activation.
			System.out.println("Test20 - bug 4837garbage collection of the generated key result sets was closing the activation.");
			PreparedStatement ps = con.prepareStatement("insert into t11(c11) values(?)", Statement.RETURN_GENERATED_KEYS);

			for (int i = 0; i < 100; i++) {
				ps.setInt(1, 100+i);
				ps.executeUpdate();

				ResultSet rs = ps.getGeneratedKeys();
				while (rs.next()) {
					rs.getInt(1);
				}
				rs.close();
				con.commit();

				System.runFinalization();
				System.gc();
				System.runFinalization();
				System.gc();
			}
	}
}