java mysql Data truncation: Incorrect date value: '' error

Question

I am trying to pass a date parameter into a mysql stored procedure using a java preparedStatement. But regardless of whether or not the variable is populated, I am getting the error

**com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: ''**

According to the database schema, the dateofbirth column can be null.

I am not sure why the error is coming. I am using a string input and converting it to sql date using the following code:

if(!dob.equals("")){
        try {
            contact.setDob(new Date(format.parse(dob).getTime()));
            System.out.println("New date:" + contact.getDob());
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }else{
        contact.setDob(null);
    }

My stored procedure is below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `Contacts`.`InsertContactInfo` $$
CREATE PROCEDURE `Contacts`.`InsertContactInfo` (salutation Text, category Text, firstName varchar(45), middleName varchar(45), lastName varchar(45), dob date, dom date, sex char(1), email varchar(30))
BEGIN
Insert into contact_info(Salutation,Category,sex,FirstName,LastName,MiddleName, DateofBirth, DateofMarriage, Email)
Values(salutation,category,sex,firstname,middlename,lastname,dob,dom,email);

END $$

DELIMITER ;

and am using the following code to enter the data:

java.sql.PreparedStatement statement = con.prepareStatement("call InsertContactInfo(?,?,?,?,?,?,?,?,?)");
        statement.setString(1, contact.getSalutation());
        statement.setString(2, contact.getCategory());
        statement.setString(3, contact.getSex());
        statement.setString(4, contact.getFirstName());
        statement.setString(5, contact.getLastName());
        statement.setString(6, contact.getMiddleName());
        if(contact.getDob()!= null){
            statement.setDate(7, contact.getDob());
        }else{
            statement.setNull(7, Types.DATE);
        }
        if(contact.getDom()!= null){
            statement.setDate(8, contact.getDom());
        }else{
            statement.setDate(8, null);
        }
        statement.setString(9, contact.getEmail());
        statement.executeUpdate();

Not sure where am making a mistake. Any ideas?

Gangadhar Tunugunta · Accepted Answer · 2013-07-21 13:39:05Z

2

The fields ordering is incorrect. Sex field comes in the 8th place.

pStmt = conn.prepareStatement("call InsertContactInfo(?,?,?,?,?,?,?,?,?)");
        pStmt.setString(1, "Mr");
        pStmt.setString(2, "Customer");
        pStmt.setString(3, "Gangadhar");
        pStmt.setString(4, "" );
        pStmt.setString(5, "T" );
        pStmt.setDate(6, null);
        pStmt.setDate(7, null);
        pStmt.setString(8, "M");
        pStmt.setString(9, "XXXXX");
        pStmt.executeUpdate();

edited Jul 21, 2013 at 13:39

answered Jul 21, 2013 at 13:19

Gangadhar Tunugunta

441 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

flamesavor Over a year ago

Ah... its working fine now.. I spent hours on this and was not able to find the mistake. Really feel silly now.. Thanks :)

Collectives™ on Stack Overflow

java mysql Data truncation: Incorrect date value: '' error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related