cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to transfer data from a data source into SQL query

Occasional Contributor

Unable to transfer data from a data source into SQL query

I'm trying to transfer data from a data source to be used in a where statement of a SQL query. Essentially my script does the following:

 

  1. Holds “ID” numbers in a grid data source
  2. Executes a SOAP request and one of the fields uses the “ID” numbers from the grid data source ${DataSource#IDNumber} (have looped this at the end of the test case to go back and use the next ID number and run again)
  3. Uses a Microsoft SQL Server driver to connect to a DB and run a Select SQL query on one of the tables and compare this against some of the data in the SOAP request

 

I can do this for one “ID” number if I write it as where = '123' however I want to be able to use the ID number that was used in the test case for that loop i.e ${DataSource#IDNumber}.  I have tried it writing the below but it doesn’t work can someone help please?

 

Select *

From OpsControl.Piece

Where OpsControl.Piece.TrackingNumber = 'DataSource#IDNumber'

 

Many thanks

Chris

Hi, 

 

 

Tags (2)
4 REPLIES 4
Community Hero

Re: Unable to transfer data from a data source into SQL query

See if this page helps
https://www.soapui.org/jdbc/reference/the-jdbc-request-window.html

In the "Parameters Table", you provide ${DataSource#IDNumber} as parameter value and use name in the query as shown in documentaion like ':parameterName'



Regards,
Rao.
Occasional Contributor

Re: Unable to transfer data from a data source into SQL query

Hi Nmrao

 

Thanks for the feedback, I have tried putting a property transfer step in before the JDBC step to bring the field i want to use from the SOAP response into the parameter

 

when I run it the value is passed into parameters table however when I try to use it in my SQL it does not recognise it - below message is displayed

 

  • 2016-02-24 11:44:55 - Error getting response; java.lang.IllegalArgumentException: Parameter 'Tracking' was not found in the SQL Query.

the parameter is called Tracking and my sql is below - is the way i'm referring the parameter incorrect?

 

Select *
From opscontrol.piece
Where opscontrol.piece.TrackingNumber = 'Tracking'

 

(note in my initial request ID is the same as tracking - i called it ID for ease of reference)

 

i've added some screen shots - any help would be greatly appreciated

Occasional Contributor

Re: Unable to transfer data from a data source into SQL query

Just found it needs to be - then it works.... :-)

 

Select *
From opscontrol.piece
Where opscontrol.piece.TrackingNumber = :Tracking

Community Hero

Re: Unable to transfer data from a data source into SQL query

Actually it seems to be some problem in displaying a colon and then p (in my last reply). Any ways, glad to know.


Regards,
Rao.
New Here?
Join us and watch the welcome video:
6 / 7 API Testing Mistakes Video
Top Kudoed Authors