ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Sqlrpgle

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sqlrpgle

    I am trying to retrieve the execption error message of a SQL statement used in the SQLRPGLE program, and write into a LOG file with this error message. For this I am using System Data strucure as follows:

    D SDS
    D sdProgram *proc
    D sdMessageId 40 46A
    D sdMessageText 91 170A
    C*
    C/EXEC SQL
    C+ SELECT ABALPH INTO :ALPH
    C+ FROM FILE01 WHERE
    C+ ABAT1 = 'B'
    C/END-EXEC
    C SQLCOD IFNE 0
    C EVAL ERRMSG = sdMessageText
    C ENDIF
    C*
    and checking the SQLCOD <> 0. I expected that the error message will be stored in the variable "sdMessageText" when SQLCOD <> 0. But this variable contains blanks even if the SQL statement fails.

    Is there any other way to fetch the SQL error message using SQLRPGLE program?

    Thanks in advance.

  • #2
    Re: Sqlrpgle

    when you compile the program you get the following Sql data structure;

    PHP Code:
     D*      SQL Communications area                                   
     D SQLCA           DS                                              
     D  SQLAID                 1      8A   INZ
    (X'0000000000000000')    
     
    D  SQLABC                 9     12B 0                             
     D  SQLCOD                13     16B 0                             
     D  SQLERL                17     18B 0                             
     D  SQLERM                19     88A                               
     D  SQLERP                89     96A                               
     D  SQLERRD               97    120B 0 DIM
    (6)                      
     
    D  SQLERR                97    120A                               
     D   SQLER1               97    100B 0                             
     D   SQLER2              101    104B 0                             
     D   SQLER3              105    108B 0                             
     D   SQLER4              109    112B 0                             
     D   SQLER5              113    116B 0                             
     D   SQLER6              117    120B 0                             
     D  SQLWRN               121    131A                               
    D   SQLWN0              121    121A 
    D   SQLWN1              122    122A 
    D   SQLWN2              123    123A 
    D   SQLWN3              124    124A 
    D   SQLWN4              125    125A 
    D   SQLWN5              126    126A 
    D   SQLWN6              127    127A 
    D   SQLWN7              128    128A 
    D   SQLWN8              129    129A 
    D   SQLWN9              130    130A 
    D   SQLWNA              131    131A 
    D  SQLSTT               132    136A 
    You can use a combination of these to get what you want.

    hth
    Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

    Comment


    • #3
      Re: Sqlrpgle

      Hi,

      if you are on release V5R3 or higher, you can use the SQL Command GET DIAGNOSTICS to retrieve the message text:

      Example:
      PHP Code:
      D MyResult        S              3P 0   
      D MyText          S             50A     
       
      *-----------------------------------------------------------------------
      c/EXEC SQL                                               
      C
      +    Select MyFld into :MyResult from MyTable                                   
      c
      /END-EXEC                                               
                                                               
      C                   
      If        SQLCOD < *Zeros            
      c
      /EXEC SQL   Get Diagnostics Condition 1 :MyText Message_Text 
      C
      /END-EXEC                                               
      C     MyText        Dsply                                
      C                   
      EndIf 
      If you are not yet on release V5R3M0, you can determine the message text from the following information:
      1. all SQL-Messages are stored in the message file QSQLMSG
      2. the message-id is determined as SQL + absolute value of the SQL code, i.e. -204 --> SQL0204
      3. the variable message textes are returned in SQLERM
      4. to get a message from a message file you either can use an API or the CL command RTVMSG.

      Birgitta

      Comment


      • #4
        Re: Sqlrpgle

        Thanks a lot.

        Comment

        Working...
        X