How to add variable in SQL query while fetching data so Based on variable in Data source as Database in kaalon

Product Forums Katalon Studio
, ,
1

Hi Team,
Need some help
I have selected datasourse as database in datafiles
And wrote SQL query to fetch data and added same data file in test suite as data source

Now I want that the SQL query which I used as Where condition in which I am passing one number

But now I want to pass Variable against it so

When I run test suite then based on that number Data should be fetched in test suite and then test suite should get executed as per the records in it

1 Like
2

Hi there,

Thank you very much for your topic. Please note that it may take a little while before a member of our community or from Katalon team responds to you.

Thanks!

3

Hi @siddarth.punamiya, if you have access to Copilot or Chatgpt you could do a query like this: How to do data driven testing and pass variables using SQL in Katalon Studio.

1 Like
4

Hi @siddarth.punamiya,

Thank you for sharing your issue. I would like to suggest the following steps:

  1. Define a Test Suite Variable:

    • Go to your Test Suite.
    • Click on the “Variables” tab.
    • Add a new variable, e.g., inputNumber, and set its default value if needed.

  2. Modify the SQL Query:

    • Open your Data File.
    • Modify your SQL query to use a placeholder for the variable. For example:
      SELECT * FROM your_table WHERE your_column = ?

  3. Script to Set SQL Query Dynamically:

    • In your Test Suite, add a Test Case to set the SQL query dynamically before fetching the data.

    • Use the following script to update the Data File with the variable value:

      // Get the test suite variable
String inputNumber = testSuiteContext.getTestSuiteVariables().get('inputNumber')

// Get the Data File Test Data Object
TestData dataFile = findTestData('Path/To/Your/DataFile')

// Set the SQL query dynamically
dataFile.query = "SELECT * FROM your_table WHERE your_column = " + inputNumber

// Reload the data (if necessary)
dataFile.loadData()

  4. Run the Test Suite:

    • When running the test suite, ensure you set the inputNumber variable to the desired value.
    • The test suite will execute based on the data fetched using the dynamic SQL query.

Please try and let me know if it works

5

Please upvote for my idea as Katalon is not supporting this feature and they have decided to take it up in there future releases
But if it gets good votes then they can implement this ASAP

Below is the link for my Idea which I posted

https://katalon-inc.my.site.com/katalonhelpcenter/s/kidea/a1vRA0000009yZdYAI/id00000113

6

Just some feedback. You have described how you think the solution should be implemented but not described clearly what the problem is. Is the solution from @Elly_Tran what is required? I mean it sounds like Data Driven Testing?

7

Hi I cannot see Set Variable option in Test Suite itself first and second thing i am directly fetching the data from Database by using Test Data as Database connection and query settings

I am also facing one more issue
when running the same Test suite it fails because below error
Data source is .dat file in which jdbc:oracle:thin:@DBname
DBData

false
passwd


readAsString
true


SELECT query

On Jenkins its failing do you know why

com.kms.katalon.execution.exception.ExecutionException: java.lang.IllegalArgumentException: Data source of test data: Data Files/ProductHub/ChangeControlData/CCUIBevDeleteUpdate is not available.
at com.kms.katalon.execution.entity.TestCaseBindingExecutedEntity.prepareTestCase(TestCaseBindingExecutedEntity.java:65)