statementregressiontest.java

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

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

	/**
	 * Tests fix for BUG#1901 -- PreparedStatement.setObject(int, Object, int,
	 * int) doesn't support CLOB or BLOB types.
	 * 
	 * @throws Exception
	 *             if this test fails for any reason
	 */
	public void testBug1901() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
			this.stmt
					.executeUpdate("CREATE TABLE testBug1901 (field1 VARCHAR(255))");
			this.stmt.executeUpdate("INSERT INTO testBug1901 VALUES ('aaa')");

			this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug1901");
			this.rs.next();

			Clob valueAsClob = this.rs.getClob(1);
			Blob valueAsBlob = this.rs.getBlob(1);

			PreparedStatement pStmt = this.conn
					.prepareStatement("INSERT INTO testBug1901 VALUES (?)");
			pStmt.setObject(1, valueAsClob, java.sql.Types.CLOB, 0);
			pStmt.executeUpdate();
			pStmt.setObject(1, valueAsBlob, java.sql.Types.BLOB, 0);
			pStmt.executeUpdate();
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
		}
	}

	/**
	 * Test fix for BUG#1933 -- Driver property 'maxRows' has no effect.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug1933() throws Exception {
		if (versionMeetsMinimum(4, 0)) {
			Connection maxRowsConn = null;
			PreparedStatement maxRowsPrepStmt = null;
			Statement maxRowsStmt = null;

			try {
				Properties props = new Properties();

				props.setProperty("maxRows", "1");

				maxRowsConn = getConnectionWithProps(props);

				maxRowsStmt = maxRowsConn.createStatement();

				assertTrue(maxRowsStmt.getMaxRows() == 1);

				this.rs = maxRowsStmt.executeQuery("SELECT 1 UNION SELECT 2");

				this.rs.next();

				maxRowsPrepStmt = maxRowsConn
						.prepareStatement("SELECT 1 UNION SELECT 2");

				assertTrue(maxRowsPrepStmt.getMaxRows() == 1);

				this.rs = maxRowsPrepStmt.executeQuery();

				this.rs.next();

				assertTrue(!this.rs.next());

				props.setProperty("useServerPrepStmts", "false");

				maxRowsConn = getConnectionWithProps(props);

				maxRowsPrepStmt = maxRowsConn
						.prepareStatement("SELECT 1 UNION SELECT 2");

				assertTrue(maxRowsPrepStmt.getMaxRows() == 1);

				this.rs = maxRowsPrepStmt.executeQuery();

				this.rs.next();

				assertTrue(!this.rs.next());
			} finally {
				maxRowsConn.close();
			}
		}
	}

	/**
	 * Tests the fix for BUG#1934 -- prepareStatement dies silently when
	 * encountering Statement.RETURN_GENERATED_KEY
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug1934() throws Exception {
		if (isRunningOnJdk131()) {
			return; // test not valid on JDK-1.3.1
		}

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
			this.stmt.executeUpdate("CREATE TABLE testBug1934 (field1 INT)");

			System.out.println("Before prepareStatement()");

			this.pstmt = this.conn.prepareStatement(
					"INSERT INTO testBug1934 VALUES (?)",
					java.sql.Statement.RETURN_GENERATED_KEYS);

			assertTrue(this.pstmt != null);

			System.out.println("After prepareStatement() - " + this.pstmt);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
		}
	}

	/**
	 * Tests fix for BUG#1958 - Improper bounds checking on
	 * PreparedStatement.setFoo().
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug1958() throws Exception {
		PreparedStatement pStmt = null;

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1958");
			this.stmt.executeUpdate("CREATE TABLE testBug1958 (field1 int)");

			pStmt = this.conn
					.prepareStatement("SELECT * FROM testBug1958 WHERE field1 IN (?, ?, ?)");

			try {
				pStmt.setInt(4, 1);
			} catch (SQLException sqlEx) {
				assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
						.getSQLState()));
			}
		} finally {
			if (pStmt != null) {
				pStmt.close();
			}

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

	/**
	 * Tests the fix for BUG#2606, server-side prepared statements not returning
	 * datatype YEAR correctly.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug2606() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2606");
			this.stmt
					.executeUpdate("CREATE TABLE testBug2606(year_field YEAR)");
			this.stmt.executeUpdate("INSERT INTO testBug2606 VALUES (2004)");

			PreparedStatement yrPstmt = this.conn
					.prepareStatement("SELECT year_field FROM testBug2606");

			this.rs = yrPstmt.executeQuery();

			assertTrue(this.rs.next());

			assertEquals(2004, this.rs.getInt(1));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2606");
		}
	}

	/**
	 * Tests the fix for BUG#2671, nulls encoded incorrectly in server-side
	 * prepared statements.
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testBug2671() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS test3");
				this.stmt
						.executeUpdate("CREATE TABLE test3 ("
								+ " `field1` int(8) NOT NULL auto_increment,"
								+ " `field2` int(8) unsigned zerofill default NULL,"
								+ " `field3` varchar(30) binary NOT NULL default '',"
								+ " `field4` varchar(100) default NULL,"
								+ " `field5` datetime NULL default '0000-00-00 00:00:00',"
								+ " PRIMARY KEY  (`field1`),"
								+ " UNIQUE KEY `unq_id` (`field2`),"
								+ " UNIQUE KEY  (`field3`),"
								+ " UNIQUE KEY  (`field2`)"
								+ " ) TYPE=InnoDB CHARACTER SET utf8");

				this.stmt
						.executeUpdate("insert into test3 (field1, field3, field4) values (1,'blewis','Bob Lewis')");

				String query = "              " + "UPDATE                   "
						+ "  test3                  "
						+ "SET                      "
						+ "  field2=?               " + "  ,field3=?          "
						+ "  ,field4=?           " + "  ,field5=?        "
						+ "WHERE                    "
						+ "  field1 = ?                 ";

				java.sql.Date mydate = null;

				this.pstmt = this.conn.prepareStatement(query);

				this.pstmt.setInt(1, 13);
				this.pstmt.setString(2, "abc");
				this.pstmt.setString(3, "def");
				this.pstmt.setDate(4, mydate);
				this.pstmt.setInt(5, 1);

				int retval = this.pstmt.executeUpdate();
				assertTrue(retval == 1);
			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS test3");
			}
		}
	}

	/**
	 * Tests fix for BUG#3103 -- java.util.Date not accepted as parameter to
	 * PreparedStatement.setObject().
	 * 
	 * @throws Exception
	 *             if the test fails
	 * 
	 * @deprecated uses deprecated methods of Date class
	 */
	public void testBug3103() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
			this.stmt
					.executeUpdate("CREATE TABLE testBug3103 (field1 DATETIME)");

			PreparedStatement pStmt = this.conn
					.prepareStatement("INSERT INTO testBug3103 VALUES (?)");

			java.util.Date utilDate = new java.util.Date();

			pStmt.setObject(1, utilDate);
			pStmt.executeUpdate();

			this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug3103");
			this.rs.next();

			java.util.Date retrUtilDate = new java.util.Date(this.rs
					.getTimestamp(1).getTime());

			// We can only compare on the day/month/year hour/minute/second
			// interval, because the timestamp has added milliseconds to the
			// internal date...
			assertTrue("Dates not equal", (utilDate.getMonth() == retrUtilDate
					.getMonth())
					&& (utilDate.getDate() == retrUtilDate.getDate())
					&& (utilDate.getYear() == retrUtilDate.getYear())
					&& (utilDate.getHours() == retrUtilDate.getHours())
					&& (utilDate.getMinutes() == retrUtilDate.getMinutes())
					&& (utilDate.getSeconds() == retrUtilDate.getSeconds()));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
		}
	}

	/**
	 * Tests fix for BUG#3520
	 * 
	 * @throws Exception
	 *             ...
	 */
	public void testBug3520() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS t");
			this.stmt.executeUpdate("CREATE TABLE t (s1 int,primary key (s1))");
			this.stmt.executeUpdate("INSERT INTO t VALUES (1)");
			this.stmt.executeUpdate("INSERT INTO t VALUES (1)");
		} catch (SQLException sqlEx) {
			System.out.println(sqlEx.getSQLState());
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS t");
		}
	}

	/**
	 * Test fix for BUG#3557 -- UpdatableResultSet not picking up default values
	 * 
	 * @throws Exception
	 *             if test fails.
	 */
	public void testBug3557() throws Exception {
		boolean populateDefaults = ((com.mysql.jdbc.ConnectionProperties) this.conn)
				.getPopulateInsertRowWithDefaultValues();

		try {
			((com.mysql.jdbc.ConnectionProperties) this.conn)
					.setPopulateInsertRowWithDefaultValues(true);

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

			this.stmt.executeUpdate("CREATE TABLE testBug3557 ( "
					+ "`a` varchar(255) NOT NULL default 'XYZ', "
					+ "`b` varchar(255) default '123', "
					+ "PRIMARY KEY  (`a`))");

			Statement updStmt = this.conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			this.rs = updStmt.executeQuery("SELECT * FROM testBug3557");

			assertTrue(this.rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE);

			this.rs.moveToInsertRow();

			assertEquals("XYZ", this.rs.getObject(1));
			assertEquals("123", this.rs.getObject(2));
		} finally {
			((com.mysql.jdbc.ConnectionProperties) this.conn)
					.setPopulateInsertRowWithDefaultValues(populateDefaults);

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

	/**
	 * Tests fix for BUG#3620 -- Timezone not respected correctly.
	 * 
	 * @throws SQLException
	 *             if the test fails.
	 */
	public void testBug3620() throws SQLException {
		if (isRunningOnJRockit()) {
			// bug with their timezones
			return;
		}
		
		if (isRunningOnJdk131()) {
			// bug with timezones, no update
			// for new DST in USA
			return;
		}
		
		long epsillon = 3000; // 3 seconds time difference

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
			this.stmt
					.executeUpdate("CREATE TABLE testBug3620 (field1 TIMESTAMP)");

			PreparedStatement tsPstmt = this.conn
					.prepareStatement("INSERT INTO testBug3620 VALUES (?)");

			Calendar pointInTime = Calendar.getInstance();
			pointInTime.set(2004, 02, 29, 10, 0, 0);

			long pointInTimeOffset = pointInTime.getTimeZone().getRawOffset();

			java.sql.Timestamp ts = new java.sql.Timestamp(pointInTime
					.getTime().getTime());

			tsPstmt.setTimestamp(1, ts);
			tsPstmt.executeUpdate();

			String tsValueAsString = getSingleValue("testBug3620", "field1",
					null).toString();

			System.out.println("Timestamp as string with no calendar: "
					+ tsValueAsString.toString());

			Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

			this.stmt.executeUpdate("DELETE FROM testBug3620");

			Properties props = new Properties();
			props.put("useTimezone", "true");
			// props.put("serverTimezone", "UTC");

			Connection tzConn = getConnectionWithProps(props);

			Statement tsStmt = tzConn.createStatement();

			tsPstmt = tzConn
					.prepareStatement("INSERT INTO testBug3620 VALUES (?)");

			tsPstmt.setTimestamp(1, ts, cal);
			tsPstmt.executeUpdate();

			tsValueAsString = getSingleValue("testBug3620", "field1", null)
					.toString();

			Timestamp tsValueAsTimestamp = (Timestamp) getSingleValue(
					"testBug3620", "field1", null);

			System.out.println("Timestamp as string with UTC calendar: "
					+ tsValueAsString.toString());
			System.out.println("Timestamp as Timestamp with UTC calendar: "
					+ tsValueAsTimestamp);

			this.rs = tsStmt.executeQuery("SELECT field1 FROM testBug3620");
			this.rs.next();

			Timestamp tsValueUTC = this.rs.getTimestamp(1, cal);

			//
			// We use this testcase with other vendors, JDBC spec
			// requires result set fields can only be read once,
			// although MySQL doesn't require this ;)
			//
			this.rs = tsStmt.executeQuery("SELECT field1 FROM testBug3620");
			this.rs.next();

			Timestamp tsValueStmtNoCal = this.rs.getTimestamp(1);

			System.out
					.println("Timestamp specifying UTC calendar from normal statement: "
							+ tsValueUTC.toString());

			PreparedStatement tsPstmtRetr = tzConn
					.prepareStatement("SELECT field1 FROM testBug3620");

			this.rs = tsPstmtRetr.executeQuery();
			this.rs.next();

			Timestamp tsValuePstmtUTC = this.rs.getTimestamp(1, cal);

			System.out
					.println("Timestamp specifying UTC calendar from prepared statement: "
							+ tsValuePstmtUTC.toString());

			//
			// We use this testcase with other vendors, JDBC spec
			// requires result set fields can only be read once,
			// although MySQL doesn't require this ;)
			//
			this.rs = tsPstmtRetr.executeQuery();
			this.rs.next();

			Timestamp tsValuePstmtNoCal = this.rs.getTimestamp(1);

			System.out
					.println("Timestamp specifying no calendar from prepared statement: "
							+ tsValuePstmtNoCal.toString());

			long stmtDeltaTWithCal = (ts.getTime() - tsValueStmtNoCal.getTime());

			long deltaOrig = Math.abs(stmtDeltaTWithCal - pointInTimeOffset);

			assertTrue(
					"Difference between original timestamp and timestamp retrieved using java.sql.Statement "
							+ "set in database using UTC calendar is not ~= "
							+ epsillon + ", it is actually " + deltaOrig,