• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

Executing a Stored Procedure Call with NamedParameterJdbcTemplate

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using Spring 3.0, I'm trying to make a stored procedure call using NamedParameterJdbcTemplate but getting a weird exception. My code reads:



Executing the test unit for this method would produce the following exception:



The stored proc works if I employ an extension of StoredProcedure, so I know the store proc works (from the SQL and the code). However, I have not been able to get it to work through templates.

Let me also add that the stored proc takes in 7 parameters, but even if I add an additional "mySqlParameters.addValue()" for the 7th parameter (which I do not update for this particular callSP()) and change the call to "{call MY_WRAPPER(?,?,?,?,?,?,?)}", it still complains about that it expects 0 parameters rather than 7. I have tried to change the SQL string to "MY_WRAPPER", "MY_WRAPPER(?,?,?,?,?,?)" or "call MY_WRAPPER(?,?,?,?,?,?)", but the exception remains.

I tried to pass in the actual SQL string ("myStoredProc") directly to ps.execute() or ps.executeUpdate(), but surprisingly, the debugger doesn't even get to "return ps.execute()" or "return ps.executeUpdate()". Also, I am making the invocation solely for updating and do not expect any data back. Any idea where the issue is orginating?
 
Favil Von
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, it turned out that I needed to explicitly name the parameters in my call, which is a good thing:



However, now I'm encountering, what it seems like, an uncommitted insert (note that my update basically inserts a new row and is not an update -- the PL/SQL wrapper's actual functionality is not important here, and I am looking at it as a blackbox for now). When I execute the method, I see it's writing to a row, but the subsequent attempts to run the Junit again would not commit the execute(). Nonetheless, after running the test another time, the insert occurs (what I am observing is that the sequence object [Oracle] is being incremented, but when the table is queried, it seems like one or two sequence numbers have been skipped).

1st call to callSP(), seq number is, say, 1000, with a row entry values
2nd call to callSP(), I don't see any new insert
3rd call to callSP(), no insert is witnessed
4th call to callSP(), seq number is 1003, with a row entry values
5th call to callSP(), no insert is witnessed
6th call to callSP(), seq number is 1005, with a row entry values



Any reason why this behavior is occurring?
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!