Issue DB2 query for update

mapudemm · October 12, 2022, 10:46am

hello everyone , can someone help me with this error

i can connect with the database, and i can exec query select
but unfortunately i can’t exec query UPDATE and DELETE, please some one if you guys know how to fix this issue it will mean a lot for me

Andrej_Podhajsky · October 12, 2022, 11:14am

hello,
as far as i remember UPDTE and DELETE are not queries. Since they do not return list of rows that
you can browse thru.
(This was long time ago) To execute those commands, you need to use STATEMENT there should be class in JDBC connector for that action, try to look in documentation of DB2.

i found example - should be universal enough to help you:

mapudemm · October 12, 2022, 3:29pm

Thanks for the information , i will check it tomorrow . Hopefully can fixing my problem, once again, thanks @Andrej_Podhajsky

mapudemm · October 13, 2022, 7:59am

Hi @Andrej_Podhajsky i’m still get error , would you help me to fix my code, i’m trying to implement using the solution that you mention before,

here’s my keyword code

package com.database

import java.sql.DriverManager
import java.sql.Statement
import com.ibm.db2.cmx.runtime.internal.metadata.StatementAttributes
import com.ibm.db2.cmx.runtime.internal.repository.metadata.parser.MetadataParser.StatementInfo
import com.ibm.db2.jcc.DB2ResultSetMetaData
import com.ibm.db2.jcc.am.Connection
import com.ibm.db2.jcc.am.ResultSet
import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.util.CryptoUtil
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.ResultSet;
import java.sql.SQLException;
//import java.sql.Statement;


public class QueryDB2 {
	private static Connection connection = null;

	/**
	 * Open and return a connection to database
	 * @param dataFile absolute file path
	 * @return an instance of java.sql.Connection
	 */

	//Establishing a connection to the DataBase

	@Keyword
	def connectDB(String url,String dbname, String port, String username, String password){

		//Load driver class for your specific database type
		Class.forName('com.ibm.db2.jcc.DB2Driver')

		String conn = "jdbc:db2://" + CryptoUtil.decode(CryptoUtil.getDefault(url)) + ":" + port + "/" + dbname

		//String connectionString = "jdbc:sqlite:" + dataFile

		if(connection != null && !connection.isClosed()){
			connection.close()

		}
		connection = DriverManager.getConnection(conn, username, CryptoUtil.decode(CryptoUtil.getDefault(password)))
		return connection

	}

	/**
	 * execute a SQL query on database
	 * @param queryString SQL query string
	 * @return a reference to returned data collection, an instance of java.sql.ResultSet
	 */

	//Executing the constructed Query and Saving results in resultset

	@Keyword

	def executeQuery(String queryString) {

		Statement stm = connection.createStatement()

		ResultSet rs = stm.executeQuery(queryString)

		DB2ResultSetMetaData rsmd = rs.getMetaData();
		int columnsNumber = rsmd.getColumnCount();


		List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
		while (rs.next()){
			Map<String, Object> row = new HashMap<String, Object>(columnsNumber);
			for(int i = 1; i <= columnsNumber; ++i){
				row.put(rsmd.getColumnName(i), rs.getObject(i));
			}
			rows.add(row);
		}

		return rows
	}


	//Closing the connection

	@Keyword

	def closeDatabaseConnection() {

		if(connection != null && !connection.isClosed()){

			connection.close()

		}

		connection = null

	}

	/**
	 * Execute non-query (usually INSERT/UPDATE/DELETE/COUNT/SUM...) on database
	 * @param queryString a SQL statement
	 * @return single value result of SQL statement
	 */

	@Keyword

	def execute(String queryString) {

		StatementInfo stm = connection.createStatement()

		boolean result = stm.execute(queryString)

		return result
	}
}

and this is my test case to update the database

import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testng.keyword.TestNGBuiltinKeywords as TestNGKW
import com.kms.katalon.core.testobject.TestObject as TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import com.kms.katalon.core.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable as GlobalVariable
import org.openqa.selenium.Keys as Keys

