Get SQL query results as variables

system · January 4, 2019, 2:21pm

This is a companion discussion topic for the original entry at https://docs.katalon.com/katalon-studio/docs/get-sql-query-results-as-variables.html

Gan_Jyi_Yng · September 25, 2019, 1:24am

may i know where does this println record result stored?

ThanhTo · September 25, 2019, 4:10am

Hi @Gan_Jyi_Yng

It’s stored in the record variable. The println statement simply prints the value of that variable in the console log. What are you trying to achieve ?

Gan_Jyi_Yng · September 25, 2019, 5:41am

i wanted to validate the data stored in Database is exact as expectation or not.
sample database

//get data, i have data files, database query select * from database.table
def data = findTestData(‘Data Files/Database’)

// Get specific data as a variable, like find column A which is equal to 123, then get value of column C
//validate, then value column C if 0, passed. else failed.
how to?

ThanhTo · September 25, 2019, 6:02am

Hi @Gan_Jyi_Yng

findTestdata('Test Data ID')

returns an object of type Test Data which has methods like getValue(String columnName, int rowIndex)

You can visit the documentation:
https://docs.katalon.com/javadoc/com/kms/katalon/core/testdata/TestData.html
for more information.

Essentially you can do this:

TestData data = findTestData('Data Files/Database');
String columnA = data.getValue("Column A", i);
String columnC = data.getValue("Column C", i);
if(columnA.equals("123) && Integer.parseInt(columnC) == 0) {
 // Decide what to do in here
}

Gan_Jyi_Yng · September 25, 2019, 6:41am

question about the int rowIndex, is this means the row number? can i do not specify row number but find it using the value? e.g. column A, value 123? because the row number is dynamic, value 123 not always at row 11th…can be 15th, 20th…
String columnA = data.getValue(“Column A”, i);
String columnC = data.getValue(“Column C”, i);

ThanhTo · September 25, 2019, 6:56am

Hi @Gan_Jyi_Yng

The rowIndex is just a mean to access the ith row. If you only want to check if inside your data there exists a row with column A being ‘abc’ and column C being 0 then you can simply do:

TestData data = findTestData('Data Files/Database');
// Iterating over all rows of the dataset
for(int i = 0; i < data.getRowNumbers(); i ++) {
String columnA = data.getValue("Column A", i);
String columnC = data.getValue("Column C", i);
if(columnA.equals("123) && Integer.parseInt(columnC) == 0) {
 // Decide what to do in here - mark pass/failed or whatever
break; // This statement escapes the loop
}
}

Gan_Jyi_Yng · September 25, 2019, 7:05am

got it.
but execution error on
groovy.lang.MissingPropertyException: No such property: data for class

here is my code

import static com.kms.katalon.core.testdata.TestDataFactory.findTestData

import com.kms.katalon.core.logging.KeywordLogger
import com.kms.katalon.core.util.KeywordUtil
import com.kms.katalon.core.testdata.TestData as TestData

def KeywordLogger result = KeywordUtil.logInfo()

for(int i = 0; i < data.getRowNumbers(); i ++) {
TestData data = findTestData(‘Data Files/Database’);
String columnA= data.getValue(“columnA”, index);
String columnB= data.getValue(“columnB”, index);
if(columnA.equals(“Time111”) && Integer.parseInt(columnB) == true) {
KeywordUtil.markPassed(‘PASSED: value is correct’)
} else {
KeywordUtil.markFailed(‘FAILED: value is incorrect’)
}
break;
}

ThanhTo · September 25, 2019, 7:14am

Please move the line

TestData = findTestData('....');

to before the for loop, because we’re using data.getRowNumbers() which returns the number of rows in the dataset as the stopping condition, it is assumed that data must exist before that.

Gan_Jyi_Yng · September 25, 2019, 7:22am

tried move that line to before the for loop.
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData

import com.kms.katalon.core.logging.KeywordLogger
import com.kms.katalon.core.util.KeywordUtil
import com.kms.katalon.core.testdata.TestData as TestData

def KeywordLogger result = KeywordUtil.logInfo()
TestData data = findTestData(‘Data Files/Database’)
for(int i = 0; i < data.getRowNumbers(); i ++) {
String columnA= data.getValue(“columnA”, i);
String columnB= data.getValue(“columnB”, i);
if(columnA.equals(“Time111”) && Integer.parseInt(columnB) == true) {
KeywordUtil.markPassed(‘PASSED: value is correct’)
} else {
KeywordUtil.markFailed(‘FAILED: value is incorrect’)
}
break;
}

get error, the datafiles fetch data 57rows…i guess i need to tell the limit or anything else in the codes???
java.lang.IllegalArgumentException: Invalid row index 0 for test data ‘jdbc:postgresql://localhost:5432/DB1?currentSchema=SCHEMA’. Row index must be between 1…57

ThanhTo · September 25, 2019, 7:39am

Hi @Gan_Jyi_Yng

The error is saying the index 1 is not valid, because the first row is the header row. Please change the code from

for(int i = 0 .....

to

for(int i = 1 ...

and try again.

Mate_Mrse · September 25, 2019, 7:44am

You can get values by column name, as well:

data.getObjectValue("column A", i)

Gan_Jyi_Yng · September 25, 2019, 7:46am

error became
java.lang.NullPointerException

Gan_Jyi_Yng · September 25, 2019, 7:49am

i change the < to > , test execution no error
i > data.getRowNumbers()

i am not sure if above is correct, as the result i wanted to print at console does not print
2019-09-25 15:46:57.515 INFO c.k.katalon.core.main.TestCaseExecutor - --------------------
2019-09-25 15:46:57.518 INFO c.k.katalon.core.main.TestCaseExecutor - START Test Cases/DatabaseTest/Get DB data
2019-09-25 15:46:57.761 DEBUG testcase.Get DB data - 1: result = logInfo()
2019-09-25 15:46:57.769 INFO com.kms.katalon.core.util.KeywordUtil - null
2019-09-25 15:46:57.770 DEBUG testcase.Get DB data - 2: data = findTestData(“Data Files/Database”)
2019-09-25 15:46:58.006 INFO c.k.katalon.core.db.DatabaseConnection - dbServerInfo = PostgreSQL 11.5 (Debian 11.5-1.pgdg90+1)
2019-09-25 15:46:58.033 DEBUG testcase.Get DB data - 3: for ([i = 1, i > data.getRowNumbers(), (i++)])
2019-09-25 15:46:58.044 INFO c.k.katalon.core.main.TestCaseExecutor - END Test Cases/DatabaseTest/Get DB data

ThanhTo · September 25, 2019, 8:20am

Hi, please post the full error, just java.lang.NullPointException isn’t really informative. Please copy and paste the whole error ( red text ).

Don’t change < to >, because we are trying to loop from the first row to the end of the data. A tip is that the NullPointerException happens when you try to call a method on a null object.

If you look at the script I gave you, there are many objects involved columnA, columnB, data. I suggest you look at the error log to see which object is null and why, it would be more helpful than relying on the script I gave without understanding why.

Gan_Jyi_Yng · September 25, 2019, 9:23am

okay, here the full error console.

2019-09-25 17:21:38.380 INFO c.k.katalon.core.main.TestCaseExecutor - --------------------
2019-09-25 17:21:38.384 INFO c.k.katalon.core.main.TestCaseExecutor - START Test Cases/DatabaseTest/Get DB data
2019-09-25 17:21:38.603 DEBUG testcase.Get DB data - 1: result = logInfo()
2019-09-25 17:21:38.608 INFO com.kms.katalon.core.util.KeywordUtil - null
2019-09-25 17:21:38.608 DEBUG testcase.Get DB data - 2: data = findTestData(“Data Files/Database”)
2019-09-25 17:21:38.885 INFO c.k.katalon.core.db.DatabaseConnection - dbServerInfo = PostgreSQL 11.5 (Debian 11.5-1.pgdg90+1)
2019-09-25 17:21:38.915 DEBUG testcase.Get DB data - 3: for ([i = 1, i < data.getRowNumbers(), (i++)])
2019-09-25 17:21:38.930 DEBUG testcase.Get DB data - 1: columnA = data.getValue(“columnA”, i)
2019-09-25 17:21:38.950 ERROR c.k.katalon.core.main.TestCaseExecutor - Test Cases/DatabaseTest/Get DB data FAILED.
Reason:
java.lang.NullPointerException
at com.kms.katalon.core.testdata.DBData.verifyColumnName(DBData.java:110)
at com.kms.katalon.core.testdata.AbstractTestData.getObjectValue(AbstractTestData.java:136)
at com.kms.katalon.core.testdata.AbstractTestData.getValue(AbstractTestData.java:107)
at com.kms.katalon.core.testdata.TestData$getValue$0.call(Unknown Source)
at Get DB data.run(Get DB data:11)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:337)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1569403296109.run(TempTestCase1569403296109.groovy:21)

2019-09-25 17:21:38.987 INFO c.k.katalon.core.main.TestCaseExecutor - END Test Cases/DatabaseTest/Get DB data

Gerard_van_der_Winke · September 25, 2019, 1:16pm

Looks to me you might have a column name issue.
I see comments above with text talking about “Column A” with a space between “Coulmn” and “A”, while your code is mentioning column name “ColumnA” ( data.getValue(“columnA”) ).
Just make sure your column name you are trying to use in the getValue corresponds to the actual column name.

Gan_Jyi_Yng · September 26, 2019, 1:14am

yes i am using exact column name, in this thread i replace them to generic name for easy understanding.
anyway i guess the java null pointer exception issue comes from column name cannot be used. i saw some open thread about this but got no solution from Katalon people.
i changed to use column index (int) and it works well.

Gerard_van_der_Winke · October 7, 2019, 2:30pm

It’s definitely a bug with the Data Files of Data Type = Database Data (Internal Data does not have this issue).

Another temporary workaround (until fixed) is:

TestData data = findTestData(‘Data Files/DBdata’);

println(data.getColumnNames()); /* Prints: [accountid, url] */

for(int i = 1; i <= data.getRowNumbers(); i ++) {
String columnA= data.getValue( data.getColumnNames().toString().indexOf(‘accountid’) , i);
println(columnA);
}

Gan_Jyi_Yng · October 11, 2019, 5:21am

thanks.
good to know that is katalon bug.
i will do workaround.

Topic		Replies	Views
Get SQL query results as variables Katalon Studio katalon-studio , api-testing	2	722	December 28, 2018
Get SQL query results as variables Katalon Studio integrations , katalon-studio	12	12767	August 19, 2020
Get values from Data Type (Database Data) Katalon TestOps katalon-testops	3	1047	October 22, 2021
Data base get values Katalon Studio katalon-studio , web-testing	7	1850	August 17, 2021
DB Row Value - How to assign an each row value ( from particular table) into individual variable Archive katalon-studio	6	1059	August 8, 2022

Get SQL query results as variables

Related topics