Announcement Announcement Module
Collapse
No announcement yet.
Calling stored procs - variations Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calling stored procs - variations

    Hi All,

    since I exclusively work with Oracle stored procs, I wanted to know the different variations I could use effectively.

    Initially, every call to a stored proc I made from Java would be by calling an inner class that extends StoredProcedure. This works fine but gets a little tedious when the IN/OUT parameters are simple types (ints and strings mostly).

    I want to know how far I can go with using JDBCTemplate directly before resorting to using the StoredProcedure class. I guess with resultsets its pretty much mandatory that you cannot use JDBCTemplate directly.

    So if I have the following stored procs:

    1. function takes no parameters and returns an int.
    2. function takes one or more parameters and returns an int.

    Here's my attempt at (1):

    Code:
    public int callFunctionInDb() {
            JdbcTemplate jt = new JdbcTemplate(ds);
            int test = jt.queryForInt("{?=call testpkg.testcase1}");
            return test;
        }
    but it causes an error:

    Code:
    org.springframework.dao.InvalidDataAccessApiUsageException: Cannot execute [{?=call testpkg.testcase1}] as a static query: it contains bind variables
    Any advice on this?

    Thanks

    Rakesh

    PS is there somewhere I can see lots of examples of the different variations you can have?

  • #2
    You could run it as a standard query -

    Code:
    public int callFunctionInDb() {
            JdbcTemplate jt = new JdbcTemplate(ds);
            int test = jt.queryForInt("select testpkg.testcase1() from dual");
            return test;
        }
    or like this if you have parameters -

    Code:
    public int callFunctionInDb() {
            JdbcTemplate jt = new JdbcTemplate(ds);
            int test = jt.queryForInt("select testpkg.testcase1(?, ?) from dual",
                     new Object[] {"P1", new Integer(1)});
            return test;
        }
    or you could use the JdbcTemplate call method for more advanced usage -

    Map call(CallableStatementCreator csc, final List declaredParameters)

    The best source for examples is searching this forum.

    Comment


    • #3
      Thanks for your input but wrapping the proc in a select statement will not always work. If the proc has any DML statements, it is illegal to call a proc like that.

      Unfortunately, there are no decent examples of CallableStatements either here, the docs or even in the latest Spring book. I guess once you have seen the prepared statements in action, it should be easy to figure out how the calleable statements work (errr, not in my case!).

      Anyway, I have posted to the Architecture forum about a reusable class i created after many refactorings. I would appreciate your feedback.

      Thanks

      Rakesh

      Comment

      Working...
      X