Logo Search packages:      
Sourcecode: mysql-connector-java version File versions

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
 */
00064 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
     */
00115     public StatementRegressionTest(String name) {
        super(name);
    }

    /**
     * Runs all test cases in this test suite
     *
     * @param args
     */
00124     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.
     */
00133     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
     */
00161     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.
     */
00190     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
     */
00248     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.
     */
00272     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.
     */
00303     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.
     */
00328     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
     *
     * @deprecated uses deprecated methods of Date class
     */
00379     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");
        }
    }

    /**
     * Tests fix for BUG#3520
     *
     * @throws Exception ...
     */
00419     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.
     */
00438     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");
        }
    }

    /**
     * Tests fix for BUG#3620 -- Timezone not respected correctly.
     *
     * @throws SQLException if the test fails.
     */
00466     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");

            Properties props = new Properties();
            props.put("useTimezone", "true");
            //props.put("serverTimezone", "UTC");

            Connection tzConn = getConnectionWithProps(props);

            Statement tsStmt = tzConn.createStatement();

            tsPstmt = tzConn.prepareStatement(
                    "INSERT INTO testBug3620 VALUES (?)");

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

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

            Timestamp tsValueAsTimestamp = (Timestamp) getSingleValue("testBug3620",
                    "field1", null);

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

            this.rs = tsStmt.executeQuery("SELECT field1 FROM testBug3620");
            this.rs.next();

            Timestamp tsValueUTC = this.rs.getTimestamp(1, cal);

            //
            // We use this testcase with other vendors, JDBC spec
            // requires result set fields can only be read once,
            // although MySQL doesn't require this ;)
            //
            this.rs = tsStmt.executeQuery("SELECT field1 FROM testBug3620");
            this.rs.next();

            Timestamp tsValueStmtNoCal = this.rs.getTimestamp(1);

            System.out.println(
                "Timestamp specifying UTC calendar from normal statement: " +
                tsValueUTC.toString());

            PreparedStatement tsPstmtRetr = tzConn.prepareStatement(
                    "SELECT field1 FROM testBug3620");

            this.rs = tsPstmtRetr.executeQuery();
            this.rs.next();

            Timestamp tsValuePstmtUTC = this.rs.getTimestamp(1, cal);

            System.out.println(
                "Timestamp specifying UTC calendar from prepared statement: " +
                tsValuePstmtUTC.toString());

            //
            // We use this testcase with other vendors, JDBC spec
            // requires result set fields can only be read once,
            // although MySQL doesn't require this ;)
            //
            this.rs = tsPstmtRetr.executeQuery();
            this.rs.next();

            Timestamp tsValuePstmtNoCal = this.rs.getTimestamp(1);

            System.out.println(
                "Timestamp specifying no calendar from prepared statement: " +
                tsValuePstmtNoCal.toString());

            long stmtDeltaTWithCal = (ts.getTime() -
                tsValueStmtNoCal.getTime());

            long deltaOrig = Math.abs(stmtDeltaTWithCal - pointInTimeOffset);
            
            assertTrue(
                "Difference between original timestamp and timestamp retrieved using java.sql.Statement " +
                "set in database using UTC calendar is not ~= " + epsillon +
                ", it is actually " + deltaOrig,
                (deltaOrig < epsillon));

            long pStmtDeltaTWithCal = (ts.getTime() -
                tsValuePstmtNoCal.getTime());

            System.out.println(Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) +
                " < " + epsillon +
                (Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) < epsillon));
            assertTrue(
                "Difference between original timestamp and timestamp retrieved using java.sql.PreparedStatement " +
                "set in database using UTC calendar is not ~= " + epsillon +
                ", it is actually " + pStmtDeltaTWithCal,
                (Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) < epsillon));

            System.out.println(
                "Difference between original ts and ts with no calendar: " +
                (ts.getTime() - tsValuePstmtNoCal.getTime()) +
                ", offset should be " + pointInTimeOffset);
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
        }
    }

    /**
     * Tests that DataTruncation is thrown when data is truncated.
     *
     * @throws Exception if the test fails.
     */
