Java Mysql delete records between dates syntax error

Question

I am trying to run a method in Java that deletes all records between two dates, however, it gives me a Synax error. This is the code:

public void updateIntegraton(String dateInit, String dateEnd)
{
    Connection conn = ConnectionManager.getConnection();
    String sql_delete = "DELETE FROM my_schema.my_table WHERE DATE(day_date) BETWEEN DATE(?) AND DATE(?)";
    String sql_safe_updates = "SET SQL_SAFE_UPDATES = 0";
    PreparedStatement ps_delete;
    PreparedStatement ps_safe_updates;

    try {
        ps_safe_updates = conn.prepareStatement(sql_safe_updates);
        System.out.println("Setting safe updates off.");
        ps_safe_updates.execute();
        ps_delete = conn.prepareStatement( sql_delete );
        ps_delete.setString(1, dateInit);
        ps_delete.setString(2, dateEnd);
        System.out.println("Delete Query: " + ps_delete);
        System.out.println("Deleting all records between dates " + dateInit +" and "+ dateEnd);
        ps_delete.execute(sql_delete);
        System.out.println("Deleted records. ");            
    } catch (SQLException e) {
        System.out.println("Something went wrong on Delete");
        e.printStackTrace();
    }
}

As you can see, I am printing the delete sql statement after I replace the '?' parameters with values. Here is an example of what it prints on the console:

Delete Query: com.mysql.jdbc.PreparedStatement@1686d1f: DELETE FROM my_schema.my_table WHERE DATE(day_date) BETWEEN DATE('2016-04-01') AND DATE('2016-04-08')

I run that exact same query on MySQL Workbench and rows get deleted, however I get this syntax error when I run my java code:

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 '?) AND DATE(?)' at line 1

I have used this same syntax to run other sql statements and got no errors. The only thing in my head is that the "execute" method used in the ps_delete.execute(sql_delete) part of my code does not work, but I have used "executeUpdate" and "executeQuery" methods that also give me errors.

I am using the mysql-connector-java-5.0.8-bin.jar library to create the connection to my MySQL library.

Any idea of why this is happening and how can I solve it?

Thanks

RubioRic · Accepted Answer · 2016-04-08 16:53:09Z

3

 ps_delete.execute(sql_delete); // you execute the not prepared request

It seems to me that it should be:

 ps_delete.execute();

edited Apr 8, 2016 at 16:53

RubioRic

2,4684 gold badges32 silver badges38 bronze badges

answered Apr 8, 2016 at 16:35

sebius

1022 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:01:42Z

0

referring to another post on StackOverflow You should do it with the setDate method. [JDBC: Inserting Date values into MySQL

Your code should be like this:

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    try {
        ps_safe_updates = conn.prepareStatement(sql_safe_updates);
        System.out.println("Setting safe updates off.");
        ps_safe_updates.execute();
        ps_delete = conn.prepareStatement( sql_delete );
        ps_delete.**setDate**(1, sdf.parse(dateInit));
        ps_delete.**setDate**(2, sdf.parse(dateEnd));

        ps_delete.execute();

    }

I don't try the code, just write to give You an idea of how it works. let me know

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Apr 8, 2016 at 16:51

Roberto Benazzato

4671 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

Java Mysql delete records between dates syntax error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related