JDBC Step Script Assertion To Pass MULTIPLE Instances of Same Value to Properties Step

Hi,

 

This is a bit complicated so I'm going to try and be really clear in my query - I apologise if I'm not.

 

I have a GET REST request that I'm testing that retrieves data from a database and there are many, many records I need to test certain scenarios against - so the Datasource Loop is the obvious option.

 

Essentially I need to query the database, extract each of the records' unique identifiers, pass these identifiers to a Properties step for subsequent use in my GET request (the unique identifiers are used as the GET request's Query Parameters.

 

The URI appears as follows: 

/api/1/{namespace}/{dataset}?UniqueIdentifier=x

 

So my test case object hierarchy would be something like the following I think:

 

JDBC Step (grabs the unique identifiers for each of the records in the database)

Properties Step (holds the unique identifiers)

Datasource (Grid to hold 1 row for each unique identifier)

GET REST Request (uses the unique identifier as the GET's query parameter 

Datasource Loop (loops back, picks up the next unique identifier in the Datasource to pass onto the GET Request)

 

The trouble with the above is that you have to manually create as many properties in the Properties step as there are records in the table queried by the JDBC step and configure the Datasource accordingly too.

 

nmrao put together the following script assertion (set on a JDBC step) that extracts the results of a JDBC step and passes the query result to a Properties Step.  I then have another script (courtesy of @Rao of course) that builds my REST request's query string from the rows in my Properties step (saving me a lot of trouble - I've been using it for every single test I created on my current project)

 

essentially I use something like the following in my JDBC step

 

select top 1 column1 from table

and the column1 and it's value are written to the Properties step, to be used as the GET's Query Parameter and value

 

/*
* All Rao's work
* 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
*/
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"

HOWEVER - the above script assertion only works for 1 row only - which WAS perfect - exactly what I needed, until this new requirement came out forcing me to change my approach.

 

I need to alter the script assertion so that instead of extracting up to a single row of the table's data as separate properties (to use as Query Parameters on my GET request), it extracts multiple instances of the same column value (like the table's unique identifier/primary key)

 

so instead of the query requiring only 1 record in the resultset - it supports multiple records in the result set and passes each over to the Properties step.

 

obviously I would need to change the query in the JDBC to support returning multiple rows (which is fine - I just remove the 'top1' parameter from the query - but it's the script assertion I need to try and tailor so it extracts all the records unique identifiers.

 

I hope I've been clear this time!  - is this even possible?

 

thanks!

 

richie