Malvon Malvon - 14 days ago 5
Java Question

MapSqlParameterSource Does Not Map Properly with NamedParameterJdbcTemplate for a Store Proc

I am trying to call a stored procedure using mapped parameters with

NamedParameterJdbcTemplate
(Spring 3.0), but for some strange reason certain values do not get passed to the stored procedure (Oracle 11.2)!

The interface of the stored procedure in question:

PROCEDURE my_stored_proc(
h_id IN NUMBER,
h_type IN VARCHAR2,
h_status in varchar2 DEFAULT null,
h_end_date IN DATE DEFAULT null,
h_reason IN VARCHAR2 DEFAULT null,
h_rating IN VARCHAR2 DEFAULT null,
h_position IN VARCHAR2 DEFAULT null,
h_rater_id IN VARCHAR2 DEFAULT null,
h_start_date IN DATE DEFAULT null,
h_rater IN VARCHAR2 DEFAULT null,
h_supervisor IN VARCHAR2 DEFAULT null,
h_grade IN VARCHAR2 DEFAULT null)


I only need to pass a small subset of parameters for the purpose of my functionality (not all):

String sql = "{call my_stored_proc(:h_id,:h_type,:h_reason,:h_position)}";

MapSqlParameterSource sqlParamMap = new MapSqlParameterSource();
sqlParamMap.addValue("h_id", myObj.getHId(), Types.NUMERIC);
sqlParamMap.addValue("h_type", myObj.getHType(), Types.VARCHAR);
sqlParamMap.addValue("h_reason", myObj.getHReason(), Types.VARCHAR);
sqlParamMap.addValue("h_position", myObj.getHPosition(), Types.VARCHAR);

NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds);
template.execute(sql, sqlParamMap, new PreparedStatementCallback<Boolean> {

@Override
public Boolean doInPreparedStatement(PreparedStatement ps)
throws SQLException, DataAccessException {
return ps.execute();
}
);


As you can see, I am not utilizing all the stored procedure's parameters -- only the required and needed based on this particular functionality. Nonetheless, when I let
my_stored_proc()
to simply write the values of the parameters to a table,
h_reason
and
h_position
are empty even though during debugging
myObj.getHReason()
and
myObj.getHPosition()
both have proper values.

If I execute the stored procedure via the following anonymous block from SQL:

DECLARE
h_id NUMBER;
h_type VARCHAR2(200);
h_reason VARCHAR2(200);
h_position VARCHAR2(200);
begin
h_id := 12352;
h_type := 'Z';
h_reason := 'L';
h_position := 'P';

my_stored_proc(
h_id => h_id,
h_type => h_type,
h_reason => h_reason,
h_position => h_position
);
END;


The stored procedure does insert a right set of values, so it is not the stored procedure that is at fault. Do I need to specify all the parameters even if not all are utilized?

Answer

In my experience, I have had numerous issues with MapSqlParameterSource and ever since, I have switched to using Map<String, Object> to resolve the issue of variable binding.

Comments