Malvon Malvon - 1 year ago 114
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

(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_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> {

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
to simply write the values of the parameters to a table,
are empty even though during debugging
both have proper values.

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

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

h_id => h_id,
h_type => h_type,
h_reason => h_reason,
h_position => h_position

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download