Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Data truncation error - How to troubleshoot.

  1. #1
    Join Date
    Sep 2011
    Posts
    171

    Default Data truncation error - How to troubleshoot.

    Hello Everyone,

    I am getting "data truncation" error when I move data from spreadsheet to database. (see attached) I wonder how I can troubleshoot it? I also attached the transformation.

    Regards,
    Alex


    2011/10/12 14:29:12 - Spoon - Transformation opened.
    2011/10/12 14:29:12 - Spoon - Launching transformation [SetDatePutEverythingToExcel]...
    2011/10/12 14:29:12 - Spoon - Started the transformation execution.
    2011/10/12 14:29:12 - SetDatePutEverythingToExcel - Dispatching started for transformation [SetDatePutEverythingToExcel]
    2011/10/12 14:29:12 - Transformation metadata - Natural sort of steps executed in 0 ms (3 time previous steps calculated)
    2011/10/12 14:29:12 - Table output.0 - Connected to database [iris_staging] (commit=1000)
    2011/10/12 14:29:12 - MainRows Excel Input.0 - Finished processing (I=2, O=0, R=0, W=2, U=0, E=0)
    2011/10/12 14:29:12 - Add constants.0 - Finished processing (I=0, O=0, R=2, W=2, U=0, E=0)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Because of an error, this step can't continue:
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting row into table [tblStgFundMarketablePosition] with values: [MBS], [wer32], [3123ed], [Long], [ 12.0], [ 12.0], [2011/08/31], [ 2.0], [12345], [5], [5], [P], [2011/10/11 00:00:00.000], [123]
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting/updating row
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Data truncation
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:434)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:117)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting/updating row
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Data truncation
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1415)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:273)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 3 more
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.sql.DataTruncation: Data truncation
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:382)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:504)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1364)
    2011/10/12 14:29:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 4 more
    2011/10/12 14:29:12 - Table output.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2011/10/12 14:29:12 - Spoon - The transformation has finished!!
    2011/10/12 14:29:12 - SetDatePutEverythingToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/10/12 14:29:12 - SetDatePutEverythingToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/10/12 14:29:12 - SetDatePutEverythingToExcel - SetDatePutEverythingToExcel
    2011/10/12 14:29:12 - SetDatePutEverythingToExcel - SetDatePutEverythingToExcel
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Alex,

    It's likely a field length issue, since you have your strings as length of -1 from the excel file.
    You need the length of the strings coming from your excel file to be <= the length of the field in the DB.

  3. #3
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you, gutlez!

    The error disappeared somehow, I did not have a chance to find the reason. Perhaps you are correct.

    Regards,
    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.