Using Stored Procedure in Keyword

Product Forums Katalon Studio
,
1

I have a SQL db that I want to query using a stored procedure, but I can’t figure out how to pass a parameter (user name) to the stored proc. Here’s what I have so far:

`@Keyword

def executeQuery(String queryString)  {

	
	Statement stm = connection.createStatement()

	ResultSet rs = stm.executeQuery(queryString)

	return rs

}`

I invoke this keyword here:

CustomKeywords.'com.mysql.cp.StoredProc.execute'('qa.UnitTest_UserTwoFactorAuthentication_Get')

and when I run it I get this error:

'UnitTest_UserTwoFactorAuthentication_Get' expects parameter '@userEmailAddress', which was not supplied.

2

I found the issue with this - posting my solutions below for posterity:

The Keyword looks like this

	 * 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

	}

	//Closing the connection```

The Test Step that invokes the keyword looks like this:

CustomKeywords.'com.MyApp.StoredProc.connectDB'(GlobalVariable.userAccountsDb, '', '', '', '')

CustomKeywords.'com.MyApp.StoredProc.execute' ('exec [qa].[UnitTest_UserTwoFactorAuthentication_Set_TwoFactorStatus] \'testuser1@email.test\', \'UserState1\'')

CustomKeywords.'com.MyApp.StoredProc.execute' ('exec [qa].[UnitTest_UserTwoFactorAuthentication_Set_TwoFactorStatus] \'testuser@email.test\', \'UserState2\'')

CustomKeywords.'com.MyApp.StoredProc.execute' ('exec [qa].[UnitTest_UserTwoFactorAuthentication_Set_TwoFactorStatus] \'testuser1@email.test\', \'UserState3\'')


CustomKeywords.'com.MyApp.StoredProc.closeDatabaseConnection'()
3

Hi There!

Please help me to execute store procedure from keyword as it is giving ’ ORA-00900: invalid sql statement.’

simple store proc code:
create or replace procedure agileplm_dm.TEST_PROC
is
begin
null;
–DBMS_OUTPUT.PUT_LINE(‘Test PLSQ code’);
end;
/

I am invoking keyword with my test proc as below:

CustomKeywords.‘com.database.StoredProc.execute’(’ EXEC [agileplm_dm].[TEST_PROC]')

Below are the logs:

Test Cases/BI DB testcase FAILED.
Reason:
org.codehaus.groovy.runtime.InvokerInvocationException: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

at com.database.StoredProc.invokeMethod(StoreProcExecution.groovy)
at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:50)
at BI DB testcase.run(BI DB testcase:23)
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:430)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:421)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:400)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:392)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:273)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:142)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:133)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1632926960092.run(TempTestCase1632926960092.groovy:25)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1792)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1745)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:334)
at java_sql_Statement$execute.call(Unknown Source)
at com.database.StoredProc.execute(StoreProcExecution.groovy:105)
... 14 more

image
image1308×611 53.5 KB