This discussion is archived
11 Replies Latest reply: Apr 26, 2010 6:39 PM by jschellSomeoneStoleMyAlias RSS

problem with comparing dates with db

730895 Newbie
Currently Being Moderated
hello all!
i am trying to compare date and time but have an error like:
SQLException: Can not issue data manipulation statements with executeQuery().
SQLState: S1009
VendorError: 0
my db look like that:
CREATE TABLE calendar (
          id INT, 
          MYUSER VARCHAR(30) NOT NULL, 
          title VARCHAR(100) NOT NULL, 
          date DATE NOT NULL,
                start TIME,
                end TIME
                
          
     );
and the function is:
 public  void  deleteCalendarEvent(int id,String date,String start) throws ClassNotFoundException {
          try {
   DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
        Date Date = df.parse(date);

DateFormat sdf = new SimpleDateFormat("hh:mm:ss");
Date stime = sdf.parse(start);

                    // This will load the MySQL driver, each DB has its own driver
               Class.forName("com.mysql.jdbc.Driver");
               // Setup the connection with the DB
               connect = DriverManager
                         .getConnection("jdbc:mysql://localhost/feedback?"
                                   + "user=sqluser&password=sqluserpw");





                        // Statements allow to issue SQL queries to the database
               statement = connect.createStatement();
               // Result set get the result of the SQL query
               resultSet = statement
                         .executeQuery( "delete * from FEEDBACK.COMMENTS where id = '"+id+"' and date='"+Date+"' and start='"+stime+"'");


System.out.println("the event was deleted");
thanks all
  • 1. Re: problem with comparing dates with db
    masijade Explorer
    Currently Being Moderated
    That has nothing to do with dates. Did you actually read the exception you pasted? Well, what does it mean? And do the API docs for the class in question provide any other possible methods to use?

    P.S. Use PreparedStatement. not a cobbled together SQL String, see the tutorials (this is more than a simple suggestion).
  • 2. Re: problem with comparing dates with db
    730895 Newbie
    Currently Being Moderated
    you mean that the problem is inverted commas ?
  • 3. Re: problem with comparing dates with db
    masijade Explorer
    Currently Being Moderated
    What do you think
    Can not issue data manipulation statements with executeQuery()
    might mean?
  • 4. Re: problem with comparing dates with db
    730895 Newbie
    Currently Being Moderated
    ok i did some changes but still it didnt delete the row ,and i dont have any errors in consol.
    the code:
     preparedStatement = connect
                   .prepareStatement( "delete  from FEEDBACK.Calendar where id = '"+id+"' and date='"+Date+"' and start='"+stime+"'");
    
                   preparedStatement.executeUpdate();
                            resultSet = statement
                   .executeQuery("select * from FEEDBACK.Calendar");
  • 5. Re: problem with comparing dates with db
    843789 Newbie
    Currently Being Moderated
    vitaly87 wrote:
    ok i did some changes but still it didnt delete the row ,and i dont have any errors in consol.
    First: don't build your SQL like that, [use parameters for your prepared statements|http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html] instead (as you've [already been told|http://forums.sun.com/thread.jspa?messageID=10978213#10978213]).

    Next: executeUpdate() returns [the number of affected rows|http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#executeUpdate()]. It's easily possible that your delete statement had no effect, because your where clause is too restrictive and/or wrong.
  • 6. Re: problem with comparing dates with db
    masijade Explorer
    Currently Being Moderated
    vitaly87 wrote:
    and i dont have any errors in consol.
    Well, seeing as how you have "cleverly" avoided showing your catch block, this statement has no real meaning.

    And, the rest of this post has been addressed above.
  • 7. Re: problem with comparing dates with db
    730895 Newbie
    Currently Being Moderated
    ok i get it:)
    i did like that ,but know it wrote me:
    run:
    Exception in thread "main" java.lang.ClassCastException: java.util.Date cannot be cast to java.sql.Date
    at workdb.first.deleteCalendarEvent(first.java:68)
    at workdb.first.main(first.java:36)
    Java Result: 1
    the error in line preparedStatement.setDate(2, (java.sql.Date) Date);
       preparedStatement  = connect.prepareStatement( "delete  from FEEDBACK.Calendar where id = ? and date=? and start=?");
                            preparedStatement.setInt(1,id);
                            preparedStatement.setDate(2, (java.sql.Date) Date);
                            preparedStatement.setTime(3, (Time) stime);
    and the Date parameter is
       DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date Date = df.parse(date);
    Edited by: vitaly87 on Apr 26, 2010 5:59 AM
  • 8. Re: problem with comparing dates with db
    masijade Explorer
    Currently Being Moderated
    preparedStatement.setDate(2, new java.sql.Date(Date.getTime()))
    And use a different name. "Date" is not a good name (in practice) for a "Date" object.
  • 9. Re: problem with comparing dates with db
    730895 Newbie
    Currently Being Moderated
    thanks still my db is the same i wrote:
      DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
          Date Date1 = df.parse(date);
    
    DateFormat sdf = new SimpleDateFormat("hh:mm:ss");
    Date stime = sdf.parse(start);
    
            preparedStatement  = connect.prepareStatement( "delete  from FEEDBACK.Calendar where id = ? and date=? and start=?");
                            preparedStatement.setInt(1,id);
                         preparedStatement.setDate(2, new java.sql.Date(Date1.getDate()));
    
                            preparedStatement.setTime(3, new java.sql.Time(stime.getTime()));
    i think i get the problem becouse the time that i get from get time it from 1970..so i need to do minus right?

    Edited by: vitaly87 on Apr 26, 2010 7:56 AM

    Edited by: vitaly87 on Apr 26, 2010 8:56 AM
  • 10. Re: problem with comparing dates with db
    masijade Explorer
    Currently Being Moderated
    What do you mean you "need to do minus"? Date can handle times before 1970 and getTime will still return the proper time. Now it might help to know what "my db is the same" means.
  • 11. Re: problem with comparing dates with db
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    vitaly87 wrote:
    i think i get the problem becouse the time that i get from get time it from 1970..so i need to do minus right?
    No.

    1. You need to figure out the difference between display and data.
    2. You should read the java docs which explain the contents of the Date and Time entities.
    3. Your database entities look suspect. Although possible I don't believe most major databases have a data type that only has a time component. And Date is also suspect for the same reason although at least one database does have that, but others do not.