1

I am inserting data to about 20 MySQL database tables using user input on JSP page. Some of the tables are inserted four rows of data.

I am getting "java.sql.SQLException: Data truncated for column" even though the data are not longer than that allowed in the corresponding column. eg inserting the integer 9 in an integer column.

Extract from my code:

    String sql = "insert into BO_Server_Details values (?,?,?,?,?,?,?)";
         PreparedStatement ps = conn.prepareStatement(sql);
         //Coding for date values 
//         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date1);
//         java.sql.Date sqlDate = new java.sql.Date(date1.getTime());
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setString(3, Server_Model_Config);
         ps.setString(4, Server_SN);
         ps.setString(5, UPS_Supply_Earthing);
         ps.setString(6, Server_Change_Extra_HD);
         ps.setString(7, Last_Change_Date1);
         int i1=ps.executeUpdate();
         System.out.println("Record inserted successfully");

         sql = "insert into BO_Server_DateTime values (?,?,?,?,?)";
//         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date2);
//         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3,1);
         ps.setString(4, DTTM2);
         ps.setString(5, Last_Change_Date2);
         int i2=ps.executeUpdate();

         //System.out.println("Record inserted successfully");

         sql = "insert into BO_Server_DateTime values (?,?,?,?,?)";
//         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date3);
//         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3,2);
         ps.setString(4, DTTM3);
         ps.setString(5, Last_Change_Date3);
         int i3=ps.executeUpdate();


         sql = "insert into BO_Server_DateTime values (?,?,?,?,?)";
//         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date4);
//         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3,3);
         ps.setString(4, DTTM4);
         ps.setString(5, Last_Change_Date4);
         int i4=ps.executeUpdate();

//
         sql = "insert into BO_Server_DateTime values (?,?,?,?,?)";
//         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date5);
//         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3,4);
         ps.setString(4, DTTM5);
         ps.setString(5, Last_Change_Date5);
         int i5=ps.executeUpdate();

//
//                  
         sql = "insert into BO_Server_AMC values (?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date54);
////         sqlDate = new java.sql.Date(date1.getTime());
////         date2 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Qly_PM_Date);
////         java.sql.Date sqlDate2 = new java.sql.Date(date2.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setString(3, PM_Done_Or_Not);
         ps.setString(4, Qly_PM_Date);
         ps.setString(5, Last_Change_Date54);
         int i6 = ps.executeUpdate();
////         
         sql = "insert into BO_HD_Usage values (?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date6);
////         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, dbData);
         ps.setInt(4, ImageData);
         ps.setString(5, Last_Change_Date6);
         int i7 = ps.executeUpdate();
////         
         sql = "insert into BO_HotFix_Version values (?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date7);
////         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, 1);
         ps.setInt(4, Version_No1);
         ps.setString(5, Last_Change_Date7);
         int i8 = ps.executeUpdate();
////         
         sql = "insert into BO_HotFix_Version values (?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date8);
////         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, 2);
         ps.setInt(4, Version_No2);
         ps.setString(5, Last_Change_Date8);
         int i9 = ps.executeUpdate();
////         
         sql = "insert into BO_HotFix_Version values (?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date9);
////         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, 3);
         ps.setInt(4, Version_No3);
         ps.setString(5, Last_Change_Date9);
         int i10 = ps.executeUpdate();
////         
         sql = "insert into BO_HotFix_Version values (?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date10);
////         sqlDate = new java.sql.Date(date1.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, 4);
         ps.setInt(4, Version_No4);
         ps.setString(5, Last_Change_Date10);
         int i11 = ps.executeUpdate();
////         

         sql = "insert into BO_Pending_Records values (?,?,?,?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date11);
