Can you extract JDBC Query Results (columns and values) and Save to Properties For Later Use?

There is an update in the script to handle unwanted properties. Please checkout on git link for the latest.

The script @ git is updated with comments.

[Richie] "if I comment and uncomment the relevant lines -it will pick up the JDBC resultset (for a single record result) rather than rely on the hardcoded JDBC result in the script?"

[Rao] Yes, that's correct. Fixed response was used for demonstration purpose. Obviously, the script has handle the current test execution response.

Please note that, no separate Groovy Script test step is required, just add that to Script Assertion of JDBC Test step.

Hey nmrao 

 

Again - Rao Rocks! :):):):):):):):):)

 

Looking at the comments - did I understand that right - if I comment and uncomment the relevant lines -it will pick up the JDBC resultset (for a single record result) rather than rely on the hardcoded JDBC result in the script?

 

WOW man - thats more than I could've hoped for - cant give you enough Kudos...honestly - thanks so much man!

 

 

richie

Hey nmrao 

 

great  - yep - I can see you've replaced

def map = [:]

with

step.propertyNames?.each { step.removeProperty(it) }

 

sorry - the last 4 lines (ignoring the log.info) is the bit of the code I can't read.

 

Can you

 

1.  Explain how it 'handles unwanted properties'?  i.e. how it determines what are unwanted?  what the code is actually doing?

 

2.  Explain what the last 4 lines (as below) are actually doing?

 

thanks man,

 

richie

a moment of clarity in my otherwise cloudy day!

 

thank you! :)

 

richie

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

"context.response" - didn't work for JDBC Test step's Script Assertion?
Hope you not added additional Groovy Script test step.

Hey rao

 

Yeah - I've got the code snippet as a script assertion on the JDBC step - but once I'd commented out the hardcoded SQL resultset in the script to use the results from the JDBC step, it didn't work.

 

When I executed the assertion when the line was 'def jdbcResponse = context.response', a dialogue generated stating 'Error in assertion script of the [JDBC Request] test step: null'

 

Replaced it with 'def jdbcResponse = context.expand( '${JDBC Request#ResponseAsXml#//Results/*}' )' and it worked!?!?!?

 

In the interests of my education - any idea why?

 

Cheers!

 

richie

Need to see the screen shot of jdbc step after executing the query.