In soapui, i have a request xml and for which i would get an response.
I have established jdbc connection.
Now i need to verify the input xml against the database persisted fields.
the xml elements and DB column names are different, but the values should be same. already i mapped the input xml elements as alias in property, now i need to map the db fields against these elements and need to assert the db values are same as the xml input fields.
I've done this in a variety of ways, so there is not a definitive approach. You need to find the approach that best meets your needs.
Approach One - In the test step that calls the service, e.g. getLocations, I've wrote a Groovy script assertion that compare the number of locations in the response with the number of locations in the db. E.g. select count(*) from locations; In this approach I'm not validating the details, just the counts.
Approach Two - Again using a Groovy script assertion, I call an external Groovy class to validate the response against the db. Why external? Well, I use this particular script in a lot of my tests. When I started using SoapUI, I didn't know about this feature and I would code Groovy script assertions. Then when I needed to re-use, I would copy the script assertion to another test. However, if you need to extend or maintain that script assertion, you have to update it everywhere. Poor DRY approach. With the external class, all I need in the test step is a Groovy script assertion that calls the external class with the XML from the response. Then, if I need to change the assertion, then it's only n one place. Back to the question, in my external class, I would then iterate over the XML response and for each entry, make a call against the db for the id in question and then compare the db response with the XML response. With the external class, you can create methods as appropriate check various aspects of the response. For example, in some cases, the value in the response and db should be exactly the same. In other cases, the value in the response would be adjusted from the db value. E.g. db value might be a float, but response is integer by apply 2 places up, or two places down.
I don't tend to use the SoapUI Jdbc/Odbc test step. I've only used this on occasion and then only for the simplest of checks/assertions.
Instead, I prefer the external class that uses Groovy/Java to call the db.
In terms of mapping the names of the nodes in the response to the db columns, I wouldn't bother with setting properties. I rather use a list or array or whatever in the script assertion code.
I did below,
establish sql connection using groovy and in same script
