statementregressiontest.java

MySql Java Connector

     *         doesn't work.
     */
    public void testQuotedIdRecognition() throws Exception {
        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");
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId");
        }
    }

    /**
     * Tests PreparedStatement.setCharacterStream() to ensure it accepts > 4K
     * streams
     *
     * @throws Exception if an error occurs.
     */
    public void testSetCharacterStream() throws Exception {
        try {
            stmt.executeUpdate("DROP TABLE IF EXISTS charStreamRegressTest");
            stmt.executeUpdate(
                "CREATE TABLE charStreamRegressTest(field1 text)");

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

            char[] charBuf = new char[16384];

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

            CharArrayReader reader = new CharArrayReader(charBuf);

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

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

            rs.next();

            String result = rs.getString(1);

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

            stmt.execute("TRUNCATE TABLE charStreamRegressTest");

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

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

            rs.next();

            result = 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
            stmt.executeUpdate("DROP TABLE IF EXISTS `charStream'RegressTest`");
            stmt.executeUpdate(
                "CREATE TABLE `charStream'RegressTest`(field1 text)");

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

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

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

            rs.next();

            result = rs.getString(1);

            assertTrue("Retrieved value of length " + result.length()
                + " != length of inserted value " + charBuf.length,
                result.length() == charBuf.length);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                    // ignore
                }

                rs = null;
            }

            stmt.executeUpdate("DROP TABLE IF EXISTS `charStream'RegressTest`");
            stmt.executeUpdate("DROP TABLE IF EXISTS charStreamRegressTest");
        }
    }

    /**
     * Tests a bug where Statement.setFetchSize() does not work for values
     * other than 0 or Integer.MIN_VALUE
     *
     * @throws Exception if any errors occur
     */
    public void testSetFetchSize() throws Exception {
        int oldFetchSize = stmt.getFetchSize();

        try {
            stmt.setFetchSize(10);
        } finally {
            stmt.setFetchSize(oldFetchSize);
        }
    }

    /**
     * Tests fix for BUG#907
     *
     * @throws Exception if an error occurs
     */
    public void testSetMaxRows() throws Exception {
        Statement maxRowsStmt = null;

        try {
            maxRowsStmt = this.conn.createStatement();
            maxRowsStmt.setMaxRows(1);
            maxRowsStmt.executeQuery("SELECT 1");
        } finally {
            if (maxRowsStmt != null) {
                maxRowsStmt.close();
            }
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @throws Exception DOCUMENT ME!
     */
    public void testUpdatableStream() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS updateStreamTest");
            this.stmt.executeUpdate(
                "CREATE TABLE updateStreamTest (keyField INT NOT NULL AUTO_INCREMENT PRIMARY KEY, field1 BLOB)");

            int streamLength = 16385;
            byte[] streamData = new byte[streamLength];

            /* create an updatable statement */
            Statement updStmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);

            /* fill the resultset with some values */
            ResultSet updRs = updStmt.executeQuery(
                    "SELECT * FROM updateStreamTest");

            /* move to insertRow */
            updRs.moveToInsertRow();

            /* update the table */
            updRs.updateBinaryStream("field1",
                new ByteArrayInputStream(streamData), streamLength);

            updRs.insertRow();
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS updateStreamTest");
        }
    }

    private void createGGKTables() throws Exception {
        //Delete and recreate table
        dropGGKTables();

        this.stmt.executeUpdate("CREATE TABLE testggk ("
            + "id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,"
            + "val INT NOT NULL" + ")");
    }

    private void doGGKTestPreparedStatement(int[] values, boolean useUpdate)
        throws Exception {
        //Generate the the multiple replace command
        StringBuffer cmd = new StringBuffer("REPLACE INTO testggk VALUES ");
        int newKeys = 0;

        for (int i = 0; i < values.length; i++) {
            cmd.append("(");

            if (values[i] == 0) {
                cmd.append("NULL");
                newKeys += 1;
            } else {
                cmd.append(values[i]);
            }

            cmd.append(", ");
            cmd.append(count++);
            cmd.append("), ");
        }

        cmd.setLength(cmd.length() - 2); //trim the final ", "

        //execute and print it
        System.out.println(cmd.toString());

        PreparedStatement pStmt = this.conn.prepareStatement(cmd.toString(),
                Statement.RETURN_GENERATED_KEYS);

        if (useUpdate) {
            pStmt.executeUpdate();
        } else {
            pStmt.execute();
        }

        //print out what actually happened
        System.out.println("Expect " + newKeys
            + " generated keys, starting from " + nextID);

        ResultSet rs = pStmt.getGeneratedKeys();
        StringBuffer res = new StringBuffer("Got keys");

        int[] generatedKeys = new int[newKeys];
        int i = 0;

        while (rs.next()) {
            if (i < generatedKeys.length) {
                generatedKeys[i++] = rs.getInt(1);
            }

            res.append(" " + rs.getInt(1));
        }

        int numberOfGeneratedKeys = i;

        assertTrue(
            "Didn't retrieve expected number of generated keys, expected "
            + newKeys + ", found " + numberOfGeneratedKeys,
            numberOfGeneratedKeys == newKeys);
        assertTrue("Keys didn't start with correct sequence: ",
            generatedKeys[0] == nextID);

        System.out.println(res.toString());

        //Read and print the new state of the table
        rs = stmt.executeQuery("SELECT id, val FROM testggk");
        System.out.println("New table contents ");

        while (rs.next())
            System.out.println("Id " + rs.getString(1) + " val "
                + rs.getString(2));

        //Tidy up
        System.out.println("");
        nextID += newKeys;
    }

    /**
                    	  */
    private void doGGKTestStatement(int[] values, boolean useUpdate)
        throws Exception {
        //Generate the the multiple replace command
        StringBuffer cmd = new StringBuffer("REPLACE INTO testggk VALUES ");
        int newKeys = 0;

        for (int i = 0; i < values.length; i++) {
            cmd.append("(");

            if (values[i] == 0) {
                cmd.append("NULL");
                newKeys += 1;
            } else {
                cmd.append(values[i]);
            }

            cmd.append(", ");
            cmd.append(count++);
            cmd.append("), ");
        }

        cmd.setLength(cmd.length() - 2); //trim the final ", "

        //execute and print it
        System.out.println(cmd.toString());

        if (useUpdate) {
            stmt.executeUpdate(cmd.toString(), Statement.RETURN_GENERATED_KEYS);
        } else {
            stmt.execute(cmd.toString(), Statement.RETURN_GENERATED_KEYS);
        }

        //print out what actually happened
        System.out.println("Expect " + newKeys
            + " generated keys, starting from " + nextID);

        ResultSet rs = stmt.getGeneratedKeys();
        StringBuffer res = new StringBuffer("Got keys");

        int[] generatedKeys = new int[newKeys];
        int i = 0;

        while (rs.next()) {
            if (i < generatedKeys.length) {
                generatedKeys[i++] = rs.getInt(1);
            }

            res.append(" " + rs.getInt(1));
        }

        int numberOfGeneratedKeys = i;

        assertTrue(
            "Didn't retrieve expected number of generated keys, expected "
            + newKeys + ", found " + numberOfGeneratedKeys,
            numberOfGeneratedKeys == newKeys);
        assertTrue("Keys didn't start with correct sequence: ",
            generatedKeys[0] == nextID);

        System.out.println(res.toString());

        //Read and print the new state of the table
        rs = stmt.executeQuery("SELECT id, val FROM testggk");
        System.out.println("New table contents ");

        while (rs.next())
            System.out.println("Id " + rs.getString(1) + " val "
                + rs.getString(2));

        //Tidy up
        System.out.println("");
        nextID += newKeys;
    }

    private void dropGGKTables() throws Exception {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testggk");
    }
}