Help with SQL Database connection & script

Hi guys, am new to Katalon Studio and am trying to follow the various tutorials out there but am struggling to figure it all out as they seem a bit too incomplete for a newbie.

For reference I followed these 3 links to progress as much as I could:
https://www.katalon.com/resources-center/tutorials/connect-db-gui-testing/ Katalon Database Custom Keyword - YouTube
https://docs.katalon.com/display/KD/Database+Settings

I basically want to have Katalon Studio automatically connect to the SQL Server database, run a script, then close the connection.

So far under keywords I have added this, I changed ‘mysql’ to ‘sqlserver’ - SEE keyword.txt attachment.

And for the test script see the attached testscript.txt file.

The error I get is:

Test Cases/demo FAILED because (of) org.codehaus.groovy.runtime.InvokerInvocationException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 10.62.xx.xxxx/databasename, port 1433 has failed. Error: “null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.

I have tried disabling firewalls on both desktop and server, I can ping the server and connect to it from my local desktop.

Thanks for looking!

keyword.txt

testscript.txt

The sql server is on default port 1433 using TCP so that seems correct.

Does the Test connection button under Project Settings -> Database work?

My Testcase looks like this:

CustomKeywords.‘com.db.sql.connectDB’(‘server_name’, ‘1433’, ‘database’, ‘user’, 'password)

CustomKeywords.‘com.db.sql.execute’(‘UPDATE Account SET BALANCE = 12.00 WHERE ACCOUNT_NUMBER = 10001’)

CustomKeywords.‘com.db.sql.closeDatabaseConnection’()


Here is the keyword script I found and modified that works for me:

package com.db

import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Driver;
import com.kms.katalon.core.annotation.Keyword

public class sql {
private static Connection connection = null;

   @Keyword
   def connectDB(String server, String port, String dbname, String username, String password){
	    String url = "jdbc:sqlserver://" + server + ":" + port + ";databaseName=" + dbname + ";user=" + username + ";password=" + password 	
	   		   
	   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 execute(String queryString) {
	   Statement stm = connection.createStatement()
	   boolean result = stm.execute(queryString)
	   return result
   }

}

1 Like

Hi David, thank you ever so much for the reply! I got it working with your example. The database connection was working as you suggested. Just a missing single quote after ‘password’ in your above example.

Now need to read up on printing an output to screen.
Just wondering if you have followed any useful tutorials for Katalon itself? I am tempted with the Udemy course that seems to be advertised.

Ah excellent! I have looked at the tutorials. I have not done that Udemy course. I found the solution for the database connection by watching a youtube video. they need some better tutorials but you can’t really complain when they have provided such a great product for free! :slight_smile:

1 Like

That is true to some extent.
I have purchased the course so will try it out, then maybe see what I can find on YouTube and go from there. Hopefully by then I can start building up some usable tests.

Thanks David Rysz, got exactly what I needed.

Cheers

1 Like

Here are some YouTube videos relating to sql database connections and Katalon Studio:

1 Like

@David Rysz said:
Ah excellent! I have looked at the tutorials. I have not done that Udemy course. I found the solution for the database connection by watching a youtube video. they need some better tutorials but you can’t really complain when they have provided such a great product for free! :slight_smile:

Hi David, I completed the Udemy course and can recommend it. It was definitely worth ÂŁ10 and I learnt a lot from it.

Only issue I now have is that some of my scripts seem to have stopped working, for example the above database connection/sql query. I believe I was using Katalon Studio v4.61 and jumped to v5+ which has caused this, have you found the same issue at all?

I have to use update query, please help

For more information about SQL Database Connection & Input - visit here & if want to know advanced concepts of Microsoft SQL Server then find out our well curated SQL Server Course

CustomKeywords.‘com.db.sql.connectDB’(‘server_name’, ‘1433’, ‘database’, ‘user’, 'password)

CustomKeywords.‘com.db.sql.execute’(‘UPDATE tablename SET isactive= 0 WHERE id = 10001’)

tried with both single and double quotes but no luck. can someone please help me?

Thanks

Start SQL Server Management Studio. The first time you run SSMS, the Connect to Server window opens. If it doesn’t open, you can open it manually by selecting Object Explorer > Connect > Database Engine.

The Connect link in Object Explorer

In the Connect to Server window, follow the list below:

For Server type, select Database Engine (usually the default option).

For Server name, enter the name of your SQL Server instance. (This article uses the instance name SQL2016ST on the hostname NODE5 [NODE5\SQL2016ST].) If you’re unsure how to determine your SQL Server instance name, see Additional tips and tricks for using SSMS.

For Authentication, select Windows Authentication. This article uses Windows Authentication, but SQL Server login is also supported. If you select SQL Login, you’re prompted for a username and password. For more information about authentication types, see Connect to the server (database engine).

“Server name” field with option of using SQL Server instance

You can also modify additional connection options by selecting Options. Examples of connection options are the database you’re connecting to, the connection timeout value, and the network protocol. This article uses the default values for all the options.

After you’ve completed all the fields, select Connect.

Thanks Lewis for your reply :slight_smile:
No luck with the above steps and below are the errors in console
eason:
org.codehaus.groovy.runtime.metaclass.MissingMethodExceptionNoStack: No signature of method: sqlConnection.QueryInSQL.connectDB() is applicable for argument types: (java.lang.String, java.lang.String, java.lang.String) values: