Forum Discussion

richie's avatar
richie
Community Hero
6 years ago
Solved

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

  • richie 

    That case is obvious.


    If I remember well, this was brought to discussion then. That time, it was given in the way so that you could get it going, but it was not logical because of constraint of single row as result.

    Your entire use case can be done with single groovy script(hope this was too mentioned). But that can take some time and need to be coded accordingly.

    Currently, it is limiting due to the use of Property test step (which I never prefer).

    Even with your current test steps, it should be possible to continue to use.

    1. Store the JDBC test step result (multiple rows) into csv file. Have to write the simple script.
    2. Introduce a DataSource test step right after JDBC step and read above csv file.
    3. Read each row and Assign them into Properties step (which you have already, dealing single row)
      NOTE: But not sure if OTB allows to do this. Otherwise, you may know already how to read a csv file and use those values. If this is case, it may require to adopt these changes in below step.
    4. Groovy script which is dealing with Dynamic parameters and do REST call - no change (if Properties step can hold the data from CSV)
    5. Datasource loop.

    I think you should be ok.

3 Replies

  • nmrao's avatar
    nmrao
    Champion Level 3

    richie 

    That case is obvious.


    If I remember well, this was brought to discussion then. That time, it was given in the way so that you could get it going, but it was not logical because of constraint of single row as result.

    Your entire use case can be done with single groovy script(hope this was too mentioned). But that can take some time and need to be coded accordingly.

    Currently, it is limiting due to the use of Property test step (which I never prefer).

    Even with your current test steps, it should be possible to continue to use.

    1. Store the JDBC test step result (multiple rows) into csv file. Have to write the simple script.
    2. Introduce a DataSource test step right after JDBC step and read above csv file.
    3. Read each row and Assign them into Properties step (which you have already, dealing single row)
      NOTE: But not sure if OTB allows to do this. Otherwise, you may know already how to read a csv file and use those values. If this is case, it may require to adopt these changes in below step.
    4. Groovy script which is dealing with Dynamic parameters and do REST call - no change (if Properties step can hold the data from CSV)
    5. Datasource loop.

    I think you should be ok.

      • richie's avatar
        richie
        Community Hero

        Hi TanyaYatskovska & nmrao 

         

        Apologies - I've been side tracked on documentation for the last couple of days.

         

        @Rao - the single record options was EXACTLY what I needed originally - I never needed the complication of multiple records in the resultset.  It was only that there was a late requirement change for a very small use case and one of the ways I considered handling this was to retrieve multiple rows for the initial JDBC data extraction step. 

         

        HOWEVER - they canned the requirement completely - so I don't actually need to bother with this anymore.

         

        I've made a note as to your guidance for future reference however, cos lets face it - I need  all the help I can get!

         

        I'm accepting @Rao's answer without attempting it - if I had more time I'd like to try it out - but I'm maxed at work so no time for playing,

         

        Cheers to both!

         

        richie