////         sqlDate = new java.sql.Date(date1.getTime());
////         date2 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",eFeap_Error_Log_Attended1);
////         sqlDate2 = new java.sql.Date(date2.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, 1);
         System.out.println("Synch_Records_Pending1:" +Synch_Records_Pending1);
         ps.setInt(4, Synch_Records_Pending1);
         ps.setInt(5, Transfer_Records_Pending1);
         ps.setInt(6, PNF_Records_Pending1);
         ps.setString(7, eFeap_Error_Log_Attended1);
         ps.setString(8, Last_Change_Date11);
         int i12 = ps.executeUpdate();

////         
         sql = "insert into BO_Pending_Records values (?,?,?,?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date12);
////         sqlDate = new java.sql.Date(date1.getTime());
////         date2 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",eFeap_Error_Log_Attended2);
////         sqlDate2 = new java.sql.Date(date2.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, 2);
         System.out.println("Synch_Records_Pending2:" +Synch_Records_Pending2);
         ps.setInt(4, Synch_Records_Pending2);
         ps.setInt(5, Transfer_Records_Pending2);
         ps.setInt(6, PNF_Records_Pending2);
         ps.setString(7, eFeap_Error_Log_Attended2);
         ps.setString(8, Last_Change_Date12);
         int i13 = ps.executeUpdate();
////         
         sql = "insert into BO_Pending_Records values (?,?,?,?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date13);
////         sqlDate = new java.sql.Date(date1.getTime());
////         date2 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",eFeap_Error_Log_Attended3);
////         sqlDate2 = new java.sql.Date(date2.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, 3);
         System.out.println("Synch_Records_Pending3:" +Synch_Records_Pending3);
         ps.setInt(4, Synch_Records_Pending3);
         ps.setInt(5, Transfer_Records_Pending3);
         ps.setInt(6, PNF_Records_Pending3);
         ps.setString(7, eFeap_Error_Log_Attended3);
         ps.setString(8, Last_Change_Date13);
         int i14 = ps.executeUpdate();
////         
         sql = "insert into BO_Pending_Records values (?,?,?,?,?,?,?,?)";
////         date1 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",Last_Change_Date14);
////         sqlDate = new java.sql.Date(date1.getTime());
////         date2 = Utilities.formatToDate("yyyy-MM-dd","yyyy-MM-dd",eFeap_Error_Log_Attended4);
////         sqlDate2 = new java.sql.Date(date2.getTime());
         ps = conn.prepareStatement(sql);
         ps.setString(1, BO_Code);
         ps.setString(2, MMYY);
         ps.setInt(3, 4);
         System.out.println("Synch_Records_Pending4:" +Synch_Records_Pending4);
         ps.setInt(4, Synch_Records_Pending4);
         ps.setInt(5, Transfer_Records_Pending4);
         ps.setInt(6, PNF_Records_Pending4);
         ps.setString(7, eFeap_Error_Log_Attended4);
         ps.setString(8, Last_Change_Date14);
         int i15 = ps.executeUpdate();


         conn.close();
Ace
  • 13
  • 4
  • 1
    Welcome to Stackoverflow, please read [How To Ask](https://stackoverflow.com/help/how-to-ask). Pay special attention to [How To Create MCVE](https://stackoverflow.com/help/mcve). The more effort you'll put into posting a good question: one which is easy to read, understand and which is [on topic](https://stackoverflow.com/help/on-topic) - the chances are higher that it will attract the relevant people and you'll get help even faster. Good luck! – Nir Alfasi Nov 20 '17 at 06:00
  • which line are you getting the error? – CS_noob Nov 20 '17 at 06:18
  • The error is as follows java.sql.SQLException: Data truncated for column 'Synch_Records_Pending' at row 1 11:03:09,835 ERROR [STDERR] at com.niapune.struts2.SbmtAction.execute(SbmtAction.java:694) – Ace Nov 20 '17 at 06:50
  • Row 1 corresponding to this: sql = "insert into BO_Pending_Records values (?,?,?,?,?,?,?,?)"; – Ace Nov 20 '17 at 12:28

0 Answers0