topic Re: Couldn't parse value for column with 'bit' and and unique identifier default value in Talend Studio

Couldn't parse value for column with 'bit' and and unique identifier default value

Anonymous — Sat, 16 Nov 2024 02:44:36 GMT

Here's the job design

I experienced the following problem related to loading schema definition in tInputFileDelimited.

[SOLVED!!!] 1. Schema Definition for 'bit(MSSQL)' data type

**CSV is exported from MSSQL

I'm a bit confused here about how I should treat 'bit' data type.

When I look at the input CSV, the value in 'mobile_inq' column is written as 'FALSE' or 'TRUE' , and in the original source DB '0' or '1'.

So I'm not sure how I should set the data type in tInputDelimitedFile's schema definition and tMap.

Please let me know how I could fixed this.

There's no 'bit' type in schema definition so I tried to set to the following types:

1) Set "mobile_inq"'s data type to 'int'

Error log:

Exception in component tFileInputDelimited_5 (Data_Update1)
java.lang.RuntimeException: Couldn't parse value for column 'mobile_inq' in 'row13', value is 'FALSE'. 
Details: java.lang.NumberFormatException: For input string: "FALSE"

2) Set "mobile_inq"'s data type to 'boolean'

org.talend.designer.runprocess.ProcessorException: Job compile errors 
Detail Message: Type mismatch: cannot convert from int to boolean

3. Set 'mobile_inq' data type to string

Type mismatch: cannot convert from int to String

3. Unique Identifier's "msrepl_tran_version" method newid() is undefined

Detail Message: The method newid() is undefined for the type Data_Update1

if I remove newid() from default value column, the error below stills occur.

Exception in component tDBOutput_6 (Data_Update1)
java.sql.SQLSyntaxErrorException: BLOB, TEXT, GEOMETRY or JSON column 'msrepl_tran_version' can't have a default value

The data type should be 'uniqueidentifier' and default value '(newid())' in MSSQL, but I don't know how I should set it here.

Please let me know if anything is unclear.

Thank you very much.

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

manodwhb — Thu, 09 Apr 2020 06:44:23 GMT

mobile_inq ,you need to read as string and which you need to convert into into by using below expression.

Row1.mobile_inq.equals("FALSE")?0:1

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

Anonymous — Thu, 09 Apr 2020 07:34:28 GMT

@manowdwhb
I tried to set mobile_inq as string but there's an error again.
>> Type mismatch: cannot convert from int to String

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

manodwhb — Thu, 09 Apr 2020 07:56:19 GMT

your source is int, which you want to convert into boolean then use below way.

row1.colomn==1?true:false

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

Anonymous — Thu, 09 Apr 2020 08:06:56 GMT

@manodwhb
I changed source to int and output to boolean, but the initial error message still appears.

>> tFileInputDelimited_5 Couldn't parse value for column 'mobile_inq' in 'row13', value is 'FALSE'.

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

Anonymous — Mon, 13 Apr 2020 06:05:50 GMT

@crotmn wrote:

Here's the job design

I experienced the following problem related to loading schema definition in tInputFileDelimited.

1. Schema Definition for 'bit(MSSQL)' data type

I'm a bit confused here about how I should treat 'bit' data type.

When I look at the input CSV, the value in 'mobile_inq' column is written as 'FALSE' or 'TRUE' , and in the original DB '0' or '1'.

So I'm not sure how I should set the data type in schema's definition and tMap.

<< The error occurred in the input component, so I think it's likely related to the schema/data type setting...

Please let me know how I could fixed this.

++ Does changing data type mapping setting help in this case??

here is the details for CSV and original MSSQL.

Input <CSV file>

column 'mobile_inq': FALSE(value)

Original <MSSQL DB>

column 'mobile_inq': 0 (value,, data type = bit)

There's no 'bit' type in schema definition so I tried to set to the following types:

1) Set "mobile_inq"'s data type to 'int'

E

