Forum Discussion

hvo's avatar
hvo
New Contributor
6 years ago

How to loop row response from JDBC Request to send Soap Request

Hi, I am new of ReadyAPI. I have a JDBC Request to get data from table using 2 columns (IntA, IntB). I can put the variable to Soap request to get the first row in the table. But I want to loop all...
  • richie's avatar
    richie
    6 years ago

    Hi hvo 

     

    sorry about the delay - I thought I'd responded to this already.

     

    Ok - so you're extracting some values from a table and then passing them to a SOAP request within a looping test case and you want to pass each uniterated value pair extracted from the table to each subsequent request.  I gotcha.

     

    First thing and Im not picking holes here - but why are you trying to extract multiple values from a table when the SOAP request you're hitting appears to just add the 2 numeric values together?  It seems like a lot of work for little gain - especially as you want to iterate through the extracted db values which is going to be fiddly.

     

    Is there a reason you have hardcoded some values into a single file (using the Filetype Datasource) or multiple files (1 for each test using the Directorytype Datasource)?

     

    Back to the JDBC step. My question about whether the number of records returned in yoru resultset via your SQL is constant is cos you can cheat abit - there isn't any native functionality in ReadyAPI! that allows the user to loop through retrieved records iteratively - I've had this problem myself and seen multiple people post about it - especially with the additional complication of never knowing how many records are being returned or if there are many records returned.

    If you want to use the native funcntionality method - you would need to setup property transfers for each of the extracted data records - not elegant, nor efficient but entirely feasible if you just need say 10 or less records.  The problem is if many records are returned in yoru SQL query (so a bit of tedious work) and especially if the number of records returned is NOT constant.(you could setup say 10 property transfers - but you could end up with empty transfers if the SQL executes and only retrieves 8 rows for example).

     

    The best option is groovyscript - and I cannot help with that - one of the groovy scripters on the site could help with that.

    If accordinng to the screenshot of the excel page - you have maybe 8 tests?  If you still maintain extracting values from a database to generate the numerics for input on yoru SOAP requests - you can add a clause to your SQL to limit the number of records you retrieve - i.e. so you only ever retrieve 8 records.

    If you do this, then its feasible to add 8 property transfers to extract the records in one go to a grid and then use the grid as the datasource for the SOAP part of the test (as you appear to have setup)

     

    From your screenshot it looks like you're using mySQL RDBMS.  I've never used mySQL myself - but I had a quick search and to restrict the number of returned rows you can use the LIMIT function - yoru screenshot indicates your SQL is as follows:

    select IntA, IntB
from calculator

    so just add in the limit operator (there are a couple of parameters - count and offset - but I think you can just add in the count value and this will specify the max number retrieved as follows:

     

    select IntA, IntB
from calculator
limit, 8

    see the link for info on mySQL's limit

     

    As I state above - for yoru scenario the far better, more efficient and elegant option is groovy  to parse your JDBC response - extract the values for later use.  I have a JDBC script assertion that Rao put together for me to extract the table column name and data values but this only works for a SINGLE record - so this won't help you.

     

    So - do you really need to extract the numerics from a table are can you use hardcoded files?

    If you still want to use the JDBC step - can you limit the number of records that are returned?

     

    Cheers,

     

    rich

     

     

richie's avatar
richie
Community Hero

Hey hvo 

 

I'm sorry - I'm struggling to understand from your description

 

Are you saying that your JDBC step retrieves multiple records which you then want to pass onto the subsequent SOAP step - but currently your test just picks up the first record (row) of data from your query?

 

so essentially you want your test to cycle through each row of data retrieved in your JDBC response to pass onto the subsequent SOAP step? is that it?

 

Sounds to me like a JDBC step script assertion might do this.....but that's outside my skillset

 

Some questions:

Q1. What's the property Transfer step in your 'Initial SOAP Data' test case doing?  Is it grabbing the first row of data only (rowNumber1's <CALCULATOR.INTA/> and <CALCULATOR.INTB/> tag values), rather than all of the rowNumber's data?

 

Q2. Is the number of returned rows constant (never changing)?

if not, but you don't need more than x number of rows you can always control the number returned in the JDBC step by adding a clause to your SQL in the JDBC step (you haven't mentioned which RDBMS you're querying and controlling the number of returned rows can be way different depending on your RDBMS - i.e. Oracle you can do it one way (actually many ways), SQLServer uses something top X or i think latest versions support Fetch too, etc.).

