Forum Discussion

cstott1's avatar
cstott1
Occasional Contributor
9 years ago

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, 

 

 

JDBC
sql
    • cstott1's avatar
      cstott1
      Occasional Contributor
      9 years ago

      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

      • cstott1's avatar
        cstott1
        Occasional Contributor
        9 years ago

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

         

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