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/ https://www.youtube.com/watch?v=B0f0xOIsQtE
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:

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