Validating CSV file content saved in DB using Jmeter

Question

I have csv file which will be processed and saved in DB. I need to verify the contents of CSV vs DB. CSV file has

IDENTIFIER|AUDIT_ACTION|EMPLID|NAME|EFFDT|LAST_NAME|FIRST_NAME
PERSON_STG|A|731757|Abhijit |01-Oct-2010|SS|Abhijit
PERSON_STG|A|743910|Singh|02-May-2011|S|Arshita
.......................

DB has table which has column for each value in CSV file (for example Emplid, Firstname, lastname)

I need to verify that each value in csv separated by delimiter saved in the database.

I have tried

1. Placing csv file in SFTP and is processed
2. JDBC config and JDBC request to get the values from DB

Can you help on how to compare CSV file JDBC response

Answer 1

Records in CSV is not in same order in DB. DB has more records. So there is mismatch for all the records. How to loop for each column in CSV for example EMPLID value '731757' have to be searched for all the EMPID values in DB, if found, then assertion should pass.

enter image description here

Answer 2

1. Given you have data in the database which looks like:

   mysql> select * from cvdata;
   +------------+--------------+--------+---------+-------------+-----------+------------+
   | IDENTIFIER | AUDIT_ACTION | EMPLID | NAME    | EFFDT       | LAST_NAME | FIRST_NAME |
   +------------+--------------+--------+---------+-------------+-----------+------------+
   | PERSON_STG | A            | 731757 | Abhijit | 01-Oct-2010 | SS        | Abhijit    |
   | PERSON_STG | A            | 743910 | Singh   | 02-May-2011 | S         | Arshita    |
   +------------+--------------+--------+---------+-------------+-----------+------------+
   2 rows in set (0.00 sec)
   

2. If you issue a SELECT statement in the JDBC Request sampler configured like:

   

3. You will get the following JMeter Variables (can be visualized using Debug Sampler and View Results Tree listener combination)

   

   See Debugging JDBC Sampler Results in JMeter article to learn more about working with JDBC Requests results in JMeter

4. Add a Loop Controller to your Test Plan and configure it to iterate Forever

5. Add CSV Data Set Config as a child of the Loop Controller and configure it like:

   

   The above configuration assumes that your CSV file looks like:

   IDENTIFIER|AUDIT_ACTION|EMPLID|NAME|EFFDT|LAST_NAME|FIRST_NAME
   PERSON_STG|A|731757|Abhijit |01-Oct-2010|SS|Abhijit
   PERSON_STG|A|743910|Singh|02-May-2011|S|Arshita
   

   if it is different - you will need to amend your CSV Data Set Configuration.

   Note that I intentionally left a white space after Abhijit to trigger a test failure for demo purposes, remove it if you want to check the "happy path"

6. That's almost it, all you need to do now is to compare if

   • IDENTIFIER variable is equal to IDENTIFIER_1 variable on 1st iteration
   • IDENTIFIER variable is equal to IDENTIFIER_2 on 2nd iteration
   • AUDIT_ACTION variable is equal to AUDIT_ACTION_1 on first iteration
   • AUDIT_ACTION variable is equal to AUDIT_ACTION_2 on 2nd iteration
   • etc...

   In order to check these you can add a JSR223 Sampler as a child of the Loop Controller and put the following code into "Script" area:

   int loop = (vars.get('__jm__Loop Controller__idx') as int) + 1
   if (!vars.get('IDENTIFIER_' + loop).equals(vars.get('IDENTIFIER'))) {
       SampleResult.setSuccessful(false)
       SampleResult.setResponseMessage('IDENTIFIER mismatch')
   }
   if (!vars.get('AUDIT_ACTION_' + loop).equals(vars.get('AUDIT_ACTION'))) {
       SampleResult.setSuccessful(false)
       SampleResult.setResponseMessage('AUDIT_ACTION mismatch')
   }
   if (!vars.get('EMPLID_' + loop).equals(vars.get('EMPLID'))) {
       SampleResult.setSuccessful(false)
       SampleResult.setResponseMessage('EMPLID mismatch')
   }
   if (!vars.get('NAME_' + loop).equals(vars.get('NAME'))) {   
       SampleResult.setSuccessful(false)
       SampleResult.setResponseMessage('NAME mismatch')
   }
   if (!vars.get('EFFDT_' + loop).equals(vars.get('EFFDT'))) {
       SampleResult.setSuccessful(false)
       SampleResult.setResponseMessage('EFFDT mismatch')
   }
   if (!vars.get('LAST_NAME_' + loop).equals(vars.get('LAST_NAME'))) {
       SampleResult.setSuccessful(false)
       SampleResult.setResponseMessage('LAST_NAME mismatch')
   }
   if (!vars.get('FIRST_NAME_' + loop).equals(vars.get('FIRST_NAME'))) {
       SampleResult.setSuccessful(false)
       SampleResult.setResponseMessage('FIRST_NAME mismatch')
   }
   

In case of any mismatch it will fail: