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.
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)
Hi Nguyen,
Thanks for your response . In case if I need to modify the table Dynamically how to overcome that ?
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
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?
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.
Hi ,
We are using Metadata to get the required values from DB.
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.