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 17:59:55 GMT
Thank you very much for your help. The original solution works with a
little modification:
 
<procedure id ="userIdSynonym.deleteById" parameterClass ="DaoId"> 
  {#rc,jdbcType=INTEGER,mode=OUT# = call
PERMIT_DELETE_USER_ID_SYNONYM_BY_ID_SP(#id#, #updaterId#)} 
</ procedure> 
 
You had {#rc,jdbcType=INTEGER,mode=OUT# = after call.


________________________________

	From: Jeff Butler [mailto:jeffgbutler@gmail.com] 
	Sent: Thursday, December 20, 2007 12:39 PM
	To: user-java@ibatis.apache.org
	Subject: Re: How do I access value returned by Stored Procedure
with the RETURN statement (DB2)
	
	
	Unfortunately no.  Stored procedure return values are handled as
output parameters per the JDBC spec.  The iBATIS queryForXXX methods
only return objects from resultSets - which is not what you have here.
	 
	You could use a HashMap as your parameter object to avoid adding
unwanted propertites to domain objects.  If you are interested in an old
debate on this topic, search the mailing list archives for "DB trash". 
	 
	Jeff Butler
	
	
	On Dec 20, 2007 10:57 AM, Kezerashvili, Denis
<Denis.Kezerashvili@gs.com> wrote:
	

		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