Data truncation

[Juan Carlos Espinoza]

i have a problem

com.mysql.jdbc. MysqlDataTrunca tion: Data truncation: Data truncated for column 'FGiroNPagDol' at row 1
at com.mysql.jdbc. SQLError.conver tShowWarningsTo SQLWarnings(SQL Error.java:717)
at com.mysql.jdbc. MysqlIO.scanFor AndThrowDataTru ncation(MysqlIO .java:3183)
at com.mysql.jdbc. MysqlIO.sqlQuer yDirect(MysqlIO .java:1874)
at com.mysql.jdbc. Connection.exec SQL(Connection. java:3256)
at com.mysql.jdbc. PreparedStateme nt.executeInter nal(PreparedSta tement.java:131 3)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1585)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1500)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1485)
at packapp.Desempa quetar.grabarEn TablaGiros(Dese mpaquetar.java: 872)
at packapp.Operaci ones.desempaque tar(Operaciones .java:333)
at packinterfaz.se rvletinterfaz.P rocesarArchivo. processRequest( ProcesarArchivo .java:156)
at packinterfaz.se rvletinterfaz.P rocesarArchivo. doPost(Procesar Archivo.java:26 6)
at javax.servlet.h ttp.HttpServlet .service(HttpSe rvlet.java:710)
at javax.servlet.h ttp.HttpServlet .service(HttpSe rvlet.java:803)
at org.apache.cata lina.core.Appli cationFilterCha in.internalDoFi lter(Applicatio nFilterChain.ja va:269)
at org.apache.cata lina.core.Appli cationFilterCha in.doFilter(App licationFilterC hain.java:188)
at org.apache.cata lina.core.Stand ardWrapperValve .invoke(Standar dWrapperValve.j ava:210)
at org.apache.cata lina.core.Stand ardContextValve .invoke(Standar dContextValve.j ava:174)
at org.apache.cata lina.core.Stand ardHostValve.in voke(StandardHo stValve.java:12 7)
at org.apache.cata lina.valves.Err orReportValve.i nvoke(ErrorRepo rtValve.java:11 7)
at org.apache.cata lina.core.Stand ardEngineValve. invoke(Standard EngineValve.jav a:108)
at org.apache.cata lina.connector. CoyoteAdapter.s ervice(CoyoteAd apter.java:151)
at org.apache.coyo te.http11.Http1 1Processor.proc ess(Http11Proce ssor.java:870)
at org.apache.coyo te.http11.Http1 1BaseProtocol$H ttp11Connection Handler.process Connection(Http 11BaseProtocol. java:665)
at org.apache.tomc at.util.net.Poo lTcpEndpoint.pr ocessSocket(Poo lTcpEndpoint.ja va:528)
at org.apache.tomc at.util.net.Lea derFollowerWork erThread.runIt( LeaderFollowerW orkerThread.jav a:81)
at org.apache.tomc at.util.threads .ThreadPool$Con trolRunnable.ru n(ThreadPool.ja va:685)
at java.lang.Threa d.run(Thread.ja va:595)
Error = Data truncation: Data truncated for column 'FGiroNPagDol' at row 1


please help me

[r035198x]

Could you show the sql that gave this exception?

[JosAH]

And also please show the type of column 'FGiroNPagDol' and the value you
wanted to store in it.

kind regards,

Jos

[JavaJunior]

Hi,
I have the same problems when I try to convert data from *.dbf to MySQL table:

Exception in thread "main" com.mysql.jdbc. MysqlDataTrunca tion: Data truncation: Data truncated for column 'DATAPROD' at row 1
at com.mysql.jdbc. SQLError.conver tShowWarningsTo SQLWarnings(SQL Error.java:717)
at com.mysql.jdbc. MysqlIO.scanFor AndThrowDataTru ncation(MysqlIO .java:3102)
at com.mysql.jdbc. MysqlIO.sqlQuer yDirect(MysqlIO .java:1862)
at com.mysql.jdbc. Connection.exec SQL(Connection. java:3249)
at com.mysql.jdbc. PreparedStateme nt.executeInter nal(PreparedSta tement.java:126 8)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1541)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1455)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1440)
at converting.Conv ertData2.rewrit e(ConvertData2. java:231)
at startapp.StartC lass.main(Start Class.java:26)

The type of 'DATAPROD' in *.dbf file is Date (like 02.02.2007) and it will be VARCHAR in MySQL.

