Results 1 to 6 of 6
  1. #1
    glisando is offline Member
    Join Date
    Mar 2010
    Posts
    4
    Rep Power
    0

    Default Prepared statement's 'setDate' method and MySQL execption

    Hi Guys...

    I am new here. I was looking for a JDBC section in this forum, couldn't find one and this particular section seems the closest to what I was looking for, so I am posting here.

    I am listing down the full source code here:

    Java Code:
    import java.io.IOException;   
    import java.sql.Connection;   
    import java.sql.PreparedStatement;   
    import java.sql.SQLException;   
    import javax.annotation.Resource;   
    import javax.servlet.ServletException;   
    import javax.servlet.http.HttpServlet;   
    import javax.servlet.http.HttpServletRequest;   
    import javax.servlet.http.HttpServletResponse;   
    import javax.sql.DataSource;   
    import java.sql.Date;    
      
    public class JDBCUpdateServlet extends HttpServlet {   
          
       @Resource(name = "jdbc/DMIT")   
      
    private DataSource dataSource;   
      
      
    @Override  
      protected void doGet(HttpServletRequest request, HttpServletResponse response)   
          throws ServletException, IOException   
      {   
      
    String insertBookingSql = "insert into booking (idbooking, bookingdate, FK_idfacility, FK_iddate) values (?,?,?,?)";   
      
      
    String updateBookingbookingdateSql = "update booking set bookingdate = ? where idbooking = ?";   
    //String deleteBookingSql = "delete from booking where idbooking = ?";   
      
      
    PreparedStatement insertBookingStatement;   
    //PreparedStatement updateBookingdateStatement;   
    //PreparedStatement deleteBookingStatement;   
      
      
    try  
        {   
          Connection connection = dataSource.getConnection();   
      
    insertBookingStatement = connection.prepareStatement(insertBookingSql);   
    //updateBookingdateStatement = connection.prepareStatement(updateBookingbookingdateSql);   
    //deleteBookingStatement = connection.prepareStatement(deleteBookingSql);   
      
      
    //*** FIRST ROW ****   
      
    //ID   
    insertBookingStatement.setString(1, "B05");   
      
      
    //Booking date   
    Date bookingDate = new Date(20090629);   
    insertBookingStatement.setDate(2,bookingDate);   
      
      
    //FK_idfacility   
    insertBookingStatement.setString(3, "T2033B");   
      
      
    //FK_iddate   
    Date idDate = new Date(20090630);   
    insertBookingStatement.setDate(4,idDate);   
      
      
    insertBookingStatement.executeUpdate();   
      
    //*** END OF FIRST ROW ****   
      
      
      
    //insertCustomerStatement.setInt(1, 2);   
    //insertCustomerStatement.setString(2, "Jane");   
    //insertCustomerStatement.setString(3, "Davis");   
    //insertCustomerStatement.setString(4, null);   
      
    //insertCustomerStatement.executeUpdate();   
      
      
      
    //updateCustomerLastNameStatement.setString(1, "Jones");   
    //updateCustomerLastNameStatement.setInt(2, 2);   
      
    //updateCustomerLastNameStatement.executeUpdate();   
      
      
    //deleteCustomerStatement.setInt(1, 1);   
    //   
    //deleteCustomerStatement.executeUpdate();   
      
      
    connection.close();   
      
      
    response.getWriter().println("Database Updated Successfully");   
      
      
        }   
      
    catch (SQLException e)   
        {   
      
    e.printStackTrace();   
      
     }   
      
     }   
      
    }
    However when I run this servlet, I am getting the following error

    com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityCons traintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ebooking`.`booking`, CONSTRAINT `FK_iddate` FOREIGN KEY (`FK_iddate`) REFERENCES `calendar` (`iddate`)) '

    I believe I understand what the error message is saying, that I can't enter a value in the FK_iddate column that dosen't exist in the iddate column of the calendar table. I find this puzzling as the value I am trying to add for FK_iddate column already exists in the calendar table and the relationship between the calendar and booking table is '1(calendar) to many(booking)' However when I run the SQL query directly in MYSQL itself, I am not getting any error and a new row is sucessfully added in the booking table. The MySQL query is as follows:

    Java Code:
    insert into booking (idbooking, bookingdate, FK_idfacility, FK_iddate)   
    values ('B05','2009-06-29','T2033B','2009-06-30');
    Any idea what is wrong, I am very puzzled by this.

    regards
    glisando

  2. #2
    PhHein's Avatar
    PhHein is offline Senior Member
    Join Date
    Apr 2009
    Location
    Germany
    Posts
    1,429
    Rep Power
    7

    Default

    Math problems? Call 1-800-[(10x)(13i)^2]-[sin(xy)/2.362x]
    The Ubiquitous Newbie Tips

  3. #3
    glisando is offline Member
    Join Date
    Mar 2010
    Posts
    4
    Rep Power
    0

    Default

    Hi there...

    Yes I did post in the other forum you mentioned. I dont think there is anything wrong. I am looking for answers, and I think there is nothing wrong in opening up the problem to more people. Infact I have also posted the same problem in one other forum too. When the problem is eventually solved, I will be sharing the solution with everyone, I think more people will benefit this way. Thanks.

  4. #4
    PhHein's Avatar
    PhHein is offline Senior Member
    Join Date
    Apr 2009
    Location
    Germany
    Posts
    1,429
    Rep Power
    7

    Default

    Then say upfront that you have crossposted. Boy, I'd be pissed to give you a lengthy reply and somebody else had already given the same reply on another forum. You're likely wasting volunteer's time here.
    Math problems? Call 1-800-[(10x)(13i)^2]-[sin(xy)/2.362x]
    The Ubiquitous Newbie Tips

  5. #5
    glisando is offline Member
    Join Date
    Mar 2010
    Posts
    4
    Rep Power
    0

    Default

    I am sorry I didnt know there was a need to declare upfront that one has posted the same question in another forum. In the past I have posted questions in more than one forum and never once did I get the same reply from the various forums.

    I respect your views, though personally I don't feel that I am wasting anybody's time here. If I stick with just one forum, there is a possibility I may not get the answers I need. Also as I mentioned before, once the problem has been solved, the solution will be shared with one and all.

    If have wasted your time in anyway, I am terribly sorry.

  6. #6
    glisando is offline Member
    Join Date
    Mar 2010
    Posts
    4
    Rep Power
    0

    Default

    Hi Guys...the problem has been solved and the working code is as below.

    Java Code:
    import java.io.IOException;
    //import java.sql.Connection;
    //import java.sql.PreparedStatement;
    //import java.sql.SQLException;
    import javax.annotation.Resource;
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import javax.sql.DataSource;
    import java.sql.*;
     
    //import java.util.Calendar;
     
     
    public class JDBCUpdateServlet extends HttpServlet {
     
     
     
     
    @Resource(name = "jdbc/DMIT")
    private DataSource dataSource;
     
     
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
         throws ServletException, IOException
      {
     
    String insertBookingSql = "insert into booking (idbooking, bookingdate, FK_idfacility, FK_iddate) values (?,?,?,?)";
     
     
    //String updateBookingbookingdateSql = "update booking set bookingdate = ? where idbooking = ?";
    //String deleteBookingSql = "delete from booking where idbooking = ?";
     
     
    PreparedStatement insertBookingStatement;
     
     
     
     
    try
        {
          Connection connection = dataSource.getConnection();
     
    insertBookingStatement = connection.prepareStatement(insertBookingSql);
     
     
    Date bdate = new Date(0000-00-00);
    Date iddate = new Date(0000-00-00);
     
     
     
     
    //*** FIRST ROW ****
     
    //ID
    insertBookingStatement.setString(1, "B05");
     
     
    //Booking date
    insertBookingStatement.setDate(2,Date.valueOf("2009-06-29"));
     
    //FK_idfacility
    insertBookingStatement.setString(3, "T2033B");
     
    //FK_iddate
    insertBookingStatement.setDate(4,Date.valueOf("2009-06-29"));
     
    insertBookingStatement.executeUpdate();
     
    //*** END OF FIRST ROW ****
     
     
     
    connection.close();
     
     
    response.getWriter().println("Database Updated Successfully");
     
     
        }
     
    catch (SQLException e)
        {
     
    e.printStackTrace();
     
     }
     
     }
     
    }
    For my purpose, 'Date' should have been used this way:
    Date.valueOf("2009-06-29"). I had mistakenly done this
    Date d1 = new Date(20090630); and d1 was assigned a 1970 date and hence the error message about the FK_iddate column not existing in the iddate column of the calendar table. I hope this info helps those facing the same problem as me. Thanks PhHein for the help.

Similar Threads

  1. Hi Null pointer Execption
    By kirtichopra2003 in forum Advanced Java
    Replies: 0
    Last Post: 10-09-2009, 11:56 AM
  2. Null Pointer Execption
    By bl00dshooter in forum AWT / Swing
    Replies: 2
    Last Post: 09-09-2009, 11:38 PM
  3. MySQL/JDBC Prepared Statement Select query
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-12-2009, 06:29 PM
  4. MySQL/JDBC prepared statement problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-12-2009, 12:21 AM
  5. get Trouble with SetDate
    By hungleon88 in forum JDBC
    Replies: 0
    Last Post: 09-17-2008, 10:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •