Announcement Announcement Module
Collapse
No announcement yet.
Problem with org.springframework.jdbc.object.StoredProcedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with org.springframework.jdbc.object.StoredProcedure

    i wrote a Procedure in my oracle db :
    create or replace package body test is
    procedure main_p(a in varchar2, b in varchar2) is
    begin
    //do something;
    end;
    end test;

    then invoked it in my class:
    public class TestSpringStoredProcedure extends StoredProcedure{
    public TestSpringStoredProcedure(DataSource ds) {
    setDataSource(ds);
    setSql("test.main_p");
    declareParameter(new SqlParameter("a", Types.VARCHAR));
    declareParameter(new SqlParameter("b", Types.VARCHAR));
    compile();
    }

    public Map executeCalculation() {
    Map inParameters = new HashMap();
    inParameters.put("a", new String("w"));
    inParameters.put("b", new String("y"));

    Map out = execute(inParameters); // Call on parent class
    return out;
    }
    }

    it works

    but when i changed Procedure to Function:
    create or replace package body test is
    function main_p(a in varchar2, b in varchar2) return varchar2 is
    begin
    return a;
    end;
    end test;

    i met a exception:
    org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call test.main_p(?, ?)}];

    i know when i invoke Function use jdbc i should write like this
    ?=call test.main_p(?,?)
    CallableStatement cstmt = con.prepareCall("{?=call test.main_p(?,?)}");
    cstmt.registerOutParameter(1,Types.FLOAT);
    cstmt.setString(2,"x");
    cstmt.setFloat(3,"y");



    so i try this in my TestSpringStoredProcedure class:

    ......
    declareParameter(new SqlOutParameter("c", Types.FLOAT));
    declareParameter(new SqlParameter("a", Types.VARCHAR));
    declareParameter(new SqlParameter("b", Types.VARCHAR));
    ......

    another exception thrown :org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call test.main_p(?, ?, ?)}];
    it was handled as call test.main_p(?, ?, ?)

    can somebody help me?
    Last edited by wcheng; Apr 11th, 2006, 05:02 AM.

  • #2
    Mabe you could try SqlReturnResultSet instead of SqlOutParameter.
    In any case you have to set the "function" property of your StoredProcedure to "true".

    Regards,
    Andreas

    Comment


    • #3
      thank you
      Andreas.
      i try like this
      setDataSource(ds);
      setSql(PROC_NAME);
      setFunction(true);
      declareParameter(new SqlReturnResultSet("rs", new RowMapper(){
      public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
      return new Integer(1);
      }
      } ));

      declareParameter(new SqlParameter("a", Types.VARCHAR));
      declareParameter(new SqlParameter("b", Types.VARCHAR));


      Map inParameters = new HashMap();
      inParameters.put("a", new String("w"));
      inParameters.put("b", new String("y"));

      Map out = execute(inParameters); // Call on parent class


      it ends in a exception :
      org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call test.main_p(?)}];

      where is my second in param?

      Comment


      • #4
        I'm at a loss.
        Does it work with SqlOutParameter (with "function" property set to "true")?


        Regards,
        Andreas

        Comment


        • #5
          ok,it works!
          thank you very much!

          Comment

          Working...
          X