statementregressiontest.java

mysql的jdbc驱动

				this.pstmt = this.conn
						.prepareStatement("UPDATE testBug4718 SET field1='Will we ignore LIMIT ?,?'");
				assertTrue(this.pstmt instanceof ServerPreparedStatement);

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

	/**
	 * Tests fix for BUG#5012 -- ServerPreparedStatements dealing with return of
	 * DECIMAL type don't work.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug5012() throws Exception {
		PreparedStatement pStmt = null;
		String valueAsString = "12345.12";

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
			this.stmt
					.executeUpdate("CREATE TABLE testBug5012(field1 DECIMAL(10,2))");
			this.stmt.executeUpdate("INSERT INTO testBug5012 VALUES ("
					+ valueAsString + ")");

			pStmt = this.conn
					.prepareStatement("SELECT field1 FROM testBug5012");
			this.rs = pStmt.executeQuery();
			assertTrue(this.rs.next());
			assertEquals(new BigDecimal(valueAsString), this.rs
					.getBigDecimal(1));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");

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

	/**
	 * Tests fix for BUG#5133 -- PreparedStatement.toString() doesn't return
	 * correct value if no parameters are present in statement.
	 * 
	 * @throws Exception
	 */
	public void testBug5133() throws Exception {
		String query = "SELECT 1";
		String output = this.conn.prepareStatement(query).toString();
		System.out.println(output);

		assertTrue(output.indexOf(query) != -1);
	}

	/**
	 * Tests for BUG#5191 -- PreparedStatement.executeQuery() gives
	 * OutOfMemoryError
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug5191() throws Exception {
		PreparedStatement pStmt = null;

		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191C");

			this.stmt.executeUpdate("CREATE TABLE testBug5191Q"
					+ "(QuestionId int NOT NULL AUTO_INCREMENT, "
					+ "Text VARCHAR(200), " + "PRIMARY KEY(QuestionId))");

			this.stmt.executeUpdate("CREATE TABLE testBug5191C"
					+ "(CategoryId int, " + "QuestionId int)");

			String[] questions = new String[] { "What is your name?",
					"What is your quest?",
					"What is the airspeed velocity of an unladen swollow?",
					"How many roads must a man walk?", "Where's the tea?", };

			for (int i = 0; i < questions.length; i++) {
				this.stmt.executeUpdate("INSERT INTO testBug5191Q(Text)"
						+ " VALUES (\"" + questions[i] + "\")");
				int catagory = (i < 3) ? 0 : i;

				this.stmt.executeUpdate("INSERT INTO testBug5191C"
						+ "(CategoryId, QuestionId) VALUES (" + catagory + ", "
						+ i + ")");
				/*
				 * this.stmt.executeUpdate("INSERT INTO testBug5191C" +
				 * "(CategoryId, QuestionId) VALUES (" + catagory + ", (SELECT
				 * testBug5191Q.QuestionId" + " FROM testBug5191Q " + "WHERE
				 * testBug5191Q.Text LIKE '" + questions[i] + "'))");
				 */
			}

			pStmt = this.conn.prepareStatement("SELECT qc.QuestionId, q.Text "
					+ "FROM testBug5191Q q, testBug5191C qc "
					+ "WHERE qc.CategoryId = ? "
					+ " AND q.QuestionId = qc.QuestionId");

			int catId = 0;
			for (int i = 0; i < 100; i++) {
				execQueryBug5191(pStmt, catId);
			}

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

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

	public void testBug5235() throws Exception {
		Properties props = new Properties();
		props.setProperty("zeroDateTimeBehavior", "convertToNull");

		Connection convertToNullConn = getConnectionWithProps(props);
		Statement convertToNullStmt = convertToNullConn.createStatement();
		try {
			convertToNullStmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
			convertToNullStmt
					.executeUpdate("CREATE TABLE testBug5235(field1 DATE)");
			convertToNullStmt
					.executeUpdate("INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");

			PreparedStatement ps = convertToNullConn
					.prepareStatement("SELECT field1 FROM testBug5235");
			this.rs = ps.executeQuery();

			if (this.rs.next()) {
				Date d = (Date) rs.getObject("field1");
				System.out.println("date: " + d);
			}
		} finally {
			convertToNullStmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
		}
	}

	public void testBug5450() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			String table = "testBug5450";
			String column = "policyname";

			try {
				Properties props = new Properties();
				props.setProperty("characterEncoding", "utf8");

				Connection utf8Conn = getConnectionWithProps(props);
				Statement utfStmt = utf8Conn.createStatement();

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

				this.stmt.executeUpdate("CREATE TABLE " + table
						+ "(policyid int NOT NULL AUTO_INCREMENT, " + column
						+ " VARCHAR(200), "
						+ "PRIMARY KEY(policyid)) DEFAULT CHARACTER SET utf8");

				String pname0 = "inserted \uac00 - foo - \u4e00";

				utfStmt.executeUpdate("INSERT INTO " + table + "(" + column
						+ ")" + " 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();

			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 (rs.next()) {
				// Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types
				// when calling getString()...
				String retrTimestampString = new String(rs.getBytes(1));
				Timestamp retrTimestamp = 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(rs.getBytes(2));
				Time retrTime = 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 {
			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();
			}

			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())
					;

				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)");