Re: Can I QMHRCVPM messages generated by embedded SQL

["Steve Richter" <stephenrichter@xxxxxxxxx>]

qmhrcvpm ( or rcvmsg ) can only work with active invocation entries.
Once your sql procedure is returned to, the QSQROUTE program that the
SQL0805 message was sent to has ended. The only way to receive that
message at that point is to use the api to read from the joblog.  Does
SQL send any messages to the sql procedure? QMHRCVPM should be able to
receive those messages.

-Steve

On 7/16/07, Sarvapriya_Tripathi@xxxxxxxxx <Sarvapriya_Tripathi@xxxxxxxxx> wrote:
> All,
>
> I am trying to generate a meaningful error message in case of errors thrown
> by embedded SQL. To that end, I wanted to create a proc like this:
>
>     p GetSQLErrMsg    b
>     d GetSQLErrMsg    pi            10i 0
>     d  SqlState                      5a   Const
>     d  SqlCode                      10i 0 Const
>     d  ErrorMsg                     60a
>
>     d ReturnCd        s             10i 0
>
>     d RCVM0100        DS                  qualified
>     d   BytesRtn                    10I 0
>     d   BytesAvail                  10I 0
>     d   MsgSev                      10I 0
>     d   MsgID                        7A
>     d   MsgType                      2A
>     d   MsgKey                       4A
>     d                                7A
>     d   CCSID_status                10I 0
>     d   CCSID                       10I 0
>     d   MsgDtaLen                   10I 0
>     d   MsgDtaAvail                 10I 0
>     d   MsgDta                    8000A
>
>     d MsgKey          s              4A   Inz(*ALLx'00')
>     d I               s                   Like(tInteger) Inz(5)
>     d J               s                   Like(tInteger)
> Inz(%size(ErrorMsg))
>     d wwMsg           s                   Like(ErrorMsg)
>
>     d                 ds
>     d InMsgID                        7a
>     d SQLText                        3a   Overlay(InMsgID) Inz('SQL')
>     d MsgNbr                         4  0 Overlay(InMsgID:4)
>
>      /free
>
>       ReturnCd = OK;
>       MsgNbr = %abs(SqlCode);
>       wwMsg = 'SQL Failed. SQLSTATE = ' + SQLSTT + ' SQLCOD = '
> +%char(SQLCOD);
>       if (SqlCode < 0);
>         monitor;
>           // check the last 5 messages
>           dou ((RCVM0100.MsgID = InMsgID) or (I = 0));
>             QMHRCVPM( RCVM0100: %size(RCVM0100): 'RCVM0100': '*': 2:
> '*PRV':
>                       MsgKey: 0: '*SAME': ApiError);
>             I -= 1;
>             MsgKey = RCVM0100.MsgKey;
>           enddo;
>           //* retrieve message data so that it can be printed on the
> screen
>           if (I > 0);
>             if (RCVM0100.MsgDtaLen < J);
>               J = RCVM0100.MsgDtaLen;
>             endif;
>             wwMsg = %subst(RCVM0100.MsgDta: 1: J);
>           else;
>             wwMsg = 'SQL Failed. SQLSTATE = ' + SQLSTT + ' SQLCOD = ' +
>                     %char(SQLCOD);
>           endif;
>         on-error;
>           Returncd = GeneralError;
>         endmon;
>       endif;
>
>       ErrorMsg = wwMsg;
>       return ReturnCd;
>
>      /end-free
>     p GetSQLErrMsg    e
>
> But the message that I want to capture, SQL0805 (SQL package TN050P in
> TTN001N not found at DRDA Server), seems elusive. This message, and some
> others, are sent by QSQROUTE as follows:
>
>                            Display Message Details
>  Message ID . . . . . . :   SQL0805       Severity . . . . . . . :   30
>  Date sent  . . . . . . :   07/16/07      Time sent  . . . . . . :
> 16:55:48
>  Message type . . . . . :   Diagnostic
>  From . . . . . . . . . :   FISISXT       CCSID  . . . . . . . . :   65535
>  From program . . . . . . . . . :   QSQROUTE
>   From library . . . . . . . . :     QSYS
>   From module  . . . . . . . . :     QSQROUTE
>   From procedure . . . . . . . :     NORMEXIT
>   From statement . . . . . . . :     14286
>  To program . . . . . . . . . . :   QSQROUTE
>   To library . . . . . . . . . :     QSYS
>   To module  . . . . . . . . . :     QSQROUTE
>   To procedure . . . . . . . . :     NORMEXIT
>   To statement . . . . . . . . :     14286
>
> More...
>  Press Enter to continue.
>  F1=Help   F3=Exit   F12=Cancel
>
> This is my call stack at the time I try to capture this message:
>
>                               Display Call Stack
>                                                             System:
> N05537A
>  Job:   QPADEV0021     User:   FISISXT        Number:   580459
>  Thread:   0000012F
>        Program
>  Rqs    or
>  Lvl    Procedure   Library     Statement        Instruction
>        ...
>        ...
>   4    QUOCMD      QSYS                            0399
>      < PEP_TN050P  TTN001N
>        TN050P      TTN001N     0000040600
>        DSPSFL      TTN001N     0000049500
>        RTVFORMDTL  TTN001N     0000078400
>      < TVFORMDIST  TTN001N     0000086700
>      < TSQLERRMSG  TTN001N     0000093000