Question

Passing null values in the JDBC Query

  • 22 July 2022
  • 4 replies
  • 414 views

Hi,

We are using JDBC Query to insert data into the sql server database. 

using the following kind of query:

INSERT INTO ${SCHEMA_NAME}.${TABLE_NAME}(a,b,c) VALUES ('${record:value("/a")}', '${record:value("/b")}',  '${record:value("/c")}')

As we are enclosing in the single quotes even for null values it is inserting empty strings. if I remove the quotes getting exception by saying invalid query as the String are not enclosed within the  quotes.

So looking for a check which adds the quotes if the values is present otherwise to return the null.

Tried ${record:value("/a") != NULL ?record:value("/a") : NULL} but query failing.


4 replies

Userlevel 5
Badge +1

@gowrinadh 

Can you please give a try with below condition.

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

 

Rather than passing NULL values , please use single quotes and let see if it works.

@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 

kindly let me know what is your input and what will be your expected output you are looking for ,so i can give a try and help you on it.

Userlevel 3
Badge +1

@gowrinadh @akanshajain6793 You might want to use expression evaluator before the stage where you are inserting records and have temporary field(likely with target column name) which can be used in insert record.

Tried below to insert into JDBC Producer and it is inserting NULL when there is an empty value.

 

${str:isNullOrEmpty(record:value("/a"))?NULL:record:value("/a")}

 

Reply