import java.sql.*;
import java.net.*;
import java.util.*;
import java.io.*;

public class bug8669 {

    protected static String dbUrl = "jdbc:mysql:///test?";
    protected static Connection conn = null;


    public static void main(String[] args) throws Exception {
            Statement stmt = null;
            ResultSet rs = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(dbUrl,"root","");
  
            try {
                stmt = conn.createStatement(
                        java.sql.ResultSet.TYPE_FORWARD_ONLY,
                        java.sql.ResultSet.CONCUR_UPDATABLE);
                String AESkey = "q2s3d4f5g6o0!;#[";

                stmt.executeUpdate("DROP TABLE IF EXISTS bug8564");
                stmt.executeUpdate("CREATE TABLE bug8564 ("
                                + "msg_id         INT NOT NULL AUTO_INCREMENT UNIQUE, "
                                + "subject        CHAR(255) NOT NULL, PRIMARY KEY (msg_id))");
                stmt.executeUpdate("DROP TABLE IF EXISTS bug8564msg");
                stmt.executeUpdate("CREATE TABLE bug8564msg ("
                        + "msg_id INT NOT NULL AUTO_INCREMENT UNIQUE)");
                stmt.executeUpdate("INSERT INTO bug8564msg (msg_id) VALUES(1)");
                stmt.executeUpdate("INSERT INTO bug8564msg (msg_id) VALUES(2)");

                int autoIncKeyFromApi = -1;

                PreparedStatement insSenderStmt = null;
                insSenderStmt = conn.prepareStatement(
                        "INSERT INTO bug8564 (  subject) "
                                + " VALUES (AES_ENCRYPT(?,?));",
                        Statement.RETURN_GENERATED_KEYS);
                insSenderStmt.setString(1, "testSubj"); // subject
                insSenderStmt.setString(2, AESkey);
//                log.debug("insSenderStmt: " + //((DelegatingPreparedStatement)
//                        insSenderStmt//)
//                                //.getDelegate()
//                                .toString());
                int retVal = insSenderStmt.executeUpdate();
                rs = insSenderStmt.getGeneratedKeys();
                if (rs.next()) {
                    autoIncKeyFromApi = rs.getInt(1);
                } else {
                    System.out.println("problem with: autoIncKeyFromApi = rs.getInt(1)");
                }
                rs.close();
                System.out.println("Key 1 returned from getGeneratedKeys(): "
                        + autoIncKeyFromApi);
                String aesEncVal = "%EF%BF%AF%13%EF%BF%90%2F%06Wx%03%EF%BE%92%0F%EF%BF%9E%EF%BF%8F%"
                        +
"04%EF%BF%A6%EF%BE%82%EF%BE%AEk%17%EF%BF%991%EF%BF%AE%EF%BF%8Cch%EF%BF%B2"
                        +
"%EF%BF%97%21%EF%BE%8C%EF%BF%90D%EF%BF%9C%09%3B%EF%BE%90%EF%BF%96n%EF%BE%91"
                        +
"%5B%EF%BF%80%0A%3B%EF%BF%BA%EF%BE%BB%3Ad%EF%BF%AB%2F";
                insSenderStmt.setString(1, null);
                insSenderStmt.setString(2, AESkey);
                insSenderStmt = conn.prepareStatement(
                        "INSERT INTO bug8564  (subject) VALUES (?);",
                        Statement.RETURN_GENERATED_KEYS);
                insSenderStmt.setString(1, URLDecoder.decode(aesEncVal, "UTF-8"));
//                log.debug("insSenderStmt showing bug: "
//                        + insSenderStmt.toString());
                retVal = insSenderStmt.executeUpdate();
                rs = insSenderStmt.getGeneratedKeys();
                if (rs.next()) {
                    autoIncKeyFromApi = rs.getInt(1);
                } else {
                    System.out.println("problem with: autoIncKeyFromApi = rs.getInt(1)");
                }
                rs.close();
                System.out.println("Key 2 returned from getGeneratedKeys():"
                        + autoIncKeyFromApi);

                PreparedStatement selDraftStmt = conn.prepareStatement("SELECT bug8564.msg_id, AES_DECRYPT(subject,?) AS subject "
                                + " FROM  bug8564 , bug8564msg WHERE "
                                + "bug8564.msg_id = bug8564msg.msg_id "
                                + "ORDER BY msg_id ASC");
                selDraftStmt.setString(1, AESkey);
//                System.out.println("selDraftStmt: " + selDraftStmt.toString());
                ResultSet crs = selDraftStmt.executeQuery();
                int i = 0;
                if (crs == null) {
//                    log.debug("row set is null!");
                }
                if (!crs.next()) {
//                    log.debug("no more results" + i);
                } else {
                    do {
                        i++;
                        System.out.println(crs.getInt("msg_id") + ":"
                                + crs.getString("subject"));
                    } while (crs.next());
//                    log.debug(i + " drafts found.");
                }

                selDraftStmt = conn.prepareStatement("SELECT bug8564.msg_id, "
                                + " AES_DECRYPT(subject,?) AS subject "
                                + " FROM  bug8564 , bug8564msg WHERE "
                                + "bug8564.msg_id = bug8564msg.msg_id ");
                selDraftStmt.setString(1, AESkey);
                System.out.println("Now second round with correct result");// - selDraftStmt: "+ selDraftStmt.toString());
                crs = selDraftStmt.executeQuery();
                i = 0;
                if (crs == null) {
//                    log.debug("row set is null!");
                }
                if (!crs.next()) {
//                    log.debug("no more results" + i);
                } else {
                    do {
                        i++;
                        System.out.println(crs.getInt("msg_id") + ":"
                                + crs.getString("subject"));
                    } while (crs.next());
//                    log.debug(i + " drafts found.");
                }
                rs.close();

                rs = null;
            } finally {

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

                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ex) {
                        // ignore
                    }
                }
            }

	}
}