Postgres

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:

Recent Comments