11 Replies Latest reply: Apr 26, 2010 8:39 PM by jschellSomeoneStoleMyAlias RSS

    problem with comparing dates with db

    730895
      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
          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
            you mean that the problem is inverted commas ?
            • 3. Re: problem with comparing dates with db
              masijade
              What do you think
              Can not issue data manipulation statements with executeQuery()
              might mean?
              • 4. Re: problem with comparing dates with db
                730895
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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.