Below I'm wrote the void when I'm insert data into MySQL table:

public static void rewrite() throws SQLException {
getConnect();
createStmt();
try {
ostr = new FileOutputStrea m(outputFolder + outputFile);
ostrw = new OutputStreamWri ter(ostr, "UTF-8");
}
catch(IOExcepti on e) {
echonl("rewrite 1 : " + e.getMessage() + e.toString() + outputFolder + outputFile );
}
Object []rowObjects;
echonl("countin g");
rowCount = 0;
String strSQLIns = "";
strSQLIns += "insert into " + tablename + " (";
try {
try {
for(int j=0; j<fieldCount; j++) {
DBFField field = reader.getField ( j);
strSQLIns += convertFieldNam e(field.getName ()) ;
if (j != fieldCount - 1) {
strSQLIns += ",";
}
}
strSQLIns += ") values ";
while((rowObjec ts = reader.nextReco rd()) != null) {
rowCount++;
if (rowCount == 1) {
ostrw.write(str SQLIns);
}
else {
if (rowCount % multiple_rows == 0) {
ostrw.write(";" );
ostrw.write("\n ");
ostrw.write(str SQLIns);
}
else {
if (rowCount > 1) {
ostrw.write("," );
}
}
}
if (rowCount % 20000 == 0) {
echo(Integer.to String(rowCount ) + "..");
}
if (rowCount % 100000 == 0) {
echonl("");
}
pstmt = incon.prepareSt atement(strSQLI ns + insertRowStmt(r owObjects));
pstmt.executeUp date();
//ostrw.write("\n ");
//ostrw.write(ins ertRowStmt(rowO bjects));
}
}
catch(DBFExcept ion e) {
echonl("rewrite-- : " + e.getMessage()) ;
}
ostrw.close();
}
catch(IOExcepti on e) {
echonl("rewrite 3 : " + e.getMessage() + "**" + e.toString() + outputFolder + outputFile);
}
}

P.S. In MySQL table put only one row and value in this column is "Wed May".
How I can solve this problem?

[r035198x]

Hi,
I have the same problems when I try to convert data from *.dbf to MySQL table:

Exception in thread "main" com.mysql.jdbc. MysqlDataTrunca tion: Data truncation: Data truncated for column 'DATAPROD' at row 1
at com.mysql.jdbc. SQLError.conver tShowWarningsTo SQLWarnings(SQL Error.java:717)
at com.mysql.jdbc. MysqlIO.scanFor AndThrowDataTru ncation(MysqlIO .java:3102)
at com.mysql.jdbc. MysqlIO.sqlQuer yDirect(MysqlIO .java:1862)
at com.mysql.jdbc. Connection.exec SQL(Connection. java:3249)
at com.mysql.jdbc. PreparedStateme nt.executeInter nal(PreparedSta tement.java:126 8)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1541)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1455)
at com.mysql.jdbc. PreparedStateme nt.executeUpdat e(PreparedState ment.java:1440)
at converting.Conv ertData2.rewrit e(ConvertData2. java:231)
at startapp.StartC lass.main(Start Class.java:26)

The type of 'DATAPROD' in *.dbf file is Date (like 02.02.2007) and it will be VARCHAR in MySQL.

Below I'm wrote the void when I'm insert data into MySQL table:

public static void rewrite() throws SQLException {
getConnect();
createStmt();
try {
ostr = new FileOutputStrea m(outputFolder + outputFile);
ostrw = new OutputStreamWri ter(ostr, "UTF-8");
}
catch(IOExcepti on e) {
echonl("rewrite 1 : " + e.getMessage() + e.toString() + outputFolder + outputFile );
}
Object []rowObjects;
echonl("countin g");
rowCount = 0;
String strSQLIns = "";
strSQLIns += "insert into " + tablename + " (";
try {
try {
for(int j=0; j<fieldCount; j++) {
DBFField field = reader.getField ( j);
strSQLIns += convertFieldNam e(field.getName ()) ;
if (j != fieldCount - 1) {
strSQLIns += ",";
}
}
strSQLIns += ") values ";
while((rowObjec ts = reader.nextReco rd()) != null) {
rowCount++;
if (rowCount == 1) {
ostrw.write(str SQLIns);
}
else {
if (rowCount % multiple_rows == 0) {
ostrw.write(";" );
ostrw.write("\n ");
ostrw.write(str SQLIns);
}
else {
if (rowCount > 1) {
ostrw.write("," );
}
}
}
if (rowCount % 20000 == 0) {
echo(Integer.to String(rowCount ) + "..");
}
if (rowCount % 100000 == 0) {
echonl("");
}
pstmt = incon.prepareSt atement(strSQLI ns + insertRowStmt(r owObjects));
pstmt.executeUp date();
//ostrw.write("\n ");
//ostrw.write(ins ertRowStmt(rowO bjects));
}
}
catch(DBFExcept ion e) {
echonl("rewrite-- : " + e.getMessage()) ;
}
ostrw.close();
}
catch(IOExcepti on e) {
echonl("rewrite 3 : " + e.getMessage() + "**" + e.toString() + outputFolder + outputFile);
}
}

