Not sure if this belongs here or in priorities. I am interested in getting
into an architectural discussion about the possibility of implementing this
feature so I decided to include it here.

I think it would be VERY useful to have a trigger/stored procedure
language enhancement such that you could do something like this:

CREATE PROCEDURE APROC( KEY INTEGER, VAL VARCHAR( 10 ))
AS
DECLARE VARIABLE ROWS_AFFECTED INTEGER;
BEGIN

UPDATE ATBL
SET ACOL = :VAL
WHERE AKEY = :KEY
AFFECTED :ROWS_AFFECTED;

IF ( ROWS_AFFECTED = 0 ) THEN
INSERT INTO ATBL ( AKEY, ACOL ) VALUES ( :KEY, :VAL );

END

From my understanding of BLR it would be very trivial to conjure up a way to
pull this off. Is all the Affected return value needs to represent is how
many rows were walked through during the operation. That is simple a counter
in the rse loop.

The only hard part that I wonder about is how the stored procedure / trigger
language could be enhanced to allow for parsing and storing the results and
plugging them into the variables. I still don't have a clear picture of how
BLR maps to the parsed SQL yet.

It would also require a more dynamic (or granular) storage mechanism
associated to the statement handle of the stored procedure. The BLR block
would have to have additional storage variables I suppose and then those
would just be used in the return values.

Is this suggestion totally off the wall or is there some merit to it? Mostly
what I am feeling for is, on an architectural level, is this a straight
forward enhancement or would there be serious problems and deficiencies in
the current implementation of things to introduce this?

I have no real fondness to the token AFFECTED. It could be anything as long
as it is fairly intuitive to its purpose.

Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com