00605     public void testBug3697() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug3697 (field1 VARCHAR(255))");

            StringBuffer updateBuf = new StringBuffer(
                    "INSERT INTO testBug3697 VALUES ('");

            for (int i = 0; i < 512; i++) {
                updateBuf.append("A");
            }

            updateBuf.append("')");

            try {
                this.stmt.executeUpdate(updateBuf.toString());
            } catch (DataTruncation dtEx) {
                // This is an expected exception....
            }

            SQLWarning warningChain = this.stmt.getWarnings();

            System.out.println(warningChain);
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
        }
    }

    /**
     * Tests fix for BUG#3804, data truncation on server should throw
     * DataTruncation exception.
     *
     * @throws Exception if the test fails
     */
00640     public void testBug3804() throws Exception {
        if (versionMeetsMinimum(4, 1)) {
            try {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
                this.stmt.executeUpdate(
                    "CREATE TABLE testBug3804 (field1 VARCHAR(5))");

                boolean caughtTruncation = false;

                try {
                    this.stmt.executeUpdate(
                        "INSERT INTO testBug3804 VALUES ('1234567')");
                } catch (DataTruncation truncationEx) {
                    caughtTruncation = true;
                    System.out.println(truncationEx);
                }

                assertTrue("Data truncation exception should've been thrown",
                    caughtTruncation);
            } finally {
                this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
            }
        }
    }

    /**
     * Tests BUG#3873 - PreparedStatement.executeBatch() not returning all
     * generated keys (even though that's not JDBC compliant).
     *
     * @throws Exception if the test fails
     */
00671     public void testBug3873() throws Exception {
        PreparedStatement batchStmt = null;

        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug3873 (keyField INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dataField VARCHAR(32))");
            batchStmt = this.conn.prepareStatement("INSERT INTO testBug3873 (dataField) VALUES (?)",
                    Statement.RETURN_GENERATED_KEYS);
            batchStmt.setString(1, "abc");
            batchStmt.addBatch();
            batchStmt.setString(1, "def");
            batchStmt.addBatch();
            batchStmt.setString(1, "ghi");
            batchStmt.addBatch();

            int[] updateCounts = batchStmt.executeBatch();

            this.rs = batchStmt.getGeneratedKeys();

            while (this.rs.next()) {
                System.out.println(this.rs.getInt(1));
            }

            this.rs = batchStmt.getGeneratedKeys();
            assertTrue(this.rs.next());
            assertTrue(1 == this.rs.getInt(1));
            assertTrue(this.rs.next());
            assertTrue(2 == this.rs.getInt(1));
            assertTrue(this.rs.next());
            assertTrue(3 == this.rs.getInt(1));
            assertTrue(!this.rs.next());
        } finally {
            if (batchStmt != null) {
                batchStmt.close();
            }

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

    /**
     * Tests that you can close a statement twice without an NPE.
     *
     * @throws Exception if an error occurs.
     */
00717     public void testCloseTwice() throws Exception {
        Statement closeMe = this.conn.createStatement();
        closeMe.close();
        closeMe.close();
    }

    /**
     * Tests all forms of statements influencing getGeneratedKeys().
     *
     * @throws Exception if the test fails.
     */
00728     public void testGetGeneratedKeysAllCases() throws Exception {
        System.out.println("Using Statement.executeUpdate()\n");

        try {
            createGGKTables();

            //Do the tests
            for (int i = 0; i < tests.length; i++) {
                doGGKTestStatement(tests[i], true);
            }
        } finally {
            dropGGKTables();
        }

        nextID = 1;
        count = 0;

        System.out.println("Using Statement.execute()\n");

        try {
            createGGKTables();

            //Do the tests
            for (int i = 0; i < tests.length; i++) {
                doGGKTestStatement(tests[i], false);
            }
        } 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.
     */
00797     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.
     */
00837     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
     */
00862     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.
     */
00920     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.
     */
00951     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 :(
     */
00977     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.
     */
01048     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);

            assertTrue("Retrieved value of length " + result.length() +
                " != length of inserted value " + charBuf.length,
                result.length() == charBuf.length);
        } finally {
            ((com.mysql.jdbc.Connection) this.conn).setTraceProtocol(false);

            if (this.rs != null) {
                try {
                  this.rs.close();
                } catch (Exception ex) {
                    // ignore
                }

                this.rs = null;
            }

            this.stmt.executeUpdate("DROP TABLE IF EXISTS `charStream'RegressTest`");
            this.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
     */
01157     public void testSetFetchSize() throws Exception {
        int oldFetchSize = this.stmt.getFetchSize();

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

    /**
     * Tests fix for BUG#907
     *
     * @throws Exception if an error occurs
     */
01172     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();
            }
        }
    }

    /**
     * Tests for timestamp NPEs occuring in binary-format timestamps.
     *
     * @throws Exception DOCUMENT ME!
     *
     * @deprecated yes, we know we are using deprecated methods here :)
     */
