I'm using jdbc.SQLServerDriver in a Java Maven project to insert test records into a test reporting database. When I run a test locally either through Intellij or by running 'mvn clean compile', 'mvn test' in Powershell, the records are successfully inserted into the database. However, when I run through Jenkins (declarative pipeline), I get the following error message: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2.
I have looked at several resources around CallableStatement and several StackOverflow posts about the error message, but I do not understand why the parameter would be set when running locally, but not in Jenkins.
Here is my Jenkinsfile:
pipeline {
agent any
stages {
stage('Compile') {
steps {
withMaven(maven: 'LocalMaven'){
bat 'mvn clean compile'
}
}
}
stage('Test') {
steps {
withMaven(maven: 'LocalMaven'){
bat 'mvn test'
}
}
}
}
}
Here is my code to execute the stored proc:
public static void ExecuteStoredProc(String procedureName, Hashtable parameters, Connection connection)
{
try {
String paraAppender;
StringBuilder builder = new StringBuilder();
// Build the paramters list to be passed in the stored proc
for (int i = 0; i < parameters.size(); i++) {
builder.append("?,");
}
paraAppender = builder.toString();
paraAppender = paraAppender.substring(0,
paraAppender.length() - 1);
CallableStatement stmt = connection.prepareCall("{Call "
+ procedureName + "(" + paraAppender + ")}");
// Creates Enumeration for getting the keys for the parameters
Enumeration params = parameters.keys();
// Iterate in all the Elements till there is no keys
while (params.hasMoreElements()) {
// Get the Key from the parameters
String paramsName = (String) params.nextElement();
// Set Paramters name and Value
stmt.setString(paramsName, parameters.get(paramsName)
.toString());
}
// Execute Query
stmt.execute();
} catch (Exception e) {
System.out.println(procedureName);
System.out.println(parameters.keySet());
System.out.println(e.getMessage());
}
}
}
Here are the values I am passing in in the Hashtable:
public static void CreateRun(Connection connection)
{
//Params
Hashtable table = new Hashtable();
table.put("Machine", "Machine");
table.put("ClientOS", "CLientOS");
table.put("Environment", "Environment");
table.put("Browser", "Browser");
table.put("BrowserVersion", "BrowserVersion");
table.put("RunBuild", "RunBuild");
table.put("DevMachine", "1");
table.put("ExpectedCases", "1");
DatabaseUtil.ExecuteStoredProc("sp_CreateRun",table, connection );
}
And here is the stored proc:
... PROC [dbo].[sp_CreateRun]
@Machine varchar(45),
@ClientOS varchar(45),
@Environment varchar(45),
@Browser varchar(45),
@BrowserVersion varchar(45),
@RunBuild varchar(45),
@DevMachine bit,
@ExpectedCases int
AS
BEGIN
INSERT into Run (Start_Time, End_Time, Machine, Client_OS, Environment, Browser, Browser_Version, Run_Build, Dev_Machine, Expected_Cases)
values (GETDATE(),GetDate(),@Machine,@ClientOS,@Environment,@Browser, @BrowserVersion,@RunBuild,@DevMachine,@ExpectedCases)
END
Thanks in advance for taking a look.
Thanks, all, for the helpful comments. I never did figure out how to make this work with CallableStatement, but I did get it working both locally and on Jenkins using Spring SimpleJdbcCall. I really like how much cleaner the code is now.
public static void ExecuteStoredProc(String procedureName, Map<String, String> parameters)
{
JdbcTemplate template = new JdbcTemplate(SpringJDBCConfig.sqlDataSource());
MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource();
for (String key : parameters.keySet()) {
sqlParameterSource.addValue(key, parameters.get(key));
}
SimpleJdbcCall call = new SimpleJdbcCall(template)
.withCatalogName("matrix")
.withSchemaName("dbo")
.withProcedureName(procedureName);
call.execute(sqlParameterSource);
}