How to create a test case to check a DB value

Hi,

I am relatively new Katalon Studio. I wish to use the K Studio for 3 use cases:

  • testing our product website (no problem)

  • testing that a database value has been created (via a SQL statement)

  • Mocking an External API in order to create a set of Regression tests. Using K Studio to orchestrate a set of Regression tests.

So for the second item:
I am currently struggling to understand how to create a test case that will test a DB value. I have successfully created a data file DB type , that returns the value that can be checked

I have not yet managed to write a test case that checks the value 1 returned from this data file .

So I also tried writing a new set of keywords , which appears to have worked , but again have not managed to use this in a test case…

I have searched all the documentation on how to create DB tests but none seems to show the full test case set up including how to retrieve a value from the DB and then test it.

For the 3rd item how to mock an external API then run a set of tests that uses this data to perform a known set of regression tests, has anyone used K studio to do this ?

So if any one can point me in the right direction help with the DB checking point , I would be grateful , and also the mocking API orchestration…

many thanks in advance
Richard

Kinda depends what your goal is. What do you mean test the DB? Test values? Calculations? etc…I mean you can set up Katalon with a default connection

You can setup database calls in your data files section:

You can then use this to get data back and get variables for values from the DB:

Then with the variables you can do all sorts of validations.

I would look this up DBData (Katalon Studio API Specification)

You can edit queries and replaces pieces, you can get values, column, rows etc. you can do it dynamically or hard coded. There is a lot you can do with the DB.
image

1 Like

Hi Blaze,

Thanks for your reply.

Predominately I would like to test db values that should have been set from input into the front end or as part of the solutions functionality.

Would you mind giving me a fictitious full example of your 'public static DBdata ’ snippet above please…, I have yet to get my head completely around the syntax… e.g. for findTestData …

Many thanks
Richard

Sure that public static stuff I setup under keywords. This is where you would put methods. You don’t have to do that, you can put it right in the test case.

Basically you want this:

import com.kms.katalon.core.testdata.DBData
DBData userData = findTestData('pathToYourQuery');

put the import at the top of your test case. make sure to go to settings and set up your global connections like I mentioned above. You can hit that test connection to make sure its working. Then under Data Files you would create a new data set.
image

name it what ever you want and choose “Database Data” from drop down
image

you then write your query

note the summary, results and edit query option. hit edit query. Here you can paste your query, you can select the global connections or set up individual ones if you prefer per query to specific data bases. you can test connection again. once you have your query in hit ok and it will show results:

once that is created you can right click it and go to properties. I have my stuff work related covered but basically where it has ID there and I have it blacked out but circled red. You copy everything after “Data Files/” and that is your path.

So checklist:
set up global DB under Project, settings
create a query under Data Files
create the actual query
copy the properties for the path

now you can import that statement I gave you and create your DB in your test. Then that part that says path you just fill that in with what you got for properties.

After that it’s as simple as capturing your values. you can edit queries or do the getvalue() thing I mentioned. and then do stuff like:

WebUI.verifyMatch(actualText, expectedHere, false, FailureHandling.CONTINUE_ON_FAILURE)

you would pull values into a variable for the actual and then ‘words’ for what you expect the value to be.

This is only one way to do it mind you. Not that it’s the best and only approach or anything.

1 Like

Blaze,

Thank you so much for second post, we have now managed to achieve what we wanted with reading the DB with your help.

On the other topic I raised ,have you tried mocking any API inputs into your solution at all ? We have a solution with 5-6 External system inputs and it would be great to try and Mock data coming in . It would mean getting K studio to orchestrate the different inputs so we could write a set of regression tests …

many thanks again
Richard

excellent I am glad it was helpful. I have not done anything with API and katalon yet unfortunately.