01193     public void testTimestampNPE() throws Exception {
        try {
            Timestamp ts = new Timestamp(System.currentTimeMillis());

            this.stmt.executeUpdate("DROP TABLE IF EXISTS testTimestampNPE");
            this.stmt.executeUpdate(
                "CREATE TABLE testTimestampNPE (field1 TIMESTAMP)");

            this.pstmt = this.conn.prepareStatement(
                    "INSERT INTO testTimestampNPE VALUES (?)");
            this.pstmt.setTimestamp(1, ts);
            this.pstmt.executeUpdate();

            this.pstmt = this.conn.prepareStatement(
                    "SELECT field1 FROM testTimestampNPE");

            this.rs = this.pstmt.executeQuery();

            this.rs.next();

            System.out.println(this.rs.getString(1));

            this.rs.getDate(1);

            Timestamp rTs = this.rs.getTimestamp(1);
            assertTrue("Retrieved year of " + rTs.getYear() +
                " does not match " + ts.getYear(), rTs.getYear() == ts.getYear());
            assertTrue("Retrieved month of " + rTs.getMonth() +
                " does not match " + ts.getMonth(),
                rTs.getMonth() == ts.getMonth());
            assertTrue("Retrieved date of " + rTs.getDate() +
                " does not match " + ts.getDate(), rTs.getDate() == ts.getDate());
        } finally {
        }
    }

    /**
     * Tests fix for updatable streams being supported in updatable result
     * sets.
     *
     * @throws Exception if the test fails.
     */
01235     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);

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

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

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

            i++;

            res.append(" " + this.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
        this.rs = this.stmt.executeQuery("SELECT id, val FROM testggk");
        System.out.println("New table contents ");

        while (this.rs.next())
            System.out.println("Id " + this.rs.getString(1) + " val " +
                        this.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) {
            this.stmt.executeUpdate(cmd.toString(), Statement.RETURN_GENERATED_KEYS);
        } else {
            this.stmt.execute(cmd.toString(), Statement.RETURN_GENERATED_KEYS);
        }

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

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

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

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

            i++;

            res.append(" " + this.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
        this.rs = this.stmt.executeQuery("SELECT id, val FROM testggk");
        System.out.println("New table contents ");

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

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

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

    /**
     * Tests fix for BUG#4311 - Error in JDBC retrieval of mediumint column 
     * when using prepared statements and binary result sets.
     * 
     * @throws Exception if the test fails.
     */
01439     public void testBug4311() throws Exception {
        try {
            int lowValue = -8388608;
            int highValue = 8388607;

            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
            this.stmt.executeUpdate(
                "CREATE TABLE testBug4311 (low MEDIUMINT, high MEDIUMINT)");
            this.stmt.executeUpdate("INSERT INTO testBug4311 VALUES (" +
                lowValue + ", " + highValue + ")");

            PreparedStatement pStmt = this.conn.prepareStatement(
                    "SELECT low, high FROM testBug4311");
            this.rs = pStmt.executeQuery();
            assertTrue(this.rs.next());
            assertTrue(this.rs.getInt(1) == lowValue);
            assertTrue(this.rs.getInt(2) == highValue);
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
        }
    }

    /**
     * Tests fix for BUG#4510 -- Statement.getGeneratedKeys() fails
     * when key > 32767
     * 
     * @throws Exception if the test fails
     */
01467     public void testBug4510() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
            this.stmt.executeUpdate("CREATE TABLE testBug4510 (" +
                "field1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT," +
                "field2 VARCHAR(100))");
            this.stmt.executeUpdate(
                "INSERT INTO testBug4510 (field1, field2) VALUES (32767, 'bar')");

            PreparedStatement p = this.conn.prepareStatement("insert into testBug4510 (field2) values (?)",
                    Statement.RETURN_GENERATED_KEYS);

            p.setString(1, "blah");

            p.executeUpdate();

            ResultSet rs = p.getGeneratedKeys();
            rs.next();
            System.out.println("Id: " + rs.getInt(1));
            rs.close();
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
        }
    }
    
    /**
     * Tests fix for BUG#5012 -- ServerPreparedStatements dealing with
     * return of DECIMAL type don't work.
     * 
     * @throws Exception if the test fails.
     */
