Skip to main content
Question

Passing null values in the JDBC Query

G
Fan

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

    Bikram
    Headliner
    Forum|alt.badge.img+1
    • BikramHeadliner
    • Headliner
    • 486 replies
    • July 23, 2022

    @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.

    A
    Discovered Fame

    @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

    Bikram
    Headliner
    Forum|alt.badge.img+1
    • BikramHeadliner
    • Headliner
    • 486 replies
    • July 25, 2022

    @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.

    Pradeep
    StreamSets Employee
    Forum|alt.badge.img+1
    • PradeepStreamSets Employee
    • StreamSets Employee
    • 48 replies
    • July 25, 2022

    @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")}

     

    Rishi
    1 person likes this
    • Rishi
      Rishi

    Reply


    Terms & Conditions