Write SQL query in the script.

Hello.
Can you help with one problem?

I need to write sql query in the script for further use in the variables of the data from the database.

How can i do this?

What does not try, does not come out.

Thank you in advance.

Hi, Alex! I’ve made it by custom Keyword:

package com.databaseimport java.sql.DriverManagerimport java.sql.ResultSetimport java.sql.Statementimport java.sql.Connection;import java.sql.PreparedStatement;import java.sql.Driver;import com.kms.katalon.core.annotation.Keywordpublic class DeleteTestUsers {	private static Connection connection = null;	@Keyword	def connectDB(String server, String port, String dbname, String username, String password){		String url = "jdbc:postgresql://"+server+":"+port+"/"+dbname+"?user="+username+"&password="+password+"&sslmode=disable"		if(connection != null && !connection.isClosed()){			connection.close()		}		connection = DriverManager.getConnection(url)		return connection	}	@Keyword	def executeQuery(String queryString) {		Statement stm = connection.createStatement()		ResultSet rs = stm.executeQuery(queryString)		return rs	}	@Keyword	def closeDatabaseConnection() {		if(connection != null && !connection.isClosed()){			connection.close()		}		connection = null	}	@Keyword	def executeProfile(String profIdOne, String profIdTwo) {		String queryString = 'DELETE FROM "profile" WHERE "id" in ('+profIdOne+', '+profIdTwo+')'		Statement stm = connection.createStatement()		boolean result = stm.execute(queryString)		return result	}	@Keyword	def executeUser(String userIdOne, String userIdTwo) {		String queryString = 'DELETE FROM "user" WHERE "id" in ('+userIdOne+', '+userIdTwo+')'		Statement stm = connection.createStatement()		boolean result = stm.execute(queryString)		return result	}}

And the Test Case himself:

'Execution DB DELETE query for dropping users'CustomKeywords.'com.database.DeleteTestUsers.connectDB'('typeYourHostHere', 'yourPort', 'yourDbName', 'yourDbUserName', 'YourDbPassword')def profIdOne = profIdVGdef profIdTwo = profIdIHCustomKeywords.'com.database.DeleteTestUsers.executeProfile'(profIdOne, profIdTwo)def userIdOne = profIdVGdef userIdTwo = profIdIHCustomKeywords.'com.database.DeleteTestUsers.executeUser'(userIdOne, userIdTwo)	CustomKeywords.'com.database.DeleteTestUsers.closeDatabaseConnection'()

1 Like

Also, if my example is too complicated for you - please refer to this link: https://dzone.com/articles/how-to-perform-database-testing-using-katalon-stud

@Ivan Gorchakov This was super helpful and just what I needed. Thanks for sharing!

@Ivan Gorchakov, Great help. Code snippet was very helpful.

hi…
i hope this solution may help you:
How to generate script in SQL Server with data using query?

Script a database by using the Generate Scripts option

  1. Connect to a server that’s running SQL Server .
  2. Expand the Databases node.
  3. Right-click AdventureWorks2016 > Tasks > Generate Scripts :
  4. The Introduction page opens. …
  5. Select Next to open the Set Scripting Options page. …
  6. Select OK, and then select Next.