statementregressiontest.java

MySql Java Connector

/*
 Copyright (C) 2002-2004 MySQL AB

 This program is free software; you can redistribute it and/or modify
 it under the terms of version 2 of the GNU General Public License as
 published by the Free Software Foundation.
 

 There are special exceptions to the terms and conditions of the GPL 
 as it is applied to this software. View the full text of the 
 exception exception in file EXCEPTIONS-CONNECTOR-J in the directory of this 
 software distribution.

 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU General Public License for more details.

 You should have received a copy of the GNU General Public License
 along with this program; if not, write to the Free Software
 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */
package testsuite.regression;

import com.mysql.jdbc.SQLError;

import testsuite.BaseTestCase;

import java.io.ByteArrayInputStream;
import java.io.CharArrayReader;
import java.io.File;
import java.io.FileWriter;
import java.io.Writer;

import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import java.util.Calendar;
import java.util.Properties;
import java.util.TimeZone;


/**
 * Regression tests for the Statement class
 *
 * @author Mark Matthews
 */
public class StatementRegressionTest extends BaseTestCase {
    /*Each row in this table is to be converted into a single REPLACE statement.
     If the value is zero, a new record is to be created using then autoincrement
     feature. If the value is non-zero, the existing row of that value is to be
     replace with, obviously, the same key. I expect one Generated Key for each
     zero value - but I would accept one key for each value, with non-zero values
     coming back as themselves.
     */
    static final int[][] tests = {
        { 0 }, //generate 1
        { 1, 0, 0 }, //update 1, generate 2, 3
        { 2, 0, 0, }, //update 2, generate 3, 4
    };
    static int nextID = 1; //The next ID we expected to generate
    static int count = 0;

    /**
     * Constructor for StatementRegressionTest.
     *
     * @param name the name of the test to run
     */
    public StatementRegressionTest(String name) {
        super(name);
    }

    /**
     * Runs all test cases in this test suite
     *
     * @param args
     */
    public static void main(String[] args) {
        junit.textui.TestRunner.run(StatementRegressionTest.class);
    }

