cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Community Hero

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

Hey @nmrao 

 

Again - Rao Rocks! Smiley HappySmiley HappySmiley HappySmiley HappySmiley HappySmiley HappySmiley HappySmiley HappySmiley Happy

 

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

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
Community Hero

Re: 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.


Regards,
Rao.
Community Hero

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

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

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
Community Hero

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

The script @ git is updated with comments.


Regards,
Rao.
Community Hero

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

a moment of clarity in my otherwise cloudy day!

 

thank you! Smiley Happy

 

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
Community Hero

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

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


Regards,
Rao.
Community Hero

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

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!  Smiley HappySmiley Happy - 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
Highlighted
Community Hero

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

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


Regards,
Rao.
Community Hero

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

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

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
Community Hero

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

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


Regards,
Rao.