Application Integration- getting error in data mapping task for empty field value

[np1]

In my project i try to load data in mySql database but whenever any field value is empty, the flow is failed with below error.

Show More

ErrorMessage
'Failed to fetch the input value. Reason: [Failed to evaluate transform expression value. Reason: [For input string: ""]]'

Below are the details of my flow:

Test input:

{
"TABCODE_ID": "BID",
"INPFILE_TRANS_ID": ""
}

Here i am testing with two lines only but in actual data i received multiple empty field. Can anyone suggest best solution here.

[christianpaula]

Hi @np1,

Welcome to Google Cloud Community!

Empty field values in data mapping cause 'Failed to fetch the input value' error.

• Solutions:
  • Conditional Mapping: Use logic to handle empty values (e.g., map to NULL).
  • Data Cleaning: Pre-fill or remove empty fields before mapping.
  • Tool Configuration: Adjust settings to allow empty values.
  • Transform Expressions: Use functions like COALESCE to handle emptiness.
  • Error Handling: Catch and address empty value issues gracefully.
• Additional considerations:
  • Review data quality and data type compatibility.
  • Choose appropriate default values and test thoroughly.
• Best practices:
  • Handle empty values early in the data pipeline.
  • Implement comprehensive error handling.
  • Use meaningful defaults and test rigorously.

[Graham-Vosper]

Hi @np1 

We have found application integration to be quite poor at handling null strings. You may be better keeping the database field as a string if that is at all possible. (This is what we have done for our MySql inserts) 

Otherwise the only way I have found of detecting a field it null would be to do a 'GET_PROPERTY' on the JSON, map onto a String field and do a LENGTH on the string - as shown here. 

After that you should be able to use some conditional logic on the resulting 'trans_id_length' field and if 0 insert into the database without the field, or if greater that 0 then you can do your 'TO_INT' conversion and insert into the database. Not great if you have multiple integers in the database !

Good Luck !

[np1]

It is a great solution, but the thing is, in my actual data there are probably more than 23 to around 50 fields.

[Graham-Vosper]

Hi @np1 

It may be worth getting a fault raised as there doesnt seem to be an easy solution for what should be a straight forward problem. The only other thing I can suggest is appending a 0 at the start of your number, i.e.

Downside is that in the database for nulls you would get zeros, but at least it wont fail.