Re: SQL Stored procedure from within RPG

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

At first blush, you've set SQLSTATE - check for that in RPG - you can 
make up your own value, I believe.

On 6/5/2013 2:38 PM, Matt Olson wrote:
> All,
>
> I have a SQL stored procedure defined below.  When run, this procedure properly rolls back any data it was manipulation, which is good that's what I want.  However, to RPG it looks as though this procedure executed successfully.
>
> How do I indicate to RPG program that it in fact had issues and required a rollback so the RPG program can also abort properly?
>
> CREATE PROCEDURE CQRLIB.CRTSSASTATS ( )
>                  LANGUAGE SQL
>                  SPECIFIC CQRLIB.CRTSSASTATS
>                  NOT DETERMINISTIC
>                  MODIFIES SQL DATA
>                  CALLED ON NULL INPUT
>                  PROGRAM TYPE SUB
>                  CONCURRENT ACCESS RESOLUTION DEFAULT
>                  SET OPTION  ALWBLK = *ALLREAD ,
>                  ALWCPYDTA = *OPTIMIZE ,
>                  COMMIT = *NONE ,
>                  DECRESULT = (31, 31, 00) ,
>                  DFTRDBCOL = *NONE ,
>                  DYNDFTCOL = *NO ,
>                  DYNUSRPRF = *USER ,
>                  SRTSEQ = *HEX
> BEGIN
>
> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ROLLBACK;
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
>
> INSERT INTO.... VALUES....
>
> More INSERT INTO... VALUES
>
> SIGNAL SQLSTATE '38T00' SET MESSAGE_TEXT = 'Error test';
>
> END
>
>
> Thanks,
>
> Matt