Am trying to write on excel while parallel executing. However sometimes on different cells doesn’t write any value and I don’t face any issues on the console output. Below is the write keyword code on excel.
def WriteValues(int cell, int row,String value,String inputexcel) {
boolean pass=false
while(pass==false){
try{
FileInputStream file = new FileInputStream (new File(RunConfiguration.getProjectDir() + inputexcel))
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
'Write data to excel'
sheet.getRow(row).createCell(cell).setCellValue(value);
file.close();
FileOutputStream outFile =new FileOutputStream(new File(RunConfiguration.getProjectDir() + inputexcel));
workbook.write(outFile);
outFile.close();
pass=true
break;
}catch(Exception e){
WebUI.delay(1)
println(e)
}
}
I created a while-loop with try-catch functionality to handle the multiple writing functionality. Any ideas why sometimes returns empty values to my cells and without any warning on console output?
Am trying to write on excel while parallel executing. However sometimes on different cells doesn’t write any value and I don’t face any issues on the console output.
Your code does not implement any Mutual Exclusion for the .xlsx file at all. That is the whole reason why you get unsatisfactory result.
Let me explain. Here I assume:
You have an Excel file “a.xlsx” with an empty sheet “MySheet”
You have 2 Test Suites: TS1, TS2. You bind them in a single Test Suite Collection, and execute it in Parallel execution mode.
The following processing sequence is likely to occur:
TS1 opens a.xlsx, get MySheet. TS1 finds the MySheet is empty. TS1 writes “Hello” in a cell A1
TS2 opens a.xlsx, get MySheet. TS2 finds the MySheet is empty. TS2 writes “Bye” in a cell B2
TS1 closes FileOutputStream, then MySheet in TS1 is saved into a.xlsx.
TS2 closes FileOutputStream, then MySheet in TS2 is saved into a.xlsx.
How the a.xlsx will look like eventually?
You possibly expect to see
A1 has “Hello”
B2 has “Bye”
But in fact you would find
A1 is empty
B2 has “Bye”
The empty A1 would look puzzling for you.
How to overcome the difficulty?
I am negative if you can implement Mutual exclusion for bare local files (including *.xlsx files). In this case you need to invent great magics for yourself.
One idea could be DBMS (MySQL, SQL Server etc) in the backend as the data storage for your Test Suites. See https://docs.katalon.com/katalon-studio/docs/database-settings.html . In this case all the difficult mutex issues will be managed by the DB. Or I may try using Google SpreadSheet API, which provides users with built-in concurrency managements. Both are challenging though.
The easiest solution would be to run the Test Suite Collection sequentially, not parallel. Then the Mutex issues will disappear.
Another practical solution would be separating your process into 2 stages. Stage1 processes data in parallel mode while emitting the result into log-like files for each processes. After Stage1 done, Stage2 reads the log files to collect information and update an Excel file as you like.
Or sqlite if starting a server is not desired.
One day i will write a guide how sqlite can be used with katalon to grab and manage various data for persistent storage. But not yet…
SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds.
“it must lock the entire database file for the duration of its update” — this is what I meant as “great magics”.
By the way, I have found out that the Test Suites in a Test Suite Collection forks OS-level processes (= Java VM instances). They do not run on multiple Java Threads. I reported this in the following post.
Multi-processes or Multi-threads — this difference is not significant very much. A sophisticated Mutual Exclusion for persistent data storage needs to be implemented anyway.
I’ve got interested in the idea of usin SQLite3 in Test Case script in Katalon Studio. I tried it and got a small success. I have created a repository in GitHub
1. I already had SQLite installed into my Mac. I can not remember when and how. Possibly I installed it using Homebrew some months ago.
2. I downloaded the sqlite-jdbc-3.7.2.jar from Maven Central, saved in the Drivers directory of the project
3. created a test case where I just copy and pasted a sample code from http://www.octodecillion.com/groovy-with-sqlite/, as this Test Cases/TC1
4. run the test case — worked fine. In the console I saw messages:
2020-12-14 12:24:41.612 DEBUG testcase.TC1 - 7: sql.eachRow("select * from person", { -> ... })
id=1, name= leo
id=2, name= yui
2020-12-14 12:24:41.689 INFO c.k.katalon.core.main.TestCaseExecutor - END Test Cases/TC1
Now I know that I can read/write SQLite database in test case. My next question is how to write code that uses database table like an Excel worksheet. I need some Object-Relational Mapping mechanism such as groovy.sql.DataSet.
I developed Test Cases/TC2 that employs groovy.sql.DataSet.
This script did not work. When I executed it I got following error.
2020-12-14 18:38:43.141 ERROR c.k.katalon.core.main.TestCaseExecutor - ❌ Test Cases/TC2 FAILED.
Reason:
groovy.lang.GroovyRuntimeException: DataSet unable to evaluate expression. AST not available for closure: Script1607931888293$_run_closure2. Is the source code on the classpath?
at TC2.run(TC2:49)
...
package my
import groovy.sql.Sql
import groovy.sql.DataSet
public class SQLiteDataSetExample {
static void run() {
// The code of Test Case/TC2 is just copied here
}
}
The Test Cases/TC3 worked fine.The error of “DataSet unable to evaluate expression. AST not available for closure” disappeared.
Now I am sure that we can use SQLite as the local data storage for Katalon Studio test cases.
I think it is not really needed to have sqlite installed as a package, since it is just a client.
The driver should be able to handle everything needed just fine (create/query the db).
Not sure on this, will check.
However it can help to have it so one can take a look at the db for debug.
To inspect the DB, there are also some GUI tools available, like : https://sqlitebrowser.org/
This one is running fine on Linux and Windows. Never tried on Mac but should work too.
To overcome the issue with AST error, we can try the ‘old’ query form to retrieve the data:
def qry = """SELECT * FROM Author
WHERE (firstname > ?)
AND (lastname < ?)
ORDER BY lastname DESC"""
def params = ['Dierk', 'Pragt']
def result = sql.rows(qry, params)
this one should not need to evaluate the underlying script.
Anyway i will use a keyword for this, so both solutions should work fine in this case
Mhm… For some reason i cannot make it work on 8.0.0 (the alpha version), got some import errors.
But is working fine with 7.8.0 (will try also some newest versions)
@ThanhTo did something changed in 8.0.0 on the external drivers import mechanism?
It behaves like the jar from the Drivers folder is not loaded.
Will dig a bit more on this if the time afford it, but some devs may want to try this sample project on various versions. @kazurayam btw, you have something wrong in the keyword, there is a line started with ‘==’ but was easy to fix (has to be on the above line after the closing bracket of the list)
I could reproduce the problem. In Katalon Studio 8.0 alpha, my test case is unable to import groovy.sql.Sql and groovy.sql.DataSet class. See the screenshot below.
In KS 8.0.alpha2, we added groovy-text, groovy-xml, groovy-json. We will add groovy-sql or other groovy libs as built-in libraries if you think they are necessary for the next alpha or the beta release.
The work-around solution in v8.0.0.alpha2 is adding groovy-sql-3.0.2 to the Drivers folder.
aha, make sense. @kazurayam thx for digging into it. @duyluong i think will be useful to have groovy-sql added by default. there may be lot of users using custom keywords based on it, at the moment of migrating to the new version they may have the surprise that suddenly all testcases fail.
I found a solution by creating temporary excel files for each parallel execution and then a TC will read those temporary excel files and merge them into one excel file
