IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 25 replies
  • Latest Post - ‏2017-01-11T21:28:03Z by Ashlyn
Damery
Damery
84 Posts

Pinned topic SQLRPGLE exception handling

‏2013-11-21T13:21:53Z | error exception sql sqlrpgle

Is there a Best Practice for handling SQL errors/exceptions?

Is there a way to catch those in a monitor group?

Or is the only way within the SQL status code manual handling?

 

It appears that right after running your SQL statement you need to check the SQL Status code and then create a manual error message and pass that back up the process flow till you reach a program to handle it.

Is this about it?

  • scott_klement
    scott_klement
    306 Posts
    ACCEPTED ANSWER

    Re: SQLRPGLE exception handling

    ‏2013-11-22T05:57:44Z  

    Embedded SQL sets the SQLSTATE and SQLCODE variables with the status of each statement.  It does not send exceptions to your program, so if you do not explicitly check for an error, the program will continue running after an error occurs.

    The code to check for an error depends somewhat on the statement you ran and what your program plans to do with it, but typically it looks something like this:

    if %subst(sqlstt:1:2)<>'00' and %subst(sqlstt:1:2)<>'01';
       // an error occurred.
    endif;
    

    The SQLSTATE variable (SQLSTT for short) contains a 5A value.  The first two characters identify the type of status, and the last 3 characters identify a specific error code.  An SQLSTATE value that begins with 00 means "success", and 01 means "successful, but with a warning".   So if the statement finished with these, it has run successfully.  Anything else indicates a failure of some sort.

    One easy way to deal with this is to write a subprocedure that accepts the SQLSTATE as a parameter.  I used to do this at my previous employer, unfortunately, I no longer have access to the code.  If I recall correctly, it looked like this (this is off the top of my head, and may be wrong... but should give you the idea, at least.)

         H NOMAIN
     
        *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
          * CheckSQL(): Check for an SQL error
          *
          *    peSqlStt = (input) SQL State to check
          *       peMsg = (input) Message to log when error occurs.
          *
          * Returns *ON if Error or End-of File
          *
          * Sends warning messages as *DIAG to job log,
          *  sends error messages as *ESCAPE to caller.
          *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
         P CheckSQL        B                   export
         D                 PI             1n
         D   peSqlStt                     5a   const
         D   peMsg                      256a   varying const
    
         D ErrorCode       DS
         D  BytesProv                    10I 0 inz(0)
         D  BytesAvail                   10I 0 inz(0)
    
         D QMHSNDPM        PR                  ExtPgm('QMHSNDPM')
         D   MessageID                    7A   Const
         D   QualMsgF                    20A   Const
         D   MsgData                    256A   Const
         D   MsgDtaLen                   10I 0 Const
         D   MsgType                     10A   Const
         D   CallStkEnt                  10A   Const
         D   CallStkCnt                  10I 0 Const
         D   MessageKey                   4A
         D   ErrCode                           likeds(ErrorCode)
    
         D MsgType         s             10a   inz(*blanks)
         D MsgKey          s              4A
         D error           s              1n   inz(*on)
          /free
             select;
             when %subst(peSqlStt:1:2) = '00';
                error = *off;
             when %subst(peSqlStt:1:2) = '02';
                error = *on;
             when %subst(peSqlStt:1:2) = '01';
                MsgType = '*DIAG';
                error = *off;
             other;
                MsgType = '*ESCAPE';
                error = *on;
             endsl;
    
             if MsgType <> *blanks;
                QMHSNDPM( 'CPF9897'
                        : 'QCPFMSG   *LIBL'
                        : peMsg
                        : %len(%trimr(peMsg))
                        : MsgType
                        : '*'
                        : 1
                        : MsgKey
                        : ErrorCode );
             endif;
    
             return error;
          /end-free
         P                 E
    

    The idea is that you can call this after any SQL statement.  If the statement failed, it will send an *ESCAPE message, which means your program will "blow up" unless you're monitoring for it.  So it's an easy way to make sure the program stops if something goes wrong.  For example, following any old SQL statement, you can just call this.

    
            exec SQL insert into MYTABLE values(X, Y, Z);
              CheckSQL(SQLSTT: 'Insert into MyTable');
    
    

    The CheckSQL routine will make your program "blow up" (unless monitored), and so if I'm writing code that I'm sure will 'never' fail, I do it like above.  Then if something does go wrong, the program will crash and someone will file an error report to me.  The job log will contain useful information, including the SQL error (which SQL puts in there for you) and the text 'Insert into my table' which I can use to figure out where things failed in my code.

    It also returns *OFF when there's no error, or *ON if the end of file was reached.  So I can use it easily in a loop when fetching...

    
            exec SQL declare C1 cursor for
                  select * from THEFILE;
              exec SQL open C1;
              CheckSQL(SQLSTT: 'Open Cursor 1');
          
              exec SQL fetch next from C1 into :TheDS;
              dow CheckSQL(SQLSTT: 'Fetch from Cursor 1') = *OFF;
          
                   // do logic here
                   exec SQL fetch next from C1 into :TheDS;      
              enddo;
    

    This CheckSQL routine can easily be put into a service program so you can call it from everywhere...  that way, you can use the same code for handling errors in just about all of your programs.  (Unless they expect errors sometimes, in which case this routine wouldn't be appropriate... but in my experience that's pretty rare.)

    Updated on 2013-11-22T06:00:20Z at 2013-11-22T06:00:20Z by scott_klement
  • B.Hauser
    B.Hauser
    128 Posts
    ACCEPTED ANSWER

    Re: SQLRPGLE exception handling

    ‏2013-11-22T06:07:49Z  

    I've a comparable routine.

    Instead of sending your own error message, you may receive the error message that was originally sent with an SET DIAGNOSTICS statement:

    /Free
    
        Exec SQL Get Diagnostics Condition 1 :ErrText = MESSAGE_TEXT;
    

    Birgitta

     

     

  • ZachJ
    ZachJ
    10 Posts
    ACCEPTED ANSWER

    Re: SQLRPGLE exception handling

    ‏2013-11-27T04:01:26Z  
    • Damery
    • ‏2013-11-26T20:43:58Z

    Just ran a test and make sure you capture your SqlStt before calling Get Diagnostics because that call will change it. I created a Original SqlStt field to hold the value.

    Example:

         D OrigSqlStt      s                   Like(SqlStt)

              Exec Sql Insert into MYTABLE VALUES('XYZ');//throws an error
              OrigSqlStt=SqlStt;
              Exec Sql Get Diagnostics Condition 1 :ErrText = MESSAGE_TEXT;
              CheckSql(OrigSqlStt:ErrText);
     

    I was actually thinking of doing it this way where the CheckSQL procedure actually gets the state itself.

     

     

              Exec Sql
                Insert into MYTABLE VALUES('XYZ');//throws an error
              CheckSql();

     

         P CheckSQL...        
         P                 B                   Export
         
         D                 pi              n  
     
         D state           s              5a
         D messageText     s           1024a   Varying
         D error           s               n
         
          /Free
          
           Exec SQL
             Get Diagnostics Condition 1
             :state = RETURNED_SQLSTATE;
             
           Select;
             // Success
             When (%Subst(state:1:2) = '00');
               error = *Off;
               
             // Warnings
             When (%Subst(state:1:2) = '01');
             
               Exec SQL
                 Get Diagnostics
                 Condition 1 :messageText = MESSAGE_TEXT; 
                 
                 // Send diag message
     
             // No Records Found
             When (%Subst(state:1:2) = '02');
               error = *On; 
               
             // Errors
             Other;
               error = *On;
               Exec SQL
                 Get Diagnostics
                 Condition 1 :messageText = MESSAGE_TEXT; 

               // Send Escape Message

                 
           EndSl;
           

           Return error;

          /End-Free
         P                 E    
     
    Updated on 2013-11-27T04:02:20Z at 2013-11-27T04:02:20Z by ZachJ
  • scott_klement
    scott_klement
    306 Posts

    Re: SQLRPGLE exception handling

    ‏2013-11-22T05:57:44Z  

    Embedded SQL sets the SQLSTATE and SQLCODE variables with the status of each statement.  It does not send exceptions to your program, so if you do not explicitly check for an error, the program will continue running after an error occurs.

    The code to check for an error depends somewhat on the statement you ran and what your program plans to do with it, but typically it looks something like this:

    if %subst(sqlstt:1:2)<>'00' and %subst(sqlstt:1:2)<>'01';
       // an error occurred.
    endif;
    

    The SQLSTATE variable (SQLSTT for short) contains a 5A value.  The first two characters identify the type of status, and the last 3 characters identify a specific error code.  An SQLSTATE value that begins with 00 means "success", and 01 means "successful, but with a warning".   So if the statement finished with these, it has run successfully.  Anything else indicates a failure of some sort.

    One easy way to deal with this is to write a subprocedure that accepts the SQLSTATE as a parameter.  I used to do this at my previous employer, unfortunately, I no longer have access to the code.  If I recall correctly, it looked like this (this is off the top of my head, and may be wrong... but should give you the idea, at least.)

         H NOMAIN
     
        *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
          * CheckSQL(): Check for an SQL error
          *
          *    peSqlStt = (input) SQL State to check
          *       peMsg = (input) Message to log when error occurs.
          *
          * Returns *ON if Error or End-of File
          *
          * Sends warning messages as *DIAG to job log,
          *  sends error messages as *ESCAPE to caller.
          *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
         P CheckSQL        B                   export
         D                 PI             1n
         D   peSqlStt                     5a   const
         D   peMsg                      256a   varying const
    
         D ErrorCode       DS
         D  BytesProv                    10I 0 inz(0)
         D  BytesAvail                   10I 0 inz(0)
    
         D QMHSNDPM        PR                  ExtPgm('QMHSNDPM')
         D   MessageID                    7A   Const
         D   QualMsgF                    20A   Const
         D   MsgData                    256A   Const
         D   MsgDtaLen                   10I 0 Const
         D   MsgType                     10A   Const
         D   CallStkEnt                  10A   Const
         D   CallStkCnt                  10I 0 Const
         D   MessageKey                   4A
         D   ErrCode                           likeds(ErrorCode)
    
         D MsgType         s             10a   inz(*blanks)
         D MsgKey          s              4A
         D error           s              1n   inz(*on)
          /free
             select;
             when %subst(peSqlStt:1:2) = '00';
                error = *off;
             when %subst(peSqlStt:1:2) = '02';
                error = *on;
             when %subst(peSqlStt:1:2) = '01';
                MsgType = '*DIAG';
                error = *off;
             other;
                MsgType = '*ESCAPE';
                error = *on;
             endsl;
    
             if MsgType <> *blanks;
                QMHSNDPM( 'CPF9897'
                        : 'QCPFMSG   *LIBL'
                        : peMsg
                        : %len(%trimr(peMsg))
                        : MsgType
                        : '*'
                        : 1
                        : MsgKey
                        : ErrorCode );
             endif;
    
             return error;
          /end-free
         P                 E
    

    The idea is that you can call this after any SQL statement.  If the statement failed, it will send an *ESCAPE message, which means your program will "blow up" unless you're monitoring for it.  So it's an easy way to make sure the program stops if something goes wrong.  For example, following any old SQL statement, you can just call this.

    
            exec SQL insert into MYTABLE values(X, Y, Z);
              CheckSQL(SQLSTT: 'Insert into MyTable');
    
    

    The CheckSQL routine will make your program "blow up" (unless monitored), and so if I'm writing code that I'm sure will 'never' fail, I do it like above.  Then if something does go wrong, the program will crash and someone will file an error report to me.  The job log will contain useful information, including the SQL error (which SQL puts in there for you) and the text 'Insert into my table' which I can use to figure out where things failed in my code.

    It also returns *OFF when there's no error, or *ON if the end of file was reached.  So I can use it easily in a loop when fetching...

    
            exec SQL declare C1 cursor for
                  select * from THEFILE;
              exec SQL open C1;
              CheckSQL(SQLSTT: 'Open Cursor 1');
          
              exec SQL fetch next from C1 into :TheDS;
              dow CheckSQL(SQLSTT: 'Fetch from Cursor 1') = *OFF;
          
                   // do logic here
                   exec SQL fetch next from C1 into :TheDS;      
              enddo;
    

    This CheckSQL routine can easily be put into a service program so you can call it from everywhere...  that way, you can use the same code for handling errors in just about all of your programs.  (Unless they expect errors sometimes, in which case this routine wouldn't be appropriate... but in my experience that's pretty rare.)

    Updated on 2013-11-22T06:00:20Z at 2013-11-22T06:00:20Z by scott_klement
  • B.Hauser
    B.Hauser
    128 Posts

    Re: SQLRPGLE exception handling

    ‏2013-11-22T06:07:49Z  

    I've a comparable routine.

    Instead of sending your own error message, you may receive the error message that was originally sent with an SET DIAGNOSTICS statement:

    /Free
    
        Exec SQL Get Diagnostics Condition 1 :ErrText = MESSAGE_TEXT;
    

    Birgitta

     

     

  • Damery
    Damery
    84 Posts

    Re: SQLRPGLE exception handling

    ‏2013-11-22T18:53:53Z  

    Embedded SQL sets the SQLSTATE and SQLCODE variables with the status of each statement.  It does not send exceptions to your program, so if you do not explicitly check for an error, the program will continue running after an error occurs.

    The code to check for an error depends somewhat on the statement you ran and what your program plans to do with it, but typically it looks something like this:

    <pre dir="ltr">if %subst(sqlstt:1:2)<>'00' and %subst(sqlstt:1:2)<>'01'; // an error occurred. endif; </pre>

    The SQLSTATE variable (SQLSTT for short) contains a 5A value.  The first two characters identify the type of status, and the last 3 characters identify a specific error code.  An SQLSTATE value that begins with 00 means "success", and 01 means "successful, but with a warning".   So if the statement finished with these, it has run successfully.  Anything else indicates a failure of some sort.

    One easy way to deal with this is to write a subprocedure that accepts the SQLSTATE as a parameter.  I used to do this at my previous employer, unfortunately, I no longer have access to the code.  If I recall correctly, it looked like this (this is off the top of my head, and may be wrong... but should give you the idea, at least.)

    <pre dir="ltr"> H NOMAIN *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * CheckSQL(): Check for an SQL error * * peSqlStt = (input) SQL State to check * peMsg = (input) Message to log when error occurs. * * Returns *ON if Error or End-of File * * Sends warning messages as *DIAG to job log, * sends error messages as *ESCAPE to caller. *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P CheckSQL B export D PI 1n D peSqlStt 5a const D peMsg 256a varying const D ErrorCode DS D BytesProv 10I 0 inz(0) D BytesAvail 10I 0 inz(0) D QMHSNDPM PR ExtPgm('QMHSNDPM') D MessageID 7A Const D QualMsgF 20A Const D MsgData 256A Const D MsgDtaLen 10I 0 Const D MsgType 10A Const D CallStkEnt 10A Const D CallStkCnt 10I 0 Const D MessageKey 4A D ErrCode likeds(ErrorCode) D MsgType s 10a inz(*blanks) D MsgKey s 4A D error s 1n inz(*on) /free select; when %subst(peSqlStt:1:2) = '00'; error = *off; when %subst(peSqlStt:1:2) = '02'; error = *on; when %subst(peSqlStt:1:2) = '01'; MsgType = '*DIAG'; error = *off; other; MsgType = '*ESCAPE'; error = *on; endsl; if MsgType <> *blanks; QMHSNDPM( 'CPF9897' : 'QCPFMSG *LIBL' : peMsg : %len(%trimr(peMsg)) : MsgType : '*' : 1 : MsgKey : ErrorCode ); endif; return error; /end-free P E </pre>

    The idea is that you can call this after any SQL statement.  If the statement failed, it will send an *ESCAPE message, which means your program will "blow up" unless you're monitoring for it.  So it's an easy way to make sure the program stops if something goes wrong.  For example, following any old SQL statement, you can just call this.

    <pre dir="ltr"> exec SQL insert into MYTABLE values(X, Y, Z); CheckSQL(SQLSTT: 'Insert into MyTable'); </pre>

    The CheckSQL routine will make your program "blow up" (unless monitored), and so if I'm writing code that I'm sure will 'never' fail, I do it like above.  Then if something does go wrong, the program will crash and someone will file an error report to me.  The job log will contain useful information, including the SQL error (which SQL puts in there for you) and the text 'Insert into my table' which I can use to figure out where things failed in my code.

    It also returns *OFF when there's no error, or *ON if the end of file was reached.  So I can use it easily in a loop when fetching...

    <pre dir="ltr"> exec SQL declare C1 cursor for select * from THEFILE; exec SQL open C1; CheckSQL(SQLSTT: 'Open Cursor 1'); exec SQL fetch next from C1 into :TheDS; dow CheckSQL(SQLSTT: 'Fetch from Cursor 1') = *OFF; // do logic here exec SQL fetch next from C1 into :TheDS; enddo; </pre>

    This CheckSQL routine can easily be put into a service program so you can call it from everywhere...  that way, you can use the same code for handling errors in just about all of your programs.  (Unless they expect errors sometimes, in which case this routine wouldn't be appropriate... but in my experience that's pretty rare.)

    Perfect, This is pretty much what I imagined, you have brought it to life. The biggest help is the ability to turn it into an *Escape message so it can be handled in the monitor group.

    Thank you.

     

    This would be a great update chapter in the redp4321redbook on RPGLE exception and error handling...with so much SQL coding going on.

  • Damery
    Damery
    84 Posts

    Re: SQLRPGLE exception handling

    ‏2013-11-22T18:55:54Z  
    • B.Hauser
    • ‏2013-11-22T06:07:49Z

    I've a comparable routine.

    Instead of sending your own error message, you may receive the error message that was originally sent with an SET DIAGNOSTICS statement:

    <pre dir="ltr">/Free Exec SQL Get Diagnostics Condition 1 :ErrText = MESSAGE_TEXT; </pre>

    Birgitta

     

     

    This is the perfect compliment to Scotts post. I was thinking of building something using the SQL MSGF but this does it for you...more perfection.

    Thank you!

  • ZachJ
    ZachJ
    10 Posts

    Re: SQLRPGLE exception handling

    ‏2013-11-25T17:05:35Z  
    • B.Hauser
    • ‏2013-11-22T06:07:49Z

    I've a comparable routine.

    Instead of sending your own error message, you may receive the error message that was originally sent with an SET DIAGNOSTICS statement:

    <pre dir="ltr">/Free Exec SQL Get Diagnostics Condition 1 :ErrText = MESSAGE_TEXT; </pre>

    Birgitta

     

     

    We do something similar to what Scott does but I love the idea of using Get Diagnostics.  It looks like by using that, we would not even have to pass the sqlState variable to the error checking procedure.  We could just use the Get Diagnostics to get the state from the previous statement.  I am playing around with this and I have a question.  When a warning happens, such as sqlState 01504 "The UPDATE or DELETE statement does not contain a where clause.", the MESSAGE_TEXT actually does not contain information about the warning.  It instead has kind of a "completion" type message such as  "5 rows updated in TABLE in LIBRARY."  If I sent a diagnostic message for this it doesn't really give any indication that there was a warning.  Is there a way to get message text about the warning using Get Diagnostics?

  • Damery
    Damery
    84 Posts

    Re: SQLRPGLE exception handling

    ‏2013-11-26T20:43:58Z  
    • ZachJ
    • ‏2013-11-25T17:05:35Z

    We do something similar to what Scott does but I love the idea of using Get Diagnostics.  It looks like by using that, we would not even have to pass the sqlState variable to the error checking procedure.  We could just use the Get Diagnostics to get the state from the previous statement.  I am playing around with this and I have a question.  When a warning happens, such as sqlState 01504 "The UPDATE or DELETE statement does not contain a where clause.", the MESSAGE_TEXT actually does not contain information about the warning.  It instead has kind of a "completion" type message such as  "5 rows updated in TABLE in LIBRARY."  If I sent a diagnostic message for this it doesn't really give any indication that there was a warning.  Is there a way to get message text about the warning using Get Diagnostics?

    Just ran a test and make sure you capture your SqlStt before calling Get Diagnostics because that call will change it. I created a Original SqlStt field to hold the value.

    Example:

         D OrigSqlStt      s                   Like(SqlStt)

              Exec Sql Insert into MYTABLE VALUES('XYZ');//throws an error
              OrigSqlStt=SqlStt;
              Exec Sql Get Diagnostics Condition 1 :ErrText = MESSAGE_TEXT;
              CheckSql(OrigSqlStt:ErrText);
     
  • ZachJ
    ZachJ
    10 Posts

    Re: SQLRPGLE exception handling

    ‏2013-11-27T04:01:26Z  
    • Damery
    • ‏2013-11-26T20:43:58Z

    Just ran a test and make sure you capture your SqlStt before calling Get Diagnostics because that call will change it. I created a Original SqlStt field to hold the value.

    Example:

         D OrigSqlStt      s                   Like(SqlStt)

              Exec Sql Insert into MYTABLE VALUES('XYZ');//throws an error
              OrigSqlStt=SqlStt;
              Exec Sql Get Diagnostics Condition 1 :ErrText = MESSAGE_TEXT;
              CheckSql(OrigSqlStt:ErrText);
     

    I was actually thinking of doing it this way where the CheckSQL procedure actually gets the state itself.

     

     

              Exec Sql
                Insert into MYTABLE VALUES('XYZ');//throws an error
              CheckSql();

     

         P CheckSQL...        
         P                 B                   Export
         
         D                 pi              n  
     
         D state           s              5a
         D messageText     s           1024a   Varying
         D error           s               n
         
          /Free
          
           Exec SQL
             Get Diagnostics Condition 1
             :state = RETURNED_SQLSTATE;
             
           Select;
             // Success
             When (%Subst(state:1:2) = '00');
               error = *Off;
               
             // Warnings
             When (%Subst(state:1:2) = '01');
             
               Exec SQL
                 Get Diagnostics
                 Condition 1 :messageText = MESSAGE_TEXT; 
                 
                 // Send diag message
     
             // No Records Found
             When (%Subst(state:1:2) = '02');
               error = *On; 
               
             // Errors
             Other;
               error = *On;
               Exec SQL
                 Get Diagnostics
                 Condition 1 :messageText = MESSAGE_TEXT; 

               // Send Escape Message

                 
           EndSl;
           

           Return error;

          /End-Free
         P                 E    
     
    Updated on 2013-11-27T04:02:20Z at 2013-11-27T04:02:20Z by ZachJ
  • scott_klement
    scott_klement
    306 Posts

    Re: SQLRPGLE exception handling

    ‏2013-12-01T22:29:48Z  
    • ZachJ
    • ‏2013-11-27T04:01:26Z

    I was actually thinking of doing it this way where the CheckSQL procedure actually gets the state itself.

     

     

              Exec Sql
                Insert into MYTABLE VALUES('XYZ');//throws an error
              CheckSql();

     

         P CheckSQL...        
         P                 B                   Export
         
         D                 pi              n  
     
         D state           s              5a
         D messageText     s           1024a   Varying
         D error           s               n
         
          /Free
          
           Exec SQL
             Get Diagnostics Condition 1
             :state = RETURNED_SQLSTATE;
             
           Select;
             // Success
             When (%Subst(state:1:2) = '00');
               error = *Off;
               
             // Warnings
             When (%Subst(state:1:2) = '01');
             
               Exec SQL
                 Get Diagnostics
                 Condition 1 :messageText = MESSAGE_TEXT; 
                 
                 // Send diag message
     
             // No Records Found
             When (%Subst(state:1:2) = '02');
               error = *On; 
               
             // Errors
             Other;
               error = *On;
               Exec SQL
                 Get Diagnostics
                 Condition 1 :messageText = MESSAGE_TEXT; 

               // Send Escape Message

                 
           EndSl;
           

           Return error;

          /End-Free
         P                 E    
     

    The problem with getting the SQL state inside the procedure is that the procedure has to be in the same module with the SQL statements.  Since you (presumably?) will need to code SQL in nearly every application, that's going to mean many (hundreds? thousands?) of copies of the CheckSQL routine.

    If you pass the SQL state as a parameter, you need only write it once, and call it from anywhere you like.

  • ZachJ
    ZachJ
    10 Posts

    Re: SQLRPGLE exception handling

    ‏2013-12-02T14:16:31Z  

    The problem with getting the SQL state inside the procedure is that the procedure has to be in the same module with the SQL statements.  Since you (presumably?) will need to code SQL in nearly every application, that's going to mean many (hundreds? thousands?) of copies of the CheckSQL routine.

    If you pass the SQL state as a parameter, you need only write it once, and call it from anywhere you like.

    I don't  think that is entirely accurate.  The testing I have done uses a separate module/service program that contains the CheckSQL() procedure.  The documentation for the Get Diagnostics statement says it will get "information about the previous SQL statement that was executed."  It doesn't specifically state what the scope is but it must be the job and not the module.

  • scott_klement
    scott_klement
    306 Posts

    Re: SQLRPGLE exception handling

    ‏2013-12-03T02:46:07Z  
    • ZachJ
    • ‏2013-12-02T14:16:31Z

    I don't  think that is entirely accurate.  The testing I have done uses a separate module/service program that contains the CheckSQL() procedure.  The documentation for the Get Diagnostics statement says it will get "information about the previous SQL statement that was executed."  It doesn't specifically state what the scope is but it must be the job and not the module.

    Ah, sorry.   I didn't notice the use of 'GET DIAGNOSTICS', I thought you were just using the SQLSTT that's populated automatically by the precompiler.   My apologies...   I never even thought of calling GET DIAGNOSTICS

  • Damery
    Damery
    84 Posts

    Re: SQLRPGLE exception handling

    ‏2014-01-07T13:47:41Z  

    Ah, sorry.   I didn't notice the use of 'GET DIAGNOSTICS', I thought you were just using the SQLSTT that's populated automatically by the precompiler.   My apologies...   I never even thought of calling GET DIAGNOSTICS

    I have found an issue when using this GET DIAGNOSTICS routine within a service. You must use *CALLER for the activation group when creating the service program or you lose the scope of the SQLCA information used in the GET DIAGNOSTICS.

    I tested with *Caller then put it in production which is using QSRV for some reason and suddenly all my test errors came back with no errors.

    This is just an FYI!

  • SarahKemp
    SarahKemp
    3 Posts

    Re: SQLRPGLE exception handling

    ‏2014-05-13T19:01:58Z  
    • Damery
    • ‏2014-01-07T13:47:41Z

    I have found an issue when using this GET DIAGNOSTICS routine within a service. You must use *CALLER for the activation group when creating the service program or you lose the scope of the SQLCA information used in the GET DIAGNOSTICS.

    I tested with *Caller then put it in production which is using QSRV for some reason and suddenly all my test errors came back with no errors.

    This is just an FYI!

    Another proviso when using GET DIAGNOSTICS in a service program: if the call to GET DIAGNOSTICS is the first use of the service program (non-persistent connection) - it will return SQLSTATE 00000 even after an error. I have not had a chance to research thoroughly yet, but I assume this is because the service program was not instantiated at the time that the error occurred (since it wasn't called until after the statement is run). An example:

    /free
      exec sql INSERT INTO TABLE VALUES(:RECORD_FMT);
      if checkSQL() = *ON;
        //handle errors
      endif;
    /end-free

    Where checkSQL() uses GET DIAGNOSTICS. This code will correctly return information about errors encountered by the previous query in all cases except when this is the first call to checkSQL() and the service program containing checkSQL() has not been running previous to the call. 

    If my assumptions here stand out as incorrect to anyone, I would really appreciate a comment back - finding information about details like this has been very difficult. 

    Updated on 2014-05-13T19:06:33Z at 2014-05-13T19:06:33Z by SarahKemp
  • Damery
    Damery
    84 Posts

    Re: SQLRPGLE exception handling

    ‏2014-05-14T03:06:36Z  
    • SarahKemp
    • ‏2014-05-13T19:01:58Z

    Another proviso when using GET DIAGNOSTICS in a service program: if the call to GET DIAGNOSTICS is the first use of the service program (non-persistent connection) - it will return SQLSTATE 00000 even after an error. I have not had a chance to research thoroughly yet, but I assume this is because the service program was not instantiated at the time that the error occurred (since it wasn't called until after the statement is run). An example:

    /free
      exec sql INSERT INTO TABLE VALUES(:RECORD_FMT);
      if checkSQL() = *ON;
        //handle errors
      endif;
    /end-free

    Where checkSQL() uses GET DIAGNOSTICS. This code will correctly return information about errors encountered by the previous query in all cases except when this is the first call to checkSQL() and the service program containing checkSQL() has not been running previous to the call. 

    If my assumptions here stand out as incorrect to anyone, I would really appreciate a comment back - finding information about details like this has been very difficult. 

    Hello SarahKemp,

    I have not had any issues. You are saying the first use of the Exported procedure checkSQL() does not work? Maybe you have SQL in your procedure before you GET DIAGNOSTICS?

  • SarahKemp
    SarahKemp
    3 Posts

    Re: SQLRPGLE exception handling

    ‏2014-05-14T15:09:11Z  
    • Damery
    • ‏2014-05-14T03:06:36Z

    Hello SarahKemp,

    I have not had any issues. You are saying the first use of the Exported procedure checkSQL() does not work? Maybe you have SQL in your procedure before you GET DIAGNOSTICS?

    Thanks for your reply Damery, I explained the issue I'm seeing more thoroughly in this question: http://stackoverflow.com/questions/23643045/initialize-activate-sql-prior-to-get-diagnostics

    T
    he entire procedure (up to the GET DIAGNOSTICS statement) is:

    P checkSQL      B                 EXPORT
    D checkSQL      PI              N
    D  sqlSt                       5A CONST OPTIONS(*NOPASS)
    D  sqlMsg                    256A CONST VARYING OPTIONS(*NOPASS)
     *

    D state         S              5A
    D msgText       S           1024A VARYING
    D msgType       S             10A INZ(*BLANKS)
    D msgKey        S              4A
    D error         S              1N INZ(*ON)
     /free
      if %parms() > 1;
        state = sqlSt;
        msgText = sqlMsg;
      else;
        exec sql
          GET DIAGNOSTICS CONDITION 1
           :msgText = MESSAGE_TEXT,
           :state = RETURNED_STATE;
      endif;
      ...

    So no SQL is in the procedure prior to the GET DIAGNOSTICS. As I explain in the question I referenced, it seems like SQL needs a separate activation on top of the usual service program activation. If I run an initSQL() function prior to any SQL statements the GET DIAGNOSTICS will return correctly. It is only when GET DIAGNOSTICS is the first statement run in this newly activated service program that it returns false success. If I do not end the job between calls it works. 

  • Damery
    Damery
    84 Posts

    Re: SQLRPGLE exception handling

    ‏2014-05-14T17:57:14Z  
    • SarahKemp
    • ‏2014-05-14T15:09:11Z

    Thanks for your reply Damery, I explained the issue I'm seeing more thoroughly in this question: http://stackoverflow.com/questions/23643045/initialize-activate-sql-prior-to-get-diagnostics

    T
    he entire procedure (up to the GET DIAGNOSTICS statement) is:

    P checkSQL      B                 EXPORT
    D checkSQL      PI              N
    D  sqlSt                       5A CONST OPTIONS(*NOPASS)
    D  sqlMsg                    256A CONST VARYING OPTIONS(*NOPASS)
     *

    D state         S              5A
    D msgText       S           1024A VARYING
    D msgType       S             10A INZ(*BLANKS)
    D msgKey        S              4A
    D error         S              1N INZ(*ON)
     /free
      if %parms() > 1;
        state = sqlSt;
        msgText = sqlMsg;
      else;
        exec sql
          GET DIAGNOSTICS CONDITION 1
           :msgText = MESSAGE_TEXT,
           :state = RETURNED_STATE;
      endif;
      ...

    So no SQL is in the procedure prior to the GET DIAGNOSTICS. As I explain in the question I referenced, it seems like SQL needs a separate activation on top of the usual service program activation. If I run an initSQL() function prior to any SQL statements the GET DIAGNOSTICS will return correctly. It is only when GET DIAGNOSTICS is the first statement run in this newly activated service program that it returns false success. If I do not end the job between calls it works. 

    That looks like it should work, the only difference I see with what mine does is that I get the diagnostics info first then I check the parms coming in?

          /free                                                                                         
             Exec SQL GET DIAGNOSTICS EXCEPTION 1                                                       
                          : Diagnostics.State = RETURNED_SQLSTATE,                                      
                          : Diagnostics.errorText = MESSAGE_TEXT;
             If %Parms >= %ParmNum(Info);                                                               
  • SarahKemp
    SarahKemp
    3 Posts

    Re: SQLRPGLE exception handling

    ‏2014-05-14T18:43:07Z  
    • Damery
    • ‏2014-05-14T17:57:14Z

    That looks like it should work, the only difference I see with what mine does is that I get the diagnostics info first then I check the parms coming in?

          /free                                                                                         
             Exec SQL GET DIAGNOSTICS EXCEPTION 1                                                       
                          : Diagnostics.State = RETURNED_SQLSTATE,                                      
                          : Diagnostics.errorText = MESSAGE_TEXT;
             If %Parms >= %ParmNum(Info);                                                               

    The midrange mailing list has suggested it may be a bug. I'm looking at what PTFs are available to me, would you mind sharing your release / PTF level? We are on V6R1M0 with our latest PTF at TL63058. Unfortunately we can't move past 6.1 but if you have this working on that release it would be very good news...

  • Damery
    Damery
    84 Posts

    Re: SQLRPGLE exception handling

    ‏2014-05-14T20:37:14Z  
    • SarahKemp
    • ‏2014-05-14T18:43:07Z

    The midrange mailing list has suggested it may be a bug. I'm looking at what PTFs are available to me, would you mind sharing your release / PTF level? We are on V6R1M0 with our latest PTF at TL63058. Unfortunately we can't move past 6.1 but if you have this working on that release it would be very good news...

    We are V7R1M0 I dont have authority to the latest PTF level.

  • dlstrawn
    dlstrawn
    39 Posts

    Re: SQLRPGLE exception handling

    ‏2017-01-04T01:29:44Z  
    • B.Hauser
    • ‏2013-11-22T06:07:49Z

    I've a comparable routine.

    Instead of sending your own error message, you may receive the error message that was originally sent with an SET DIAGNOSTICS statement:

    <pre dir="ltr">/Free Exec SQL Get Diagnostics Condition 1 :ErrText = MESSAGE_TEXT; </pre>

    Birgitta

     

     

    Birgitta,

    I am using the statement as you suggested, but here is my problem:  I have a file with 4 check constraints.  On an insert into this file I need to know specifics about what went wrong so that I can give the user an understandable error message so they can correct their error (they entered the values for the insert on a screen)...  The best I can get from Message_Text is the name of the constraint, but nothing about the allowed values for the column in the table (i.e. viewable must be 'Y' or " ')...  Without doing some fairly complex processing, how do I get more specifics about the constraint violated using get diagnostics (which I love the idea of using)?

  • BuckCalabro
    BuckCalabro
    9 Posts

    Re: SQLRPGLE exception handling

    ‏2017-01-04T19:18:55Z  
    • dlstrawn
    • ‏2017-01-04T01:29:44Z

    Birgitta,

    I am using the statement as you suggested, but here is my problem:  I have a file with 4 check constraints.  On an insert into this file I need to know specifics about what went wrong so that I can give the user an understandable error message so they can correct their error (they entered the values for the insert on a screen)...  The best I can get from Message_Text is the name of the constraint, but nothing about the allowed values for the column in the table (i.e. viewable must be 'Y' or " ')...  Without doing some fairly complex processing, how do I get more specifics about the constraint violated using get diagnostics (which I love the idea of using)?

    I have a file with 4 check constraints.  On an insert into this file I need to know specifics about what went wrong so that I can give the user an understandable error message

    One thought is to place a message ID in the constraint name itself.  Like ORD2201.  Then ADDMSGD that message ID, plus first and second-level text into a message file that your application will use to SNDMSG back to the user.  If your application uses message subfiles, this is idea already familiar to the end user.

    With respect to telling the end user what values are acceptable, I think that is better handled via a drop-down than an entry field.  The problem is that there is no way a static text message could keep up with the dynamic contents of the foreign key table - the source of the constraint violation.  A different approach to this might be to enable the F4 (or some other) key to allow the user to prompt.  The subsequent window would select the list of valid values from the foreign key table and show them to the end user.  Position the cursor and Enter to pick the one that she wants...

  • dlstrawn
    dlstrawn
    39 Posts

    Re: SQLRPGLE exception handling

    ‏2017-01-05T01:33:57Z  

    I have a file with 4 check constraints.  On an insert into this file I need to know specifics about what went wrong so that I can give the user an understandable error message

    One thought is to place a message ID in the constraint name itself.  Like ORD2201.  Then ADDMSGD that message ID, plus first and second-level text into a message file that your application will use to SNDMSG back to the user.  If your application uses message subfiles, this is idea already familiar to the end user.

    With respect to telling the end user what values are acceptable, I think that is better handled via a drop-down than an entry field.  The problem is that there is no way a static text message could keep up with the dynamic contents of the foreign key table - the source of the constraint violation.  A different approach to this might be to enable the F4 (or some other) key to allow the user to prompt.  The subsequent window would select the list of valid values from the foreign key table and show them to the end user.  Position the cursor and Enter to pick the one that she wants...

    thanks for the advice.  I like the idea of using a message ID to retrieve a message description, I do that already...  a drop down box sounds reasonable, but it is overkill for this situation since there are only 2 possible values, but it would certainly be the way to go if there were a larger list of possible values.  Thanks again!

  • B.Hauser
    B.Hauser
    128 Posts

    Re: SQLRPGLE exception handling

    ‏2017-01-05T07:58:51Z  
    • dlstrawn
    • ‏2017-01-04T01:29:44Z

    Birgitta,

    I am using the statement as you suggested, but here is my problem:  I have a file with 4 check constraints.  On an insert into this file I need to know specifics about what went wrong so that I can give the user an understandable error message so they can correct their error (they entered the values for the insert on a screen)...  The best I can get from Message_Text is the name of the constraint, but nothing about the allowed values for the column in the table (i.e. viewable must be 'Y' or " ')...  Without doing some fairly complex processing, how do I get more specifics about the constraint violated using get diagnostics (which I love the idea of using)?

    I assume Get Diagnostics will not return more than the generic error message and may be the constraint name and liblrary.

    But if you know the constraint name and library, you can get the check clause from the SYSCHKCST catalog view:

    
    Select Check_Clause, ...
    From QSYS2.SYSCHKCST
    Where Constraint_Schema = 'YOURSCHEMA' and Constraint_Name = 'YOURCONSTRAINT'
    

     

    In either way I'd not wait in an interactive session until inserting or updating a row. Instead I'd add an addtional check routine where I can return a specific message that is performed before update. Or already restrict the input before (DDS for example with VALUES) in an Web-Applicathin with a combox with the allowed values.

    Birgitta
     

  • dlstrawn
    dlstrawn
    39 Posts

    Re: SQLRPGLE exception handling

    ‏2017-01-05T23:24:28Z  
    • B.Hauser
    • ‏2017-01-05T07:58:51Z

    I assume Get Diagnostics will not return more than the generic error message and may be the constraint name and liblrary.

    But if you know the constraint name and library, you can get the check clause from the SYSCHKCST catalog view:

    <pre dir="ltr"> Select Check_Clause, ... From QSYS2.SYSCHKCST Where Constraint_Schema = 'YOURSCHEMA' and Constraint_Name = 'YOURCONSTRAINT' </pre>

     

    In either way I'd not wait in an interactive session until inserting or updating a row. Instead I'd add an addtional check routine where I can return a specific message that is performed before update. Or already restrict the input before (DDS for example with VALUES) in an Web-Applicathin with a combox with the allowed values.

    Birgitta
     

    Great thoughts, Birgitta...  the problem with using SYSCHKCST is that it is going to be a techno/user unfriendly response...  I wish there was a field in the database where you can store a user friendly response to a check constraint violation...  (we can always wish, right)  In terms of the interactive session handling the issue before updating:  I am inserting, so while you could check before hand in the RPG, it seems that method is a violation of the data centric ideal, but it certainly could be done...  In my opinion, if my app were a web app, the drop down would be a good idea, but still requires validation outside of the database...  But, for a green screen (and the accompanying "green screen" user), drop downs (f4 for prompt, then cursoring down to selection, or forcing use of mouse - remember "green screen" user!) are a little more cumbersome...  so far ideas are great, though!  I know your thoughts on using SYSCHKCST are going to come in handy, and naming the constraint with a message ID will also be helpful...  If I am missing something in what I said above, don't hessitate to set me straight!

  • BuckCalabro
    BuckCalabro
    9 Posts

    Re: SQLRPGLE exception handling

    ‏2017-01-06T16:16:03Z  

    That's an interesting idea:

    Create a new table.

    Use the constraint name as the primary key.

    Put your user friendly help text in another column in that table.

    When an RI violation occurs, fetch the help text from the friendly help table and show that to the end user.

     

    Don't forget Birgitta's very good advice on where to check for RI violations.  Instead of just letting DB2 throw an error when an RI violation occurs, check the situation in your program before it actually gets that far.  Then your program can guide the user in addressing the situation before it actually throws an error.  For example, let's say that the end user is entering a new customer, and there's an RI rule that says the credit rating must exist in the credit rating table.  But this is a special customer; a giant customer which gets their own unique credit rating.  If we just let the transaction happen, the INSERT will fall over because of the missing credit rating.  Then the user will have to do something to park the customer entry program, start the 'maintain credit rating' program - to which she may not be authorised!, enter the new credit rating, and then come back to working on entering the new customer info.

     

    If, on the other hand, the program intercepts this situation before trying the INSERT, the code can tell her to call Joe in Accounting and have him update the credit rating table.  Or, the program could actually tell her what the situation is, and call the proper program and walk her through the process before returning her back to where she left off in the customer entry process.

     

    It seems like double work, but there are distinct benefits to the functionality.

  • Damery
    Damery
    84 Posts

    Re: SQLRPGLE exception handling

    ‏2017-01-07T14:58:07Z  

    That's an interesting idea:

    Create a new table.

    Use the constraint name as the primary key.

    Put your user friendly help text in another column in that table.

    When an RI violation occurs, fetch the help text from the friendly help table and show that to the end user.

     

    Don't forget Birgitta's very good advice on where to check for RI violations.  Instead of just letting DB2 throw an error when an RI violation occurs, check the situation in your program before it actually gets that far.  Then your program can guide the user in addressing the situation before it actually throws an error.  For example, let's say that the end user is entering a new customer, and there's an RI rule that says the credit rating must exist in the credit rating table.  But this is a special customer; a giant customer which gets their own unique credit rating.  If we just let the transaction happen, the INSERT will fall over because of the missing credit rating.  Then the user will have to do something to park the customer entry program, start the 'maintain credit rating' program - to which she may not be authorised!, enter the new credit rating, and then come back to working on entering the new customer info.

     

    If, on the other hand, the program intercepts this situation before trying the INSERT, the code can tell her to call Joe in Accounting and have him update the credit rating table.  Or, the program could actually tell her what the situation is, and call the proper program and walk her through the process before returning her back to where she left off in the customer entry process.

     

    It seems like double work, but there are distinct benefits to the functionality.

    I have an error handling service program with a procedure that will create a hard error automatically and then sends the error information up to a service that detects if it is interactive and can popup a generic error screen or if batch then it can email / sndmsg / create a report etc. all controlled by a table with who gets the information. This procedure allows you to override it with Unique information that gets appended to the error and it also allows you to handle the error manually if you so choose to do it at a particular failure check.

    This way you can create a dynamic error message at the failure spot with dynamic incoming information before creating your hard error and handling your error.