resultsetregressiontest.java

开发MySql数据库的最新JDBC驱动。

			this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
		}
	}

	/**
	 * Tests that ResultSet.getLong() does not truncate values.
	 * 
	 * @throws Exception
	 *             if any errors occur
	 */
	public void testGetLongBug() throws Exception {
		this.stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
		this.stmt
				.executeUpdate("CREATE TABLE IF NOT EXISTS getLongBug (int_col int, bigint_col bigint)");

		int intVal = 123456;
		long longVal1 = 123456789012345678L;
		long longVal2 = -2079305757640172711L;
		this.stmt.executeUpdate("INSERT INTO getLongBug "
				+ "(int_col, bigint_col) " + "VALUES (" + intVal + ", "
				+ longVal1 + "), " + "(" + intVal + ", " + longVal2 + ")");

		try {
			this.rs = this.stmt
					.executeQuery("SELECT int_col, bigint_col FROM getLongBug ORDER BY bigint_col DESC");
			this.rs.next();
			assertTrue(
					"Values not decoded correctly",
					((this.rs.getInt(1) == intVal) && (this.rs.getLong(2) == longVal1)));
			this.rs.next();
			assertTrue(
					"Values not decoded correctly",
					((this.rs.getInt(1) == intVal) && (this.rs.getLong(2) == longVal2)));
		} finally {
			if (this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception ex) {
					// ignore
				}
			}

			this.stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
		}
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws Exception
	 *             DOCUMENT ME!
	 */
	public void testGetTimestampWithDate() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
			this.stmt.executeUpdate("CREATE TABLE testGetTimestamp (d date)");
			this.stmt
					.executeUpdate("INSERT INTO testGetTimestamp values (now())");

			this.rs = this.stmt.executeQuery("SELECT * FROM testGetTimestamp");
			this.rs.next();
			System.out.println(this.rs.getTimestamp(1));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
		}
	}

	/**
	 * Tests a bug where ResultSet.isBefireFirst() would return true when the
	 * result set was empty (which is incorrect)
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testIsBeforeFirstOnEmpty() throws Exception {
		try {
			// Query with valid rows: isBeforeFirst() correctly returns True
			this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'version'");
			assertTrue("Non-empty search should return true", this.rs
					.isBeforeFirst());

			// Query with empty result: isBeforeFirst() falsely returns True
			// Sun's documentation says it should return false
			this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'garbage'");
			assertTrue("Empty search should return false ", !this.rs
					.isBeforeFirst());
		} finally {
			this.rs.close();
		}
	}

	/**
	 * Tests a bug where ResultSet.isBefireFirst() would return true when the
	 * result set was empty (which is incorrect)
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testMetaDataIsWritable() throws Exception {
		try {
			// Query with valid rows
			this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'version'");

			ResultSetMetaData rsmd = this.rs.getMetaData();

			int numColumns = rsmd.getColumnCount();

			for (int i = 1; i <= numColumns; i++) {
				assertTrue("rsmd.isWritable() should != rsmd.isReadOnly()",
						rsmd.isWritable(i) != rsmd.isReadOnly(i));
			}
		} finally {
			this.rs.close();
		}
	}

	/**
	 * Tests fix for bug # 496
	 * 
	 * @throws Exception
	 *             if an error happens.
	 */
	public void testNextAndPrevious() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
			this.stmt
					.executeUpdate("CREATE TABLE testNextAndPrevious (field1 int)");
			this.stmt
					.executeUpdate("INSERT INTO testNextAndPrevious VALUES (1)");

			this.rs = this.stmt
					.executeQuery("SELECT * from testNextAndPrevious");

			System.out.println("Currently at row " + this.rs.getRow());
			this.rs.next();
			System.out.println("Value at row " + this.rs.getRow() + " is "
					+ this.rs.getString(1));

			this.rs.previous();

			try {
				System.out.println("Value at row " + this.rs.getRow() + " is "
						+ this.rs.getString(1));
				fail("Should not be able to retrieve values with invalid cursor");
			} catch (SQLException sqlEx) {
				assertTrue(sqlEx.getMessage().startsWith("Before start"));
			}

			this.rs.next();

			this.rs.next();

			try {
				System.out.println("Value at row " + this.rs.getRow() + " is "
						+ this.rs.getString(1));
				fail("Should not be able to retrieve values with invalid cursor");
			} catch (SQLException sqlEx) {
				assertTrue(sqlEx.getMessage().startsWith("After end"));
			}
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
		}
	}

	/**
	 * Tests fix for BUG#1630 (not updatable exception turning into NPE on
	 * second updateFoo() method call.
	 * 
	 * @throws Exception
	 *             if an unexpected exception is thrown.
	 */
	public void testNotUpdatable() throws Exception {
		this.rs = null;

		try {
			String sQuery = "SHOW VARIABLES";
			this.pstmt = this.conn
					.prepareStatement(sQuery, ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);

			this.rs = this.pstmt.executeQuery();

			if (this.rs.next()) {
				this.rs.absolute(1);

				try {
					this.rs.updateInt(1, 1);
				} catch (SQLException sqlEx) {
					assertTrue(sqlEx instanceof NotUpdatable);
				}

				try {
					this.rs.updateString(1, "1");
				} catch (SQLException sqlEx) {
					assertTrue(sqlEx instanceof NotUpdatable);
				}
			}
		} finally {
			if (this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					// ignore
				}
			}
		}
	}

	/**
	 * Tests that streaming result sets are registered correctly.
	 * 
	 * @throws Exception
	 *             if any errors occur
	 */
	public void testStreamingRegBug() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
			this.stmt
					.executeUpdate("CREATE TABLE StreamingRegBug ( DUMMYID "
							+ " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
			this.stmt
					.executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
			this.stmt
					.executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
			this.stmt
					.executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
			this.stmt
					.executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");

			PreparedStatement streamStmt = null;

			try {
				streamStmt = this.conn.prepareStatement(
						"SELECT DUMMYID, DUMMYNAME "
								+ "FROM StreamingRegBug ORDER BY DUMMYID",
						java.sql.ResultSet.TYPE_FORWARD_ONLY,
						java.sql.ResultSet.CONCUR_READ_ONLY);
				streamStmt.setFetchSize(Integer.MIN_VALUE);

				this.rs = streamStmt.executeQuery();

				while (this.rs.next()) {
					this.rs.getString(1);
				}

				this.rs.close(); // error occurs here
			} catch (SQLException sqlEx) {

			} finally {
				if (streamStmt != null) {
					try {
						streamStmt.close();
					} catch (SQLException exWhileClose) {
						exWhileClose.printStackTrace();
					}
				}
			}
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
		}
	}

	/**
	 * Tests that result sets can be updated when all parameters are correctly
	 * set.
	 * 
	 * @throws Exception
	 *             if any errors occur
	 */
	public void testUpdatability() throws Exception {
		this.rs = null;

		this.stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
		this.stmt.execute("CREATE TABLE IF NOT EXISTS updatabilityBug ("
				+ " id int(10) unsigned NOT NULL auto_increment,"
				+ " field1 varchar(32) NOT NULL default '',"
				+ " field2 varchar(128) NOT NULL default '',"
				+ " field3 varchar(128) default NULL,"
				+ " field4 varchar(128) default NULL,"
				+ " field5 varchar(64) default NULL,"
				+ " field6 int(10) unsigned default NULL,"
				+ " field7 varchar(64) default NULL," + " PRIMARY KEY  (id)"
				+ ") TYPE=InnoDB;");
		this.stmt.executeUpdate("insert into updatabilityBug (id) values (1)");

		try {
			String sQuery = " SELECT * FROM updatabilityBug WHERE id = ? ";
			this.pstmt = this.conn
					.prepareStatement(sQuery, ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			this.conn.setAutoCommit(false);
			this.pstmt.setInt(1, 1);
			this.rs = this.pstmt.executeQuery();

			if (this.rs.next()) {
				this.rs.absolute(1);
				this.rs.updateInt("id", 1);
				this.rs.updateString("field1", "1");
				this.rs.updateString("field2", "1");
				this.rs.updateString("field3", "1");
				this.rs.updateString("field4", "1");
				this.rs.updateString("field5", "1");
				this.rs.updateInt("field6", 1);
				this.rs.updateString("field7", "1");
				this.rs.updateRow();
			}

			this.conn.commit();
			this.conn.setAutoCommit(true);
		} finally {
			if (this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					// ignore
				}
			}

			this.stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
		}
	}

	/**
	 * Test fixes for BUG#1071
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testUpdatabilityAndEscaping() throws Exception {
		Properties props = new Properties();
		props.setProperty("useUnicode", "true");
		props.setProperty("characterEncoding", "big5");

		Connection updConn = getConnectionWithProps(props);
		Statement updStmt = updConn.createStatement(
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

		try {
			updStmt
					.executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
			updStmt
					.executeUpdate("CREATE TABLE testUpdatesWithEscaping (field1 INT PRIMARY KEY, field2 VARCHAR(64))");
			updStmt
					.executeUpdate("INSERT INTO testUpdatesWithEscaping VALUES (1, null)");

			String stringToUpdate = "\" \\ '";

			this.rs = updStmt
					.executeQuery("SELECT * from testUpdatesWithEscaping");

			this.rs.next();
			this.rs.updateString(2, stringToUpdate);
			this.rs.updateRow();

			assertTrue(stringToUpdate.equals(this.rs.getString(2)));
		} finally {
			updStmt
					.executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
			updStmt.close();
			updConn.close();
		}
	}

	/**
	 * Tests the fix for BUG#661 ... refreshRow() fails when primary key values
	 * have escaped data in them.
	 * 
	 * @throws Exception
	 *             if an error occurs
	 */
	public void testUpdatabilityWithQuotes() throws Exception {
		Statement updStmt = null;

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdWithQuotes");
			this.stmt
					.executeUpdate("CREATE TABLE testUpdWithQuotes (keyField CHAR(32) PRIMARY KEY NOT NULL, field2 int)");

			PreparedStatement pStmt = this.conn
					.prepareStatement("INSERT INTO testUpdWithQuotes VALUES (?, ?)");
			pStmt.setString(1, "Abe's");
			pStmt.setInt(2, 1);
			pStmt.executeUpdate();

			updStmt = this.conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);

			this.rs = updStmt.executeQuery("SELECT * FROM testUpdWithQuotes");
			this.rs.next();
			this.rs.updateInt(2, 2);
			this.rs.updateRow();
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdWithQuotes");

			if (this.rs != null) {
				this.rs.close();
			}

			this.rs = null;

			if (updStmt != null) {
				updStmt.close();
			}

			updStmt = null;
		}
	}

	/**
	 * Checks whether or not ResultSet.updateClob() is implemented
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testUpdateClob() throws Exception {
		if (isRunningOnJdk131()) {
			return; // test not valid on JDK-1.3.1
		}

		Statement updatableStmt = this.conn.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdateClob");
			this.stmt
					.executeUpdate("CREATE TABLE testUpdateClob(intField INT NOT NULL PRIMARY KEY, clobField TEXT)");
			this.stmt
					.executeUpdate("INSERT INTO testUpdateClob VALUES (1, 'foo')");

			this.rs = updatableStmt
					.executeQuery("SELECT intField, clobField FROM testUpdateClob");
			this.rs.next();

			Clob clob = this.rs.getClob(2);

			clob.setString(1, "bar");

			this.rs.updateClob(2, clob);
			this.rs.updateRow();