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 16:18:45 GMT
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