Ah yes - sorry 'bout that. I'm glad you got it working.
Jeff Butler
On Dec 20, 2007 11:59 AM, Kezerashvili, Denis <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
> *Sent:* Thursday, December 20, 2007 12:39 PM
>
> *To:* [email protected]
> *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 <[EMAIL PROTECTED]>
> 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:[EMAIL PROTECTED]
> > *Sent:* Thursday, December 20, 2007 11:19 AM
> > *To:* [email protected]
> > *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 <[EMAIL PROTECTED]>
> > 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
> > >
> >
> >
>
