Hey @rao
I only recently got my DB connection up and running in ReadyAPI! - and I've just gone back to the script assertion to extract the JDBC resultset (1 record only) and pass the query results to the properties step.
Now I've only just gotten my DB connection working so I had to test the hard coded script. However - when I execute the script now (after commenting out the hardcoded SQL results) - I get a null result and the script assertion fails.
/* Courtesy of Rao
* Below is the Script Assertion for the JDBC Request test step
* extracts column names and values for a single row (assuming query retuns a single row)
* and put the extracted data into following Properties test step
*/
//For testing, using fixed response
//Comment it and uncomment line 19 when you need the script to process current jdbc test step response
//def jdbcResponse = """<Results>
// <ResultSet fetchSize="0">
// <Row rowNumber="1">
// <CATEGORY.CAT_ID>7</CATEGORY.CAT_ID>
// <CATEGORY.CAT_NAME>Human Resource</CATEGORY.CAT_NAME>
// </Row>
// </ResultSet>
//</Results>"""
def jdbcResponse = context.response //I thought the problem was here
def xml = new XmlSlurper().parseText(jdbcResponse)
//Modify the test step name if different from Properties
def step = context.testCase.testSteps['Properties']
//Clean up properties from previous run
step.propertyNames?.each { step.removeProperty(it) }
//Find Row element; Get its children i.e., columns and values and add them as properties to Properties step
xml.'**'.find{it.name() == 'Row'}.childNodes().each {
def prop = step.hasProperty(it.name()) ? step.getProperty(it.name()) : step.addProperty(it.name())
prop.value = it.text()
}
log.info "Check the properties step for the details"
The sql I used was as follows:
Select top 1 [mdm].[vw_CertNom_Certificate].[MUID],
[mdm].[vw_CertNom_Certificate].[ID]
from [mdm].[vw_CertNom_Certificate]
and the generated resultset in query was as follows:
<Results>
<ResultSet fetchSize="128">
<Row rowNumber="1">
<MUID>1FE73AC0-BCB5-4AF7-9B97-7376E98DDA6E</MUID>
<ID>1</ID>
</Row>
</ResultSet>
</Results>
As I mentioned above I believe the problem is grabbing the response for some reason
(def jdbcResponse = context.response //I think it's not grabbing the result here - I think??)
But I couldn't work out the problem - but I finally worked out the following:
//so I tried replacing the following line
def jdbcResponse = context.response
//with the following
def jdbcResponse = context.expand( '${JDBC Request#ResponseAsXml#//Results/*}' )
and it works - again I've fixed a script without someone giving me the answer - YAY! :):) - I know thats not really a big deal for anybody else - but I finally feel like I'm gettin somewhere! -
Anyway the updated script to grab attribute values from a JDBC resultset and pass to a properties step is as follows:
/* Courtesy of Rao
* Below is the Script Assertion for the JDBC Request test step and passes them to a Properties step
*/
def jdbcResponse = context.expand( '${JDBC Request#ResponseAsXml#//Results/*}' )
def xml = new XmlSlurper().parseText(jdbcResponse)
//Modify the test step name if different from Properties
def step = context.testCase.testSteps['Properties']
//Clean up properties from previous run
step.propertyNames?.each { step.removeProperty(it) }
//def map = [:]
//Find Row element; Get its children i.e., columns and values and add them as properties to Properties step
xml.'**'.find{it.name() == 'Row'}.childNodes().each {
def prop = step.hasProperty(it.name()) ? step.getProperty(it.name()) : step.addProperty(it.name())
prop.value = it.text()
}
log.info "Check the properties step for the details"
Cheers,
richie
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