01498     public void testBug5012() throws Exception {
      PreparedStatement pStmt = null;
      String valueAsString = "12345.12";
      
      try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
            this.stmt.executeUpdate("CREATE TABLE testBug5012(field1 DECIMAL(6,2))");
            this.stmt.executeUpdate("INSERT INTO testBug5012 VALUES (" + valueAsString + ")");
            
            pStmt = this.conn.prepareStatement("SELECT field1 FROM testBug5012");
            this.rs = pStmt.executeQuery();
            assertTrue(this.rs.next());
            assertTrue(new BigDecimal(valueAsString).equals(this.rs.getBigDecimal(1)));
      } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
            
            if (pStmt != null) {
                  pStmt.close();
            }
      }
    }
    
    /**
     * Tests for BUG#5191 -- PreparedStatement.executeQuery() gives 
     * OutOfMemoryError
     * 
     * @throws Exception if the test fails.
     */
01526     public void testBug5191() throws Exception {
        PreparedStatement pStmt = null;

        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191C");

            this.stmt.executeUpdate("CREATE TABLE testBug5191Q"
                    + "(QuestionId int NOT NULL AUTO_INCREMENT, "
                    + "Text VARCHAR(200), " + "PRIMARY KEY(QuestionId))");

            this.stmt.executeUpdate("CREATE TABLE testBug5191C"
                    + "(CategoryId int, " + "QuestionId int)");

            String[] questions = new String[] { "What is your name?",
                    "What is your quest?",
                    "What is the airspeed velocity of an unladen swollow?",
                    "How many roads must a man walk?", "Where's the tea?", };

            for (int i = 0; i < questions.length; i++) {
                this.stmt.executeUpdate("INSERT INTO testBug5191Q(Text)"
                        + " VALUES (\"" + questions[i] + "\")");
                int catagory = (i < 3) ? 0 : i;

                this.stmt.executeUpdate("INSERT INTO testBug5191C"
                        + "(CategoryId, QuestionId) VALUES (" + catagory + ", "
                        + i + ")");
                /*
                 this.stmt.executeUpdate("INSERT INTO testBug5191C"
                 + "(CategoryId, QuestionId) VALUES (" + catagory
                 + ", (SELECT testBug5191Q.QuestionId"
                 + " FROM testBug5191Q "
                 + "WHERE testBug5191Q.Text LIKE '" + questions[i]
                 + "'))");
                 */
            }

            pStmt = this.conn.prepareStatement("SELECT qc.QuestionId, q.Text "
                    + "FROM testBug5191Q q, testBug5191C qc "
                    + "WHERE qc.CategoryId = ? "
                    + " AND q.QuestionId = qc.QuestionId");

            int catId = 0;
            for (int i = 0; i < 100; i++) {
                execQueryBug5191(pStmt, catId);
            }

        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191C");

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

    public void testBug5235() throws Exception
         {
      Properties props = new Properties();
      props.setProperty("zeroDateTimeBehavior", "convertToNull");
      
      Connection convertToNullConn = getConnectionWithProps(props);
            Statement convertToNullStmt = convertToNullConn.createStatement();
            try {
                  convertToNullStmt.executeUpdate ("DROP TABLE IF EXISTS testBug5235");
                  convertToNullStmt.executeUpdate ("CREATE TABLE testBug5235(field1 DATE)");
                  convertToNullStmt.executeUpdate("INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");

               PreparedStatement ps = convertToNullConn.prepareStatement ("SELECT field1 FROM testBug5235");
               this.rs = ps.executeQuery();

               

               if (this.rs.next())
               {  Date d = (Date) rs.getObject ("field1");
                  System.out.println ("date: " + d);
               }
            }
            finally {
                  convertToNullStmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
            }
         }
      

    public void testBug5510() throws Exception {
      // This is a server bug that should be fixed by 4.1.6
      if (versionMeetsMinimum(4, 1, 6)) {
            try {
                  this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5510");
                  
                  this.stmt.executeUpdate("CREATE TABLE `testBug5510` ("
                                + "`a` bigint(20) NOT NULL auto_increment,"
                                + "`b` varchar(64) default NULL,"
                                + "`c` varchar(64) default NULL,"
                                + "`d` varchar(255) default NULL,"
                                + "`e` int(11) default NULL,"
                                + "`f` varchar(32) default NULL,"
                                + "`g` varchar(32) default NULL,"
                                + "`h` varchar(80) default NULL,"
                                + "`i` varchar(255) default NULL,"
                                + "`j` varchar(255) default NULL,"
                                + "`k` varchar(255) default NULL,"
                                + "`l` varchar(32) default NULL,"
                                + "`m` varchar(32) default NULL,"
                                + "`n` timestamp NOT NULL default CURRENT_TIMESTAMP on update"
                                + " CURRENT_TIMESTAMP,"
                                + "`o` int(11) default NULL,"
                                + "`p` int(11) default NULL,"
                                + "PRIMARY KEY  (`a`)"
                                      + ") ENGINE=InnoDB DEFAULT CHARSET=latin1");
                  PreparedStatement pStmt = this.conn.prepareStatement("INSERT INTO testBug5510 (a) VALUES (?)");
                  pStmt.setNull(1, 0);
                  pStmt.executeUpdate();
                  
            } finally {
                  this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5510");
            }
      }
    }
    /**
     * @param pStmt
     * @param catId
     * @throws SQLException
     */
01651     private void execQueryBug5191(PreparedStatement pStmt, int catId)
            throws SQLException {
        pStmt.setInt(1, catId);

        this.rs = pStmt.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.next());
        // assertTrue(rs.next());

        assertFalse(this.rs.next());
    }
    
    public void testBug5450() throws Exception {
      if (versionMeetsMinimum(4, 1)) {
        String table = "testBug5450";
        String column = "policyname";

        try {
            Properties props = new Properties();
            props.setProperty("characterEncoding", "utf8");
            
            Connection utf8Conn = getConnectionWithProps(props);
            Statement utfStmt = utf8Conn.createStatement();
            
            this.stmt.executeUpdate("DROP TABLE IF EXISTS " + table);

            this.stmt.executeUpdate("CREATE TABLE " + table
                    + "(policyid int NOT NULL AUTO_INCREMENT, " + column
                    + " VARCHAR(200), " + "PRIMARY KEY(policyid)) DEFAULT CHARACTER SET utf8");

            String pname0 = "inserted \uac00 - foo - \u4e00";

            utfStmt.executeUpdate("INSERT INTO " + table + "(" + column
                        + ")" + " VALUES (\"" + pname0 + "\")");

            this.rs = utfStmt.executeQuery("SELECT " + column + " FROM " + table);

            this.rs.first();
            String pname1 = this.rs.getString(column);
            
            assertEquals(pname0, pname1);
            byte[] bytes = this.rs.getBytes(column);

            String pname2 = new String(bytes, "utf-8"); 
            assertEquals(pname1, pname2);

            utfStmt.executeUpdate("delete from " + table + " where " + column
                    + " like 'insert%'");

            PreparedStatement s1 = utf8Conn.prepareStatement("insert into " + table
                    + "(" + column + ") values (?)");
            
            s1.setString(1, pname0);
            s1.executeUpdate();
            
            
            String byteesque = "byte " + pname0;
            byte[] newbytes = byteesque.getBytes("utf-8");

            s1.setBytes(1, newbytes);
            s1.executeUpdate();

            this.rs = utfStmt.executeQuery("select " + column + " from " + table
                    + " where " + column + " like 'insert%'");
            this.rs.first();
            String pname3 = this.rs.getString(column);
            assertEquals(pname0, pname3);

            this.rs = utfStmt.executeQuery("select " + column + " from " + table
                    + " where " + column + " like 'byte insert%'");
            this.rs.first();

            String pname4 = this.rs.getString(column);
            assertEquals(byteesque, pname4);

        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS " + table);
        }
      }
    }
    
    public void testPStmtTypesBug() throws Exception {
      try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
            this.stmt.executeUpdate("CREATE TABLE testPStmtTypesBug(field1 INT)");
            this.pstmt = this.conn.prepareStatement("INSERT INTO testPStmtTypesBug VALUES (?)");
            this.pstmt.setObject(1, null, Types.INTEGER);
            this.pstmt.addBatch();
            this.pstmt.setInt(1, 1);
            this.pstmt.addBatch();
            this.pstmt.executeBatch();
            
      } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
      }
    }
    
    public void testTruncationWithChar() throws Exception {
      try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testTruncationWithChar");
            this.stmt.executeUpdate("CREATE TABLE testTruncationWithChar (field1 char(2))");
            
            this.pstmt = this.conn.prepareStatement("INSERT INTO testTruncationWithChar VALUES (?)");
            this.pstmt.setString(1, "00");
            this.pstmt.executeUpdate();
      } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testTruncationWithChar");
      }
    }

      /**
       * Tests fix for BUG#5133 -- PreparedStatement.toString() doesn't
       * return correct value if no parameters are present in statement.
       * 
       * @throws Exception
       */
