statementregressiontest.java

在资料浩瀚的互联网中

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

    /**
     * Tests fix for BUG#4119 -- misbehavior in a managed environment from 
     * MVCSoft JDO
     * 
     * @throws Exception if the test fails.
     */
    public void testBug4119() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
            this.stmt.executeUpdate("CREATE TABLE `testBug4119` (" +
                "`field1` varchar(255) NOT NULL default ''," +
                "`field2` bigint(20) default NULL," +
                "`field3` int(11) default NULL," +
                "`field4` datetime default NULL," +
                "`field5` varchar(75) default NULL," +
                "`field6` varchar(75) default NULL," +
                "`field7` varchar(75) default NULL," +
                "`field8` datetime default NULL," + " PRIMARY KEY  (`field1`)" +
                ")");

            PreparedStatement pStmt = this.conn.prepareStatement(
                    "insert into testBug4119 (field2, field3," +
                    "field4, field5, field6, field7, field8, field1) values (?, ?," +
                    "?, ?, ?, ?, ?, ?)");

            pStmt.setString(1, "0");
            pStmt.setString(2, "0");
            pStmt.setTimestamp(3,
                new java.sql.Timestamp(System.currentTimeMillis()));
            pStmt.setString(4, "ABC");
            pStmt.setString(5, "DEF");
            pStmt.setString(6, "AA");
            pStmt.setTimestamp(7,
                new java.sql.Timestamp(System.currentTimeMillis()));
            pStmt.setString(8, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
            pStmt.executeUpdate();
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
        }
    }

    /**
     * Tests that max_rows and 'limit' don't cause exceptions to be thrown.
     *
     * @throws Exception if the test fails.
     */
    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;

            this.stmt.executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
            this.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 localCount = 0;
            int rowCount = 128; //maxAllowedPacket * 4;

            for (int i = 0; i < rowCount; i++) {
                out.write((localCount++) + "\t" + (localCount++) + "\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 = this.stmt.executeUpdate("LOAD DATA LOCAL INFILE '" +
                    fileNameBuf.toString() + "' INTO TABLE loadDataRegress");
            assertTrue(updateCount == rowCount);
        } finally {
        	this.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 {
        try {
            this.stmt.executeUpdate(
                "DROP TABLE IF EXISTS testParameterBoundsCheck");
            this.stmt.executeUpdate(
                "CREATE TABLE testParameterBoundsCheck(f1 int, f2 int, f3 int, f4 int, f5 int)");

            PreparedStatement pstmt = this.conn.prepareStatement(
                    "UPDATE testParameterBoundsCheck SET f1=?, f2=?,f3=?,f4=? WHERE f5=?");

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

            try {
                pstmt.setString(25, "");
            } catch (SQLException sqlEx) {
                assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(
                        sqlEx.getSQLState()));
            }
        } finally {
            this.stmt.executeUpdate(
                "DROP TABLE IF EXISTS testParameterBoundsCheck");
        }
    }

    /**
     * Tests fix for BUG#1511
     *
     * @throws Exception if the quoteid parsing fix in PreparedStatement
     *         doesn't work.
     */
    public void testQuotedIdRecognition() throws Exception {
        if (!this.versionMeetsMinimum(4, 1)) {
            try {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId");
                this.stmt.executeUpdate(
                    "CREATE TABLE testQuotedId (col1 VARCHAR(32))");

                PreparedStatement pStmt = this.conn.prepareStatement(
                        "SELECT * FROM testQuotedId FROM WHERE col1='ABC`DEF' or col1=?");
                pStmt.setString(1, "foo");
                System.out.println(pStmt);
            } finally {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId");
            }
        }
    }

    /**
     * Tests that binary dates/times are encoded/decoded correctly.     
     * 
     * @throws Exception if the test fails.
     *
     * @deprecated because we need to use this particular constructor for the
     *             date class, as Calendar-constructed dates don't pass the
     *             .equals() test :(
     */
    public void testServerPrepStmtAndDate() throws Exception {
        try {
            this.stmt.executeUpdate(
                "DROP TABLE IF EXISTS testServerPrepStmtAndDate");
            this.stmt.executeUpdate("CREATE TABLE testServerPrepStmtAndDate(" +
                "`P_ID` int(10) NOT NULL default '0'," +
                "`H_ID` int(10) NOT NULL default '0'," +
                "`R_ID` int(10) NOT NULL default '0'," +
                "`H_Age` int(10) default NULL," +
                "`R_Date` date NOT NULL default '0000-00-00'," +
                "`Comments` varchar(255) default NULL," +
                "`Weight` int(10) default NULL," +
                "`HeadGear` char(1) NOT NULL default ''," +
                "`FinPos` int(10) default NULL," +
                "`Jock_ID` int(10) default NULL," +
                "`BtnByPrev` double default NULL," +
                "`BtnByWinner` double default NULL," +
                "`Jock_All` int(10) default NULL," +
                "`Draw` int(10) default NULL," + "`SF` int(10) default NULL," +
                "`RHR` int(10) default NULL," +
                "`ORating` int(10) default NULL," +
                "`Odds` double default NULL," +
                "`RaceFormPlus` int(10) default NULL," +
                "`PrevPerform` int(10) default NULL," +
                "`TrainerID` int(10) NOT NULL default '0'," +
                "`DaysSinceRun` int(10) default NULL," +
                "UNIQUE KEY `P_ID` (`P_ID`)," +
                "UNIQUE KEY `R_H_ID` (`R_ID`,`H_ID`)," +
                "KEY `R_Date` (`R_Date`)," + "KEY `H_Age` (`H_Age`)," +
                "KEY `TrainerID` (`TrainerID`)," + "KEY `H_ID` (`H_ID`)" + ")");

            Date dt = new java.sql.Date(102, 1, 2); // Note, this represents the date 2002-02-02

            PreparedStatement pStmt2 = this.conn.prepareStatement(
                    "INSERT INTO testServerPrepStmtAndDate (P_ID, R_Date) VALUES (171576, ?)");
            pStmt2.setDate(1, dt);
            pStmt2.executeUpdate();
            pStmt2.close();

            this.rs = this.stmt.executeQuery(
                    "SELECT R_Date FROM testServerPrepStmtAndDate");
            this.rs.next();

            System.out.println("Date that was stored (as String) " +
                this.rs.getString(1)); // comes back as 2002-02-02

            PreparedStatement pStmt = this.conn.prepareStatement(
                    "Select P_ID,R_Date from testServerPrepStmtAndDate Where R_Date = ?   and P_ID = 171576");
            pStmt.setDate(1, dt);

            this.rs = pStmt.executeQuery();

            assertTrue(this.rs.next());

            assertTrue("171576".equals(this.rs.getString(1)));

            Date retDt = this.rs.getDate(2);

            assertTrue(dt.equals(this.rs.getDate(2)));
        } finally {
            this.stmt.executeUpdate(
                "DROP TABLE IF EXISTS testServerPrepStmtAndDate");
        }
    }

    /**
     * Tests PreparedStatement.setCharacterStream() to ensure it accepts > 4K
     * streams
     *
     * @throws Exception if an error occurs.
     */
    public void testSetCharacterStream() throws Exception {
        try {
            ((com.mysql.jdbc.Connection) this.conn).setTraceProtocol(true);

            this.stmt.executeUpdate("DROP TABLE IF EXISTS charStreamRegressTest");
            this.stmt.executeUpdate(
                "CREATE TABLE charStreamRegressTest(field1 text)");

            this.pstmt = this.conn.prepareStatement(
                    "INSERT INTO charStreamRegressTest VALUES (?)");

            //char[] charBuf = new char[16384];
            char[] charBuf = new char[32];

            for (int i = 0; i < charBuf.length; i++) {
                charBuf[i] = 'A';
            }

            CharArrayReader reader = new CharArrayReader(charBuf);

            this.pstmt.setCharacterStream(1, reader, charBuf.length);
            this.pstmt.executeUpdate();

            this.rs = this.stmt.executeQuery(
                    "SELECT LENGTH(field1) FROM charStreamRegressTest");

            this.rs.next();

            System.out.println("Character stream length: " + this.rs.getString(1));

            this.rs = this.stmt.executeQuery("SELECT field1 FROM charStreamRegressTest");

            this.rs.next();

            String result = this.rs.getString(1);

            assertTrue(result.length() == charBuf.length);

            this.stmt.execute("TRUNCATE TABLE charStreamRegressTest");

            // Test that EOF is not thrown
            reader = new CharArrayReader(charBuf);
            this.pstmt.clearParameters();
            this.pstmt.setCharacterStream(1, reader, charBuf.length);
            this.pstmt.executeUpdate();

            this.rs = this.stmt.executeQuery(
                    "SELECT LENGTH(field1) FROM charStreamRegressTest");

            this.rs.next();

            System.out.println("Character stream length: " + this.rs.getString(1));

            this.rs = this.stmt.executeQuery("SELECT field1 FROM charStreamRegressTest");

            this.rs.next();

            result = this.rs.getString(1);

            assertTrue("Retrieved value of length " + result.length() +
                " != length of inserted value " + charBuf.length,
                result.length() == charBuf.length);

            // Test single quotes inside identifers
            this.stmt.executeUpdate("DROP TABLE IF EXISTS `charStream'RegressTest`");
            this.stmt.executeUpdate(
                "CREATE TABLE `charStream'RegressTest`(field1 text)");

            this.pstmt = this.conn.prepareStatement(
                    "INSERT INTO `charStream'RegressTest` VALUES (?)");

            reader = new CharArrayReader(charBuf);
            this.pstmt.setCharacterStream(1, reader, (charBuf.length * 2));
            this.pstmt.executeUpdate();

            this.rs = this.stmt.executeQuery(
                    "SELECT field1 FROM `charStream'RegressTest`");

            this.rs.next();

            result = this.rs.getString(1);