Anyway - if you know the number of returned rows in your response you can actually do this a rubbish way (which is what I've done before) - I've created 2 transfers for each row of data returned and then populated a grid datasource to use later - but  this only works (and is a tedious, non-elegant way of doing it) if you know the number of rows returned and it's not in the multiples of 10 or 100s as thats a rubbish inefficient way of doing this.

 

Q3.  whats the 'Datasource' doing in your 'Add 2 Numbers' test case - is it a grid being populated by the property transfer step in your 'Initial SOAP Data' test case?

 

As I say - I think this is a job for a scripter (and that's completely outside my skillset) - but hopefully the above will clarify your position abit

 

Cheers,

 

rich

 

 

hvo's avatar
hvo
New Contributor
6 years ago

Hi richie,

I really appreciate from your reply. I will answer your questions in sequence.

 

Are you saying that your JDBC step retrieves multiple records which you then want to pass onto the subsequent SOAP step - but currently your test just picks up the first record (row) of data from your query?

Answer: Correct

so essentially you want your test to cycle through each row of data retrieved in your JDBC response to pass onto the subsequent SOAP step? is that it?

Answer: Correct

 

Some questions:

Q1. What's the property Transfer step in your 'Initial SOAP Data' test case doing?  Is it grabbing the first row of data only (rowNumber1's <CALCULATOR.INTA/> and <CALCULATOR.INTB/> tag values), rather than all of the rowNumber's data?

Answer: the property Transfer step is getting the first row of  table. I don't know how to get all rows. I  have attached the image. Please take a look for me.

Q2. Is the number of returned rows constant (never changing)?

if not, but you don't need more than x number of rows you can always control the number returned in the JDBC step by adding a clause to your SQL in the JDBC step (you haven't mentioned which RDBMS you're querying and controlling the number of returned rows can be way different depending on your RDBMS - i.e. Oracle you can do it one way (actually many ways), SQLServer uses something top X or i think latest versions support Fetch too, etc.).

Answer:  I used My SQL Workbench and I don't userstand what you mentioned is "by adding a clause to your SQL in the JDBC step". My test case is that verify the additional of numbers. I configured  a table Calculator (id, IntA, IntB) which has 10 rows with different number types (ex: postive or negative numbers, null...) and I want to verify all rows but currently it just get the first row in the table.

 

Q3.  whats the 'Datasource' doing in your 'Add 2 Numbers' test case - is it a grid being populated by the property transfer step in your 'Initial SOAP Data' test case?

Answer:  Correct. I am using Datasource from Excel file. Please see ExcelFile.png attached.

 

Thank you very much,

hvo

  • richie's avatar
    richie
    Community Hero
    6 years ago

    Hi hvo 

     

    sorry about the delay - I thought I'd responded to this already.

     

    Ok - so you're extracting some values from a table and then passing them to a SOAP request within a looping test case and you want to pass each uniterated value pair extracted from the table to each subsequent request.  I gotcha.

     

    First thing and Im not picking holes here - but why are you trying to extract multiple values from a table when the SOAP request you're hitting appears to just add the 2 numeric values together?  It seems like a lot of work for little gain - especially as you want to iterate through the extracted db values which is going to be fiddly.

     

    Is there a reason you have hardcoded some values into a single file (using the Filetype Datasource) or multiple files (1 for each test using the Directorytype Datasource)?

     

    Back to the JDBC step. My question about whether the number of records returned in yoru resultset via your SQL is constant is cos you can cheat abit - there isn't any native functionality in ReadyAPI! that allows the user to loop through retrieved records iteratively - I've had this problem myself and seen multiple people post about it - especially with the additional complication of never knowing how many records are being returned or if there are many records returned.

    If you want to use the native funcntionality method - you would need to setup property transfers for each of the extracted data records - not elegant, nor efficient but entirely feasible if you just need say 10 or less records.  The problem is if many records are returned in yoru SQL query (so a bit of tedious work) and especially if the number of records returned is NOT constant.(you could setup say 10 property transfers - but you could end up with empty transfers if the SQL executes and only retrieves 8 rows for example).

     

    The best option is groovyscript - and I cannot help with that - one of the groovy scripters on the site could help with that.

    If accordinng to the screenshot of the excel page - you have maybe 8 tests?  If you still maintain extracting values from a database to generate the numerics for input on yoru SOAP requests - you can add a clause to your SQL to limit the number of records you retrieve - i.e. so you only ever retrieve 8 records.

    If you do this, then its feasible to add 8 property transfers to extract the records in one go to a grid and then use the grid as the datasource for the SOAP part of the test (as you appear to have setup)

     

    From your screenshot it looks like you're using mySQL RDBMS.  I've never used mySQL myself - but I had a quick search and to restrict the number of returned rows you can use the LIMIT function - yoru screenshot indicates your SQL is as follows:

    select IntA, IntB
from calculator

    so just add in the limit operator (there are a couple of parameters - count and offset - but I think you can just add in the count value and this will specify the max number retrieved as follows:

     

    select IntA, IntB
from calculator
limit, 8

    see the link for info on mySQL's limit

     

    As I state above - for yoru scenario the far better, more efficient and elegant option is groovy  to parse your JDBC response - extract the values for later use.  I have a JDBC script assertion that Rao put together for me to extract the table column name and data values but this only works for a SINGLE record - so this won't help you.

     

    So - do you really need to extract the numerics from a table are can you use hardcoded files?

    If you still want to use the JDBC step - can you limit the number of records that are returned?

     

    Cheers,

     

    rich

     

     

    • nmrao's avatar
      nmrao
      Champion Level 3
      6 years ago
      richie, similar solution is done in one of your questions, if I remember right.
      Initially you wanted it for single row result, then later came back with a request to process multiple row result.

      I don't remember that question though, may be it would be easy for you to dig out.
      • richie's avatar
        richie
        Community Hero
        6 years ago

        Hey nmrao 

         

        yepthats right!

         

        the requirement changed altering the solution I needed from a single row to multiple rows (despite me asking repeatedly initially to confirm it would only be 1 row)

         

        However - after I'd created the new post referencing your script enquiring if it could be extended for multiple rows, the multi row requirement was backed out (such is agile!) and so there was never any need other than educational interest to get it working for multiple rows - which normally I'd do anyway - but 80 hour weeks dont leave much time for 'education' -  so I never got back to it.

         

        Cheers,

         

        rich