import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * test class to show mysql java connector problem w/ 65535 limit on prep statements;
 * see line 65 in com.mysql.jdbc.MysqlIO where this limit is apparently set:
private static int maxBufferSize = 65535;

 * output of this program is
Retrieved string of length: 65535 and expected: 80000

 * when using mysql server v. 3 & v4., using mysql driver v. 3.1.1 alpha & v. 3.0.12 production
 *
 * (solutions: eliminate limit, or throw exception explaining limit was exceeded.  do NOT silently truncate)
 *
 * In order to run test:
 *
 *  create this table in DB 'test'
CREATE DATABASE 'test';
CREATE TABLE `test1` (
 `TestKey` int(11) NOT NULL default '0',
 `BitTest` char(1) default NULL,
 `TextTest` text default NULL,
 `BooleanTest` char(1) default NULL,
 PRIMARY KEY  (`TestKey`)
 ) TYPE=MyISAM;

 * @author Larry Hamel, Codeguild, Inc.
 */
public class MysqlConnTest {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:mysql://localhost/test";
        String user = "test";
        String pass = "test";



        String insert = "INSERT INTO test.TEST1 (TestKey, BitTest, TextTest, BooleanTest) VALUES (1, null, ?, null)";
        String select = "SELECT * FROM test.TEST1 WHERE TestKey = 1";
        String updCommand = "UPDATE test.TEST1 SET BitTest = null, TextTest = ? , BooleanTest = null WHERE TestKey = 1";
        Connection conn = null;
        String teststr = repeatString("0123456789", 8000 ); // string of length 80000

        try {

            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            Statement s = conn.createStatement();
            s.executeQuery(select);
            ResultSet rs = s.getResultSet();
            boolean exists = rs.next();
            if ( exists ) System.out.println("first col of select: " + rs.getString(1));

            s.close();

            PreparedStatement prepStatement = null;
            if ( exists ) {
                prepStatement = conn.prepareStatement(updCommand);
            } else {
                System.out.println("Inserting.");
                prepStatement = conn.prepareStatement(insert);
            }

            prepStatement.setString(1, teststr);

            prepStatement.execute();
            if ( prepStatement.getUpdateCount() <= 0) {
                System.out.println("Failed to update: " + prepStatement.getWarnings());
            }
            prepStatement.close();

            s = conn.createStatement();
            s.executeQuery(select);
            rs = s.getResultSet();
            if ( rs.next() ) {
                String result = rs.getString("TextTest");
                if ( result == null) result = "";
                System.out.println("Retrieved string of length: " + result.length() + " and expected: " + teststr.length());
            } else {
                System.out.println("cannot find any rows");
            }

            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            if ( conn != null ) conn.close();
        }
    }

    static public String repeatString(String str, int n) {
        if (str == null) return null;

        StringBuffer buffer = new StringBuffer(n*str.length());
            int val = n * str.length();
            if (val > buffer.capacity()) {
                buffer.ensureCapacity(val);
            }
            //            StringBuffer buffer = new StringBuffer(n * str.length());
            for (int i = 0; i < n; i++) {
                buffer.append(str);
            }
            return buffer.toString();
    }
}