resultsetregressiontest.java

MySql Java Connector

        int intVal = 123456;
        long longVal1 = 123456789012345678L;
        long longVal2 = -2079305757640172711L;
        stmt.executeUpdate("INSERT INTO getLongBug " + "(int_col, bigint_col) "
            + "VALUES (" + intVal + ", " + longVal1 + "), " + "(" + intVal
            + ", " + longVal2 + ")");

        try {
            rs = stmt.executeQuery(
                    "SELECT int_col, bigint_col FROM getLongBug ORDER BY bigint_col DESC");
            rs.next();
            assertTrue("Values not decoded correctly",
                ((rs.getInt(1) == intVal) && (rs.getLong(2) == longVal1)));
            rs.next();
            assertTrue("Values not decoded correctly",
                ((rs.getInt(1) == intVal) && (rs.getLong(2) == longVal2)));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                    // ignore
                }
            }

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

    /**
     * DOCUMENT ME!
     *
     * @throws Exception DOCUMENT ME!
     */
    public void testGetTimestampWithDate() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
            this.stmt.executeUpdate("CREATE TABLE testGetTimestamp (d date)");
            this.stmt.executeUpdate(
                "INSERT INTO testGetTimestamp values (now())");

            this.rs = this.stmt.executeQuery("SELECT * FROM testGetTimestamp");
            this.rs.next();
            System.out.println(this.rs.getTimestamp(1));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
        }
    }

    /**
     * Tests a bug where ResultSet.isBefireFirst() would return true when the
     * result set was empty (which is incorrect)
     *
     * @throws Exception if an error occurs.
     */
    public void testIsBeforeFirstOnEmpty() throws Exception {
        try {
            //Query with valid rows: isBeforeFirst() correctly returns True
            rs = stmt.executeQuery("SHOW VARIABLES LIKE 'version'");
            assertTrue("Non-empty search should return true", rs.isBeforeFirst());

            //Query with empty result: isBeforeFirst() falsely returns True
            //Sun's documentation says it should return false
            rs = stmt.executeQuery("SHOW VARIABLES LIKE 'garbage'");
            assertTrue("Empty search should return false ", !rs.isBeforeFirst());
        } finally {
            rs.close();
        }
    }

    /**
     * Tests a bug where ResultSet.isBefireFirst() would return true when the
     * result set was empty (which is incorrect)
     *
     * @throws Exception if an error occurs.
     */
    public void testMetaDataIsWritable() throws Exception {
        try {
            //Query with valid rows
            rs = stmt.executeQuery("SHOW VARIABLES LIKE 'version'");

            ResultSetMetaData rsmd = rs.getMetaData();

            int numColumns = rsmd.getColumnCount();

            for (int i = 1; i <= numColumns; i++) {
                assertTrue("rsmd.isWritable() should != rsmd.isReadOnly()",
                    rsmd.isWritable(i) != rsmd.isReadOnly(i));
            }
        } finally {
            rs.close();
        }
    }

    /**
     * Tests fix for bug # 496
     *
     * @throws Exception if an error happens.
     */
    public void testNextAndPrevious() throws Exception {
        try {
            stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
            stmt.executeUpdate("CREATE TABLE testNextAndPrevious (field1 int)");
            stmt.executeUpdate("INSERT INTO testNextAndPrevious VALUES (1)");

            rs = stmt.executeQuery("SELECT * from testNextAndPrevious");

            System.out.println("Currently at row " + rs.getRow());
            rs.next();
            System.out.println("Value at row " + rs.getRow() + " is "
                + rs.getString(1));

            rs.previous();

            try {
                System.out.println("Value at row " + rs.getRow() + " is "
                    + rs.getString(1));
                fail(
                    "Should not be able to retrieve values with invalid cursor");
            } catch (SQLException sqlEx) {
                assertTrue(sqlEx.getMessage().startsWith("Before start"));
            }

            rs.next();

            rs.next();

            try {
                System.out.println("Value at row " + rs.getRow() + " is "
                    + rs.getString(1));
                fail(
                    "Should not be able to retrieve values with invalid cursor");
            } catch (SQLException sqlEx) {
                assertTrue(sqlEx.getMessage().startsWith("After end"));
            }
        } finally {
            stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
        }
    }

    /**
     * Tests fix for BUG#1630 (not updatable exception turning into NPE on
     * second updateFoo() method call.
     *
     * @throws Exception if an unexpected exception is thrown.
     */
    public void testNotUpdatable() throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            String sQuery = "SHOW VARIABLES";
            pstmt = conn.prepareStatement(sQuery,
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                rs.absolute(1);

                try {
                    rs.updateInt(1, 1);
                } catch (SQLException sqlEx) {
                    assertTrue(sqlEx instanceof NotUpdatable);
                }

                try {
                    rs.updateString(1, "1");
                } catch (SQLException sqlEx) {
                    assertTrue(sqlEx instanceof NotUpdatable);
                }
            }
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (Exception e) {
                    // ignore
                }
            }
        }
    }

    /**
     * Tests that streaming result sets are registered correctly.
     *
     * @throws Exception if any errors occur
     */
    public void testStreamingRegBug() throws Exception {
        try {
            stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
            stmt.executeUpdate("CREATE TABLE StreamingRegBug ( DUMMYID "
                + " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
            stmt.executeUpdate(
                "INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
            stmt.executeUpdate(
                "INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
            stmt.executeUpdate(
                "INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
            stmt.executeUpdate(
                "INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");

            Statement streamStmt = null;

            try {
                streamStmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                        java.sql.ResultSet.CONCUR_READ_ONLY);
                streamStmt.setFetchSize(Integer.MIN_VALUE);

                ResultSet rs = streamStmt.executeQuery(
                        "SELECT DUMMYID, DUMMYNAME "
                        + "FROM StreamingRegBug ORDER BY DUMMYID");

                while (rs.next()) {
                    rs.getString(1);
                }

                rs.close(); // error occurs here
            } finally {
                if (streamStmt != null) {
                    streamStmt.close();
                }
            }
        } finally {
            stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
        }
    }

    /**
     * Tests that result sets can be updated when all parameters are correctly
     * set.
     *
     * @throws Exception if any errors occur
     */
    public void testUpdatability() throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
        stmt.execute("CREATE TABLE IF NOT EXISTS updatabilityBug ("
            + " id int(10) unsigned NOT NULL auto_increment,"
            + " field1 varchar(32) NOT NULL default '',"
            + " field2 varchar(128) NOT NULL default '',"
            + " field3 varchar(128) default NULL,"
            + " field4 varchar(128) default NULL,"
            + " field5 varchar(64) default NULL,"
            + " field6 int(10) unsigned default NULL,"
            + " field7 varchar(64) default NULL," + " PRIMARY KEY  (id)"
            + ") TYPE=InnoDB;");
        stmt.executeUpdate("insert into updatabilityBug (id) values (1)");

        try {
            String sQuery = " SELECT * FROM updatabilityBug WHERE id = ? ";
            pstmt = conn.prepareStatement(sQuery,
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            conn.setAutoCommit(false);
            pstmt.setInt(1, 1);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                rs.absolute(1);
                rs.updateInt("id", 1);
                rs.updateString("field1", "1");
                rs.updateString("field2", "1");
                rs.updateString("field3", "1");
                rs.updateString("field4", "1");
                rs.updateString("field5", "1");
                rs.updateInt("field6", 1);
                rs.updateString("field7", "1");
                rs.updateRow();
            }

            conn.commit();
            conn.setAutoCommit(true);
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (Exception e) {
                    // ignore
                }
            }

            stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
        }
    }

    /**
     * Test fixes for BUG#1071
     *
     * @throws Exception if the test fails.
     */
    public void testUpdatabilityAndEscaping() throws Exception {
        Properties props = new Properties();
        props.setProperty("useUnicode", "true");
        props.setProperty("characterEncoding", "big5");

        Connection updConn = getConnectionWithProps(props);
        Statement updStmt = updConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        try {
            updStmt.executeUpdate(
                "DROP TABLE IF EXISTS testUpdatesWithEscaping");
            updStmt.executeUpdate(
                "CREATE TABLE testUpdatesWithEscaping (field1 INT PRIMARY KEY, field2 VARCHAR(64))");
            updStmt.executeUpdate(
                "INSERT INTO testUpdatesWithEscaping VALUES (1, null)");

            String stringToUpdate = "\" \\ '";

            this.rs = updStmt.executeQuery(
                    "SELECT * from testUpdatesWithEscaping");

            this.rs.next();
            this.rs.updateString(2, stringToUpdate);
            this.rs.updateRow();

            assertTrue(stringToUpdate.equals(rs.getString(2)));
        } finally {
            updStmt.executeUpdate(
                "DROP TABLE IF EXISTS testUpdatesWithEscaping");
            updStmt.close();
            updConn.close();
        }
    }

    /**
     * Tests the fix for BUG#661 ... refreshRow() fails when primary key values
     * have escaped data in them.
     *
     * @throws Exception if an error occurs
     */
    public void testUpdatabilityWithQuotes() throws Exception {
        Statement updStmt = null;

        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdWithQuotes");
            this.stmt.executeUpdate(
                "CREATE TABLE testUpdWithQuotes (keyField CHAR(32) PRIMARY KEY NOT NULL, field2 int)");

            PreparedStatement pStmt = this.conn.prepareStatement(
                    "INSERT INTO testUpdWithQuotes VALUES (?, ?)");
            pStmt.setString(1, "Abe's");
            pStmt.setInt(2, 1);
            pStmt.executeUpdate();

            updStmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);

            this.rs = updStmt.executeQuery("SELECT * FROM testUpdWithQuotes");
            this.rs.next();
            this.rs.updateInt(2, 2);
            this.rs.updateRow();
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdWithQuotes");

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

            this.rs = null;

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

            updStmt = null;
        }
    }

    /**
     * Checks whether or not ResultSet.updateClob() is implemented
     *
     * @throws Exception if the test fails
     */
    public void testUpdateClob() throws Exception {
        Statement updatableStmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdateClob");
            this.stmt.executeUpdate(
                "CREATE TABLE testUpdateClob(intField INT NOT NULL PRIMARY KEY, clobField TEXT)");
            this.stmt.executeUpdate(
                "INSERT INTO testUpdateClob VALUES (1, 'foo')");

            this.rs = updatableStmt.executeQuery(
                    "SELECT intField, clobField FROM testUpdateClob");
            this.rs.next();

            Clob clob = this.rs.getClob(2);

            clob.setString(1, "bar");

            this.rs.updateClob(2, clob);
            this.rs.updateRow();

            this.rs.moveToInsertRow();

            clob.setString(1, "baz");
            this.rs.updateInt(1, 2);
            this.rs.updateClob(2, clob);
            this.rs.insertRow();

            clob.setString(1, "bat");
            this.rs.updateInt(1, 3);
            this.rs.updateClob(2, clob);
            this.rs.insertRow();

            this.rs.close();

            this.rs = this.stmt.executeQuery(
                    "SELECT intField, clobField FROM testUpdateClob ORDER BY intField");

            this.rs.next();
            assertTrue((this.rs.getInt(1) == 1)
                && this.rs.getString(2).equals("bar"));

            this.rs.next();
            assertTrue((this.rs.getInt(1) == 2)
                && this.rs.getString(2).equals("baz"));

            this.rs.next();
            assertTrue((this.rs.getInt(1) == 3)
                && this.rs.getString(2).equals("bat"));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdateClob");
        }
    }
}