import java.sql.*;

/**
 * Testcase for MySQL Connector/J 5.1.7 bug report.
 *
 * I think the bug is in UpdatableResultSet.deleteRow(), where the binary data is being corrupted
 * because it is going through a String (with some character encoding), rather than being treated
 * as raw binary data.  Compare to com.mysql.jdbc.PreparedStatement.setBytes(), which seems to work
 * correctly.
 */
public class TestDeleteRowBinaryColumn {

    public static void main(String[] args) throws SQLException {
        String databaseUrl = "jdbc:mysql://gandalf/chris?autoGenerateTestcaseScript=true";
        String databaseUser = "webapp";
        String databasePassword = "v1cut";
        Connection conn = DriverManager.getConnection(databaseUrl, databaseUser, databasePassword);

        // create the table
        Statement createStmt = conn.createStatement();
        createStmt.executeUpdate("DROP TABLE IF EXISTS testtable_bincolumn");
        createStmt.close();
        createStmt = conn.createStatement();
        createStmt.executeUpdate("CREATE TABLE testtable_bincolumn (" +
                "bincolumn binary(8) NOT NULL, " +
                "PRIMARY KEY (bincolumn)" +
                ") ENGINE=InnoDB");
        createStmt.close();

        String pkValue1 = "0123456789ABCD90";
        String pkValue2 = "0123456789ABCD00";
        // put some data in it
        createStmt = conn.createStatement();
        createStmt.executeUpdate("INSERT INTO testtable_bincolumn (bincolumn) " +
                "VALUES (unhex('"+pkValue1+"')), (unhex('"+pkValue2+"'))");
        createStmt.close();

        // cause the bug
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery("SELECT * FROM testtable_bincolumn WHERE bincolumn = unhex('"+pkValue1+"')");
        if (rs.next()) {
            System.out.println("We got a row for "+pkValue1+", about to delete it ...");
            rs.deleteRow();
        }
        rs.close();
        stmt.close();

        // At this point the row with pkValue1 should be deleted.  We'll select it back to see.
        // If the row comes back, the testcase has failed.

        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT * FROM testtable_bincolumn WHERE bincolumn = unhex('"+pkValue1+"')");
        if (rs.next()) {
            System.out.println("Testcase #1 FAILED, the row was not deleted by deleteRow()");
        } else {
            System.out.println("Testcase #1 PASSED, the row was deleted by deleteRow()");
        }

        rs.close();
        stmt.close();

        // Now, show a case where it happens to work, because the binary data is different
        stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        rs = stmt.executeQuery("SELECT * FROM testtable_bincolumn WHERE bincolumn = unhex('"+pkValue2+"')");
        if (rs.next()) {
            System.out.println("We got a row for "+pkValue2+", about to delete it ...");
            rs.deleteRow();
        }
        rs.close();
        stmt.close();

        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT * FROM testtable_bincolumn WHERE bincolumn = unhex('"+pkValue2+"')");
        if (rs.next()) {
            System.out.println("Testcase #2 FAILED, the row was not deleted by deleteRow()");
        } else {
            System.out.println("Testcase #2 PASSED, the row was deleted by deleteRow()");
        }

        rs.close();
        stmt.close();
        conn.close();
    }
}