0

I am at wits end with a Java web app that will not run a delete in Mysql. I get no errors, and when I copy and paste my query into the Mysql workbench, it does what it is supposed to. SELECT and INSERT both work for this table in my code. Here is my code:

public void deleteParticipant(String email){
        try{
            connect();
            PreparedStatement pst = conn.prepareStatement(
                "DELETE FROM activity_connections WHERE user_email=? AND activity_id=? LIMIT 1"
            );
            pst.setString(1, email);
            pst.setString(2, this.id);
            pst.executeUpdate();
            pst.close();
            conn.close();
        }catch(ClassNotFoundException ce){
            System.out.println("Driver error: " + ce);
            ce.printStackTrace();
        }catch(SQLException se){
            System.out.println("SQL error: " + se);
            se.printStackTrace();
        }
    }

And here is the connect method

protected static Connection conn;
public static String CONNECTION_STRING = "jdbc:mysql://localhost/kokosole?autoReconnect=true&useSSL=false";
public static String USERNAME = "root";
public static String PASSWORD = "password123";
protected static void connect() throws SQLException, ClassNotFoundException {
    String driver = "com.mysql.jdbc.Driver";
    String url = CONNECTION_STRING;
    Class.forName(driver);
    conn = DriverManager.getConnection(url, USERNAME, PASSWORD);
}

Why is this not working?

Isaac Krementsov
  • 646
  • 2
  • 12
  • 28
  • 1
    Add `conn.commit()`? – KellyM Oct 14 '18 at 01:24
  • 1
    And use [try with resources](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html) to properly manage your resources - you're in danger of leaving the connection open if something goes wrong – MadProgrammer Oct 14 '18 at 01:49
  • @KellyMarchewa The connection is set to autocommit – Isaac Krementsov Oct 14 '18 at 02:16
  • When you tested the query in MySQL Workbench were you connected using the same user as what you're connecting with in your code? Have you tried using a DELETE statement without a WHERE clause (with a test database, of course)? In the `activity_connections` table, is the `activity_id` a string or a number? Your code treats it as a string. – Emmanuel Rosa Oct 14 '18 at 02:57
  • @Emmanuel Rosa I was connected as root user both for Mysql work bench and the Java app. I did not try delete without `WHERE`, but I used `WHERE 1=1`. `activity_id` is indeed a string. – Isaac Krementsov Oct 14 '18 at 03:38
  • 1
    print the value of `this.id` – Riad Oct 14 '18 at 06:15
  • You may want to start logging the queries to see what's going on. See https://stackoverflow.com/questions/568564/how-can-i-view-live-mysql-queries – Emmanuel Rosa Oct 14 '18 at 08:57
  • @Riad I already printed and copied the query into Sal server to see if it would work and it did. I even ran a select statement with the same constraints and got the result I wanted. – Isaac Krementsov Oct 14 '18 at 12:45
  • @EmmanuelRosa I logged the queries and `DELETE` is not even showing up. – Isaac Krementsov Oct 14 '18 at 13:15
  • OK, it looks like you have some traction now, Issac. At this point your issue requires troubleshooting, something we can't do because we don't have your computer. You should be trying things to answer questions such as "Is it just DELETE statements which are not executing?" Continue to narrow it down and come back when your have a more specific issue. – Emmanuel Rosa Oct 14 '18 at 15:03
  • As I said earlier, I have been able to use `UPDATE`, `INSERT`, and `SELECT` just fine. – Isaac Krementsov Oct 14 '18 at 15:22

0 Answers0