Prepared statement's 'setDate' method and MySQL execption

[glisando]

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:

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:

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

[PhHein]

Crossposter: Java Database Connectivity (JDBC) - Prepared statement's 'setDate' method

[glisando]

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.

[PhHein]

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.

[glisando]

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.

[glisando]

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

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.