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

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

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?


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:

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!

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.