Data truncation: Data too long for column 'Phone' at row 1

Question

I am inserting data into temp table in MYSQL database.I have given size of 'Phone' column as VARCHAR(20) and have inserted data of exactly 10 character and it is constant.

Here is my code:

java.sql.PreparedStatement insertStmt = null;

                 String insertQuery = "insert into "+tableName+"(";
                 for(String col : columnList){
                             insertQuery+=col+",";
                         }
                insertQuery = insertQuery.substring(0,insertQuery.lastIndexOf(","));
                         insertQuery+=") values (";
                         for(String col : columnList){
                             insertQuery+="?,";
                         }
                insertQuery = insertQuery.substring(0,insertQuery.lastIndexOf(","));
                            insertQuery+=")";
                         insertStmt = mysqlConn.prepareStatement(insertQuery);

                while(rs.next())
                     {
                    for(int j=1;j<=columnList.size();j++)
                    {
                        insertStmt.setString(j,rs.getString(j).trim());
                        System.out.println(rs.getString(j).trim());
                    }
                    insertStmt.addBatch();
                    i++;

                    }
                insertStmt.executeBatch();  

                mysqlConn.setAutoCommit(true);
                rs = null;

and i am getting below error :

java.sql.BatchUpdateException: Data truncation: Data too long for column 'Phone' at row 1
Completed Temp tables 1&2
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)
    at MultipleDataExtraction.addDataToTempTable(MultipleDataExtraction.java:132)
    at MultipleDataExtraction.addDataToFinalTempTable(MultipleDataExtraction.java:94)

Table definition :

EmpNo varchar(15) YES NULL,   Name varchar(18) YES NULL,   Address varchar(40) YES NULL,   Phone varchar(20) YES NULL,   Age varchar(4) YES NULL,  DeptNo varchar(20) YES NULL

Insert statement:

insert into temp__final_table(Name,Address,Phone,EmpNo,Age,DeptNo) values ('Clerk#000000959','Skye Norling','2112 Ravine Drive','7091581728','Clerk#000000959','26')

Please help..

The data is too long (obviously) - spit out the insert statement before executing it and paste it in here. — dwjv, Jul 11 '14 at 13:44
The error is pretty clear, your column `phone` isn't long enough for the phone number you're trying to insert. What is the `phone` value you're trying, and what is the column definition in mysql? — Elliott Frisch, Jul 11 '14 at 13:44
That's a terrible way to create an SQL statement. At the very least look at how it's done in the Java tutorial: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — Paul, Jul 11 '14 at 13:45
Sample phone values are 7091581728,7896146536 and also the all the values including phone are getting inserted in MYSQL database table but same error is coming(only for phone). — user3824693, Jul 11 '14 at 13:48
As others pointed out the problem is that the value is too long for the column. Run DESCRIBE (http://dev.mysql.com/doc/refman/5.7/en/describe.html) and post the results as well as the INSERT statement being executed. — Paul, Jul 11 '14 at 13:49
If you don't edit your question to include an insert statement generated by your code then no one can help you. The database isn't lying, you're trying to jam too much into the column. Just add `System.out.println("My insert statement: " + insertStmt)` to your code. — Paul, Jul 11 '14 at 13:51
DESCRIBE example_schema.temp__final_table Table definition is: EmpNo varchar(15) YES NULL Name varchar(18) YES NULL Address varchar(40) YES NULL Phone varchar(20) YES NULL Age varchar(4) YES NULL DeptNo varchar(20) YES NULL Insert statement: insert into temp__final_table(Name,Address,Phone,EmpNo,Age,DeptNo) values ('Clerk#000000959','Skye Norling','2112 Ravine Drive','7091581728','Clerk#000000959','26') — user3824693, Jul 11 '14 at 13:56
@user3824693 Did you notice you put the address in for the phone there? Name, Address, Phone. You gave Clerk #, Name, Address, Phone. Acttually, you have EmpNo in there twice. — Elliott Frisch, Jul 11 '14 at 14:02

score 1 · Answer 1 · answered Jul 11 '14 at 14:14

This is your problem :

'2112 Ravine Drive'

in the insert query.

insert into temp__final_table(Name,Address,Phone,EmpNo,Age,DeptNo) values ('Clerk#000000959','Skye Norling','2112 Ravine Drive','7091581728','Clerk#000000959','26')

You are inserting address in the phone field.

Also, 'Clerk#000000959' seems to be the EmpNo. You seem to be inserting it in multiple columns in the same row.

Also, a suggestion would be to use numeric fields where necessary. Your DeptNo looks like a perfect candidate for integer type. You don't need to use varchar for all columns.

Hope this helps!!!

Bear in mind that using an integer type will have side-effects, like losing leading zeroes. Just because something happens to only contains numbers doesn't actually mean it's a number (for instance, ZIP codes). A good rule of thumb is to only treat it as a number if you are going to do math with it. — Charles Wood, Apr 14 '21 at 15:13

Data truncation: Data too long for column 'Phone' at row 1

1 Answers1

Linked