topic Re: value too long for type character varying(40)ERROR in Talend Studio

value too long for type character varying(40)ERROR

Anonymous — Wed, 25 Jul 2012 05:47:09 GMT

Hi,
whean I run the job Iam getting error
mysql source table columnn(value) length(255), psql destination table column(value)length(40)
ERROR: value too long for type character varying(40)ERROR:current transaction is aborted,commands ignored until end of transaction blocError.

Thanks in advance.

Re: value too long for type character varying(40)ERROR

Anonymous — Wed, 25 Jul 2012 05:53:50 GMT

Hi
The error indicates that the lengh of inserted value is longer than the lenth of this column in DB table.
Please check it.
Regards,
Pedro

Re: value too long for type character varying(40)ERROR

Anonymous — Wed, 25 Jul 2012 13:57:00 GMT

I don't have permissions to change the length of the fields in both the databases
is there any component to con-just the data

Re: value too long for type character varying(40)ERROR

janhess — Wed, 25 Jul 2012 14:07:49 GMT

Use one of the string functions provided or trim with java.

Re: value too long for type character varying(40)ERROR

Anonymous — Thu, 26 Jul 2012 06:45:00 GMT

Hi,
I am using out put expression row2.value.StringHandling.TRIM(1,30) i am getting error StringHandling cannot be resolved or is not a field
and i tried to one more row2.value.TalendString.talendTrim(0,30) i am getting erro TalendString cannot be resolved or is not a field

Re: value too long for type character varying(40)ERROR

Anonymous — Thu, 26 Jul 2012 06:46:28 GMT

Hi
Try this.
row2.value.substring(0,30)
Regards,
Pedro

Re: value too long for type character varying(40)ERROR

Anonymous — Thu, 26 Jul 2012 06:53:51 GMT

Hi,
Thanks for reply. The out put error resolved
but This is Input error value too long for type character varying(255)ERROR:
java.lang.StringIndexOutOfBoundsException: String index out of range: 39
at java.lang.String.substring(String.java:1935)

Re: value too long for type character varying(40)ERROR

Anonymous — Thu, 26 Jul 2012 06:56:05 GMT

Hi
What's your expression?
It seems that the expression you typed is row2.value.substring(0,39).
But the input value is shorter than that.
Regards,
Pedro

Re: value too long for type character varying(40)ERROR

Anonymous — Thu, 26 Jul 2012 07:06:22 GMT

Hi
The input length is 255 using mysql value(column)
The output value(column1) length is 40, name(column2) length is 60
I mapped mysql to psql
row2 out1
mysql(source) psql(dest)
value row2.value(0,30) value(column)
row2.value(0,39) name(column)

Re: value too long for type character varying(40)ERROR

Anonymous — Thu, 26 Jul 2012 07:13:36 GMT

Hi
The max length of name is 60.
But the length of some actual data is shorter than that, even shorter than 39.
That's why you get the error. String index out of range: 39
Are you familiar with Java?
Regards,
Pedro

Re: value too long for type character varying(40)ERROR

Anonymous — Fri, 27 Jul 2012 07:24:09 GMT

Hi,
I tried this one
Relational.ISNULL(row2.value)?"":row2.value.trim().length()<40 ?row2.value.trim():row2.value.trim().substring(0,39)
resolved error in output side

but i got ERROR: value too long for type character varying(255)ERROR: current transaction is aborted, commands ignored until end of transaction blockERROR: current transaction is aborted, commands
This is input side
Do you have any idea to Input side solve this issue?

Re: value too long for type character varying(40)ERROR

Anonymous — Fri, 27 Jul 2012 07:33:10 GMT

Hi
Try this.
row2.value==null?"" row2.value.trim().length()<40 ?row2.value.trim():row2.value.trim().substring(0,39))
Regards,
Pedro