Forum Discussion

Prabaharan's avatar
Prabaharan
Contributor
6 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.

 

 

  • Prabaharan's avatar
    Prabaharan
    6 years ago

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

    As suggested I have written groovy script for this.

     

  • JHunt's avatar
    JHunt
    Community Hero
    6 years ago

    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
      Prabaharan
      Contributor
      6 years ago

      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
        JHunt
        Community Hero
        6 years ago

        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 += prop.name != "ResponseAsXml" && prop.value != "NULL"
            ? aliases[prop.name]
            : []
    columns.join(', ')
}) VALUES (${= 
    def values = []
    for (def prop: context.testStep.propertyList) 
        values += prop.name != "ResponseAsXml" && prop.value != "NULL" 
            ? ":" + prop.name 
            : []
    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.