import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;

import com.mysql.jdbc.util.BaseBugReport;

public class BugPreparedStatement extends BaseBugReport {
  // OS is Windows XP
  // JVM is jdk1.5.0_02
  // Connector/J is mysql-connector-java-5.0.3 ( $Date: 2006-07-26 17:26:47
  // +0200 (Wed, 26 Jul 2006) $, $Revision: 5553 $ )
  // MySQL is 5.0.21-community-debug-log

  // character_set_client utf8
  // character_set_connection utf8
  // character_set_database utf8
  // character_set_filesystem binary
  // character_set_results
  // character_set_server utf8
  // character_set_system utf8
  // character_sets_dir C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
  // collation_connection utf8_general_ci
  // collation_database utf8_general_ci
  // collation_server utf8_general_ci

  private static final String DB_JDBCDRIVER = "com.mysql.jdbc.Driver";

  private static final String DB_SERVER = "localhost";

  private static final String DB_USR = "root"; // ### adjust ###

  private static final String DB_PWD = "mysql"; // ### adjust ###

  private static final String DB_CATALOG = "test";

  private static final String DB_TABLENAME = "mytest";

  private static final String DB_JDBCURL = "jdbc:mysql://" + DB_SERVER + "/"
      + DB_CATALOG + "?user=" + DB_USR + "&password=" + DB_PWD
      + "&profileSQL=true" + "&useUsageAdvisor=false"
      + "&dumpQueriesOnException=true" + "&explainSlowQueries=false"
      + "&traceProtocol=false" + "&useOldUTF8Behavior=false"
      + "&logger=com.mysql.jdbc.log.Log4JLogger" + "&useServerPrepStmts=false";

  public void setUp() throws Exception {
    Class clazz = Class.forName(DB_JDBCDRIVER);
    clazz.newInstance();
    System.out.println(DB_JDBCURL);
  }

  public void tearDown() throws Exception {
    String sql1 = "DROP TABLE IF EXISTS " + DB_TABLENAME;
    Statement stmt1 = getConnection().createStatement();
    executePS(stmt1, sql1);
  }

  public void runTest() throws Exception {
    String sql1 = "CREATE TABLE IF NOT EXISTS " + DB_TABLENAME + " (" //
        + "col1 varchar(10) PRIMARY KEY not null,"//
        + "col2 varchar(10) not null"//
        + ") ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_bin;";
    Statement stmt1 = getConnection().createStatement();
    executePS(stmt1, sql1);

    String sql2 = "DELETE FROM " + DB_TABLENAME;
    Statement stmt2 = getConnection().createStatement();
    executePS(stmt2, sql2);

    String sql3 = "INSERT INTO " + DB_TABLENAME
        + " (col1,col2) VALUES(\"A\",\"B\")";
    Statement stmt3 = getConnection().createStatement();
    executePS(stmt3, sql3); // OK

    String sql4 = "INSERT INTO " + DB_TABLENAME + " (col1,col2) VALUES(?,?)";
    PreparedStatement stmt4 = getConnection().prepareStatement(sql4);
    stmt4.setString(1, "B");
    stmt4.setString(2, "C");
    // ### throws com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an
    // ### error in your SQL syntax; check the manual that corresponds to your MySQL
    // ### server version for the right syntax to use near '?,?)' at line 1
    executePS(stmt4, sql4);
  }

  public String getUrl() {
    return DB_JDBCURL;
  }

  public static void main(String[] args) throws Exception {
    System.out.println(System.getProperties());
    new BugPreparedStatement().run();
  }

  private void executePS(Statement stmt, String sql) throws SQLException {
    System.out.println("about to call [" + sql + "]");
    boolean success = stmt.execute(sql);
    System.out.println("called -- result is " + success);
    SQLWarning sw = stmt.getWarnings();
    while (sw != null) {
      System.out.println(sw);
      sw = sw.getNextWarning();
    }
  }
}