Error on database testing: communications link failure

Archive
,
#1 
I am getting error

Getting Error:

02-02-2018 02:20:15 PM - [ERROR]  - com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
02-02-2018 02:20:15 PM - [END]    - End action : sqlConnection.DemoMySql.connectDB

I followed exactly as mentioned https://www.katalon.com/resources-center/tutorials/connect-db-gui-testing/

#2

That error message indicates your database is somehow not reachable. Please double check again your database server and also your connection URL first

#3

Now I am getting following error:

Cannot cast object 'ConnectionID:1 ClientConnectionId: 15eb12d8-0684-4dac-9392-c6ef22e94aeb' with class 'com.microsoft.sqlserver.jdbc.SQLServerConnection' to class 'com.mysql.jdbc.Connection'
#4

i solved it with my own code: find my keyword in the attachment.

keyword.txt

#5

I just had the same problem. In my opinion, kishor’s changes to the Keywords file should not work at all. Because like me, he didn’t use a mysql server but a MS SQL server. Therefore the line …

import com.mysql.jdbc.Connection

should be changed or complemented with …

import com.microsoft.sqlserver.jdbc.SQLServerConnection

In addition, it seems helpful to me to point out the different structure of the connection string for the SQL Server, since the source linked in the above-mentioned Katalon article does not really continue at this point. But the project settings dialog for the database connection in Katalon Studio does this already:

image.png

#6

Of course, for SQL Server even the line

private static Connection connection = null

has to be changed in …

private static SQLServerConnection connection = null
#7

Forget completely about this above mentioned, much too cumbersome Keywords file! All you need is the groovy.sql.sql class and only one Keyword routine to build the desired SQL instance, like this one:

package general

import com.kms.katalon.core.annotation.Keyword
import groovy.sql.Sql
public class DBUtils {
  @Keyword
  def getSQLInstance(String dbSystem, String dbServer, String dbPort, String dbName, String dbUsername, String dbPassword) {
    String connectionString
    String serverDriver
    if (dbSystem == 'mysql') {
      connectionString = 'jdbc:' + dbSystem + '://' + dbServer + (dbPort ? (':' + dbPort) : '') + '/' + dbName
      serverDriver = 'com.mysql.jdbc.Driver'
    }
    else if (dbSystem == 'sqlserver') {
      connectionString = 'jdbc:' + dbSystem + '://' + dbServer + (dbPort ? (':' + dbPort) : '') + ';databaseName=' + dbName
      serverDriver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
    }
    else if (dbSystem == 'hsqldb:mem') {
      connectionString = 'jdbc:' + dbSystem + ':' + dbName
      serverDriver = 'org.hsqldb.jdbcDriver'
    }/*    else if (dbSystem == 'hsqldb:db_xyz') {      connectionString = 'jdbc:' + dbSystem + 'complete/the/DB-specific/part'      serverDriver = 'add.the.DB-specific.driver'
    }*/    def Sql sql = Sql.newInstance(connectionString, dbUsername, dbPassword, serverDriver)
    return sql
  }
}

This is probably the grooviest way to really use the advantages of Groovy. And it is completely sufficient not to have to deal with the peculiarities of ResultSet and the cumbersome necessity of pushing its pointer forward within the TestCase. Because this groovy.sql.Sql class brings its own groovish methods to process the result sets as maps or lists:

def sql = CustomKeywords.'general.DBUtils.getSQLInstance'(dbSystem, dbServer, dbPort, dbName, dbUsername, dbPassword)
sql.eachRow('SELECT * FROM table') { row ->
  println row
}sql.close()

Look here for more details.

Get SQL query results as variables
#8

I have slightly modified/extended my getSQLInstance Keyword method to be able to use it with my SQLite databases as well:

@Keyworddef getSQLInstance (String dbSystem, String dbServerOrFile, String dbPort=null, String dbName=null, String dbUsername=null, String dbPassword=null) {	String connectionString	String serverDriver	if (dbSystem == 'mysql') {		connectionString = 'jdbc:' + dbSystem + '://' + dbServerOrFile + (dbPort ? (':' + dbPort) : '') + '/' + dbName		serverDriver = 'com.mysql.jdbc.Driver'	}	else if (dbSystem == 'sqlserver') {		connectionString = 'jdbc:' + dbSystem + '://' + dbServerOrFile + (dbPort ? (':' + dbPort) : '') + ';databaseName=' + dbName		serverDriver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'	}	else if (dbSystem == 'sqlite') {		connectionString = 'jdbc:' + dbSystem + ':' + dbServerOrFile		serverDriver = 'org.sqlite.JDBC'	}	else if (dbSystem == 'hsqldb:mem') {		connectionString = 'jdbc:' + dbSystem + ':' + dbServerOrFile		serverDriver = 'org.hsqldb.jdbcDriver'	}	def Sql sql = Sql.newInstance(connectionString, dbUsername, dbPassword, serverDriver)	return sql}
#9

please helpme with update query, I have run query to update an record

#10