JDBC callable statement executes locally, but "The value is not set for the parameter number 2" when run in Jenkins

Question

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.

Unrelated to your question: why are you using the ancient `Hashtable` and `Enumeration` instead of modern `Map` and an extended for loop or at least an `Iterator`? — RealSkeptic, Feb 26 '19 at 15:03
Have you checked output printed in `catch` block inside `ExecuteStoredProc`? Does your hashtable have enough parameters in that log statement? Also entries **are not ordered/sorted** in hashtable/hashmap. So you'd better not rely that keys from hashtable/hashmap will be fetched in the same order as you put them into. — Ivan, Feb 26 '19 at 15:06
@RealSkeptic Thanks for the suggestions! I'll take a look at Map and Iterator. — jack powers, Feb 26 '19 at 15:06
@Ivan, in the Jenkins console output I see 8 keys in the table, the same as the number I need. I am constructing the Callable Statement using the parameter name, so the unordered HashTable should not matter. Thanks! — jack powers, Feb 26 '19 at 15:10
@jackpowers I mean that your stored procedure expects that first parameter is `Machine` but there is no guarantee that `(String) params.nextElement();` will return `Machine` when called for the first time. It may return any key from that hashtable. — Ivan, Feb 26 '19 at 15:20
@Ivan, perhaps I am confused, but my understanding is that when using the parameter name rather than the index to set the string in the callable statement, as long as the name is the same name in the stored proc, order does not matter. See the top answer here: https://stackoverflow.com/questions/15334931/how-to-set-parameter-by-name-instead-of-its-position-in-jdbc-jpa-when-calling-st Again, I may be confused and not be seeing your point clearly. — jack powers, Feb 26 '19 at 15:31
But in that case you need to provide argument names and not `?` as parameters placeholders. And even then you might need to use a custom class like in an answer to this question: https://stackoverflow.com/questions/2309970/named-parameters-in-jdbc — Ivan, Feb 26 '19 at 15:40

jack powers · Accepted Answer · 2019-03-01T12:59:43.757

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);
}

JDBC callable statement executes locally, but "The value is not set for the parameter number 2" when run in Jenkins

1 Answers1