Error log:
Exception in component tFileInputDelimited_5 (Data_Update1)
java.lang.RuntimeException: Couldn't parse value for column 'mobile_inq' in 'row13', value is 'FALSE'. 
Details: java.lang.NumberFormatException: For input string: "FALSE"
2) Set "mobile_inq"'s data type to 'boolean'
org.talend.designer.runprocess.ProcessorException: Job compile errors 
Detail Message: Type mismatch: cannot convert from int to boolean
3. Set 'mobile_inq' data type to string
Type mismatch: cannot convert from int to String
2. Couldn't parse value for column 'reg_date' in 'row13', value is '2019/1/18 11:26'
Exception in component tFileInputDelimited_5 (Data_Update1)
java.lang.RuntimeException: Couldn't parse value for column 'reg_date' in 'row13', value is '2019/1/18 11:26'.
I tried to make date pattern same as my data input, but still it didn't work...

3. Unique Identifier's "msrepl_tran_version" method newid() is undefined
Detail Message: The method newid() is undefined for the type Data_Update1
if I remove newid() from default value column, the error below stills occur.
Exception in component tDBOutput_6 (Data_Update1)
java.sql.SQLSyntaxErrorException: BLOB, TEXT, GEOMETRY or JSON column 'msrepl_tran_version' can't have a default value
The data type should be 'uniqueidentifier' and default value '(newid())' in MSSQL, but I don't know how I should set it here.

Please let me know if anything is unclear.

Thank you very much.

Anyone have an idea how I solve the problems discussed above regarding data type 'bit', 'date', and newid()??

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

Anonymous — Mon, 13 Apr 2020 19:57:56 GMT

In general: The MS SQL Server does not known a boolean data type instead it use the Bit type which will be filled with numeric values.

Values equals or greater 1 will be recognised as true and values zero or less will be recognised as false.

In your file - thats what I understood you have values "TRUE" and "FALSE".

Unfortunately you have to convert the incoming String (you have to read the values in the tFileInputDelimited as String values) into a short value with "TRUE" -> 1 and "FALSE" -> 0

This can be done like this in a tMap value expression editor for the output row:

"TRUE".equalsIgnoreCase(row13.mobile_inq) ? 1 : 0

This expression also results for null input into a 0 as output and does not cause a NullPointerException.

The outgoing schema should be set as int typed.

Now regrading to your date issue. Actually the error message tells you everything.

You incoming text formatted text is yyyy/MM/dd HH:mm:ss and this is the pattern what you have to set in your schema for the tFileInputDelimited for your reg_date column.

By the way: The default value of a schema will not be used to replace a possible null value. It is only be used for creating tables and set the default value. Talend unfortunately does not use this setting consequently for all purposes a developer would expect.

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

Anonymous — Tue, 14 Apr 2020 02:31:39 GMT

@lli

@lli wrote:
Unfortunately you have to convert the incoming String (you have to read the values in the tFileInputDelimited as String values) into a short value with "TRUE" -> 1 and "FALSE" -> 0

This can be done like this in a tMap value expression editor for the output row:

"TRUE".equalsIgnoreCase(row13.mobile_inq) ? 1 : 0

This expression also results for null input into a 0 as output and does not cause a NullPointerException.

I tried to set 'mobile_inq' as String in tFileInputDelimited and 'short' (tDBOutput),, however the following error occurs.

Type mismatch: cannot convert from int to Short

When I tried to set 'mobile_inq' as Integer, the following error occurred (Output: boolean)

java.lang.RuntimeException: Couldn't parse value for column 'mobile_inq' in 'row13', value is 'FALSE'. 
Details: java.lang.NumberFormatException: For input string: "FALSE"

Integer => Integer (Output)

Type mismatch: cannot convert from boolean to Integer

Regarding the date, I realized that later on, but thanks

Here I attached the sample job and csv file for your reference.

sample.zip

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

Anonymous — Wed, 15 Apr 2020 04:43:42 GMT

I kind of figured it out later, thanks to the advice here.

The solution is to set the schema as String and have default value as "FALSE"
>> previously I left it as ((0)), so the int error comes up.

Then in tMap Component, set the output row expression as the following and data type as boolean.
Row1.mobile_inq.equals("TRUE")?true:false

Re: Couldn't parse value for column with 'bit' and and unique identifier default value

Anonymous — Wed, 15 Apr 2020 07:53:29 GMT

I recommend to turn the comparison:

Row1.mobile_inq.equals("TRUE")?true:false

should be turned to:

"TRUE".equals(Row1.mobile_inq)?true:false

This way you do not have to deal with null values and prevent NullPointerExceptions