Forum Discussion

n_earnshaw's avatar
n_earnshaw
New Contributor
5 years ago
Solved

Can SOAPUI Pro only handle 1 Table Variable in a SQL Query used to Gather Data for my test

Hi All

 

I'm a newbie to SOAPUI Pro so please be gentle :-)

 

I’m using SOAPUI Pro and trying to run a Data driven test where we connect to a SQL Database and retrieve some data to then use in our tests

The script we have written is quite complex at the moment (As we have to retrieve records that meet a large number of conditions.)

 

We had this working recently where the query involved creating one table variable.

 

We then realised we had missed a condition out.

 

As a result the query has been modified to create a second table variable.

 

When trying to test the query using the data Source step it isn’t retrieving any records.

 

When we run the query in SQL Server Management Studio we get a set of results.

 

To try and work out what part of the query was failing I commented out everything apart from the steps to create the 1st table variable. When I then ran in SOAPUI it returned some results for that table.

 

I then uncommented out the steps to create the second table variable and didn’t get any results back.

Can SOAPUI only handle one table variable?

 

The idea was that we would put some data into one table variable, some other data into another table variable and then query from those

 

At this point, it’s not an option to create a stored procedure on the database to gather the data.

  • Hi NBorovykh 

     

    Thanks for the extra info.  After speaking with our dev it actually turns out that the issue was that we are trying to do more than 1 insert into our table variables. 

     

    We found that when we did more than one insert, it didn't work but when we got this down to one insert it worked. 

    It might be a limitation with JDBC? We'll continue to have a dig around.  AS you guys have said, it doesn't sound like a limitation of SOAPUI.......

5 Replies

  • richie's avatar
    richie
    Community Hero

    Hey n_earnshaw 

     

    I'm only responding cos no one else has - Ive used lots of JDBC test steps before now - but I've never used it for table variables.

    I'm surprised that you're having problems because I would've though that SoapUI should do whatever is supported by the driver file you're using.

     

    I don't know if there is a limitation on the number of table variables you can use - but it would be rubbish if there is a limit of 1.  With this in mind, have you tried creating a very basic SQL script to create the 2 table variables - i.e. rather than have some complex SQL - just go wtih the basics - slim down the number of conditions that filter the data you're retrieving - just to verify if there is a limit?

     

    How are you using the table variables?  I'm wondering if there's an alternative approach to extracting the data rather than using table variables?  or if you're creating more than one table variable then would it be possible to use 2 Datasource (JDBC type) steps - 1 Datasource (JDBC type) step per table variable?

     

    If the above approaches dont work and you verify >1 table variable can't be created with a single JDBC step I'd raise a ticket with SmartBear about it. 

     

    Cheers,

     

    rich

    • n_earnshaw's avatar
      n_earnshaw
      New Contributor

      Hi richie 

       

      Thanks for the response.

       

      We believe there's a limit of 1 variable because (as you mentioned) we originally started with 3 table variables.  We then removed them one by one.  We found that when only one variable was used, (Irrespective of which one it was) the query worked.

       

      Ideally, because of the complexity of the query we'd like to have it as a stored proc so we can just call it and not have to worry however one of the team has managed to refactor the query so that it only uses one variable so at least it's working.

       

      A bit of Stack Overflow searching led us towards the resolution (After multiple searches) but it would be useful if there was something in  the SOAPUI documentation.

      • NBorovykh's avatar
        NBorovykh
        Moderator

        Hello n_earnshaw,

         

        I'm glad to hear that you found a workaround, but I agree that working with two table variables in SQL Query should be possible as I have not found any known limitations. 

         

        Please create a support ticket and refer to this thread in it so that our Customer Care and Dev team could investigate the issue: https://support.smartbear.com/message/?prod=ReadyAPI

         

        Thank you!