Restoring MySQL to a known state before each test case

Product Forums Katalon Studio
,
1

Hi,

I understand the SetupTestCase() method can be used to execute tasks or commands before each test case is run as part of a test suite.

I would like to use this method to reset my MySQL database to a known state. This involves dropping the database (if it exists), creating the empty database again, and then importing data into it from a .sql dump file.

I’ve figured out how to do the first two steps, but I’m having trouble figuring out how to import a .sql file using Groovy.

The general concept (restoring the database to a known state) before each test case seems like it must be a fairly common idea in this sort of automated testing, so I would be interested to know how different people are approaching this, whether or not it’s through the same process I am attempting to set up.

2

@Russ_Thomas @Brandon_Hein @kazurayam

I think it’s an interesting question too. Any opinion ?

3

If you are capable of doing the first two steps, you should be able to do the 3rd as well. I wonder what kind of troubles you have for importing a .sql file. Could you share any log?

4

‘import a sql file’ is a wrong concept.
there is no such thing in real world, but back-up database (to a certain format which sometime indeed can be a sql statement) > do your stuff > truncate db > restore data.
it is only a matter of understanding how your database works, katalon will only provide (through dedicated java libs) the drivers to connect to it.
how to use them is at the will of the programmer

5

This.

Question is (as @ThanhTo intimated) how to automate it? I’m not a DB guru, I’ll defer to the consensus.

6

Yes, the file itself is just a bunch of SQL statements. But it is not practical to hardcode the SQL statements into the test script. I would like to be able to refer to this file from within the script and execute the queries. So perhaps more generically I can state my question as, how can I access the contents of an arbitrary file from within a test script?

7

I was kinda hoping that was your true intent. That way, you could even have an external process execute your query and “magically” restore your state.

This is a helper I use to execute arbitrary batch files (Windows) …

  /**
   * Execute a batch file situated in the KS project directory.
   * @param batchFile (String) e.g. "myfile.bat"
   */
  static void runBatchFile(String batchFile) {
    String bf = RunConfiguration.getProjectDir() + '/' + batchFile
    comment("Running batch file: " + bf)
    Runtime.runtime.exec(bf)
  }

Not sure if that’s synchronous though - I’ll defer to @Brandon_Hein / @anon46315158 on that.

8

@mcavagnaro

But it is not practical to hardcode the SQL statements into the test script. I would like to be able to refer to this file from within the script and execute the queries

Of course. You can do it.

Why not you execute The MySQL Command-Line Client mysql to execute SQL from a text file

shell> mysql db_name < text_file

as the document tells?

Your next question would be how to automate invoking a command-line shell in Katalon Studio = Groovy language script? See the following:

String cmd = "Your command"
Runtime.getRuntime().exec(cmd)

Very good. In the setupTestCase() method, you can do Runtime.getRuntime().exec(....). By doing so you can bring your MySQL database back to a known state prior to each test cases.

1 Like
9

I vaguely remember, it is a bit tricky to write a Java/Groovy code which can redirect the STDIN to the process invoked by Runtime.getRuntime().exe(…). I mean, this would fail.

String cmd = "mysql db_name < text_file.sql"
Runtime.getRuntime().exec(cmd)

because the content of text_file.sql is NOT put into the OS shell pipeline, therefore the content is not passed to the myslq process.

The following is discussing something related

Redirection is a functionality of the OS shell/cmd enviroments. To invoke them correctly we should use Runtime.exec(String) instead of Runtime.exec(String).

10

so, what is stopping you?