Announcement Announcement Module
Collapse
No announcement yet.
stored procedure generating wrong string Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • stored procedure generating wrong string

    The sql string that i have been using for calling my stored procedure with a CallableStatement is:
    Code:
    {call ChangePassword(?,?,?,?,?)}
    the first 3 parameters are input, the last 2 are output. We are using sybase.

    i've tried to convert over to spring, here is the code i am using:

    Code:
    	private class MyStoredProcedure extends StoredProcedure
    	{
    
            public MyStoredProcedure(DataSource ds)
            {
                setDataSource(ds);
                setFunction(true);
                setSql("ChangePassword");
    	declareParameter(new SqlParameter("type", Types.NUMERIC ));
    	declareParameter(new SqlParameter("id", Types.NUMERIC ));
    			declareParameter(new SqlParameter("newpass", Types.VARCHAR));
                declareParameter(new SqlOutParameter("ERCODE", Types.NUMERIC ));
                declareParameter(new SqlOutParameter("ERMSG", Types.VARCHAR));
    
                compile();
    
                logger.info("call string=" + getCallString());
            }
    
            public Map changepass(Integer id, String pass) {
                Map inParams = new HashMap();
                inParams.put("type", new Integer(1));
                inParams.put("id", id);
                inParams.put("newpass", pass);
                Map out = execute(inParams);
                return out;
            }
    
        }
    I am executing it with:
    Code:
    	MyStoredProcedure sproc = new MyStoredProcedure(ds);
    	Map res = sproc.changepass(id, newPass);
    	logger.debug("ERCODE=" + res.get("ERCODE") );
    	logger.debug("ERMSG=" + res.get("ERMSG") );
    here is the error i am getting:

    org.springframework.jdbc.UncategorizedSQLException : (executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call ChangePassword(?, ?, ?, ?)}]: params=[{type=1, newpass=t5678, id=45061}]]): encountered SQLException [JZ0SC: Callable Statement: attempt to set the return status as an input parameter.]; nested exception is java.sql.SQLException: JZ0SC: Callable Statement: attempt to set the return status as an input parameter.
    java.sql.SQLException: JZ0SC: Callable Statement: attempt to set the return status as an input parameter.


    I noticed the sql generated, {? = call ChangePassword(?, ?, ?, ?)}, is not what i have used with my normal jdbc.

  • #2
    At least in Oracle a Function necessarly has a return value. So with setFunction(true) the first SqlOutParameter would be the return Parameter.
    Christoph

    Comment


    • #3
      If you are calling a procedure don't use "setFunction(true)". Either leave it out or use "setFunction(false)".

      Comment


      • #4
        I made the following change & it worked:

        Code:
        private class MyStoredProcedure extends StoredProcedure
           {
        
                public MyStoredProcedure(DataSource ds)
                {
                    super(ds, "ChangePassword");
           declareParameter(new SqlParameter("type", Types.NUMERIC ));
           declareParameter(new SqlParameter("id", Types.NUMERIC )); 
        ...

        Comment

        Working...
        X