cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to insert null for a column via jdbc step

SOLVED
Highlighted
Occasional Contributor

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Occasional Contributor

Re: Unable to insert null for a column via jdbc step

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

As suggested I have written groovy script for this.

 

6 REPLIES 6
Community Hero

Re: Unable to insert null for a column via jdbc step

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

Occasional Contributor

Re: Unable to insert null for a column via jdbc step

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

 

Community Hero

Re: Unable to insert null for a column via jdbc step

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.

 

 

Occasional Contributor

Re: Unable to insert null for a column via jdbc step

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

Community Hero

Re: Unable to insert null for a column via jdbc step

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.

Occasional Contributor

Re: Unable to insert null for a column via jdbc step

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

As suggested I have written groovy script for this.

 

New Here?
Join us and watch the welcome video:
Join the exciting event
SeptemberHubBub
Top Kudoed Authors
Join the September Hub-bub to show off, learn and win