01768       public void testBug5133() throws Exception {
            String query = "SELECT 1";
            String output = this.conn.prepareStatement(query).toString();
            System.out.println(output);
            
            assertTrue(output.indexOf(query) != -1);  
      }

      /**
     * Tests fix for BUG#5874, timezone correction goes in wrong 'direction'
     * when useTimezone=true and server timezone differs from client timezone.
     * 
     * @throws Exception if the test fails.
     */
01782     public void testBug5874() throws Exception {
      try {
            String clientTimezoneName = "America/Los_Angeles";
            String serverTimezoneName = "America/Chicago";
            
            
            TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));
            
            long epsillon = 3000; // 3 seconds difference
            
            long clientTimezoneOffsetMillis = TimeZone.getDefault().getRawOffset();
            long serverTimezoneOffsetMillis = TimeZone.getTimeZone(serverTimezoneName).getRawOffset();
            
            long offsetDifference = clientTimezoneOffsetMillis - serverTimezoneOffsetMillis;
            
            Properties props = new Properties();
            props.put("useTimezone", "true");
            props.put("serverTimezone", serverTimezoneName);
            
            Connection tzConn = getConnectionWithProps(props);
            Statement tzStmt = tzConn.createStatement();
            tzStmt.executeUpdate("DROP TABLE IF EXISTS timeTest");
            tzStmt.executeUpdate(
            "CREATE TABLE timeTest (tstamp DATETIME, t TIME)");
            
            PreparedStatement pstmt = tzConn.prepareStatement(
            "INSERT INTO timeTest VALUES (?, ?)");
            
            long now = System.currentTimeMillis(); // Time in milliseconds since 1/1/1970 GMT
            
            Timestamp nowTstamp = new Timestamp(now);
            Time nowTime = new Time(now);
            
            pstmt.setTimestamp(1, nowTstamp);
            pstmt.setTime(2, nowTime);
            pstmt.executeUpdate();
            
            rs = tzStmt.executeQuery("SELECT * from timeTest");
            
            // Timestamps look like this: 2004-11-29 13:43:21
            SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
            
            while (rs.next()) {
                  // Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types when calling getString()...
                  String retrTimestampString = new String(rs.getBytes(1));
                  Timestamp retrTimestamp = rs.getTimestamp(1);
                  
                  java.util.Date timestampOnServer = timestampFormat.parse(retrTimestampString);
                  
                  long retrievedOffsetForTimestamp = retrTimestamp.getTime() - timestampOnServer.getTime();
                  
                  assertTrue(
                              "Difference between original timestamp and timestamp retrieved using client timezone is not "
                              + offsetDifference,
                                    (Math.abs(retrievedOffsetForTimestamp - offsetDifference) < epsillon));
                  
                  String retrTimeString = new String(rs.getBytes(2));
                  Time retrTime = rs.getTime(2);
                  
                  java.util.Date timeOnServerAsDate = timeFormat.parse(retrTimeString);
                  Time timeOnServer = new Time(timeOnServerAsDate.getTime());
                  
                  long retrievedOffsetForTime = retrTime.getTime() - timeOnServer.getTime();
                  
                  assertTrue(
                              "Difference between original times and time retrieved using client timezone is not "
                              + offsetDifference,
                                    (Math.abs(retrievedOffsetForTime - offsetDifference) < epsillon));
            }
      } finally {
            stmt.executeUpdate("DROP TABLE IF EXISTS timeTest");
      }
    }

    public void testBug6823() throws SQLException {
        innerBug6823(true);
        innerBug6823(false);
    }

    /**
     * @param continueBatchOnError
     * @throws SQLException
     */
