Question

JDBC producer creating data as empty in database instead of NULL

  • 30 March 2022
  • 4 replies
  • 95 views

Hi Team

 

We were using 

JDBCProducer_01 to load data previously and the load was working fine we were able to see NULL getting loaded instead of empty string but recently we moved to 
JDBCQuery_01 and created insert statement but now we see all the NULL are replaced with empty string what can be the fix for same one we need NULL to be loaded into edw table

4 replies

Userlevel 2
Badge +1

@akanshajain6793 

You might want to try the approach suggested in 

 ?

Userlevel 2
Badge +1

@akanshajain6793 

please let me know if the issue still persist or it has been fixed.

 

 Hi tried both the result 

${record:value("/a") != NULL ?record:value("/a") :’’} and

${record:value("/a") != NULL ?record:value("/a") : NULL}

it end up inserting empty string is there any other solution to load NULL

Userlevel 2
Badge +1

@akanshajain6793 ,

In Streamsets ,for the string variable ,if no values from source then by default its value is null and if you are trying insert it into destination table ,it will insert null value.

If you are using jdbc query , then you can use explicitly null value like below.

INSERT INTO EMP VALUES ("123","xxx","Oslo",.12,NULL).

Kindly provide your insert query which is is  replacing null value with empty string, so i can check it my data collector and will try to help you on the same.

 

If its possible if you can validate the null check before inserting data to destination DB then it will very helpful.

Below example for your reference.

 

${record:value('/f1') != NULL ? record:value('/f1') : NULL}

This check will send the column value if it has else will give null value.

 

 

Thanks & Regards,

Bikram_

Reply