Forum Discussion

kendemoor's avatar
kendemoor
Occasional Contributor
6 years ago

Comparing an array from Json Response to an array from JDBC response.

Hello, I have a situation where I'd like to compare a resultset of 100 rows from both a REST call and a database query using XPath Match on the JDBC response. My issue is however that while the...
  • kendemoor's avatar
    kendemoor
    6 years ago

    I ended up doing groovyscript assertions, but I'd still like to know why my previous attempt didn't work.

    Here's my solution for reference if someone else wants to do the same.

    def SubscriptionGroupID = context.expand( '${Get SubscriptionGroups#Response#$[\'SubscriptionGroups\'][*][\'SubscriptionGroupID\']}' )
def Sort = context.expand( '${DataSource#Database sort}' )

    def sql = Sql.newInstance("redacted","redacted","redacted","com.microsoft.sqlserver.jdbc.SQLServerDriver") 
DatabaseValues = sql.rows("select TOP 100 lItemID,szUser,szName,szResponsible,szFirstname,szCustomerNr,szVATNumber From Address where lItemID IN (select ContactId from PartnerContact Order by ContactId OFFSET 2 rows) ORDER BY " + Sort)

assert DatabaseValues.lItemID.toString() == SubscriptionGroupID.toString() : "Not all SubscriptionGroupIDs match with database value"
kendemoor's avatar
kendemoor
Occasional Contributor
6 years ago

I ended up doing groovyscript assertions, but I'd still like to know why my previous attempt didn't work.

Here's my solution for reference if someone else wants to do the same.

def SubscriptionGroupID = context.expand( '${Get SubscriptionGroups#Response#$[\'SubscriptionGroups\'][*][\'SubscriptionGroupID\']}' )
def Sort = context.expand( '${DataSource#Database sort}' )

def sql = Sql.newInstance("redacted","redacted","redacted","com.microsoft.sqlserver.jdbc.SQLServerDriver") 
DatabaseValues = sql.rows("select TOP 100 lItemID,szUser,szName,szResponsible,szFirstname,szCustomerNr,szVATNumber From Address where lItemID IN (select ContactId from PartnerContact Order by ContactId OFFSET 2 rows) ORDER BY " + Sort)

assert DatabaseValues.lItemID.toString() == SubscriptionGroupID.toString() : "Not all SubscriptionGroupIDs match with database value"
  • Olga_T's avatar
    Olga_T
    SmartBear Alumni (Retired)
    6 years ago

    Hi kendemoor,

     

    Good job in solving this, and thanks for sharing the solution with the Community!

    Let me mark it as a Solution here for other users to be able to easily find it in the future.

     

    Community, maybe, does anyone know why kendemoor's first attempt didn't work? 

     

    Thanks,