How do you pass variables into queries for – Dynamic Data-Driven testing

I am very new to automation, I am trying to setup dynamic values in my automation. I’ve got some set up with nightwatch.js and I’m attempting to move my testing to Katalon studio.

I want to query my db for data and then pass results into variable. Then use these variables in another query.

 

For example, user and customer.

I query the postgres DB for a user meeting certain criteria. This username is passed into the login field and chosen at random. This part I figured out, but then I need to get the user’s name or customer. So I would pass the username into the query to find their name or pass into a query to get that username’s customer list.

 

How would I do this? I found a way to do this with promise in nightwatch.js but don’t know how to do it in katalon studio.

 

What I have done so far is this:

  • setup data file
  • data file is for DB
  • data file has query to find username (query does RANDOM())
  • in test case I define in variable view result
so how would I pass this value into another query? I want to create all my testing as dynamic, any user with x conditions and then any customer from that user with x conditions. This way everytime I run the test it choose a new user or customer but still meets my criteria for considering the test passed
1 Like

I don’t think I understand…I am brand new to Katalon and have no Java experience at all. I have only just started working with Javascript and VBScripts but know very little. I ended up creating a Data File and ran a join query to get multiple rows. I then defined each variable based upon a different column but same row so the data is linked together. This is not my ideal solution but has worked so far.

Hi there,

So sorry for the late response. It depends on your purpose so you can put the code in any test case or any custom keyword. Below is my explanation of the code:

import com.kms.katalon.core.testdata.DBData

//Get your created database data to query user's name as your desire
DBData dbTestData = findTestData('DB Get User's name')

// Suppose your SQL query is: select name from users where username='_USERNAME_', in which _USERNAME_ is a placeholder to be replaced by your random value
// In the next step, you will replace the placeholder with your value, "abc" for example
dbTestData.query = dbTestData.query.replace("'_USERNAME_'", "abc")

// You will fetch the data of your query above
dbTestData.fetchedData = dbTestData.fetchData()

//Get value that you need (ie. cell (1, 1))
dbTestData.getValue(1, 1)

2 Likes

Greetings Hung Pham,

Thank you for your response. Where would I put this script?

What I have done for my current query is:
Project
Settings
Database

In there I set up my DB connections. I then created a Data file,

type: “Test Data”

data type: “database data”

I then simply put in my query

I am not sure where to enter the code you have shared with me. Can you please explain? I am brand new to Katalon and have only started using it last week.

Hi there,

Hope the below script would help you (Suppose you already have a test data named “DB Get Users” which has the connection string).

import com.kms.katalon.core.testdata.DBData

DBData dbTestData = findTestData('DB Get Users')
// SQL: select name from users where username='_USERNAME_'
// will turn to: select name from users where username='abc'
dbTestData.query = dbTestData.query.replace("'_USERNAME_'", "abc")
dbTestData.fetchedData = dbTestData.fetchData()

I understand now
after I import
import com.kms.katalon.core.testdata.DBData

I enter the test data value query into the place of ######
DBData dbTestData = findTestData(’######’)

this runs that query and sets the results to variable dbTestData

then I use that variable here, where I replace part of that query with the dynamic value. so in the new value I would put the variable for the dynamic value
dbTestData.query = dbTestData.query.replace(oldValue, newValue)

here I just execute it again to get the new query results
dbTestData.fetchedData = dbTestData.fetchData()

then I can create a new variable and set it to equal any column and row I want from those new results:
newVariable = dbTestData.getValue(row, column)

thank you

B L said:

I understand now, after I import

import com.kms.katalon.core.testdata.DBData

I enter the test data value query into the place of ######

DBData dbTestData = findTestData(’######’)

this runs that query and sets the results to variable dbTestData

Did this work for you? For example, I ran the following script:

DBData VWS_DB_Results = findTestData(“Query text here”)

But it returned the following error:

Test Cases FAILED because (of) java.lang.IllegalArgumentException: Cannot find test data with id ‘Query text here’ because (of) ‘java.lang.IllegalArgumentException: Test data with id ‘Query text here’ does not exist’

yes this worked for me Ilya Novak, sorry for the late reply. So the problem you are having is you have to update:
DBData VWS_DB_Results = findTestData(“Query text here”)

the part with “Query Text Here” that needs to be a path. If you go to the folder “Data Files” and create a DB query there, you can call it in with the findTestData.

So first create one, make sure you set up DB connections such as the global one and utilize it for connecting. For global connections, project, settings, Database. You can connect with your DB and utilize that for your queries.