Katalon Studio

Programmatic Database query with Katalon

As mentioned previously, making dynamic database queries was a big goal for this year. Katalon makes this quite easy with a UI to set up the connection and a straightforward way of connecting to the data itself.

To start the process, select Data Files, New Test Data and select Database as the source.
Taking the configuration string from before, we connect to the database using this screen and provide the default query. This creates a default table of data that Katalon will work with when executing tests.

Within our code, we use the TesData object to get at our database source.
TestData inventoryDB = findTestData('Data Files/database object name')

We can now get the number of rows in the database, the names of the columns and read data from each column as needed. We need to reference each column with an index number rather than it’s name. Even though Katalon displays "item_number" as the column title, internally that is column 1.

However, we can still use item_number by getting a list of the columns and finding it’s index. I am adding 1 to the index since the List starts at 0 and 0 isn’t a valid column.

From there, we read information from the database using the getValue statement:
inventoryData=inventoryDB.getValue(columnIndex, loop)

From this very simple example we can get the counts of rows and column, then read the correct item from the list.

int loop=1, columnIndex=1, rowCount=1
String inventoryData=''
//Connect to Database Object in Data Files
TestData inventoryDB = findTestData('Data Files/Inventory Items Query')
rowCount=inventoryDB.getRowNumbers() //Row count for the number of results returned
List columnNames=inventoryDB.getColumnNames() //A List of the column names
columnIndex=columnNames.indexOf("item_number")+1 //Get the index of the column name we want

for (loop = 1; loop <=rowCount; loop++) {
    //Read the column data from the database and assign to a List
    inventoryData=inventoryDB.getValue(columnIndex, loop) //Read data directly from the database query
    GlobalVariable.inventorySKU[loop]=inventoryData
}

Other articles of interest:

Connecting Katalon to a Postgres Database

One of our goals for this year was to connect Katalon into our Postgres instance. It turned out to be quite simple, but we ran into a couple of errors before we got all the magic to happen.

When using the standard connection string, we get an hba_conf error along with ssl=true errors. This can be fixed in the connection string by appending the two following parameters:

sslfactory=org.postgresql.ssl.NonValidatingFactory
ssl=true

The connection string to be placed in Katalon looks like:

jdbc:postgresql://hostname:5432/dbname?sslfactory=org.postgresql.ssl.NonValidatingFactory&ssl=true

With this connection string in place, we were able to connect to the database without issue and execute queries.

Other articles of interest:

Determine if a Spreadsheet Cell contains Numeric or Text data

While working with some text data from a spreadsheet, I needed to read different kinds of SKU data. In some cases it would be a number such as 1313, while in others it might be a mix of text and numbers such as OU812.

Reding the value is normally done with .getNumericCellValue() or .getNumericCellValue(), but what happens when you don't know what the next value would be?

To overcome that, it's possible to the use .getCellType() and adjust the read based on type. Using some previous code, reading values from a spreadsheet and populating a Global List would look like the following:

for (loop = 1; loop <=rowCount; loop++) {
    //Assign spreadsheet columns to variables
    String cellType=sheet.getRow(loop).getCell(0).getCellType()
    //Determine if the cell contains Text or Numeric data
    if (cellType=="1"){
        String cellTextValue=sheet.getRow(loop).getCell(0).getStringCellValue()
        GlobalVariable.inventorySKU[loop]=cellTextValue
    }else{
        long cellIntValue=sheet.getRow(loop).getCell(0).getNumericCellValue()
        GlobalVariable.inventorySKU[loop]=cellIntValue
    }
}

In this case, 1 is String and 0 is Integer or Numeric. Based on the length of the SKU, I used long to store the value.

Additionally, in order to use the Integer value within a form, it needs to be converted to a String using:
String.valueOf(GlobalVariable.inventorySKU[loop])) such as:

WebUI.setText(findTestObject('Object/input-SKU'), String.valueOf(GlobalVariable.inventorySKU[loop]))

Apache POI – Cells

Other articles of interest:

Adding entries to an open Excel spreadsheet during runtime

When running the API test for several thousand entries, I noted that matching up an invalid request with the correct source data could be a needle in the haystack affair. Instead, it would be better to record any errors right alongside the source data in the XLSX spreadsheet file. That can be done by writing to a new cell during runtime.

The process is only a couple of additional lines of code.

To start, a new file needs to be opened using:

FileOutputStream outFile = new FileOutputStream(new File("//Postman Testing//Data Files//pricing.xlsx")

Data can then be written to the sheet using, createCell(5).setCellValue():
The .createCell(5) refers to column 5 in the spreadsheet. I'm using columns 0-4 for source data.

sheet.getRow(loop).createCell(5).setCellValue("ERROR: Failure Message on pricing request " + loop + " " + failure_message)

At the end, all data needs to be written to the workbook:

workbook.write(outFile)

And like other files, it needs to be closed when finished:
outFile.close()

During runtime, if an error is encountered, it is written inline with the source data. When the pricing.xlsx file is opened, the error message and data used to replicate the error is all in one place.

Other articles of interest:

Create a new and random UUID for an API call

When working with an API call, it may be necessary to create a new and valid UUID. This can actually be accomplished in a single line of code.

def order_id = UUID.randomUUID().toString()

This can be sent to the API object as a variable

mydate = new Date()
def order_id = UUID.randomUUID().toString()

//Initialize Order
initialize_order = WS.sendRequest(findTestObject('Initialize Order',
    [('order_id') : order_id,
    ('date') : mydate]))

Other articles of interest:

Recent Comments