ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: How do I access value returned by Stored Procedure with the RETURN statement (DB2)
Date Thu, 20 Dec 2007 17:38:44 GMT
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