statementregressiontest.java

mysql的jdbc驱动

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

		if (versionMeetsMinimum(4, 1, 0)) {

			createTable(tableName,
					"(pwd VARBINARY(30)) TYPE=InnoDB DEFAULT CHARACTER SET utf8");

			byte[] bytesToTest = new byte[] { 17, 120, -1, -73, -5 };

			PreparedStatement insStmt = this.conn
					.prepareStatement("INSERT INTO " + tableName
							+ " (pwd) VALUES (?)");
			insStmt.setBytes(1, bytesToTest);
			insStmt.executeUpdate();

			this.rs = this.stmt.executeQuery("SELECT pwd FROM " + tableName);
			this.rs.next();

			byte[] fromDatabase = this.rs.getBytes(1);

			assertEquals(bytesToTest.length, fromDatabase.length);

			for (int i = 0; i < bytesToTest.length; i++) {
				assertEquals(bytesToTest[i], fromDatabase[i]);
			}

			this.rs = this.conn
					.prepareStatement("SELECT pwd FROM " + tableName)
					.executeQuery();
			this.rs.next();

			fromDatabase = this.rs.getBytes(1);

			assertEquals(bytesToTest.length, fromDatabase.length);

			for (int i = 0; i < bytesToTest.length; i++) {
				assertEquals(bytesToTest[i], fromDatabase[i]);
			}
		}
	}

	public void testBug11540() throws Exception {
		Locale originalLocale = Locale.getDefault();
		Connection thaiConn = null;
		Statement thaiStmt = null;
		PreparedStatement thaiPrepStmt = null;

		try {
			createTable("testBug11540", "(field1 DATE, field2 TIMESTAMP)");
			this.stmt
					.executeUpdate("INSERT INTO testBug11540 VALUES (NOW(), NOW())");
			Locale.setDefault(new Locale("th", "TH"));
			Properties props = new Properties();
			props.setProperty("jdbcCompliantTruncation", "false");

			thaiConn = getConnectionWithProps(props);
			thaiStmt = thaiConn.createStatement();

			this.rs = thaiStmt
					.executeQuery("SELECT field1, field2 FROM testBug11540");
			this.rs.next();

			Date origDate = this.rs.getDate(1);
			Timestamp origTimestamp = this.rs.getTimestamp(1);
			this.rs.close();

			thaiStmt.executeUpdate("TRUNCATE TABLE testBug11540");

			thaiPrepStmt = ((com.mysql.jdbc.Connection) thaiConn)
					.clientPrepareStatement("INSERT INTO testBug11540 VALUES (?,?)");
			thaiPrepStmt.setDate(1, origDate);
			thaiPrepStmt.setTimestamp(2, origTimestamp);
			thaiPrepStmt.executeUpdate();

			this.rs = thaiStmt
					.executeQuery("SELECT field1, field2 FROM testBug11540");
			this.rs.next();

			Date testDate = this.rs.getDate(1);
			Timestamp testTimestamp = this.rs.getTimestamp(1);
			this.rs.close();

			assertEquals(origDate, testDate);
			assertEquals(origTimestamp, testTimestamp);

		} finally {
			Locale.setDefault(originalLocale);
		}
	}

	/**
	 * Tests fix for BUG#11663, autoGenerateTestcaseScript uses bogus parameter
	 * names for server-side prepared statements.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug11663() throws Exception {
		if (versionMeetsMinimum(4, 1, 0)
				&& ((com.mysql.jdbc.Connection) this.conn)
						.getUseServerPreparedStmts()) {
			Connection testcaseGenCon = null;
			PrintStream oldErr = System.err;

			try {
				createTable("testBug11663", "(field1 int)");

				Properties props = new Properties();
				props.setProperty("autoGenerateTestcaseScript", "true");
				testcaseGenCon = getConnectionWithProps(props);
				ByteArrayOutputStream testStream = new ByteArrayOutputStream();
				PrintStream testErr = new PrintStream(testStream);
				System.setErr(testErr);
				this.pstmt = testcaseGenCon
						.prepareStatement("SELECT field1 FROM testBug11663 WHERE field1=?");
				this.pstmt.setInt(1, 1);
				this.pstmt.execute();
				System.setErr(oldErr);
				String testString = new String(testStream.toByteArray());

				int setIndex = testString.indexOf("SET @debug_stmt_param");
				int equalsIndex = testString.indexOf("=", setIndex);
				String paramName = testString.substring(setIndex + 4,
						equalsIndex);

				int usingIndex = testString.indexOf("USING " + paramName,
						equalsIndex);

				assertTrue(usingIndex != -1);
			} finally {
				System.setErr(oldErr);

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

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

			}
		}
	}

	/**
	 * Tests fix for BUG#11798 - Pstmt.setObject(...., Types.BOOLEAN) throws
	 * exception.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug11798() throws Exception {
		try {
			this.pstmt = this.conn.prepareStatement("SELECT ?");
			this.pstmt.setObject(1, Boolean.TRUE, Types.BOOLEAN);
			this.pstmt.setObject(1, new BigDecimal(1), Types.BOOLEAN);
			this.pstmt.setObject(1, "true", Types.BOOLEAN);
		} finally {
			if (this.pstmt != null) {
				this.pstmt.close();
				this.pstmt = null;
			}
		}
	}

	/**
	 * Tests fix for BUG#1774 -- Truncated words after double quote
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testBug1774() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1774");
			this.stmt
					.executeUpdate("CREATE TABLE testBug1774 (field1 VARCHAR(255))");

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

			String testString = "The word contains \" character";

			pStmt.setString(1, testString);
			pStmt.executeUpdate();

			this.rs = this.stmt.executeQuery("SELECT * FROM testBug1774");
			this.rs.next();
			assertEquals(this.rs.getString(1), testString);
		} finally {
			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 {
		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 NOT 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()));