Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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
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.
@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'.
@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_Update1if 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 valueThe 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()??
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.
@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.
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