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 How do I access value returned by Stored Procedure with the RETURN statement (DB2)
Date Thu, 20 Dec 2007 14:06:12 GMT
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