import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testng.keyword.TestNGBuiltinKeywords as TestNGKW
import com.kms.katalon.core.testobject.TestObject as TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import com.kms.katalon.core.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable as GlobalVariable
import org.openqa.selenium.Keys as Keys
import com.ibm.db2.jcc.DB2Driver
import com.mysql.cj.jdbc.result.ResultSetMetaData
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement
import com.kms.katalon.core.annotation.Keyword
import com.mysql.cj.jdbc.ConnectionImpl
import com.mysql.cj.jdbc.result.ResultSetMetaData
import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.testng.keyword.TestNGBuiltinKeywords as TestNGKW
import com.kms.katalon.core.windows.keyword.WindowsBuiltinKeywords as Windows
import org.openqa.selenium.Keys as Keys

import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.checkpoint.Checkpoint
import com.kms.katalon.core.checkpoint.CheckpointFactory
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testcase.TestCaseFactory
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testdata.TestDataFactory
import com.kms.katalon.core.testobject.ObjectRepository
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI

import internal.GlobalVariable

import org.openqa.selenium.WebElement
import org.openqa.selenium.WebDriver
import org.openqa.selenium.By

import com.kms.katalon.core.mobile.keyword.internal.MobileDriverFactory
import com.kms.katalon.core.webui.driver.DriverFactory

import com.kms.katalon.core.testobject.RequestObject
import com.kms.katalon.core.testobject.ResponseObject
import com.kms.katalon.core.testobject.ConditionType
import com.kms.katalon.core.testobject.TestObjectProperty

import com.kms.katalon.core.mobile.helper.MobileElementCommonHelper
import com.kms.katalon.core.util.KeywordUtil

import com.kms.katalon.core.webui.exception.WebElementNotFoundException

import cucumber.api.java.en.And
import cucumber.api.java.en.Given
import cucumber.api.java.en.Then
import cucumber.api.java.en.When


import io.appium.java_client.android.nativekey.AndroidKey
import io.appium.java_client.android.nativekey.KeyEvent
import io.appium.java_client.android.AndroidDriver
import io.appium.java_client.AppiumDriver
import io.appium.java_client.MobileElement
import com.kms.katalon.util.CryptoUtil

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

CustomKeywords.'com.database.QueryDB2.connectDB'('ip.database',db name','port','username','password')
String updateQuery = CustomKeywords.'com.database.QueryDB2.execute'("UPDATE {table_name} SET {fieldname} = '0', {fieldname} = '0' WHERE {fieldname} = 'data'")
CustomKeywords.'com.database.QueryDB2.closeDatabaseConnection'()

and this is the error message

Caused by: org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object 'com.ibm.db2.jcc.am.k4@4c7a078' with class 'com.ibm.db2.jcc.am.k4' to class 'com.ibm.db2.cmx.runtime.internal.repository.metadata.parser.MetadataParser$StatementInfo'

Andrej_Podhajsky · October 14, 2022, 8:18am

do not replace imports for

java.sql.*

use of propper diver is set by using

Class.forName

also driver needs to be imported in project using Katalon IDE

mapudemm · October 18, 2022, 7:22am

its still not works for me, i dont know the DB2 calss name except com.ibm.db2.jcc.DB2Driver

but anyway i have the other solution , i just ask to the developers to make API update for me

thanks a lot @Andrej_Podhajsky

Topic		Replies	Views
Update SQL table using Katalon Studio Katalon Studio katalon-studio	2	1699	April 5, 2019
I have an error when execute query that update in database Katalon Studio katalon-studio	1	653	March 21, 2022
How to perform Database Testing using Katalon Studio Documentation	13	3648	May 15, 2022
Write SQL query in the script Katalon Studio katalon-studio , api-testing	6	4685	August 8, 2022
How to connect db2? Archive katalon-studio , api-testing	1	628	August 8, 2022

Issue DB2 query for update

Related Topics