DataBase Testing using Selenium

Usual DB testing involves data entry through UI and querying the DB to check if the data got commited into it or not. The other scenario is where the data gets loaded in DB through some different process and needs to be tested.

In this post, i’m going to discuss about the later.

The Data is coming from a different system and persisted to the DB. This data has to be tested against a catalog – which is source of truth.

The solution I developed was using Java, Apache POI and VB Macros. Ofcourse, cannot rule out the key role played by MS Excel and its formula.

Step#1: Setup Steps

1. Using JDBC, connect to the db.
2. Initiate the Reporting mechanism – I used Extent Reports for Test results reporting. Look up in google for details on Extent Reports

 

public void setup() throws ClassNotFoundException, SQLException{ 
//To write the data to excel eu = new ExcelUtility();
 //connector for db2 database
 Class.forName("com.ibm.db2.jcc.DB2Driver");
 //db path 
String db4URL = "jdbc:db2://10.xxx.xxx.xxx:xxxxx/CHE";

String dbLocation = dbURL; 
//connector 
c = DriverManager.getConnection(dbLocation, id, password);
//TestReport timeStamp = new SimpleDateFormat("yyyy.MM.dd.HH.mm.ss").format(new Date());
extentReports=new ExtentReports("C:/SeleniumUtilities/TestReport/ValidationReport"+timeStamp+".html");
 }

 

 

 

Step#2: Getting the SQL results and loading it into Excel

1. SQL query will be run on the database and result is saved in string array
2. This data is then transferred onto Excel workbook(File#1) using Apache POI
3. A standalone Visual Basic Script (.vbs) is then triggered by Java.
4. This VB script will then trigger a Macro residing inside the Excel File#1.
5. This Macro code will extract data from the above File#1 workbook, transform it with some basic transformation rules and then finally load it on another excel workbook(File#2).
6. This file#2 excel workbook has the formula present in it.
7. Formulas in file#2 will compare the data (loaded into it… which is nothing but the data obtained from the DB) against the product catalog – the source of truth
8. Formulas will output “Pass” or “Fail” in the relevant columns in file#2

Why transfer the data from file#1 to 2? Why not have the formula in file#1 itself?

When we ask Java to write the data in an excel with the help of Apache POI, the formulas in the file will not run automatically. You need to use Evaluate function provided by POI to evaluate the formula in each and every cell. The performance gets impacted pretty badly. For just 1300 rows of data having some 20 columns, it took almost close to an hour to do formula evaluation!

Hence i decided to separate them out and have VB do the job of transferring the data from File#1 to 2. The formulas would auto-run without any issues in file#2. It took less than 10 secs in this case, for same volume of data!!

Why use use standalone VB Script ?

The job of the this script is just to fire the Macro inside the file#1.

This is simply because Java cannot fire the Macro residing inside a Excel directly by itself. Whereas Java can run the standalone VB script residing in file system.

Why use excel formula? Why not do the validation of results in Java Program?

While that would be the best option to do from performance perspective, it was not as simple as it sounds. Multiple columns and multiple tabs had the data in source document. Also, it was not just one column of data for which the testing was required, multiple columns of data in the database had to be tested and all of them had the source information spread across multiple columns and tabs in the source file.

 

public  void runPCVSQL() {
 try{ 
//Statement object 
Statement sqlStatement = c.createStatement(); 
//SQL query 
String query = "SELECT aasd";
 //Result set object - holds the query output
ResultSet rs = sqlStatement.executeQuery(query); 
//Get the row count 
while (rs.next()){
rowCount+=1;
} 
System.out.println("rowcount: "+rowCount);
 //pointer is at the last row of rs object. Cannot bring it back to row1. hence use another object. 
ResultSet rs2 = sqlStatement.executeQuery(query); 
 resultSetMatrix =new String [rowCount][6]; 
outputExcelPath = "C:\\SeleniumUtilities\\TestData\\DataMatch.xlsm"; 
excelSheetName = "PLAN_BENEFIT"; 
int r= 0; 
while (rs2.next()){
for(int c= 1;c<=5;c++){
 resultSetMatrix[r][c-1] = rs2.getString(c); 
}
 r=r+1; 
} 
System.out.println("resultSetmatrix size:"+resultSetMatrix.length); 
//output the data to excel 
eu.outputThePlanDataToExcelType2(outputExcelPath, resultSetMatrix, rowCount,5, excelSheetName);
 //run excel macro mr = new MacroRunner();
 mr.runExcelMacro("C:\\SeleniumUtilities\\TestData\\runMacro.vbs"); 
Thread.sleep(5000); 
}catch (Exception e){
 e.printStackTrace();
 }
 }

 

Macro Runner code:

public class MacroRunner {public class MacroRunner {
 public void runExcelMacro(String vbsFilePath){
 try {
 Runtime.getRuntime().exec( "wscript "+vbsFilePath ); 
catch( IOException e ) {
System.out.println(e);
System.exit(0); 
 }
 }
}

 

Visual Basic program code:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\SeleniumUtilities\TestData\DataMatch.xlsm")

objExcel.Application.Visible = False
objExcel.Application.Run("copyPasteData")
objWorkbook.Save
objExcel.Application.Quit

WScript.Quit

 

Above program was written in a notepad and saved as .vbs extension file.

A simple macro called “CopyPasteData” was written in file#1. The above VB program, when triggered by Java, would trigger the macro in file#1 which will copy the data in it to file#2

Step#3: Result Reporting

HTML reports were created using Extent Reports jar files. Each row of data was considered as one test case