Question

JDBC producer creating data as empty in database instead of NULL

  • 30 March 2022
  • 8 replies
  • 373 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

8 replies

Userlevel 5
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_

 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 5
Badge +1

@akanshajain6793 

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

 

Userlevel 3
Badge +1

@akanshajain6793 

You might want to try the approach suggested in 

 ?

 if (value != null) {
                finalValue = value.replaceAll("'", "''")
                record.value[key] = "'${finalValue}'"
            } else {
                record.value[key] = 'null'
            }

 

am using above groovy script to handle null or empty data but jdbc insert still fails with below error

Its creating the insert statement where column is coming as null or empty with out single quotes and jbbc stage not able to isnert that

any input?

 

@Pradeep @Bikram 

 

 

 

Userlevel 5
Badge +1

@akanshajain6793 

The error is from groovy script.

Can I request you to provide me the input details and what data needs to be inserted into DB , then I can help you on it.

Userlevel 5
Badge +1

I tried with below code and could manage to insert as null string in destination DB .

${record:value('/test') != null ? 'null' :record:value('/test') }

 

 

thanks @Bikram  groovy had some issue now its working

 

Reply