ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

EXECSQL in Rexx

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

  • EXECSQL in Rexx

    Can someone familiar with Rexx tell me where I am going wrong here with the prepare statement???

    SIGNAL ON ERROR
    ERRLOC = 'UPDATE_ERROR'
    UPDATE_STMT = 'UPDATE library/file ',
    'SET NAME = a name ',
    'SET EMAIL = an email address '
    ADDRESS EXECSQL,
    'PREPARE S1 FROM :UPDATE_STMT'
    ADDRESS EXECSQL,
    'EXECUTE S1'


    /* Commit changes */
    ADDRESS EXECSQL,
    'COMMIT'
    ERRLOC = 'REPORT_ERROR'

  • #2
    Can't help but found this example https://www.ibm.com/support/knowledg...zajpssrexx.htm . It doesn't have the ADDRESS before the EXECSQL statements. By the way, you don't have a WHERE clause, you are going to update every record in the table.

    Comment


    • #3
      It's been a very long time since I've looked at REXX but, these are some things I've noticed.

      You have a SIGNAL ON ERROR but no corresponding ERROR label for the error to go to. You should add to your code (at the end would be nice):
      Code:
      ERROR:
      SIGNAL OFF ERROR
      Some REXX code to handle the error, e.g. SAY 'SQLCODE returned is:' SQLCODE
      I'm not sure what the ERRLOC variable is for as it's not used.

      Your update may not work as the text isn't quoted, I would think it should be more like:
      Code:
      UPDATE_STMT = 'UPDATE library/file SET NAME = ''a name'' SET EMAIL = ''an email address '''
      When you call the REXX procedure, you should specify CMDENV(*EXECSQL). That will eliminate the need for using the ADDRESS EXECSQL statement. Incidentally, if you had the ADDRESS statement on its own line, only 1 should be necessary until you change it back again. I also think it should be ADDRESS '*EXECSQL' though when I tried it, REXX never liked it so not sure what the SQL engines REXX address is.

      So, this may work (untested):
      Code:
      SIGNAL ON ERROR
      
      UPDATE_STMT = 'UPDATE library/file SET NAME = ''a name'' SET EMAIL = ''an email address '''
      
      EXECSQL 'PREPARE S1 FROM :UPDATE_STMT'
      EXECSQL 'EXECUTE S1'
      
      /* Commit changes */
      EXECSQL 'COMMIT'
      
      ERROR:
      SIGNAL OFF ERROR
      SAY 'SQLCODE returned is:' SQLCODE
      And call it by:
      STRREXPRC SRCMBR(member name) SRCFILE(lib/rexx source file) CMDENV(*EXECSQL)
      Last edited by john.sev99; August 27, 2017, 05:34 PM.

      Comment

      Working...
      X