How to Assert Service Response Values against a Database
How to Assert Service Response Values against a Database
Description:
The following workflow will demonstrate a method to validate the data from a Service Response against the data stored in a Database. I will be using the database and Service Response from our Parabank 2.0 Application as an example. If you wish to learn how to stand up the Parabank application that is packaged with your SOAVirt installation, please see the .pdf file attached below.
Please refer to the screenshot above as you implement your scenario to confirm your setup is correct.
Solution:
1) Create a new .tst and add a database datasource to your test suite. Rename the datasource to “DB values”.
2) Configure your database datasource and provide the SQL query below.
You can verify that you have a successful connection to your DB by clicking on Show Columns; The columns window should populate with data.
3) Add a Writable-datasource to your test suite and call it “Service values”. Select Append as the Standard Test Mode and enable the First row specifies column names option. Add the element name to the first row, similar to the database "Columns” window. This writable data source will be used to store the values from the service response so that we can compare them with your Database values.
4) Add a table-datasource to parameterize the account ids for our GET request to Parabank. Rename the table-datasource to “CustomerId”.
5) Add a new Test suite and rename it “Extract Values from Backend Service”
6) Add a Messaging Client to your new Test Suite and make a GET call to Parabank using the following:http://localhost:8080/parabank/services/bank/customers/${id}
7) Chain an XML databank to the Messaging Client to extract the response values.
8) Extract the desired data using the XML databank and store it in your writable-datasource called “Service data”.
TIP: If you expect some values to return emtpy, I recommend enabling the Extract empty elements as option and setting the value to null. This will help us assert whether an empty field is expected.
9) Run your .tst and verify that your extracted Values are populated in your writable-datasource successfully. Save your work.
10) In order for your XML Assertor to validate the extracted element values, we will need to pass the service response data through an additional Messaging Client. Add a new Messaging client to your Parent Test Suite (the highest Test Suite in your .tst) and select the “DB values” datasource for the tool. Rename your Messaging client to “Pass DB Values through Assertor”.
11) Choose Literal for your Input mode in your Request and insert the following XML content:
<customer><id/><firstName/><lastName/><address><street/><city/><state/><zipCode/></address><phoneNumber/><ssn/></customer>
12) After inserting a payload above, change the Input mode to Form Input.
This will allow you to parameterize the values stored in your “DB values” datasource.
13) In Form Input Mode, parameterize the element with the values stored in the data source that contain your Database values.
14) Set your Messaging Client's Transport to NONE. By setting the transport to NONE, we are configuring the Messaging Client to run any tools (Databank, Assertor, etc) associated with the test, but our actual request will NOT be sent anywhere. By selecting NONE for our transport, we are essentially saying "Do-not-send-to-an-Endpoint, just execute my chained tools!"
15) Chain an XML assertor to the Messaging Client.
16) Select the “Service values” (writable) datasource for your XML assertor and run your .tst to populate the assertor – Expected XML tab.
17) Configure the assertor to assert against the values from your “DB values” datasource:
• Add a String Comparison Assertion for “id” and rename it “Assert id”
• Parameterize “id” using the column from the “Service Values” datasource
18) Navigate to the Top Parent Test Suite>Execution Options>Test Relationship and select the Tests run as group option.
19) Finally, navigate to the Top Parent Test Suite>Execution Options>Advanced Options and select Flat (Lockstep) for your Multiple Data Source Iteration. This will allow for Test execution to iterate over rows in different data sources together at the same time MEANING it will assert the rows from the table of Service values against the DB values in order! The test will stop iteration based on the datasource with the least number of data rows. Save your work!
20) Run your .tst and confirm that your execution is successful.
21) Once you have confirmed that your assertion is configured correctly, you can create one for every element you wish to assert.
If you have many assertions, you may find it easier copying the assertion (right-clicking the original assertion) and modifying the xpath accordingly.
Alternative Solution:
Another similar approach would be to replace the Database-dataSource with a DB tool and add a secondary Writable-dataSource to store your database values (same as step 8 above).
This particular method would give you better visibility of the data being sent from your database, but would require a few more steps to implement. Give these methods a try and choose what works best for your use-case. 
Comments
-
Hi Omar, great post. I like this solution and will give a try. But at step 21 is there better to do the load of assert? I would like know we have a way load the assertion using text file using a specific syntax of soatest? In this case, when any changes will occurred can simply modify our text file instead of clicking.
1 -
You cannot load assertions from a file. But you could parameterize all the important values in the assertions (expected value, xpath, etc.) and then use an Excel or CSV file to hold the values.
0
