Getting null from select from DB

have test where i call select from DB:
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 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.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 internal.GlobalVariable as GlobalVariable
import org.apache.commons.lang.RandomStringUtils as RandomString
import java.lang.Integer as javaint
import com.kms.katalon.core.logging.KeywordLogger

CustomKeywords.‘com.database.DemoMySql.connectDB’(‘demo.db’,‘demo.db’,‘777’,‘demouser’,‘password’)

String rs = CustomKeywords.‘com.database.DemoMySql.executeQuery’(“select confirm_code from loan order by id desc”)
WebUI.setText(findTestObject(‘Registration step_1/phone’), ‘+373’ + te)

in console i see that select is working ok i get result:
2019-08-22 12:53:00.279 DEBUG testcase.New Test Case (1) - 10: rs = com.database.DemoMySql.executeQuery(“select confirm_code from loan order by id desc”)
2407

but when i want to enter this answer from db (2407) in field to test it i get inserted value null

Can you share your DemoMySql class?

package com.databse

import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement

import com.kms.katalon.core.annotation.Keyword
import com.mysql.jdbc.Connection

public class DemoMySql {

	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

		String conn = "jdbc:mysql://" + url + ":" + port + "/" + dbname

		//Class.forName("org.sqlite.JDBC")

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

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

			connection.close()

		}

		connection = DriverManager.getConnection(conn, username, 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)

		//Move the cursor in the ResultSet table to the first row
		rs.next()
		//Save the result as a string
		String valueFromDatabase = rs.getString(1)
		//Print our result from the database as a string to the console
		println(valueFromDatabase)



	}

	//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) {

		Statement stm = connection.createStatement()

		boolean result = stm.execute(queryString)

		return result

	}

}

Your method (above) does not return anything, that’s why you get null. You only print valueFromDatabase variable, you must return it as well.

Thank you i understand taht in keyword in section:

@Keyword
def executeQuery(String queryString) {
Statement stm = connection.createStatement()
ResultSet rs = stm.executeQuery(queryString)
//Move the cursor in the ResultSet table to the first row
rs.next()
//Save the result as a string
String valueFromDatabase = rs.getString(1)
//Print our result from the database as a string to the console
println(valueFromDatabase)
}

I need attach answer from db to some variable yes?
is it something like return valueFromDatabase
I am new to programming

Yes, exactly. Put it at the end of method.

arturs.pumpa you can take a look at this post for more information, I think it could help.

now i get error from executing testcase:

2019-08-22 14:10:14.121 ERROR c.k.katalon.core.main.TestCaseExecutor - :x: Test Cases/New Test Case (1) FAILED.
Reason:
java.lang.ClassNotFoundException: com.database.DemoMySql
at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.getCustomKeywordClassAndSetMetaClass(CustomKeywordDelegatingMetaClass.java:100)
at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:45)
at New Test Case (1).run(New Test Case (1):44)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:337)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1566472207970.run(TempTestCase1566472207970.groovy:21)

need some library to be added?

Didn’t you break your class somehow during these changes? Maybe deleted brackets or something?

fix my mistake. now no error, but still i get null

def executeQuery(String queryString) {
Statement stm = connection.createStatement()
ResultSet rs = stm.executeQuery(queryString)
rs.next()
String valueFromDatabase = rs.getString(1)
return valueFromDatabase
}
my keyword now looks like this

Can you update your method like this:

def executeQuery(String queryString) {
    Statement stm = connection.createStatement()
    ResultSet rs = stm.executeQuery(queryString)
    println rs.getString(1)
    rs.next()
    println rs.getString(1)
}

And check what is written into console?

it is so strange i try to delete all keyword code and i still get the select answer from database…
I think i need to read something about keywords, because now i have two test cases and one keyword in my katalon project, but when i change something in keyword nothing happens, like my test case take information from cash or something like this. it dont look what i change in keyword code

Now i get clear with keywords =)))
now everything are working fine!
Thank you very much my friend!!!

Welcome! Could you please provide your solution so also other people with similar problem would benefit from this topic? Thank you.

of course, but my problems also was because i don’t understand till end how keywords work =)
But now all ok, here is database execute keyword:

@Keyword
def executeQuery(String queryString) {
Statement stm = connection.createStatement()
ResultSet rs = stm.executeQuery(queryString)
//Move the cursor in the ResultSet table to the first row
rs.next()
//Save the result as a string
String valueFromDatabase = rs.getString(1)
//Print our result from the database as a string to the console
println(valueFromDatabase)
//return the value of select
return valueFromDatabase
}

to use this keyword in test case use:

String rs = CustomKeywords.‘demomysql.Demo.executeQuery’(“select code from requests order by id desc”)

1 Like

Oh okay, I see. Thanks for the explanation.

1 Like