Can SOAPUI Pro only handle 1 Table Variable in a SQL Query used to Gather Data for my test
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.
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.
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
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.