20092

MapSqlParameterSource Does Not Map Properly with NamedParameterJdbcTemplate for a stored procedure

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?

Answer1:

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.

Recommend

  • C function strchr - How to calculate the position of the character?
  • Pre-populated SQLite Database not reading properly in Android Studio
  • Activity overlaying. WindowManager.LayoutParams
  • get_absolute_url with parameters
  • EntLib Way to Bind “Null” Value to Parameter
  • Loading Bitmap to ImageView from URL in android
  • Interpreting STRACE output - pipes and forks
  • Correct implementation of List Iterator methods
  • Cannot save model when using ember render helper
  • How to render a pixel array most efficiently to a window in c++?
  • How to copy styled text in JTextPane
  • Doctrine2 bulk import try to work with another entity
  • Syntax error on tokens, AnnotationName expected instead - error on query
  • JSR-330 support in Picocontainer : @Inject … @Named(\"xxx)
  • Regarding starting the threads on a condition
  • Unable to send e-mail through Java
  • Creating a DropDownList
  • Who propagate bugfixes across branches (corporate development)?
  • NUnit 3.0 TestCase const custom object arguments
  • Undefined references when compiling gSOAP client
  • how does System.Web.HttpRequest::PathInfo work?
  • IE11 throwing “SCRIPT1014: invalid character” where all other browsers work
  • Reading a file into a multidimensional array
  • How to remove a SwiftyJSON element?
  • RxJava debounce by arbitrary value
  • pyodbc doesn't report sql server error
  • NSScanner Loop Question
  • Display java JPanel in a JFrame
  • how to adjust image in a panel in Java swing?
  • Scrapy recursive link crawler
  • Illegal mix of collations for operation for date/time comparison
  • Encrypt data by using a public key in c# and decrypt data by using a private key in php
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • Release, debug version and Authorization Google?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Hits per day in Google Big Query
  • LevelDB C iterator
  • How can i traverse a binary tree from right to left in java?