statementregressiontest.java

mysql的jdbc驱动

		} 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 {
		try {
			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 {
			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 {
		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,
					(deltaOrig < epsillon));

			long pStmtDeltaTWithCal = (ts.getTime() - tsValuePstmtNoCal
					.getTime());

			System.out
					.println(Math.abs(pStmtDeltaTWithCal - pointInTimeOffset)
							+ " < "
							+ epsillon
							+ (Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) < epsillon));
			assertTrue(
					"Difference between original timestamp and timestamp retrieved using java.sql.PreparedStatement "
							+ "set in database using UTC calendar is not ~= "
							+ epsillon
							+ ", it is actually "
							+ pStmtDeltaTWithCal, (Math.abs(pStmtDeltaTWithCal
							- pointInTimeOffset) < epsillon));

			System.out
					.println("Difference between original ts and ts with no calendar: "
							+ (ts.getTime() - tsValuePstmtNoCal.getTime())
							+ ", offset should be " + pointInTimeOffset);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
		}
	}

	/**
	 * Tests that DataTruncation is thrown when data is truncated.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug3697() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
			this.stmt
					.executeUpdate("CREATE TABLE testBug3697 (field1 VARCHAR(255))");

			StringBuffer updateBuf = new StringBuffer(
					"INSERT INTO testBug3697 VALUES ('");

			for (int i = 0; i < 512; i++) {
				updateBuf.append("A");
			}

			updateBuf.append("')");

			try {
				this.stmt.executeUpdate(updateBuf.toString());
			} catch (DataTruncation dtEx) {
				// This is an expected exception....
			}

			SQLWarning warningChain = this.stmt.getWarnings();

			System.out.println(warningChain);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
		}
	}

	/**
	 * Tests fix for BUG#3804, data truncation on server should throw
	 * DataTruncation exception.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug3804() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
				this.stmt
						.executeUpdate("CREATE TABLE testBug3804 (field1 VARCHAR(5))");

				boolean caughtTruncation = false;

				try {
					this.stmt
							.executeUpdate("INSERT INTO testBug3804 VALUES ('1234567')");
				} catch (DataTruncation truncationEx) {
					caughtTruncation = true;
					System.out.println(truncationEx);
				}

				assertTrue("Data truncation exception should've been thrown",
						caughtTruncation);
			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
			}
		}
	}

	/**
	 * Tests BUG#3873 - PreparedStatement.executeBatch() not returning all
	 * generated keys (even though that's not JDBC compliant).
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug3873() throws Exception {
		PreparedStatement batchStmt = null;

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
			this.stmt
					.executeUpdate("CREATE TABLE testBug3873 (keyField INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dataField VARCHAR(32))");
			batchStmt = this.conn.prepareStatement(
					"INSERT INTO testBug3873 (dataField) VALUES (?)",
					Statement.RETURN_GENERATED_KEYS);
			batchStmt.setString(1, "abc");
			batchStmt.addBatch();
			batchStmt.setString(1, "def");
			batchStmt.addBatch();
			batchStmt.setString(1, "ghi");
			batchStmt.addBatch();

			int[] updateCounts = batchStmt.executeBatch();

			this.rs = batchStmt.getGeneratedKeys();

			while (this.rs.next()) {
				System.out.println(this.rs.getInt(1));
			}

			this.rs = batchStmt.getGeneratedKeys();
			assertTrue(this.rs.next());
			assertTrue(1 == this.rs.getInt(1));
			assertTrue(this.rs.next());
			assertTrue(2 == this.rs.getInt(1));
			assertTrue(this.rs.next());
			assertTrue(3 == this.rs.getInt(1));
			assertTrue(!this.rs.next());
		} finally {
			if (batchStmt != null) {
				batchStmt.close();
			}

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

	/**
	 * Tests fix for BUG#4119 -- misbehavior in a managed environment from
	 * MVCSoft JDO
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug4119() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
			this.stmt.executeUpdate("CREATE TABLE `testBug4119` ("
					+ "`field1` varchar(255) NOT NULL default '',"
					+ "`field2` bigint(20) default NULL,"
					+ "`field3` int(11) default NULL,"
					+ "`field4` datetime default NULL,"
					+ "`field5` varchar(75) default NULL,"
					+ "`field6` varchar(75) default NULL,"
					+ "`field7` varchar(75) default NULL,"
					+ "`field8` datetime default NULL,"
					+ " PRIMARY KEY  (`field1`)" + ")");

			PreparedStatement pStmt = this.conn
					.prepareStatement("insert into testBug4119 (field2, field3,"
							+ "field4, field5, field6, field7, field8, field1) values (?, ?,"
							+ "?, ?, ?, ?, ?, ?)");

			pStmt.setString(1, "0");
			pStmt.setString(2, "0");
			pStmt.setTimestamp(3, new java.sql.Timestamp(System
					.currentTimeMillis()));
			pStmt.setString(4, "ABC");
			pStmt.setString(5, "DEF");
			pStmt.setString(6, "AA");
			pStmt.setTimestamp(7, new java.sql.Timestamp(System
					.currentTimeMillis()));
			pStmt.setString(8, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
			pStmt.executeUpdate();
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
		}
	}

	/**
	 * Tests fix for BUG#4311 - Error in JDBC retrieval of mediumint column when
	 * using prepared statements and binary result sets.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug4311() throws Exception {
		try {
			int lowValue = -8388608;
			int highValue = 8388607;

			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
			this.stmt
					.executeUpdate("CREATE TABLE testBug4311 (low MEDIUMINT, high MEDIUMINT)");
			this.stmt.executeUpdate("INSERT INTO testBug4311 VALUES ("
					+ lowValue + ", " + highValue + ")");

			PreparedStatement pStmt = this.conn
					.prepareStatement("SELECT low, high FROM testBug4311");
			this.rs = pStmt.executeQuery();
			assertTrue(this.rs.next());
			assertTrue(this.rs.getInt(1) == lowValue);
			assertTrue(this.rs.getInt(2) == highValue);
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
		}
	}

	/**
	 * Tests fix for BUG#4510 -- Statement.getGeneratedKeys() fails when key >
	 * 32767
	 * 
	 * @throws Exception
	 *             if the test fails
	 */
	public void testBug4510() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
			this.stmt.executeUpdate("CREATE TABLE testBug4510 ("
					+ "field1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
					+ "field2 VARCHAR(100))");
			this.stmt
					.executeUpdate("INSERT INTO testBug4510 (field1, field2) VALUES (32767, 'bar')");

			PreparedStatement p = this.conn.prepareStatement(
					"insert into testBug4510 (field2) values (?)",
					Statement.RETURN_GENERATED_KEYS);

			p.setString(1, "blah");

			p.executeUpdate();

			ResultSet rs = p.getGeneratedKeys();
			rs.next();
			System.out.println("Id: " + rs.getInt(1));
			rs.close();
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
		}
	}

	/**
	 * Server doesn't accept everything as a server-side prepared statement, so
	 * by default we scan for stuff it can't handle.
	 * 
	 * @throws SQLException
	 */
	public void testBug4718() throws SQLException {
		if (versionMeetsMinimum(4, 1, 0)
				&& ((com.mysql.jdbc.Connection) this.conn)
						.getUseServerPreparedStmts()) {
			this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT ?");
			assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

			this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT 1");
			assertTrue(this.pstmt instanceof com.mysql.jdbc.ServerPreparedStatement);

			this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT 1, ?");
			assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");
				this.stmt
						.executeUpdate("CREATE TABLE testBug4718 (field1 char(32))");

				this.pstmt = this.conn
						.prepareStatement("ALTER TABLE testBug4718 ADD INDEX (field1)");
				assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

				this.pstmt = this.conn.prepareStatement("SELECT 1");
				assertTrue(this.pstmt instanceof ServerPreparedStatement);

				this.pstmt = this.conn
						.prepareStatement("UPDATE testBug4718 SET field1=1");
				assertTrue(this.pstmt instanceof ServerPreparedStatement);

				this.pstmt = this.conn
						.prepareStatement("UPDATE testBug4718 SET field1=1 LIMIT 1");
				assertTrue(this.pstmt instanceof ServerPreparedStatement);

				this.pstmt = this.conn
						.prepareStatement("UPDATE testBug4718 SET field1=1 LIMIT ?");
				assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);