Announcement Announcement Module
Collapse
No announcement yet.
Newbie question about getting return value from stored proc Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Newbie question about getting return value from stored proc

    Folks,
    what is used to get just the RETURN value (NOT a result set) from a (sybase) stored procedure?

    I have this:

    Code:
     String sql = "exec sprocName (?,?)";
    		 int returnValue=getSimpleJdbcTemplate().queryForInt(
                                                          sql,		
    				   new Object[] { jobId, userId });
    I am getting an error that no result set is returned (which is correct -- the sproc just has:

    ...
    ...
    return 0

    at the end. I've tried a number of differnt calls here with no luck.
    Seems like it should be simple, but I haven't found anything in the documentation yet.
    Thanks!
    Last edited by jlg; Feb 20th, 2009, 02:01 PM.

  • #2
    As Javadoc says :

    The query is expected to be a single row/single column query that results in an int value.
    You should use one of the execute methods. maybe this one

    Comment


    • #3
      I think you might have to many querys at the same time.

      Comment


      • #4
        Thanks Pedro -- yeah, I realize the queryForInt is wrong (since there IS no return/result set -- only a return value).

        WHICH of the execute's did you mean -- your link just took me to the entire jdbctemplate page.

        Is there a definitive way using the jdbcTemplate/SimpleJdbcTemplate to get the return value from the stored procedure?? Any example code someplace?
        I've been searching, but so far, no luck.

        Sysbase, I'm not sure what you mean here. I have a single stored procedure, that takes parameters, returns NO results sets, and simply returns a return value -- that return value is what I'm trying to access, with no luck at the moment.

        Comment


        • #5
          It looks as though the only way to do this is to extend the StoredProcedure class like this:

          Code:
           public class SPExecute extends StoredProcedure {
          			
          			SPExecute(JdbcTemplate jdbcTemplate, String sql){
          				super(jdbcTemplate, sql);					
          				setSql(sql);
          				setFunction(true);
          			}
          			
          			public Map execute(Map inParams){
          				compile();
          				Map out = super.execute(inParams);
          				return out;
          			}
          		}
          and then calling like:
          Code:
           int returnValue;
           try {
          	    	 String storedProcString = "exec sproc_name";
                  	 SPExecute sp = new SPExecute(this.jdbcTemplate, storedProcString); 
          	    	 sp.declareParameter(new SqlOutParameter("returnValue", Types.INTEGER));	    	 
          	    	 
          	    	 sp.declareParameter(new SqlParameter("parm1",  Types.VARCHAR));
          	    	 sp.declareParameter(new SqlParameter("parm2",  Types.VARCHAR));
          	    	 sp.declareParameter(new SqlParameter("parm3", Types.INTEGER));
          	    	 sp.declareParameter(new SqlParameter("parm4",  Types.INTEGER));		    	 
          	    	 sp.declareParameter(new SqlParameter("uname", Types.VARCHAR));
          	    	 
          	    	 Map inParams = new HashMap();
          	    	 inParams.put("parm1", Parm1); //ParmN passed into method
          	    	 inParams.put("parm2", Parm2); 	    	 
          	    	 inParams.put("parm3", Parm3);
          	    	 inParams.put("parm4", Parm4);
          	    	 log.debug("execute the SPROC");
          	    	 Map rs = sp.execute(inParams); 
          	    	 returnValue = (Integer)rs.get("returnValue");
          	    	 log.debug("Returned from sproc is "+returnValue);
          	    	 
          		}
          Now the only issue I have left is getting both the returnValue AND a result set from the sproc.

          For example, if the sproc has:
          Code:
          select column_1, column_2 from table
          return 1
          the returnValue above will be "1", but not sure how to ALSO get the result Set.

          Comment


          • #6
            Just to complete this thread, to get the result set, in addition to the return value from the sproc, the result set does come back in the Map from the call.
            You just need to iterate through it (it is an ArrayList<Map>):
            Code:
            ....
            ....
             returnValue = (Integer)rs.get("returnValue");		    	
             ArrayList<Map> resultSet;		    	
             resultSet = (ArrayList<Map>)rs.get("#result-set-1");
             for (Iterator it = resultSet.iterator (); it.hasNext (); ) 
             {
                Map  row = (Map)it.next ();
               for (Iterator itArr = row.entrySet().iterator(); itArr.hasNext(); ) 
               {
            	Map.Entry entry = (Map.Entry) itArr.next();
                        Object key = entry.getKey();
            	Object value = entry.getValue();
             	log.debug("Key is "+key);
            	log.debug("Value for key is "+value);
            //In this example, just get the VALUE of the first column as Int.
            	col1=(Integer)value;
              }
             }
            I hope this is helpful to any other newbies that have to go through this!
            If ANYONE has a better way to do this, please feel free!! This is just one way I've found to do it.

            It seems to me that there should be an easy (jdbcTemplate or SimpleJdbcTemplate way to do this).

            Comment

            Working...
            X