statementregressiontest.java

在资料浩瀚的互联网中

/*
      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 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 java.io.ByteArrayInputStream;
import java.io.CharArrayReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.Writer;
import java.math.BigDecimal;
import java.sql.BatchUpdateException;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DataTruncation;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Properties;
import java.util.TimeZone;

import testsuite.BaseTestCase;

import com.mysql.jdbc.SQLError;
import com.mysql.jdbc.ServerPreparedStatement;


/**
 * Regression tests for the Statement class
 *
 * @author Mark Matthews
 */
public class StatementRegressionTest extends BaseTestCase {
    class PrepareThread extends Thread {
		Connection c;
		
		PrepareThread(Connection cn) {
			c = cn;
		}
		
		
		public void run()
		{
			for (int i = 0; i < 20; i++) // force this to end eventually
			{
				try
				{  
					c.prepareStatement("SELECT 1");
					testServerPrepStmtDeadlockCounter++; 
					Thread.sleep(400);
				} catch (SQLException sqlEx) {
					throw new RuntimeException(sqlEx);
				}
				catch (InterruptedException e)
				{
					e.printStackTrace();
				}
			}
		}
	}

	/*
     * 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;
	private int testServerPrepStmtDeadlockCounter = 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);
    }

    /**
     * Tests fix for BUG#1774 -- Truncated words after double quote
     *
     * @throws Exception if the test fails.
     */
    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 {
    	if (versionMeetsMinimum(4, 0)) {
	        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());
	
	            props.setProperty("useServerPrepStmts", "false");
	
	            maxRowsConn = getConnectionWithProps(props);
	
	            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 the fix for BUG#1934 -- prepareStatement dies silently when encountering 
     * Statement.RETURN_GENERATED_KEY
     *
     * @throws Exception if the test fails
     */
    public void testBug1934() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
            this.stmt.executeUpdate("CREATE TABLE testBug1934 (field1 INT)");

            System.out.println("Before prepareStatement()");

            this.pstmt = this.conn.prepareStatement("INSERT INTO testBug1934 VALUES (?)",
                    java.sql.Statement.RETURN_GENERATED_KEYS);

            assertTrue(this.pstmt != null);

            System.out.println("After prepareStatement() - " + this.pstmt);
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
        }
    }

    /**
     * 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 the fix for BUG#2606, server-side prepared statements not
     * returning datatype YEAR correctly.
     *
     * @throws Exception if the test fails.
     */
    public void testBug2606() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2606");
            this.stmt.executeUpdate("CREATE TABLE testBug2606(year_field YEAR)");
            this.stmt.executeUpdate("INSERT INTO testBug2606 VALUES (2004)");

            PreparedStatement yrPstmt = this.conn.prepareStatement(
                    "SELECT year_field FROM testBug2606");

            this.rs = yrPstmt.executeQuery();

            assertTrue(this.rs.next());

            assertTrue(2004 == this.rs.getInt(1));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2606");
        }
    }

    /**
     * Tests the fix for BUG#2671, nulls encoded incorrectly in server-side
     * prepared statements.
     *
     * @throws Exception if an error occurs.
     */
    public void testBug2671() throws Exception {
        if (versionMeetsMinimum(4, 1)) {
            try {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS test3");
                this.stmt.executeUpdate("CREATE TABLE test3 (" +
                    " `field1` int(8) NOT NULL auto_increment," +
                    " `field2` int(8) unsigned zerofill default NULL," +
                    " `field3` varchar(30) binary NOT NULL default ''," +
                    " `field4` varchar(100) default NULL," +
                    " `field5` datetime NOT NULL default '0000-00-00 00:00:00'," +
                    " PRIMARY KEY  (`field1`)," +
                    " UNIQUE KEY `unq_id` (`field2`)," +
                    " UNIQUE KEY  (`field3`)," + " UNIQUE KEY  (`field2`)" +
                    " ) TYPE=InnoDB CHARACTER SET utf8");

                this.stmt.executeUpdate(
                    "insert into test3 (field1, field3, field4) values (1,'blewis','Bob Lewis')");

                String query = "              " + "UPDATE                   " +
                    "  test3                  " + "SET                      " +
                    "  field2=?               " + "  ,field3=?          " +
                    "  ,field4=?           " + "  ,field5=?        " +
                    "WHERE                    " +
                    "  field1 = ?                 ";

                java.sql.Date mydate = null;

                this.pstmt = this.conn.prepareStatement(query);

                this.pstmt.setInt(1, 13);
                this.pstmt.setString(2, "abc");
                this.pstmt.setString(3, "def");
                this.pstmt.setDate(4, mydate);
                this.pstmt.setInt(5, 1);

                int retval = this.pstmt.executeUpdate();
                assertTrue(retval == 1);
            } finally {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS test3");
            }
        }
    }

    /**
     * Tests fix for BUG#3103 -- java.util.Date not accepted as parameter to
     * PreparedStatement.setObject().
     *
     * @throws Exception if the test fails
     *