How to Create / Handle Dynamic DB Queries in Katalon

Archive
,
1

How to pass dynamic DB queries to “Datafiles” at Run time. I have different Queries for Different tables.Please help me out on the same.

2

Hi Ramesh,

You can find below sample code to pass your DB queries into a ‘Data File’

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)
3

Hi Nguyen,

Thanks for your response . In case if I need to modify the table Dynamically how to overcome that ?

4

Hi Ramesh,

Unfortunately you can modify the table dynamically for existing Data Files currently. One workaround solution you can achieve for this issue is to update the current queries

5

Vinh Nguyen said:

Hi Ramesh,

Unfortunately you can modify the table dynamically for existing Data Files currently. One workaround solution you can achieve for this issue is to update the current queries

Hi @Vinh Nguyen,
Can we update the query dynamically during runtime? My script requires results from Query 1 to be fed into the “where condition” of Query 2.

For example:
Query1- Select FirstName from Table1
Result of Query 1 will be used in Query2
Query2- Select Address from Table2 where Name like FirstName

Can this be achieved using Katalon?

6

Vinh Nguyen said:

Hi Ramesh,

You can find below sample code to pass your DB queries into a ‘Data File’

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)

I am a bit confused about what you are saying.

In my case, I would like to transfer data from my database into my Data File using a SQL query. I have 50 SQL queries that I must run, but I don’t want to create 50 Data Files, each one for a different SQL Query. Is it possible to create a kind of *dummy* Data File that has no value in its “SQL Query” field as default. Then in “Script Mode”, I place each one of the 50 SQL scripts into that Data File, one my one. I *think* this is what the Ramesh asked, but I don’t fully understand how your answer explains how to do that.

Thank you.

7

Hi ,
We are using Metadata to get the required values from DB.

8

Hi!

Your idea was very helpful to me. I have a Data File titled “ViewSubjectData” and its SQL Query simply has : ‘Query’
My script has the following:

String TestQuery = blah blab blah
String DBName = “ViewSubjectData”
DBData dbTestData = findTestData(DBName)
dbTestData.query = dbTestData.query.replace("‘Query’", TestQuery)
dbTestData.fetchedData = dbTestData.fetchData()

I receive error:

Test FAILED because (of) java.lang.IllegalArgumentException: Cannot find test data with id ‘Data Files/VWS_ViewSubjectData’ because (of) ‘java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Unable to identify the table ‘Query’ for the metadata. Query: ‘Query’ Parameters: []’

Its strange because when I directly enter my desired query into the Data File then it works fine. Help is appreciated.

9