Retrieve SQL messages from Joblog

15 pts.
Tags:
AS/400
CL Program
SQL
Hi, I'm on 6.1 and I have RUNSQL in a CL program. The statement is 'UPDATE ...' and run's okay but I need to get the SQL MSG SQL7957 with the number of rows updated or the SQL0100 (row not found). I can get this with RCVMSG but only in debug mode. Can anyone help me? Rui


Software/Hardware used:
IBM i5/OS V6R1
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • pdraebel
    Google "AS400 sql error handling". That should give you some articles to go on.
    7,545 pointsBadges:
    report
  • TheRealRaven
    Other than debug, I don't know of any way to generate the message you want out of the IBM-supplied RUNSQLSTM nor RUNSQL command. If you need to retrieve status or diagnostic info from SQL statements, then you need to create or use a SQL procedure.

    With a lot of programming, you could make it work in CL, but REXX can do it easily. An UPDATE example that shows getting a row count could be like this:
    /* This shows SQL comm area row-count... */
    
    execsql ,
       'UPDATE mylib/QCUSTCDT SET STATE = ''GA'' WHERE CUSNUM = 938485',
       'with NC'
    
         say 'SQLCODE =' SQLCODE 'SQLSTATE =' SQLSTATE
         say SQLERRD.3 'rows updated'
    
    exit
    You might run it in CL like this:
    STRREXPRC SRCMBR( myRexxMbr ) SRCFILE( mylib/QREXSRC )
    The REXX could be slightly simplified:
    /* This shows SQL comm area row-count... */
    
    execsql ,
       'UPDATE mylib/QCUSTCDT SET STATE = ''GA'' WHERE CUSNUM = 938485',
       'with NC'
    
         say 'SQLCODE =' SQLCODE 'SQLSTATE =' SQLSTATE
         say SQLERRD.3 'rows updated'
    
    exit
    And you'd run that proc like this:
    STRREXPRC SRCMBR( myRexxMbr ) SRCFILE( mylib/QREXSRC ) CMDENV( *EXECSQL )
    I made a copy of the QCUSTCDT file from library QIWS for those, but you can test it on any test file you want. You have your SQL UPDATE statement in a source member for RUNSQLSTM. It's almost as easy to put the statement into a REXX source member. If you wanted to, you could have the REXX proc read from your current source member into a variable and run it that way.
    34,485 pointsBadges:
    report
  • ToddN2000
    I agree with Raven. I know of no easy way to get those messages in CL alone. Use the REXX method he describes and you should be fine.
    131,645 pointsBadges:
    report
  • RuiFerr

    Thanks for your answers, but the problem is I don't have a source member with my SQL statement because the statement is variable that is prepared in my CL program and after I call RUNSQL command passing the statement in a variable. 

    The simple way is starting debug before the RUNSQL and End debug after:

    STRDBG UPDPROD(*YES)

    RUNSQL     SQL(&SQLSTRING) COMMIT(*NONE)   

    ENDDBG   

    Then loop with RCVMSG  to get the MSGID SQL7957 or SQL0100.

    Now I'm working to create one SQLRPGLE program with SQLsting passing from Input:  

    C/EXEC SQL                        
    C+ Execute Immediate :SqlString   
    C/END-EXEC                          

    Now, what is the best way to retrieve the result, is with SQLCA or GET DIAGNOSTICS ? 

    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: