Service Virtualization

Expand all | Collapse all

Getting dynamic response for the values present in the excel file

  • 1.  Getting dynamic response for the values present in the excel file

    Posted 09-28-2015 06:21 AM

    Hi,

     

    I am using dataset in VSM and I am trying to get dynamic response for the values present in the excel file. Please suggest me the way to accomplish this.

     

    Also please help me with the sample piece of code, If i need to use JavaScript Step.

     

    Scenario - In my webservice XML request, I need to provide employee ID. This given value for employee ID should be searched in the excelsheet, and the corresponding details(Such as Role,Name) should be desplayed in the response

     

    Steps I have done till now:

     

    1. Created a VSM using RR pairs

    2. Added a dataset to VSM.

    3. Now what should I do, to search the employee ID in the excel sheet and to get the corresponding fields in the response?




  • 2.  Re: Getting dynamic response for the values present in the excel file

    Posted 09-28-2015 07:08 AM

    You need to write a java code, Sample code:

     

    FileInputStream excellFile = new FileInputStream(new File("C:\\excel1.xlsx"));

    XSSFWorkbook workbook = new XSSFWorkbook(excellFile);

    XSSFSheet sheet = workbook.getSheetAt(0);

    int empId = Integer.parseInt(testExec.getStateValue("EmployeeID"));

     

    for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++)

    {

      XSSFRow row = sheet.getRow(j);

      XSSFCell cell = row.getCell(row.getFirstCellNum());

      int empid_sheet = Integer.parseInt(cell.getStringCellValue());

      if(empid_sheet == empId )

      {

      flag = true;

      for (int k = row.getFirstCellNum()+1; k < row.getLastCellNum(); k++)

              {

      //Set all the values for each colum as shown below

      testExec.setStateValue("Role", role);

      }

      break;

    }

     

    In vsi, you can call these properties using {{}}.

     

    Revert if more help required.



  • 3.  Re: Getting dynamic response for the values present in the excel file

    Posted 09-28-2015 07:35 AM

    Hi Monika,

     

    Thanks for your suggestion. I guess this will return all the values in the excel sheet. Isn't it? Please correct me If I am wrong.

     

    Here i want to retrieve the details of anyone random empid, that will be provided by the user during runtime.




  • 4.  Re: Getting dynamic response for the values present in the excel file

    Posted 09-28-2015 09:24 AM

    You can check with CA Team. They will provide you jar files to make connection with External Data source and to Query data in Match Script. Which further you can use as Lisa Properties.



  • 5.  Re: Getting dynamic response for the values present in the excel file

    Posted 09-28-2015 09:44 AM

    Simplest way is to use dataset in the subprocess. Steps to implement this approach are given below.

    • Create Subprocess wherein you add  dataset step
    • Add assertion to the step to validate the expected and actual data coming from VSM
    • If multiple operations are there in VSI then you have to setup subprocess accordingly which is not that easy
    • Add Subprocess step to your VSM which will point to the subprocess added above
    • Add input parameters for subprocess(i.e. account number in your case).

     

    Process to read Employee ID is already given by Monika. Simply add scripted assertion to listener step and use testExec.getstateValue("") method to read your data which you will supply as input parameter to the subprocess.

     

    Values returened by subprocess can directly be supplied to the Response(VSI) You can alos visit below thread for some more details

    https://communities.ca.com/message/241707937#241707937

     

    Though I have better approach to implement it for noe you can try this.



  • 6.  Re: Getting dynamic response for the values present in the excel file

    Posted 09-06-2018 07:29 AM

    Hi, 

    I had a similar question like this.

    I have created one data set in LISA workstation.

    Now I want read  the "TagName" from the request and provide the "Tagvalue" from data set.

    Do we have something which can be done without codding in this case.

    Input:

    <response>{{{{request_tagName}}}}</response>

    Output:

    <response>TagValue</response>

     

    something like this...will work??