SQL Server Database Connection

Product Forums Katalon Studio
,
1

Hi there, I am having issues with using the Project > Settings > Database option
I believe that the Connection URL is correct, as I have tested it in another application called DBVisualizer which uses jTDS but when I switch to Microsoft JDBC driver it fails from within this app.
Example:
jdbc:sqlserver://WIN-DESKTOP\SQLEXPRESS:1433;databaseName=MTD

Now previously I had to import JDBC and have a custom keyword to connect to a database but am a little confused on what level I now need to go to.

So first of all should the ‘Database’ option connect without importing any java libraries from within the Settings?

I have installed the DBMS Keyword but cannot see it in the ‘Plugins’ folder and I cannot see a tutorial based on using it.

Currently using Katalon Studio v6.20

Thanks!

2

So yes, if you dont see the connect successful then there is something wrong there. You need to make sure this is working before you progress.

Screenshot%20(457)_LI
Screenshot%20(457)_LI1080×610 117 KB

So this is the keyword that i use for connecting to db, maybe it will help you out.

package db

import java.sql.Connection;
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement

import com.kms.katalon.core.annotation.Keyword

public class sql {

	private static Connection connection = null;

	@Keyword

	def connectDB(String url, String username, String password) {

		String conn = "jdbc:sqlserver://" + url

		if(connection != null && !connection.isClosed()) {
			connection.close()
		}
		connection = DriverManager.getConnection(conn, username, password)
		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
	}
}
3

Thanks for the quick reply, just edited the original post, I am using this for the Connection url, can you see anything wrong?

jdbc:sqlserver://WIN-*******\SQLEXPRESS:1433;databaseName=MTD

I am able to connect through SQL Server Manager very easily

4

Unfortunately I can’t get passed the stage of ‘Test Connection’ it always fails for me.

5

Your connection string looks fine to me, Maybe have a talk with your IT department checking that you will be allowed to access your database the way you are. It may be a proxy issue.

6

I would have thought that it would work as I am trying it within a VM. I.e. SQL Server Express, database & Katalon Studio are contained within the same VM?

7

Then maybe it is something to do with you connection string, ask someone else in your team to check it for you. Obviously i dont know what its meant to exacty look like so it may just be something your missing, and obviously double check the login your using. If you cant get it to work, im not sure then. Maybe one of the devs could help @duyluong @devalex88

8

image
Stuck on this if anyone else has any idea?

I have enabled port 1433 in SQL Server Configuration under IPALL tab.

9

I’m having the same kind of connection issue via the UI above or by a script which uses a connection string, both cases I get a connection refused response and to check connection details.

I’m using the same connection details within a elsewhere and this is working fine for me.

example of my connection string jdbc:sqlserver://COMPUTERNAME:1433;instanceName=INSTANCENAME;databaseName=DATABASENAME

10

Finally got this working:

jdbc:sqlserver://WIN-***********:1433;databaseName=MTD;

The above is working, without the instance name. Is there any limitation on that, just out of interest if anyone knows?

11

I never user instance as im not required to run my scripts on a specific instance of the database. If you are then this is something you will have to look into but if your like me, and it doesnt matter what instance of db your using then your fine. Ideally if you have your tests set up properly it shouldnt matter what instance you use as all hopefully all your values will not be hard coded.

12

jdbc:sqlserver://WIN-**********:1433; instanceName=SQLEXPRESS;databaseName=MTD;

This is working as well with the instance…

13

Im pretty sure if you dont specify a instance Name it will just use SQL Express as default so you shouldnt need to do that, but by all means if it works, Great :slight_smile:

14

Just to note I have now also got this running, I had to remove the port number off my connection string , as below :slight_smile:

@Keyword
def connectDB(String server, String instance, String dbname, String username, String password){
	
	String url = "jdbc:sqlserver://" + server + ";integratedSecurity=false" + ";instanceName=" + instance + ";databaseName=" + dbname + ";user=" + username + ";password=" + password + ";"
	
	if(connection != null && !connection.isClosed()){
		connection.close()
	}
	connection = DriverManager.getConnection(url)
	return connection
}