Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Question Unanswered: Affected record count always -1 with stored procedure

    I'm a C# .NET developer using the DB2 data provider. If I do an insert/delete with inline SQL I get the correct # of affected rows. If I create a command object, required parameters, and fire off the query, I always get a -1. In my example both processes are successful, but that's only determined when I view the table. I don't know if there's going to be much help with this part within this forum, but please keep reading. I think it might be within the stored procedure itself....

    In my stored procedure within DB2 I always have the list of values below. Within the sp this list is immediately below the params, but above the first BEGIN. I've just cut these items out for illustration. One curious thing is that I was able to allow rollbacks when using transactions by modifying the bold text below (changing from NONE to ALL), so I'm assuming it's gotta be something here, or something that I'm missing. Any thoughts what it might be?

    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    SPECIFIC DBName.SPROCName
    DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    SET OPTION ALWBLK = *ALLREAD ,
    ALWCPYDTA = *OPTIMIZE ,
    COMMIT = *ALL ,
    DECRESULT = (31, 31, 00) ,
    DFTRDBCOL = *NONE ,
    DYNDFTCOL = *NO ,
    DYNUSRPRF = *USER ,
    SRTSEQ = *HEX

    Thanks for your help!
    SkydiverMN

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When you call a stored procedure, there are no rows affected as part of the call statement. The actual updates are performed within specific SQL statements of the SP (each statement of a SQL SP is a separate SQL statement). In order to capture the number of rows affected by a specific insert/update/delete within the SP, you will have to capture that information with the GET DIAGNOSTICS SQL statement and return it to your calling program as one of the parms. If you have multiple insert/update/delete statements in your SP, you could have multiple return parms if you could have multiple insert/update/delete statements in your SP, or you could have logic in the SP to combine them into one number.

    CREATE PROCEDURE sqlprocg (IN deptnbr VARCHAR(3), OUT ROWS_AFFECTED INTEGER)
    LANGUAGE SQL
    BEGIN
    DECLARE SQLSTATE CHAR(5);
    DECLARE rcount INTEGER;
    UPDATE CORPDATA.PROJECT
    SET PRSTAFF = PRSTAFF + 1.5
    WHERE DEPTNO = deptnbr;
    GET DIAGNOSTICS rcount = ROW_COUNT;
    -- At this point, rcount contains the number of rows that were updated.
    ... You can return rcount to the calling program as a out parm
    SET ROWS_AFFECTED = rcount;
    END
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2009
    Posts
    8

    Question 'return' required?

    Thanks for the help. I've added the output parameter, but there's so much added automatically below the parameter declaration that I don't understand. Do I need to modify any of these too?

    Code:
            CREATE PROCEDURE DBName.ProcedureName ( 
    	IN IN_CLIENTID DECIMAL(8, 0) , 
    	IN IN_APPLICATIONID DECIMAL(8, 0) , 
    	IN IN_USERID DECIMAL(8, 0) , 
    	OUT ROWS_AFFECTED INTEGER ) 
    	DYNAMIC RESULT SETS 1 
    	LANGUAGE SQL 
    	SPECIFIC DBName.ProcedureName 
    	DETERMINISTIC 
    	MODIFIES SQL DATA 
    	CALLED ON NULL INPUT 
    	SET OPTION  ALWBLK = *ALLREAD , 
    	ALWCPYDTA = *OPTIMIZE , 
    	COMMIT = *ALL , 
    	DECRESULT = (31, 31, 00) , 
    	DFTRDBCOL = *NONE , 
    	DYNDFTCOL = *NO , 
    	DYNUSRPRF = *USER , 
    	SRTSEQ = *HEX   BEGIN 
    	   BEGIN 
    		DECLARE RCOUNT INTEGER ; 
    		DELETE FROM DB.TableName 
                              BLAH
                              BLAH
                              BLAH
                              BLAH;
      
    		GET DIAGNOSTICS RCOUNT = ROW_COUNT ; 
    		SET ROWS_AFFECTED = RCOUNT ; 
      
    		RETURN ROWS_AFFECTED ; 
    	   END ; 
    	END  ;

    I don't believe the multiple BEGIN/END is needed, but probably doesn't really make a difference either. In your example you didn't use RETURN. Is the output param just assumed to be set and then returned? Should I have the delete query inside the inner BEGIN, then my output variable declaration in the outer BEGIN? Does this make a difference?

    In my .NET code I use the following to add an output parrameter:

    Code:
            iDB2Parameter param = new iDB2Parameter("ROWS_AFFECTED",iDB2DbType.iDB2Integer);
            param.Direction= ParameterDirection.ReturnValue;
            param.Value = 0;
            cmd.Parameters.Add(param);
    I originally had OUTPUT for the parameter direction but returned -1, which isn't correct so I changed it to ReturnValue. Now that I'm testing my code I'm now getting an error with ReturnValue, telling me that the specified parameters are now incorrect. Any thoughts?

    Thanks!

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sorry, I don't know anything about SP in dot net. The example that I provided is a SQL SP (not C) that I copied straight out the DB2 manual with some minor modifications that I made to improve clarity.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •