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
    10

    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
    10

    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
  •