qshpeng 2011-02-14
从excel读取数据插入mysql库中,
莫名其妙地少数几条记录的中文字段用程序插入不了,而用客户端写sql可以成功,用肉眼看没发现那几条记录数据有什么异常。
Error:错误:basename=福××××口cellId_b=46错误原因:
---Theerroroccurredincom/fsti/cdr/persistence/sqlmapdao/sql/ImportFile.xml.
---Theerroroccurredwhileapplyingaparametermap.
---ChecktheinsertCell-InlineParameterMap.
---Checkthestatement(updatefailed).
---Cause:com.mysql.jdbc.MysqlDataTruncation:Datatruncation:Datatruncatedforcolumn'BASENAME'atrow1
com.ibatis.common.jdbc.exception.NestedSQLException:
---Theerroroccurredincom/fsti/cdr/persistence/sqlmapdao/sql/ImportFile.xml.
---Theerroroccurredwhileapplyingaparametermap.
---ChecktheinsertCell-InlineParameterMap.
---Checkthestatement(updatefailed).
---Cause:com.mysql.jdbc.MysqlDataTruncation:Datatruncation:Datatruncatedforcolumn'BASENAME'atrow1
Causedby:com.mysql.jdbc.MysqlDataTruncation:Datatruncation:Datatruncatedforcolumn'BASENAME'atrow1
atcom.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
atcom.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:500)
atcom.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:85)
atcom.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:62)
atcom.fsti.cdr.service.ImportFileService.insertCell(ImportFileService.java:42)
atcom.fsti.cdr.zte.parser.ExcelParser$1.run(ExcelParser.java:124)
atorg.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Causedby:com.mysql.jdbc.MysqlDataTruncation:Datatruncation:Datatruncatedforcolumn'BASENAME'atrow1
atcom.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:758)
atcom.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3570)
atcom.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2128)
atcom.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
atcom.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
atcom.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:998)
atorg.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
atcom.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:84)
atcom.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
atcom.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
...6more
Causedby:
com.mysql.jdbc.MysqlDataTruncation:Datatruncation:Datatruncatedforcolumn'BASENAME'atrow1
atcom.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:758)
atcom.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3570)
atcom.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2128)
atcom.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
atcom.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
atcom.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:998)
atorg.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
atcom.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:84)
atcom.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
atcom.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
atcom.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:500)
atcom.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:85)
atcom.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:62)
atcom.fsti.cdr.service.ImportFileService.insertCell(ImportFileService.java:42)
atcom.fsti.cdr.zte.parser.ExcelParser$1.run(ExcelParser.java:124)
atorg.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Causedby:
com.mysql.jdbc.MysqlDataTruncation:Datatruncation:Datatruncatedforcolumn'BASENAME'atrow1
atcom.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:758)
atcom.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3570)
atcom.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2128)
atcom.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
atcom.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
atcom.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:998)
atorg.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
atcom.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:84)
atcom.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
atcom.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
atcom.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:500)
atcom.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:85)
atcom.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:62)
atcom.fsti.cdr.service.ImportFileService.insertCell(ImportFileService.java:42)
atcom.fsti.cdr.zte.parser.ExcelParser$1.run(ExcelParser.java:124)
atorg.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
将字段改为varchar(255)和设置默认值都不能解决。其实中文值得程度没有超过255,不是长度不够引起。
将字段类型改为varbinary能解决上面的问题。
读取时要进行转换:convert(colusinggb2312)