ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kezerashvili, Denis" <Denis.Kezerashv...@gs.com>
Subject RE: How do I access value returned by Stored Procedure with the RETURN statement (DB2)
Date Thu, 20 Dec 2007 16:57:57 GMT
Thank you for the suggestion, I will try it. But I still have a
questions, is there a way to avoid having a property in the
parameterClass? Would it be possible to use something like this: 

queryForObject("userIdSynonym.deleteByUserId", userid, updatedNum); 

So that I can use the existing DaoId class and have the result of the
call stored into updatedNum.

Thank you

Denis

________________________________

	From: Jeff Butler [mailto:jeffgbutler@gmail.com] 
	Sent: Thursday, December 20, 2007 11:19 AM
	To: user-java@ibatis.apache.org
	Subject: Re: How do I access value returned by Stored Procedure
with the RETURN statement (DB2)
	
	
	Try this:
	 
	<procedure id ="userIdSynonym.deleteById" parameterClass
="DaoId"> 
	  {call #rc,jdbcType=INTEGER,mode=OUT# =  
	   PERMIT_DELETE_USER_ID_SYNONYM_BY_ID_SP(#id#, #updaterId#)} 
	</ procedure> 

	 

	This assumes your DaoId class has an integer property called
"rc".  Change the property name as appropriate.
	 
	Jeff Butler


	 
	On Dec 20, 2007 8:06 AM, Kezerashvili, Denis
<Denis.Kezerashvili@gs.com> wrote:
	

		I have a stored procedure that that deletes multiple
rows of data. It creates copies of the original rows in the audit table
and proceeds to delete them. At the end of the stored procedure I use
RETURN statement to return the number of the rows deleted. How do I
access this information from iBATIS? 

		Here is the snapshots that will give a better idea of
what is going on. 

		From mapping file: 

		        <procedure id ="userIdSynonym.deleteById"
parameterClass ="DaoId"> 
		                {call
PERMIT_DELETE_USER_ID_SYNONYM_BY_ID_SP(#id#, #updaterId#)} 
		        </procedure> 


		From calling Java code: 

		                int updatedNum =
getSqlMapClientTemplate().update( 
		                "userIdSynonym.deleteByUserId", userid);

		            logger.debug("*^*^*^*^*Deleted <" +
updatedNum 
		                + "> id synonyms*^*^*^*^*" ); 

		The resulting log record from this snippet is:
*^*^*^*^*Deleted <-1> id synonyms*^*^*^*^* 
		So the returned value is -1 for some reason. 

		Here is the stored procedure used. It's a DB2 Stored
procedure: 

		CREATE PROCEDURE
PERMIT_DELETE_USER_ID_SYNONYM_BY_USER_ID_SP 
		    (IN ID BIGINT, 
		     IN UPDATER_USER_ID BIGINT)    
		    LANGUAGE SQL MODIFIES SQL DATA 
		    BEGIN 
		        DECLARE RCOUNT INTEGER DEFAULT 0; 
		        FOR V1 AS C1 CURSOR FOR 
		            SELECT USER_ID_SYNONYM_ID, USER_ID,
USER_ID_SYNONYM_TYPE_ID, VALUE 
		            FROM PERMIT_USER_ID_SYNONYM 
		            WHERE USER_ID=ID 
		        DO 
		            INSERT INTO PERMIT_USER_ID_SYNONYM_AUDIT 
		                (USER_ID_SYNONYM_ID, 
		                USER_ID, 
		                USER_ID_SYNONYM_TYPE_ID, 
		                VALUE, 
		                ACTION, 
		                CREATED_BY) 
		            VALUES 
		                (USER_ID_SYNONYM_ID, 
		                USER_ID, 
		                USER_ID_SYNONYM_TYPE_ID, 
		                VALUE, 
		                'DELETE', 
		                UPDATER_USER_ID); 
		        END FOR; 
		        
		        DELETE FROM PERMIT_USER_ID_SYNONYM WHERE
USER_ID=ID; 
		                GET DIAGNOSTICS RCOUNT = ROW_COUNT; 
		        RETURN RCOUNT; 
		    END 
		GO 


		Any help will be greatly appreciated. I've been spinning
my wheels on this problem for 2 days now. 

		Denis 



Mime
View raw message