statementregressiontest.java

MySql Java Connector

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

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

            assertTrue(
                "Difference between original timestamp and timestamp retrieved using java.sql.Statement "
            		+ "set in database using UTC calendar is not ~= "
                + pointInTimeOffset,
                (Math.abs(stmtDeltaTWithCal - pointInTimeOffset) < epsillon));
            
            long pStmtDeltaTWithCal = (ts.getTime() - tsValuePstmtNoCal.getTime());

            assertTrue(
                "Difference between original timestamp and timestamp retrieved using java.sql.PreparedStatement "
            		+ "set in database using UTC calendar is not ~= "
                + pointInTimeOffset,
                (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 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#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 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();
    }

    /**
     * DOCUMENT ME!
     *
     * @throws Exception DOCUMENT ME!
     */
    public void testGetGeneratedKeysAllCases() throws Exception {
        System.out.println("Using Statement.executeUpdate()\n");

        try {
            createGGKTables();

            //Do the tests
            for (int i = 0; i < tests.length; i++) {
                doGGKTestStatement(tests[i], true);
            }
        } finally {
            dropGGKTables();
        }

        nextID = 1;
        count = 0;

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

        try {
            createGGKTables();

            //Do the tests
            for (int i = 0; i < tests.length; i++) {
                doGGKTestStatement(tests[i], false);
            }
        } finally {
            dropGGKTables();
        }

        nextID = 1;
        count = 0;

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

        try {
            createGGKTables();

            //Do the tests
            for (int i = 0; i < tests.length; i++) {
                doGGKTestPreparedStatement(tests[i], true);
            }
        } finally {
            dropGGKTables();
        }

        nextID = 1;
        count = 0;

        System.out.println("Using PreparedStatement.execute()\n");

        try {
            createGGKTables();

            //Do the tests
            for (int i = 0; i < tests.length; i++) {
                doGGKTestPreparedStatement(tests[i], false);
            }
        } finally {
            dropGGKTables();
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @throws Exception ...
     */
    public void testLimitAndMaxRows() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testMaxRowsAndLimit");
            this.stmt.executeUpdate(
                "CREATE TABLE testMaxRowsAndLimit(limitField INT)");

            for (int i = 0; i < 500; i++) {
                this.stmt.executeUpdate(
                    "INSERT INTO testMaxRowsAndLimit VALUES (" + i + ")");
            }

            this.stmt.setMaxRows(250);
            this.stmt.executeQuery("SELECT limitField FROM testMaxRowsAndLimit");
        } finally {
            this.stmt.setMaxRows(0);

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

    /**
     * Tests that 'LOAD DATA LOCAL INFILE' works
     *
     * @throws Exception if any errors occur
     */
    public void testLoadData() throws Exception {
        try {
            int maxAllowedPacket = 1048576;

            stmt.executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
            stmt.executeUpdate(
                "CREATE TABLE loadDataRegress (field1 int, field2 int)");

            File tempFile = File.createTempFile("mysql", ".txt");

            //tempFile.deleteOnExit();
            System.out.println(tempFile);

            Writer out = new FileWriter(tempFile);

            int count = 0;
            int rowCount = 128; //maxAllowedPacket * 4;

            for (int i = 0; i < rowCount; i++) {
                out.write((count++) + "\t" + (count++) + "\n");
            }

            out.close();

            StringBuffer fileNameBuf = null;

            if (File.separatorChar == '\\') {
                fileNameBuf = new StringBuffer();

                String fileName = tempFile.getAbsolutePath();
                int fileNameLength = fileName.length();

                for (int i = 0; i < fileNameLength; i++) {
                    char c = fileName.charAt(i);

                    if (c == '\\') {
                        fileNameBuf.append("/");
                    } else {
                        fileNameBuf.append(c);
                    }
                }
            } else {
                fileNameBuf = new StringBuffer(tempFile.getAbsolutePath());
            }

            int updateCount = stmt.executeUpdate("LOAD DATA LOCAL INFILE '"
                    + fileNameBuf.toString() + "' INTO TABLE loadDataRegress");
            assertTrue(updateCount == rowCount);
        } finally {
            stmt.executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
        }
    }

    /**
     * Tests fix for BUG#1658
     *
     * @throws Exception if the fix for parameter bounds checking doesn't work.
     */
    public void testParameterBoundsCheck() throws Exception {
        PreparedStatement pstmt = this.conn.prepareStatement(
                "UPDATE FOO	SET f1=?, f2=?,f3=?,f4 WHERE f5=?");

        pstmt.setString(1, "");
        pstmt.setString(2, "");

        try {
            pstmt.setString(25, "");
            pstmt.setInt(26, 1);
        } catch (SQLException sqlEx) {
            assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(
                    sqlEx.getSQLState()));
        }
    }

    /**
     * Tests fix for BUG#1511
     *
     * @throws Exception if the quoteid parsing fix in PreparedStatement