P.S. In MySQL table put only one row and value in this column is "Wed May".
How I can solve this problem?

Post the sql statement that was executed (Capture it by System.out.prin tln before the query was executed).
Also state your MySQL version.

[JavaJunior]

Post the sql statement that was executed (Capture it by System.out.prin tln before the query was executed).
Also state your MySQL version.

MySQL version is 4.1.
SQL statement is:
insert into ALEX (DATATIME,DOCTY PE,DOCNUM,STRNU M,KODNOMEN,NOME NKLAT,EDIZM,DAT APROD,KOLVO,VES ,CENA,SEBEST,KO DKLIENTA,KLIENT ,FILIAL) values ('02.05.07 08:45:15','®¢ ï á室* ï ª« ¤* ï','7063','1',' 00048','‘ªã¬¡à¨ ï â«.å/ª ¡/£ ¨§ áëàìï 400-600','ª£','Wed May 02 00:00:00 MSD 2007','12.9','1 2.9','132.3','1 150.94','000013 06','„¥¬ìï*®¢ ..','€«¥ªá¥¥¢ ª ')

[r035198x]

MySQL version is 4.1.
SQL statement is:
insert into ALEX (DATATIME,DOCTY PE,DOCNUM,STRNU M,KODNOMEN,NOME NKLAT,EDIZM,DAT APROD,KOLVO,VES ,CENA,SEBEST,KO DKLIENTA,KLIENT ,FILIAL) values ('02.05.07 08:45:15','®¢ ï á室* ï ª« ¤* ï','7063','1',' 00048','‘ªã¬¡à¨ ï â«.å/ª ¡/£ ¨§ áëàìï 400-600','ª£','Wed May 02 00:00:00 MSD 2007','12.9','1 2.9','132.3','1 150.94','000013 06','„¥¬ìï*®¢ ..','€«¥ªá¥¥¢ ª ')

Most likely you are inserting a value wider than is allowed by your column. What's the type of the DATAPROD column and which value are you trying to put into it.

[JavaJunior]

Most likely you are inserting a value wider than is allowed by your column. What's the type of the DATAPROD column and which value are you trying to put into it.

The type of 'DATAPROD' in *.dbf file is Date (like 02.02.2007) and it will be VARCHAR in MySQL

[r035198x]

The type of 'DATAPROD' in *.dbf file is Date (like 02.02.2007) and it will be VARCHAR in MySQL

Why don't you convert to Varchar first using TO_CHAR

[JavaJunior]

Why don't you convert to Varchar first using TO_CHAR

What do you mean "using TO_CHAR" - make field CHAR type, or something else?

I think the problem is in converting DBase type "Date" into MySQL type "Datetime". And another one problem in converting russian letters from *.dbf into MySQL table. I create mysql table with DEFAULT CHARSET=utf8.

[r035198x]

What do you mean "using TO_CHAR" - make field CHAR type, or something else?

I think the problem is in converting DBase type "Date" into MySQL type "Datetime". And another one problem in converting russian letters from *.dbf into MySQL table. I create mysql table with DEFAULT CHARSET=utf8.

I thought you said the MySQL type is Varchar. Now you say it's DateTime?

[JavaJunior]

I thought you said the MySQL type is Varchar. Now you say it's DateTime?

sorry, I mean data in DATAPROD column in mysql table look like datetime: 'Wed May 02 00:00:00 MSD 2007', but it type is varchar.

[JavaJunior]

It was easier than I think!
Field "DATAPROD" is dbf has only 8 symbols length. I set 50 symbols for this field and it works!

Now I have another problem with cyrillic encoding. )