01866     private void innerBug6823(boolean continueBatchOnError) throws SQLException {
        Properties continueBatchOnErrorProps = new Properties();
        continueBatchOnErrorProps.setProperty("continueBatchOnError", Boolean
                .toString(continueBatchOnError));
        this.conn = getConnectionWithProps(continueBatchOnErrorProps);
        Statement statement = conn.createStatement();

        String tableName = "testBug6823";

        createTable(tableName, "(id int not null primary key auto_increment,"
                + " strdata1 varchar(255) not null, strdata2 varchar(255),"
                + " UNIQUE INDEX (strdata1))");

        PreparedStatement pStmt = this.conn.prepareStatement("INSERT INTO "
                + tableName + " (strdata1, strdata2) VALUES (?,?)");

        int c = 0;
        addBatchItems(statement, pStmt, tableName, ++c);
        addBatchItems(statement, pStmt, tableName, ++c);
        addBatchItems(statement, pStmt, tableName, ++c);
        addBatchItems(statement, pStmt, tableName, c); // duplicate entry
        addBatchItems(statement, pStmt, tableName, ++c);
        addBatchItems(statement, pStmt, tableName, ++c);

        int expectedUpdateCounts = continueBatchOnError ? 6 : 3;

        BatchUpdateException e1 = null;
        BatchUpdateException e2 = null;

        int[] updateCountsPstmt = null;
        try {
            updateCountsPstmt = pStmt.executeBatch();
        } catch (BatchUpdateException e) {
            e1 = e;
            updateCountsPstmt = e1.getUpdateCounts();
        }

        int[] updateCountsStmt = null;
        try {
            updateCountsStmt = statement.executeBatch();
        } catch (BatchUpdateException e) {
            e2 = e;
            updateCountsStmt = e1.getUpdateCounts();
        }

        assertNotNull(e1);
        assertNotNull(e2);

        assertEquals(expectedUpdateCounts, updateCountsPstmt.length);
        assertEquals(expectedUpdateCounts, updateCountsStmt.length);

        if (continueBatchOnError) {
            assertTrue(updateCountsPstmt[3] == Statement.EXECUTE_FAILED);
            assertTrue(updateCountsStmt[3] == Statement.EXECUTE_FAILED);
        }

        int psRows = 0;
        this.rs = this.stmt.executeQuery("SELECT * from " + tableName
                + " WHERE strdata1 like \"ps_%\"");
        while (rs.next()) {
            psRows++;
        }
        assertTrue(psRows > 0);

        int sRows = 0;
        this.rs = this.stmt.executeQuery("SELECT * from " + tableName
                + " WHERE strdata1 like \"s_%\"");
        while (rs.next()) {
            sRows++;
        }
        assertTrue(sRows > 0);

        assertTrue(psRows + "!=" + sRows, psRows == sRows);
    }

    private void addBatchItems(Statement statement, PreparedStatement pStmt,
            String tableName, int i) throws SQLException {
        pStmt.setString(1, "ps_batch_" + i);
        pStmt.setString(2, "ps_batch_" + i);
        pStmt.addBatch();

        statement.addBatch("INSERT INTO " + tableName
                + " (strdata1, strdata2) VALUES " + "(\"s_batch_" + i
                + "\",\"s_batch_" + i + "\")");
    }
    
    public void testBug7461() throws Exception {
      String tableName = "testBug7461";
      
      try {
            createTable(tableName, "(field1 varchar(4))");
            File tempFile = File.createTempFile("mysql-test", ".txt");
            tempFile.deleteOnExit();
            
            FileOutputStream fOut = new FileOutputStream(tempFile);
            fOut.write("abcdefghijklmnop".getBytes());
            fOut.close();

            try {
                  this.stmt.executeQuery("LOAD DATA LOCAL INFILE '" + 
                        tempFile.toString() + "' INTO TABLE " + tableName);
            } catch (SQLException sqlEx) {
                  this.stmt.getWarnings();
            }
            
            
      } finally {
            dropTable(tableName);
      }
      
    }

      public void testServerPrepStmtDeadlock() throws Exception {
            
            Connection c = getConnectionWithProps(null);
            
            
            Thread testThread1 = new PrepareThread(c);
            Thread testThread2 = new PrepareThread(c);
            testThread1.start();
            testThread2.start();
            Thread.sleep(30000);
            assertTrue(testServerPrepStmtDeadlockCounter >= 10);
      }
      
      public void testBug8181() throws Exception {
            
            try {
                  this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8181");
                  this.stmt.executeUpdate("CREATE TABLE testBug8181(col1 VARCHAR(20),col2 INT)");
                  
                  this.pstmt = this.conn.prepareStatement("INSERT INTO testBug8181(col1,col2) VALUES(?,?)");
                  
                  for (int i = 0; i < 20; i++) {
                        this.pstmt.setString(1, "Test "+i);
                        this.pstmt.setInt(2, i);
                        this.pstmt.addBatch();
                }
                  
                  pstmt.executeBatch();

            } finally {
                  this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8181");
                  
                  if (this.pstmt != null) {
                        this.pstmt.close();
                  }
            }
      }
      
      /**
       * Tests fix for BUG#8487 - PreparedStatements not creating
       * streaming result sets.
       * 
       * @throws Exception if the test fails.
       */
