Assert JSON Response with multiple Rows from the DB

sbritton

I'm really struggling with getting this to work, I thought this would be an easy use case for the software. However comparing the multiple rows from the JSON response to an equivalent query in the database isn't working.

Here's what I've tried:
1. Create a writable data source that will be populated from a JSON databank.
2. The JSON databank is populated from a simple GET that returns 23 rows.
3. Then added a DB tool that is connecting fine to my postgres DB.
4. Added a Results as XML -> XML asserter and map everything up.

What isn't happening is a comparison of each line or element to each other. I've tried on the asserter choosing just one element and one row to compare, but then no iteration happens, almost like I have to select every single element individually to compare against the data source. I don't think this is the case but could use some help.

OmarR

Hello sbritton,

I wrote an article on how to implement such a scenario. The workflow was written for an XML payload, but it should also work for JSON. Give it a try and let me know if you run into any hurdles: https://forums.parasoft.com/discussion/2984/how-to-assert-service-response-values-against-a-database

sbritton

Thank OmaR I was reading through it but was running into issues still. I'm going to blow everything away and try again. I'll let you know if I get stuck.

sbritton

The JSON Asserter is not populating I add the messaging client to pass through data from the database. Also your XML model looks like it doesn't have multiple rows in the asserter. Each time I've tried to use an asserter and could get data into it to assert against, the multiple rows for each response left it looking like I'd have to assert against every single row.

This is what I have so far:

Note, I'm not using a parameterized list to get values back, just a GET that is expected to return multiple rows.

Thanks in advance for the help.

OmarR

Hello sbritton,

The Assertor should only contain a single assertion for each column in your Writable or Database DataSource. This will allow the Assertor to iterate over the rows and compare the values individually and "not against every single row". I recommend focusing on a single column assertion and progressively adding more once you have the worflow implemented correctly.

I don't see a JSON Assertor chained to your Messaging Client in the screenshot. Did you create a JSON or XML payload for the Messaging Client? The JSON will only be populated if you are passing valid JSON. Also, Ensure that the assertor is chained for the "request traffic" (step 15).

Try this:
1) Pass a single DB column through the Messaging client (without a chained Assertor)

2) Set the Transport to None
3) Run the test
When you pass a single DB value through the Messaging client (without an Assertor), what does the traffic view show? Valid data?

If so, Move forward with chaining an assertor for "request traffic" and execute your test again to verify that the Assertor populates as expected.

sbritton

Thanks! I'll check it out.

sbritton

@OmarR What an elegant solution! Thank you so much, works like a charm after I fixed one thing I was doing wrong.

sbritton

Hey @OmarR

I'm looking at a way to expand this example by using a variable in the Database data source. The reason for this is because I really don't want to create a new set of datasources, environments, etc for the same type of tests every time. Seems heavy-handed. If I could use the same data sources across multiple types of tests seems like a win to me.

I have my table with a column of values that I'd like to put into the query, called "Entity Name" with the column named "name". However when running the tests, the database data source is now coming back empty.

My query is:

select id, name from entities where name = '${Entity Name: name}';

Any help would be appreciated. Thanks in advance.

OmarR

Good morning sbritton,

Unfortunately, we won't be able to parameterize values in a database data source.
However, we can certainly use parameterization in a db tool and pass the values from the db tool through the assertor. The setup may look like the following:

I recommend making a copy of your working setup prior to trying this one