CODEWITHSUNDEEP
sql-serverspring-jdbcsimplejdbccall
MalarMani
MalarMani

Reputation: 11

Simplejdbccall Stored Procedure withNamedBinding true

I want to execute stored procedure with dynamic parameters with SimpleJdbcCall. In total I have 6 optional parameters in SQL server SP, out of them I must be able to pass any or none. My SP executes fine as expected in MS Studio. But not by SimpleJdbcCall. I tried in many ways and one of them I tried is withNamedBinding. But it gives Input Syntax error near "=" as below.

this.simpleJdbcCall = new SimpleJdbcCall(jdbcTemplateObject)
                    .withNamedBinding()
                    .withSchemaName("dbo")
                    .withProcedureName("EmployeeDetails")
                    .useInParameterNames(
                            paramNameArray)
                    .returningResultSet("detailReportData", BeanPropertyRowMapper.newInstance(Employee.class));
            Map<String,Object> out = this.simpleJdbcCall.execute(sqlSource);

Log:

2019-01-31 18:14:49 DEBUG SimpleJdbcCall:405 - The following parameters are used for call {call dbo.EmployeeDetails(empCode => ?, empName => ?, empLoc => ?)} with {empCode=0, empName='hgkghdkgf', empLoc='kjhjk'} 2019-01-31 18:14:49 DEBUG DispatcherServlet:993 - Could not complete request org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call dbo.EmployeeDetails(empCode => ?, empName => ?, empLoc => ?)}]; SQL state [S0001]; error code [102]; Incorrect syntax near '='.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '='.

Upvotes: 1

Views: 1729

Answers (1)

Praveen Kithani
Praveen Kithani

Reputation: 11

I had faced similar type of problem and found few solutions in this stack overflow question. But in my case I was using MS-SQL server and it was giving me this same syntax error. While searching for solutions I came across this example of Spring (See Section 11.5.6. Declaring parameters to use for a SimpleJdbcCall). Just in case if the link becomes unavailable, here is what it says

We can opt to declare one, some or all of the parameters explicitly. The parameter metadata is still being used. By calling the method withoutProcedureColumnMetaDataAccess we can specify that we would like to bypass any processing of the metadata lookups for potential parameters and only use the declared ones. Another situation that can arise is that one or more in parameters have default values and we would like to leave them out of the call. To do that we will just call the useInParameterNames to specify the list of in parameter names to include.

And here is the sample code

public class JdbcActorDao implements ActorDao {
private SimpleJdbcCall procReadActor;

public void setDataSource(DataSource dataSource) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.setResultsMapCaseInsensitive(true);
    this.procReadActor =
            new SimpleJdbcCall(jdbcTemplate)
                    .withProcedureName("read_actor")
                    .withoutProcedureColumnMetaDataAccess()
                    .useInParameterNames("in_id")
                    .declareParameters(
                            new SqlParameter("in_id", Types.NUMERIC),
                            new SqlOutParameter("out_first_name", Types.VARCHAR),
                            new SqlOutParameter("out_last_name", Types.VARCHAR),
                            new SqlOutParameter("out_birth_date", Types.DATE)
                    );
}


//  ... additional methods

}

I am not sure why but the withNamedBinding() method seems to not work properly with ms-sql server and creates the syntax error. So in the above solution the work around is to

  1. not to use the .withNamedBinding() method.
  2. use the .withoutProcedureColumnMetaDataAccess() method.
  3. while passing your parameter array to the .useInParameterNames() method, make sure you pass it in the sequence it is declared in the proc (as we are not using name binding). My out parameter was declared few parameters before the last one and so it was giving me error while converting var to int as my out param was int.

So now your solution should look something like this :

this.simpleJdbcCall = new SimpleJdbcCall(jdbcTemplateObject)
                .withSchemaName("dbo")
                .withProcedureName("EmployeeDetails")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames(paramNameArray)
                .returningResultSet("detailReportData", BeanPropertyRowMapper.newInstance(Employee.class));
        Map<String,Object> out = this.simpleJdbcCall.execute(sqlSource);

Give it a try and see if this works for you.

Upvotes: 1

Related Questions