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 18:03:39 GMT
Ah yes - sorry 'bout that.  I'm glad you got it working.

Jeff Butler

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

>  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