Forum Discussion

MKV's avatar
MKV
Contributor
7 years ago

How to iterate every row in JDBC resultset

Hi,

With ReadyAPI, 2.2.0, I'm trying to validate my response data with the data in DB. There are cases where there could be multiple nodes with customer details. Number of customers will vary for every response. For every n number of customers, need to validate.

I fetched the count of customers from DB. Say for ex: count of customers is 2 and perform a for loop

 

def cust_count=context.expand('${DB_Query_Count#ResponseAsXML#Results[1]/ResultSet[1]/Row[1]/Count[1]}')

log.info("Count is" +cust_count)

for (int i=1;i<=cust_count;i++)

{

def cust_name = context.expand('${DB_Query_Cust_Details#ResponseAsXML#//Results['+i+']/ResultSet['+i+']/Row['+i+']/Cust_Name['+i+']}')

 

Multiple issues:

1. Since the count is only 2, I'm expecting the for loop to be run twice only. But the loop gets executed for 50 times.

2. Although it runs for 50 times, only the data from 1st row is fetched. The rest of the 49 are shown empty only. It doesn't seem to iterate to the next row in DB resultset also.

 

Do I have to modify the script or is there any other option to iterate in DB? 

  • Radford's avatar
    Radford
    7 years ago

    I always process XML data with the XmlSlurper. Here's some sample code of looping through some dummy JDBC test step data:

     

    import groovy.util.XmlSlurper

def jdbcData = '''<Results>
    <ResultSet fetchSize="10">
        <Row rowNumber="1">
            <CUST_NO>aaa</CUST_NO>
        </Row>
        <Row rowNumber="2">
            <CUST_NO>bbb</CUST_NO>
        </Row>
        <Row rowNumber="3">
            <CUST_NO>ccc</CUST_NO>
        </Row>
    </ResultSet>
</Results>'''

def Results = new XmlSlurper().parseText(jdbcData)

Results.ResultSet.Row.each(){Row ->
	log.info(Row.CUST_NO)
}

     

    As an alternative, without using Groovy, you could use an XML Data Source test step (taking its souce as the response of the JDBC test step), paired with a DataSource Loop Test Step will take care of all the looping functionality.

  • Radford's avatar
    Radford
    Super Contributor
    7 years ago

    I always process XML data with the XmlSlurper. Here's some sample code of looping through some dummy JDBC test step data:

     

    import groovy.util.XmlSlurper

def jdbcData = '''<Results>
    <ResultSet fetchSize="10">
        <Row rowNumber="1">
            <CUST_NO>aaa</CUST_NO>
        </Row>
        <Row rowNumber="2">
            <CUST_NO>bbb</CUST_NO>
        </Row>
        <Row rowNumber="3">
            <CUST_NO>ccc</CUST_NO>
        </Row>
    </ResultSet>
</Results>'''

def Results = new XmlSlurper().parseText(jdbcData)

Results.ResultSet.Row.each(){Row ->
	log.info(Row.CUST_NO)
}

     

    As an alternative, without using Groovy, you could use an XML Data Source test step (taking its souce as the response of the JDBC test step), paired with a DataSource Loop Test Step will take care of all the looping functionality.