Data truncation

Originally Posted by 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.MysqlDataTruncation: Data truncation: Data truncated for column 'DATAPROD' at row 1
at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWa rnings(SQLError.java:717)
at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncati on(MysqlIO.java:3102)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1862)
at com.mysql.jdbc.Connection.execSQL(Connection.java: 3249)
at com.mysql.jdbc.PreparedStatement.executeInternal(P reparedStatement.java:1268)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1541)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1455)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1440)
at converting.ConvertData2.rewrite(ConvertData2.java: 231)
at startapp.StartClass.main(StartClass.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 FileOutputStream(outputFolder + outputFile);
ostrw = new OutputStreamWriter(ostr, "UTF-8");
}
catch(IOException e) {
echonl("rewrite1 : " + e.getMessage() + e.toString() + outputFolder + outputFile );
}
Object []rowObjects;
echonl("counting");
rowCount = 0;
String strSQLIns = "";
strSQLIns += "insert into " + tablename + " (";
try {
try {
for(int j=0; j<fieldCount; j++) {
DBFField field = reader.getField( j);
strSQLIns += convertFieldName(field.getName()) ;
if (j != fieldCount - 1) {
strSQLIns += ",";
}
}
strSQLIns += ") values ";
while((rowObjects = reader.nextRecord()) != null) {
rowCount++;
if (rowCount == 1) {
ostrw.write(strSQLIns);
}
else {
if (rowCount % multiple_rows == 0) {
ostrw.write(";");
ostrw.write("\n");
ostrw.write(strSQLIns);
}
else {
if (rowCount > 1) {
ostrw.write(",");
}
}
}
if (rowCount % 20000 == 0) {
echo(Integer.toString(rowCount) + "..");
}
if (rowCount % 100000 == 0) {
echonl("");
}
pstmt = incon.prepareStatement(strSQLIns + insertRowStmt(rowObjects));
pstmt.executeUpdate();
//ostrw.write("\n");
//ostrw.write(insertRowStmt(rowObjects));
}
}
catch(DBFException e) {
echonl("rewrite-- : " + e.getMessage());
}
ostrw.close();
}
catch(IOException e) {
echonl("rewrite3 : " + 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?

Originally Posted by r035198x

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

Originally Posted by JavaJunior

MySQL version is 4.1.
SQL statement is:
insert into ALEX (DATATIME,DOCTYPE,DOCNUM,STRNUM,KODNOMEN,NOMENKLAT ,EDIZM,DATAPROD,KOLVO,VES,CENA,SEBEST,KODKLIENTA,K LIENT,FILIAL) values ('02.05.07 08:45:15','�®¢ ï� á室* ï� ª« ¤* ï','7063','1','00048','‘ªã¬¡à¨ï â«.å/ª ¡/£ ¨§ áëàìï 400-600','ª£','Wed May 02 00:00:00 MSD 2007','12.9','12.9','132.3','1150.94','00001306',' „¥¬ìï*®¢ �.�.','€«¥ªá¥¥¢ª ')

Originally Posted by r035198x

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.

Originally Posted by JavaJunior

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

Originally Posted by r035198x

Why don't you convert to Varchar first using TO_CHAR

Originally Posted by JavaJunior

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.

Originally Posted by r035198x

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