Unable to insert null for a column via jdbc step

[Prabaharan]

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]

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

[Prabaharan]

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]

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.

[Prabaharan]

@JHunt , Appreciate your help. Have tried your solution, but now it is not able to identify the parameters.

Throwing an error - "Error getting response; java.lang.IllegalArgumentException: Parameter 'LangType' was not found in the SQL Query. "

This continues for other variables as well.

[JHunt]

Drat, this will be because all the TestStep properties are being added as properties to the PreparedStatement, so you can't leave out any of the properties from the query.

It looks like this is just a limitation of the JdbcRequestTestStep (which is quite limited) and trying to work around would just get even more complex.

At this point, if you want to send nulls. it is probably easier to write your own Groovy script that runs your INSERT statement instead of using JdbcRequestTestStep. You should be able to find posts on this forum or on Google for how to call the database in Groovy.

[Prabaharan · Accepted solution]

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

As suggested I have written groovy script for this.