statementregressiontest.java

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

						+ ")" + " VALUES (\"" + pname0 + "\")");

				this.rs = utfStmt.executeQuery("SELECT " + column + " FROM "
						+ table);

				this.rs.first();
				String pname1 = this.rs.getString(column);

				assertEquals(pname0, pname1);
				byte[] bytes = this.rs.getBytes(column);

				String pname2 = new String(bytes, "utf-8");
				assertEquals(pname1, pname2);

				utfStmt.executeUpdate("delete from " + table + " where "
						+ column + " like 'insert%'");

				PreparedStatement s1 = utf8Conn.prepareStatement("insert into "
						+ table + "(" + column + ") values (?)");

				s1.setString(1, pname0);
				s1.executeUpdate();

				String byteesque = "byte " + pname0;
				byte[] newbytes = byteesque.getBytes("utf-8");

				s1.setBytes(1, newbytes);
				s1.executeUpdate();

				this.rs = utfStmt.executeQuery("select " + column + " from "
						+ table + " where " + column + " like 'insert%'");
				this.rs.first();
				String pname3 = this.rs.getString(column);
				assertEquals(pname0, pname3);

				this.rs = utfStmt.executeQuery("select " + column + " from "
						+ table + " where " + column + " like 'byte insert%'");
				this.rs.first();

				String pname4 = this.rs.getString(column);
				assertEquals(byteesque, pname4);

			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS " + table);
			}
		}
	}

	public void testBug5510() throws Exception {
		// This is a server bug that should be fixed by 4.1.6
		if (versionMeetsMinimum(4, 1, 6)) {
			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5510");

				this.stmt
						.executeUpdate("CREATE TABLE `testBug5510` ("
								+ "`a` bigint(20) NOT NULL auto_increment,"
								+ "`b` varchar(64) default NULL,"
								+ "`c` varchar(64) default NULL,"
								+ "`d` varchar(255) default NULL,"
								+ "`e` int(11) default NULL,"
								+ "`f` varchar(32) default NULL,"
								+ "`g` varchar(32) default NULL,"
								+ "`h` varchar(80) default NULL,"
								+ "`i` varchar(255) default NULL,"
								+ "`j` varchar(255) default NULL,"
								+ "`k` varchar(255) default NULL,"
								+ "`l` varchar(32) default NULL,"
								+ "`m` varchar(32) default NULL,"
								+ "`n` timestamp NOT NULL default CURRENT_TIMESTAMP on update"
								+ " CURRENT_TIMESTAMP,"
								+ "`o` int(11) default NULL,"
								+ "`p` int(11) default NULL,"
								+ "PRIMARY KEY  (`a`)"
								+ ") ENGINE=InnoDB DEFAULT CHARSET=latin1");
				PreparedStatement pStmt = this.conn
						.prepareStatement("INSERT INTO testBug5510 (a) VALUES (?)");
				pStmt.setNull(1, 0);
				pStmt.executeUpdate();

			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5510");
			}
		}
	}

	/**
	 * Tests fix for BUG#5874, timezone correction goes in wrong 'direction'
	 * when useTimezone=true and server timezone differs from client timezone.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug5874() throws Exception {
		/*
		try {
			String clientTimezoneName = "America/Los_Angeles";
			String serverTimezoneName = "America/Chicago";

			TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));

			long epsillon = 3000; // 3 seconds difference

			long clientTimezoneOffsetMillis = TimeZone.getDefault()
					.getRawOffset();
			long serverTimezoneOffsetMillis = TimeZone.getTimeZone(
					serverTimezoneName).getRawOffset();

			long offsetDifference = clientTimezoneOffsetMillis
					- serverTimezoneOffsetMillis;

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

			Connection tzConn = getConnectionWithProps(props);
			Statement tzStmt = tzConn.createStatement();
			tzStmt.executeUpdate("DROP TABLE IF EXISTS timeTest");
			tzStmt
					.executeUpdate("CREATE TABLE timeTest (tstamp DATETIME, t TIME)");

			PreparedStatement pstmt = tzConn
					.prepareStatement("INSERT INTO timeTest VALUES (?, ?)");

			long now = System.currentTimeMillis(); // Time in milliseconds
			// since 1/1/1970 GMT

			Timestamp nowTstamp = new Timestamp(now);
			Time nowTime = new Time(now);

			pstmt.setTimestamp(1, nowTstamp);
			pstmt.setTime(2, nowTime);
			pstmt.executeUpdate();

			this.rs = tzStmt.executeQuery("SELECT * from timeTest");

			// Timestamps look like this: 2004-11-29 13:43:21
			SimpleDateFormat timestampFormat = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");
			SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");

			while (this.rs.next()) {
				// Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types
				// when calling getString()...
				String retrTimestampString = new String(this.rs.getBytes(1));
				Timestamp retrTimestamp = this.rs.getTimestamp(1);

				java.util.Date timestampOnServer = timestampFormat
						.parse(retrTimestampString);

				long retrievedOffsetForTimestamp = retrTimestamp.getTime()
						- timestampOnServer.getTime();

				assertTrue(
						"Difference between original timestamp and timestamp retrieved using client timezone is not "
								+ offsetDifference, (Math
								.abs(retrievedOffsetForTimestamp
										- offsetDifference) < epsillon));

				String retrTimeString = new String(this.rs.getBytes(2));
				Time retrTime = this.rs.getTime(2);

				java.util.Date timeOnServerAsDate = timeFormat
						.parse(retrTimeString);
				Time timeOnServer = new Time(timeOnServerAsDate.getTime());

				long retrievedOffsetForTime = retrTime.getTime()
						- timeOnServer.getTime();

				assertTrue(
						"Difference between original times and time retrieved using client timezone is not "
								+ offsetDifference,
						(Math.abs(retrievedOffsetForTime - offsetDifference) < epsillon));
			}
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS timeTest");
		} */
	}

	public void testBug6823() throws SQLException {
		innerBug6823(true);
		innerBug6823(false);
	}

	public void testBug7461() throws Exception {
		String tableName = "testBug7461";

		try {
			createTable(tableName, "(field1 varchar(4))");
			File tempFile = File.createTempFile("mysql-test", ".txt");
			tempFile.deleteOnExit();

			FileOutputStream fOut = new FileOutputStream(tempFile);
			fOut.write("abcdefghijklmnop".getBytes());
			fOut.close();

			try {
				this.stmt.executeQuery("LOAD DATA LOCAL INFILE '"
						+ tempFile.toString() + "' INTO TABLE " + tableName);
			} catch (SQLException sqlEx) {
				this.stmt.getWarnings();
			}

		} finally {
			dropTable(tableName);
		}

	}

	public void testBug8181() throws Exception {

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8181");
			this.stmt
					.executeUpdate("CREATE TABLE testBug8181(col1 VARCHAR(20),col2 INT)");

			this.pstmt = this.conn
					.prepareStatement("INSERT INTO testBug8181(col1,col2) VALUES(?,?)");

			for (int i = 0; i < 20; i++) {
				this.pstmt.setString(1, "Test " + i);
				this.pstmt.setInt(2, i);
				this.pstmt.addBatch();
			}

			this.pstmt.executeBatch();

		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8181");

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

	/**
	 * Tests fix for BUG#8487 - PreparedStatements not creating streaming result
	 * sets.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug8487() throws Exception {
		try {
			this.pstmt = this.conn.prepareStatement("SELECT 1",
					ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

			this.pstmt.setFetchSize(Integer.MIN_VALUE);
			this.rs = this.pstmt.executeQuery();
			try {
				this.conn.createStatement().executeQuery("SELECT 2");
				fail("Should have caught a streaming exception here");
			} catch (SQLException sqlEx) {
				assertTrue(sqlEx.getMessage() != null
						&& sqlEx.getMessage().indexOf("Streaming") != -1);
			}

		} finally {
			if (this.rs != null) {
				while (this.rs.next())
					;

				this.rs.close();
			}

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

	/**
	 * Tests multiple statement support with fix for BUG#9704.
	 * 
	 * @throws Exception
	 *             DOCUMENT ME!
	 */
	public void testBug9704() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			Connection multiStmtConn = null;
			Statement multiStmt = null;

			try {
				Properties props = new Properties();
				props.setProperty("allowMultiQueries", "true");

				multiStmtConn = getConnectionWithProps(props);

				multiStmt = multiStmtConn.createStatement();

				multiStmt
						.executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
				multiStmt
						.executeUpdate("CREATE TABLE testMultiStatements (field1 VARCHAR(255), field2 INT, field3 DOUBLE)");
				multiStmt
						.executeUpdate("INSERT INTO testMultiStatements VALUES ('abcd', 1, 2)");

				multiStmt
						.execute("SELECT field1 FROM testMultiStatements WHERE field1='abcd';"
								+ "UPDATE testMultiStatements SET field3=3;"
								+ "SELECT field3 FROM testMultiStatements WHERE field3=3");

				this.rs = multiStmt.getResultSet();

				assertTrue(this.rs.next());

				assertTrue("abcd".equals(this.rs.getString(1)));
				this.rs.close();

				// Next should be an update count...
				assertTrue(!multiStmt.getMoreResults());

				assertTrue("Update count was " + multiStmt.getUpdateCount()
						+ ", expected 1", multiStmt.getUpdateCount() == 1);

				assertTrue(multiStmt.getMoreResults());

				this.rs = multiStmt.getResultSet();

				assertTrue(this.rs.next());

				assertTrue(this.rs.getDouble(1) == 3);

				// End of multi results
				assertTrue(!multiStmt.getMoreResults());
				assertTrue(multiStmt.getUpdateCount() == -1);
			} finally {
				if (multiStmt != null) {
					multiStmt
							.executeUpdate("DROP TABLE IF EXISTS testMultiStatements");

					multiStmt.close();
				}

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

	/**
	 * Tests that you can close a statement twice without an NPE.
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testCloseTwice() throws Exception {
		Statement closeMe = this.conn.createStatement();
		closeMe.close();
		closeMe.close();
	}

	public void testCsc4194() throws Exception {
		if (isRunningOnJdk131()) {
			return; // test not valid on JDK-1.3.1
		}

		Connection sjisConn = null;
		Connection windows31JConn = null;

		try {
			String tableNameText = "testCsc4194Text";
			String tableNameBlob = "testCsc4194Blob";

			createTable(tableNameBlob, "(field1 BLOB)");
			String charset = "";

			if (versionMeetsMinimum(5, 0, 3) || versionMeetsMinimum(4, 1, 12)) {
				charset = " CHARACTER SET cp932";
			} else if (versionMeetsMinimum(4, 1, 0)) {
				charset = " CHARACTER SET sjis";
			}

			createTable(tableNameText, "(field1 TEXT)" + charset);

			Properties windows31JProps = new Properties();
			windows31JProps.setProperty("useUnicode", "true");
			windows31JProps.setProperty("characterEncoding", "Windows-31J");

			windows31JConn = getConnectionWithProps(windows31JProps);
			testCsc4194InsertCheckBlob(windows31JConn, tableNameBlob);

			if (versionMeetsMinimum(4, 1, 0)) {
				testCsc4194InsertCheckText(windows31JConn, tableNameText,
						"Windows-31J");
			}

			Properties sjisProps = new Properties();
			sjisProps.setProperty("useUnicode", "true");
			sjisProps.setProperty("characterEncoding", "sjis");

			sjisConn = getConnectionWithProps(sjisProps);
			testCsc4194InsertCheckBlob(sjisConn, tableNameBlob);

			if (versionMeetsMinimum(5, 0, 3)) {
				testCsc4194InsertCheckText(sjisConn, tableNameText,
						"Windows-31J");
			}

		} finally {

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

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

	private void testCsc4194InsertCheckBlob(Connection c, String tableName)
			throws Exception {
		byte[] bArray = new byte[] { (byte) 0xac, (byte) 0xed, (byte) 0x00,
				(byte) 0x05 };

		PreparedStatement testStmt = c.prepareStatement("INSERT INTO "
				+ tableName + " VALUES (?)");
		testStmt.setBytes(1, bArray);
		testStmt.executeUpdate();

		this.rs = c.createStatement().executeQuery(
				"SELECT field1 FROM " + tableName);
		assertTrue(this.rs.next());
		assertEquals(getByteArrayString(bArray), getByteArrayString(this.rs
				.getBytes(1)));
		this.rs.close();
	}

	private void testCsc4194InsertCheckText(Connection c, String tableName,
			String encoding) throws Exception {
		byte[] kabuInShiftJIS = { (byte) 0x87, // a double-byte
				// charater("kabu") in Shift JIS
				(byte) 0x8a, };

		String expected = new String(kabuInShiftJIS, encoding);
		PreparedStatement testStmt = c.prepareStatement("INSERT INTO "
				+ tableName + " VALUES (?)");
		testStmt.setString(1, expected);
		testStmt.executeUpdate();

		this.rs = c.createStatement().executeQuery(
				"SELECT field1 FROM " + tableName);
		assertTrue(this.rs.next());
		assertEquals(expected, this.rs.getString(1));
		this.rs.close();
	}

	/**
	 * Tests all forms of statements influencing getGeneratedKeys().
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testGetGeneratedKeysAllCases() throws Exception {
		if (isRunningOnJdk131()) {
			return; // test not valid on JDK-1.3.1
		}

		System.out.println("Using Statement.executeUpdate()\n");

		try {
			createGGKTable