statementregressiontest.java

在资料浩瀚的互联网中

    		this.stmt.executeUpdate("INSERT INTO testBug5012 VALUES (" + valueAsString + ")");
    		
    		pStmt = this.conn.prepareStatement("SELECT field1 FROM testBug5012");
    		this.rs = pStmt.executeQuery();
    		assertTrue(this.rs.next());
    		assertTrue(new BigDecimal(valueAsString).equals(this.rs.getBigDecimal(1)));
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
    		
    		if (pStmt != null) {
    			pStmt.close();
    		}
    	}
    }
    
    /**
     * 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 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");
	    	}
    	}
    }
    /**
     * @param pStmt
     * @param catId
     * @throws SQLException
     */
    private void execQueryBug5191(PreparedStatement pStmt, int catId)
            throws SQLException {
        pStmt.setInt(1, catId);

        this.rs = pStmt.executeQuery();

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

        assertFalse(this.rs.next());
    }
    
    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 testPStmtTypesBug() throws Exception {
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
    		this.stmt.executeUpdate("CREATE TABLE testPStmtTypesBug(field1 INT)");
    		this.pstmt = this.conn.prepareStatement("INSERT INTO testPStmtTypesBug VALUES (?)");
    		this.pstmt.setObject(1, null, Types.INTEGER);
    		this.pstmt.addBatch();
    		this.pstmt.setInt(1, 1);
    		this.pstmt.addBatch();
    		this.pstmt.executeBatch();
    		
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
    	}
    }
    
    public void testTruncationWithChar() throws Exception {
    	try {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testTruncationWithChar");
    		this.stmt.executeUpdate("CREATE TABLE testTruncationWithChar (field1 char(2))");
    		
    		this.pstmt = this.conn.prepareStatement("INSERT INTO testTruncationWithChar VALUES (?)");
    		this.pstmt.setString(1, "00");
    		this.pstmt.executeUpdate();
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testTruncationWithChar");
    	}
    }

	/**
	 * 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 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);
    }

    /**
     * @param continueBatchOnError
     * @throws SQLException
     */
    private void innerBug6823(boolean continueBatchOnError) throws SQLException {
        Properties continueBatchOnErrorProps = new Properties();
        continueBatchOnErrorProps.setProperty("continueBatchOnError", Boolean
                .toString(continueBatchOnError));
        this.conn = getConnectionWithProps(continueBatchOnErrorProps);
        Statement statement = conn.createStatement();

        String tableName = "testBug6823";

        createTable(tableName, "(id int not null primary key auto_increment,"
                + " strdata1 varchar(255) not null, strdata2 varchar(255),"