Table created through PDI giving Data Truncation error?

[joao.ciocca]

So... any explanations about this? I've been "getting fields" for the past 2 hours, just to be sure everything was OK... but no deal, everytime I try to run an SQL-output transformation I'm getting the data truncation error. There are 102 fields, but I'm pretty sure I triple-checked everything... any ideas or tips on how to determine which field is giving me that error?

[MattCasters]

If you're storing a Number field into say a Numeric(7,2), you can have data truncation as well.
In this case, as mentioned elsewhere, it's important to set the correct length and precision (7 and 2) on the field(s) so Kettle can round the floating point data before storing the numeric data.

[codek]

to find out which field it is, define error handling, catch the error description and write it to a file - then you can see what the full error was from the db and it should tell you the column name.

[joao.ciocca]

everything is defined in length and precision... that's why I'm finding this weird. all fields have length and precision set, they were set by PDI itself using the get fields option... and I'm using PDI's table output "SQL" button to create the table. Even so, I'm getting the error =(

[joao.ciocca]

oh! thanks codek, I'll try that!

-=edit=-

not much luck here...

err_num;err_desc;err_field
1;org.pentaho.di.core.exception.KettleDatabaseException: 
Error inserting/updating row
Data truncation;;

tried again...

err_num;err_desc;err_field;err_code
 001;org.pentaho.di.core.exception.KettleDatabaseException: 
Error inserting/updating row
Data truncation
;;TOP001
 001;org.pentaho.di.core.exception.KettleDatabaseException: 
Error inserting/updating row
Data truncation
;;TOP001
 001;org.pentaho.di.core.exception.KettleDatabaseException: 
Error inserting/updating row
Data truncation
;;TOP001
 001;org.pentaho.di.core.exception.KettleDatabaseException: 
Error inserting/updating row
Data truncation
;;TOP001

Error handling doesn't seem to be picking up the field responsible for the error =(

-=again=-
tried even TheFrog's idea from here, still no luck =(

-=one more=-
checked size, removed size limiters from the CREATE stmt, and I'm still getting data truncation error. this would mean type error, correct? but wouldn't that mean table output's sql button isn't reading the stream as it should...?

[codek]

ok so your db isnt reporting a nice error, doh! in that case, just eyeball the data. you can compare data that passes through fine to that which fails in your error hop, and see if you can see what the difference is..

[MattCasters]

It would indeed be great if databases would report WHICH field was causing the problem but very few of them actually do that.
That being said, you can route the error rows to a SQL Output step so you have the insert statement of one of the rows.
Then you can manually execute that, trim fields down, see what works and doesn't.

[joao.ciocca]

crap! now see, that's the problem when you can't do everything in a single transform... the files I were working with on this were created on a previous transformation.

remember another topic were I mentioned that some files I work with have different types of entries? the previous transform contains a switch/case to separate the different types into different files, and string cut them, since the original file is fixed width-type. One of the string cut steps was cutting the field wrong... it should be returning a 1-sized field, instead it was returning about 10-sized fields... all fixed now!

[codek]

glad to hear it