02022       public void testBug8487() throws Exception {
            try {
                  this.pstmt = this.conn.prepareStatement("SELECT 1",
                              ResultSet.TYPE_FORWARD_ONLY,
                              ResultSet.CONCUR_READ_ONLY );

                  this.pstmt.setFetchSize( Integer.MIN_VALUE );
                  this.rs = this.pstmt.executeQuery();
                  try {
                        this.conn.createStatement().executeQuery("SELECT 2");
                        fail("Should have caught a streaming exception here");
                  } catch (SQLException sqlEx) {
                        assertTrue(sqlEx.getMessage() != null && 
                                    sqlEx.getMessage().indexOf("Streaming") != -1);
                  }

            } finally {
                  if (this.rs != null) {
                        while (this.rs.next());
                        
                        rs.close();
                  }
                  
                  if (this.pstmt != null) {
                        this.pstmt.close();
                  }
            }
      }
      
      /**
       * Server doesn't accept everything as a server-side prepared statement,
       * so by default we scan for stuff it can't handle.
       * 
       * @throws SQLException
       */
02057       public void testBug4718() throws SQLException {
            if (versionMeetsMinimum(4, 1, 0)) {
                  this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT ?");
                  assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);
                  
                  try {
                        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");
                        this.stmt.executeUpdate("CREATE TABLE testBug4718 (field1 INT)");
                        
                        this.pstmt = this.conn.prepareStatement("ALTER TABLE testBug4718 ADD INDEX (field1)");
                        assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);
                        
                        this.pstmt = this.conn.prepareStatement("SELECT 1");
                        assertTrue(this.pstmt instanceof ServerPreparedStatement);
                  } finally {
                        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");
                  }
            }
      }
}

Generated by  Doxygen 1.6.0   Back to index