I have a test case where I used to reset test data - unfortunately, this only works in the dev environment b/c the db uses my credentials and I don’t have write credentials in prod (nor should I have them). So, we wrote a stored procedure to invoke that has tightly controlled credentials/limitations, and now I’m having trouble converting my db call to instead invoke a stored proc.
so I want to invoke a stored proc like EXEC [dbo].[stored_procedure_name] 'user@email.test'
Where user@email.test is the name of a user i’m resetting the test data on. I’ve spent a significant amount of time researching this and can’t find a good implementation, or even a working one. Any help appreciated!
fwiw - this particular stored proc just writes to the db, it does not return any data.
update: I’ve added a keyword per the documentation I can find, it looks like this:
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement
import com.kms.katalon.core.annotation.Keyword
public class StoredProc {
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:sqlserver://;servername=xxxxx;port=xxxxx;databaseName=xxxxx;integratedSecurity=xxxxx;"
//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)
return rs
}
//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
}
}
i’m able to successfully connect to the db and make the stored proc call but am getting error like this (below) because I need to pass an email address required by the proc as a variable, and don’t know how to do this:
here’s how i’m invoking the stored proc:
'xxxxx', 'xxxxx', '', '')
CustomKeywords.'com.c.StoredProc.execute'('my_custom_stored_proc')('user@google.test')
CustomKeywords.'com.c.StoredProc.closeDatabaseConnection'()```
here's the error:
```Test Cases/Steps/Database/Database Reset Users Stored Procedure FAILED.
Reason:
org.codehaus.groovy.runtime.InvokerInvocationException: com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'my_custom_stored_proc' expects parameter '@userEmailAddress', which was not supplied.```
any tips on how to insert that email address into that custom key word that invokes the stored proc?
starting a new thread to improve how i’m displaying my issues since i’ve made some progress.