javajenkinsjdbccallable-statement

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


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.


Solution

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