Forum Discussion

Prabaharan's avatar
5 years ago

Unable to insert null for a column via jdbc step

Hi All,


I have created successfully a test suite with jdbc test step for an insert query where the parameters are passed from an external file.


For a particular case, few columns needs to be inserted with null.

But in table the record is getting inserted with "Null' as a value, which is not supposed to be.


Please refer the attachments for better understanding.


Apprecaite if anyone can help me in resolving this.



  • JHunt , Thanks for your detailed reply. Appreciate your help.

    As suggested I have written groovy script for this.


6 Replies

  • JHunt's avatar
    Community Hero

    You should just be able to omit the column from the SQL query altogether and up with a null value for the column.

    • Prabaharan's avatar

      Thanks for your quick reply.

      I have implemented this project as a data driven for which few the values are fetched from an external file.

      Few records for the same column will hold a valid value and rest other will hold null for the same column itself, so which i will not be able to remove the column from the sql query itself.


      Hope this gives you better clarification


      • JHunt's avatar
        Community Hero

        Here's a solution you could try, but it's pretty ugly.


        Change your SQL to like this:

        INSERT INTO db2admin1.cdaddrusagetp (${= 
            def aliases = [ 
                "LangType": "LANG_TP_CD", 
                "AddUsageTp": "ADDR_USAGE_TP_CD",
                // TODO
            def columns = []
            for (def prop: context.testStep.propertyList) 
                columns += != "ResponseAsXml" && prop.value != "NULL"
                    ? aliases[]
                    : []
            columns.join(', ')
        }) VALUES (${= 
            def values = []
            for (def prop: context.testStep.propertyList) 
                values += != "ResponseAsXml" && prop.value != "NULL" 
                    ? ":" + 
                    : []
            values.join(', ')

        Here's the thing. We can write Groovy in the SQL request by using the ${= } syntax, but the syntax breaks down if we use brackets { } in the Groovy script. So above is not exactly how I'd prefer to write the script, but it doesn't have any brackets in it.