Service Virtualization

Expand all | Collapse all

SQL Database Results to Response JSON?

Jump to Best Answer
  • 1.  SQL Database Results to Response JSON?

    Posted 03-15-2018 03:47 PM

    Hello,

     

    We set up our service using a request and response pair and are currently using the SQL Database Execution (JDBC) step to return database results. We want to take the results from the database and format them into the same JSON format as the response file we used to set up the service. However, I am very new to using CA DevTest and do not know if there is a way to do this.

     

    I was thinking of using an Execute Script (JSR-223) step to format the JSON but am not sure if this is the most efficient way or how to import the database results as an array.

     

    Is there functionality in DevTest that will take SQL database results and format them into a predetermined JSON format for response?

     

    Thanks and let me know if you need any more details about my project.



  • 2.  Re: SQL Database Results to Response JSON?
    Best Answer

    Posted 03-21-2018 03:00 AM

    Hi,

     

    If I understand your requirement correctly,

    You wish to parameterize the values in JSON response with the query results.

     

    You can use Extract Value from JDBC result set filter for this and use them as property reference in the JSON response. You may go through the following links for your reference.

     

    https://docops.ca.com/devtest-solutions/10-2/en/using/using-ca-application-test/using-devtest-workstation-with-ca-application-test/building-test-cases/filters/add-a-filter/add-a-filter-from-a-jdbc-result-set

     

    https://docops.ca.com/devtest-solutions/10-2/en/reference/filter-descriptions/database-filters/extract-value-from-jdbc-result-set

     

    Thanks,

    Venkatesh



  • 3.  Re: SQL Database Results to Response JSON?

    Posted 03-21-2018 06:40 PM

    Understood. But when you say I can use the property reference in the JSON response, how does that work? Can I just simply embed the property name into the response body?

     

    For example, under the vsi, on the right hand side under Response I have the response body we expect to return. But when I add the properties in the body as follows "accountName":"{{accountNameProperty}}" the JSON response is lost and only the property is returned. Is there another way to reference the property?

     

    Thanks!



  • 4.  Re: SQL Database Results to Response JSON?

    Posted 03-22-2018 07:27 AM

    Yes, If your JSON response is as below

     { 
      "name":"John",
      "age":31,
      "city":"New York"
     }

    and you wish to parameterize only the city value which you get from DB query result set, then you need to save that result value in filter (extract value from JDBC result set) say emp_city.

    Then it can be referred in response as

     { 
      "name":"John",
      "age":31,
      "city":"{{emp_city}}"
     }

     

    If you are doing it the same way but still seeing issues with the response , check the response side data protocol.

     

    Thanks,

    Venkatesh