    /**
     * DOCUMENT ME!
     *
     * @throws Exception ...
     */
    public void testBug1774() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1774");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug1774 (field1 VARCHAR(255))");

            PreparedStatement pStmt = this.conn.prepareStatement(
                    "INSERT INTO testBug1774 VALUES (?)");

            String testString = "The word contains \" character";

            pStmt.setString(1, testString);
            pStmt.executeUpdate();

            this.rs = this.stmt.executeQuery("SELECT * FROM testBug1774");
            this.rs.next();
            assertTrue(this.rs.getString(1).equals(testString));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1774");
        }
    }

    /**
     * Tests fix for BUG#1901 -- PreparedStatement.setObject(int, Object, int,
     * int) doesn't support CLOB or BLOB types.
     *
     * @throws Exception if this test fails for any reason
     */
    public void testBug1901() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug1901 (field1 VARCHAR(255))");
            this.stmt.executeUpdate("INSERT INTO testBug1901 VALUES ('aaa')");

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

            Clob valueAsClob = this.rs.getClob(1);
            Blob valueAsBlob = this.rs.getBlob(1);

            PreparedStatement pStmt = this.conn.prepareStatement(
                    "INSERT INTO testBug1901 VALUES (?)");
            pStmt.setObject(1, valueAsClob, java.sql.Types.CLOB, 0);
            pStmt.executeUpdate();
            pStmt.setObject(1, valueAsBlob, java.sql.Types.BLOB, 0);
            pStmt.executeUpdate();
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
        }
    }

    /**
     * Test fix for BUG#1933 -- Driver property 'maxRows' has no effect.
     *
     * @throws Exception if the test fails.
     */
    public void testBug1933() throws Exception {
        Connection maxRowsConn = null;
        PreparedStatement maxRowsPrepStmt = null;
        Statement maxRowsStmt = null;

        try {
            Properties props = new Properties();

            props.setProperty("maxRows", "1");

            maxRowsConn = getConnectionWithProps(props);

            maxRowsStmt = maxRowsConn.createStatement();

            assertTrue(maxRowsStmt.getMaxRows() == 1);

            this.rs = maxRowsStmt.executeQuery("SELECT 1 UNION SELECT 2");

            this.rs.next();

            maxRowsPrepStmt = maxRowsConn.prepareStatement(
                    "SELECT 1 UNION SELECT 2");

            assertTrue(maxRowsPrepStmt.getMaxRows() == 1);

            this.rs = maxRowsPrepStmt.executeQuery();

            this.rs.next();

            assertTrue(!this.rs.next());
        } finally {
            maxRowsConn.close();
        }
    }

    /**
     * Tests fix for BUG#1958 - Improper bounds checking on
     * PreparedStatement.setFoo().
     *
     * @throws Exception if the test fails.
     */
    public void testBug1958() throws Exception {
        PreparedStatement pStmt = null;

        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1958");
            this.stmt.executeUpdate("CREATE TABLE testBug1958 (field1 int)");

            pStmt = this.conn.prepareStatement(
                    "SELECT * FROM testBug1958 WHERE field1 IN (?, ?, ?)");

            try {
                pStmt.setInt(4, 1);
            } catch (SQLException sqlEx) {
                assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(
                        sqlEx.getSQLState()));
            }
        } finally {
            if (pStmt != null) {
                pStmt.close();
            }

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

    /**
     * Tests fix for BUG#3103 -- java.util.Date not accepted as parameter to
     * PreparedStatement.setObject().
     *
     * @throws Exception if the test fails
     *
     * @deprecated uses deprecated methods of Date class
     */
    public void testBug3103() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug3103 (field1 DATETIME)");

            PreparedStatement pStmt = this.conn.prepareStatement(
                    "INSERT INTO testBug3103 VALUES (?)");

            java.util.Date utilDate = new java.util.Date();

            pStmt.setObject(1, utilDate);
            pStmt.executeUpdate();

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

            java.util.Date retrUtilDate = new java.util.Date(this.rs.getTimestamp(
                        1).getTime());

            // We can only compare on the day/month/year hour/minute/second 
            // interval, because the timestamp has added milliseconds to the
            // internal date...
            assertTrue("Dates not equal",
                (utilDate.getMonth() == retrUtilDate.getMonth())
                && (utilDate.getDate() == retrUtilDate.getDate())
                && (utilDate.getYear() == retrUtilDate.getYear())
                && (utilDate.getHours() == retrUtilDate.getHours())
                && (utilDate.getMinutes() == retrUtilDate.getMinutes())
                && (utilDate.getSeconds() == retrUtilDate.getSeconds()));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @throws Exception ...
     */
    public void testBug3520() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS t");
            this.stmt.executeUpdate("CREATE TABLE t (s1 int,primary key (s1))");
            this.stmt.executeUpdate("INSERT INTO t VALUES (1)");
            this.stmt.executeUpdate("INSERT INTO t VALUES (1)");
        } catch (SQLException sqlEx) {
            System.out.println(sqlEx.getSQLState());
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS t");
        }
    }

    /**
     * Test fix for BUG#3557 -- UpdatableResultSet not picking up default
     * values
     *
     * @throws Exception if test fails.
     */
    public void testBug3557() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3557");

            this.stmt.executeUpdate("CREATE TABLE testBug3557 ( "
                + "`a` varchar(255) NOT NULL default 'XYZ', "
                + "`b` varchar(255) default '123', " + "PRIMARY KEY  (`a`))");

            Statement updStmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            this.rs = updStmt.executeQuery("SELECT * FROM testBug3557");

            assertTrue(this.rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE);

            this.rs.moveToInsertRow();

            assertTrue("XYZ".equals(this.rs.getObject(1)));
            assertTrue("123".equals(this.rs.getObject(2)));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3557");
        }
    }

    /**
     *
     *
     * @throws SQLException ...
     */
    public void testBug3620() throws SQLException {
        long epsillon = 3000; // 3 seconds time difference

        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug3620 (field1 TIMESTAMP)");

            PreparedStatement tsPstmt = this.conn.prepareStatement(
                    "INSERT INTO testBug3620 VALUES (?)");

            Calendar pointInTime = Calendar.getInstance();
            pointInTime.set(2004, 02, 29, 10, 0, 0);

            long pointInTimeOffset = pointInTime.getTimeZone().getRawOffset();

            java.sql.Timestamp ts = new java.sql.Timestamp(pointInTime.getTime()
                                                                      .getTime());

            tsPstmt.setTimestamp(1, ts);
            tsPstmt.executeUpdate();

            String tsValueAsString = getSingleValue("testBug3620", "field1",
                    null).toString();

            System.out.println("Timestamp as string with no calendar: "
                + tsValueAsString.toString());

            Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

            this.stmt.executeUpdate("DELETE FROM testBug3620");