Hey krispokkuluri,
If I understand what you're asking for, this doesnt sound that complicated, however, the problem is that you're going to need good SQL to be able to pull this off.
What I mean by this is, if you need to verify the storedprocs are doing what is required, then you'll need to be able to turn those stored proc requirements into SQL - thinking about the typical stored procs I see, you'd need to at least be able to put together inner/outer joins, sub-queries and perhaps even use some SQL functions like CAST, CASE & COALESCE to match the requirement.
Initially I think I'd create the JDBC DB connection in the JDCB Connection profile 'Preferences'
In my test case I'd create a copy of the stored proc in a JDBC test step
I'd have a groovy step to write out the results to a Properties step
I'd add a Properties test step
I'd have a JDBC test step containing the SQL I'd put together to match the requirement
I'd have a groovy step to do grab the results of the SQL, write it to the Properties step and then compare the storedproc results against the SQL step's results
So my testcase hierarchy would be something like the following:
JDBC TestStep (storedProc)
Groovy TestStep (grab storedproc, write results to Properties step)
Properties
JDBC TestStep (SQL)
Groovy TestStep (grab SQL, write results to Properties step and compare storedproc results against SQL results)
You wouldn't be able to use the website's front end for a couple of reasons - firstly ReadyAPI! doesnt capture front end content - you'd need something like Selenium to do this - you can use groovy and import declare Selenium, but that's just adding more complication and I think this is gonna be complicated enough for you) - secondly - yes the front end is displaying data sourced from the database - however, you do not know if the data displayed in a specific field in the HTML table on the page is actually sourced from the correct table.attribute in your database. This is one of the reasons when testing front ends, you should actually alter the underlying table's data so that all data displayed on the page is unique - to ensure that page.field1 is being populated by table.field1 correctly - rather than page.field1 being incorrectly populated by table.field2. Hence the reason you should disregard the front end and just focus on the underlying database.
It sounds like you're quite new to ReadyAPI! and if you're not well versed in SQL, I think this is going to be rather a tall order for you. Are you working with anyone who knows ReadyAPI! and has good SQL skills? Even if your SQL is very good doesn't necessarily mean your T-SQL or PL/pgSQL or PL/SQL (T-SQL, PL/pgSQL, PL/SQL are used by SQLServer, PostgreSQL and Oracle to create their storedprocs) is good.
A further complication is how your requirements describing the storedprocs are defined. Will you have a textual description defining the requirement of what the storedprocs are supposed to be doing or are you expected to analyse the storedprocs yourself to determine what they are doing? If the latter, then you'll definitely need to have excellent T-SQL, Pl/pgSQL, PL/SQL, etc.)
Sorry I can't be a bit more encouraging, but I think what you're asking is a tall order unless you're SQL is very, very good along with very good ReadyAPI! skills.
Cheers,
Rich
Hi richie ,
Thanks for being so articulate. I have people who have implemented the solution ,although i need to lay down the basic groundwork inorder to reach them .The team has allowed me to involve in upgrading my skills and also are open to other solutions such as getting the data from the UI in excel format .
Another solution is to validate JSON response with the JDBC Step . Can you please help me with this ?
I have my JSON Response something like this :
(Can't provide actual data for security purposes but this is the response and they are severeal of these based on the request)
{
"$id" : "1",
"$type" : "XYZ",
"FeatureName" : "XYZ",
"FeatureQuantity" : 1,
"ToolFamily" : "XYZ",
"IsPrimary" : true,
"SupplierName" : "XYZ",
"ProductCode" : "XYZ",
"Description" : "XYZ",
"ToolIsActive" : true,
"FeatureIsActive" : true,
"IsRemixable" : true,
"IsAnnualized" : false,
"ListPrice" : 10500.00000000,
"ModifiedBy" : null,
"ModifiedDate" : null,
"CatalogId" : 21774,
"Features" : "XYZ",
"ProductType" : "A-la-carte"
},
And with the help of the developer i have got the query that gives the response to certain fields from this and there are multiple records and vary with data required . Can you please provide any familiar way to validate such requests and the response can have n number of entries and have only numbers for nodes but i want to extract only the data that i need and have to validate it with the jdbc response . Let's say i have both can you please provide a solution to validate the same .
Please let me know if there is anyway i could connect i terribly need to get this done by today .
Hi richie
Where's this json response come from?
I ran a REST API call to the Application API (Grid based Application ) than the stored proc and received the JSON response . As you can see the response nodes are individual numbers and there is no global node which making it hard to get as a data source and then validate with the JDBC response . My idea is something like this as i have very less time at hand . I would appreciate if there is any script suggestion that could help here . I have attached the two responses below .- Hope this helps to clarify Also would appreciate if you could provide a world view to the same as in what could be the best practice to validate the Response from this application in general.
P.S: I need a dynamic solution as the data that i will be validating would only increase in number can't keep individualy adding data for each node .
This is the JDBC Step:
