cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
New Contributor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

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

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.......

View solution in original post

5 REPLIES 5
Community Hero

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

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

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
New Contributor

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

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.

Moderator

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

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!

Natalie
Customer Care Team

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
New Contributor

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

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.......

View solution in original post

Community Manager

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

Hi @n_earnshaw,

 

Thanks for your investigation. Let me mark the results of your latest research with the Dev Team as a solution. If you come up with more ideas, please post them here.

---------
Tanya Gorbunova
SmartBear Community Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