PM19373: SQL Statement - Reset SQL option with 'into' after #sql introduc es errors in redefined SQL

APAR status

• Closed as program error.

Error description

• Environment: Windows
  
  Product name: IBM? Rational? Business Developer
  
  Program Version    :  7.5.1.5
  Description.
  The SQL Statement -? Reset SQL option introduces errors in redef
  ined SQL when an 'into' is included that follows the #sql statem
  ent. Normally when implementing such as an 'add with into' optio
  n the 'into' is inserted between the 'get' and the 'with sql' cl
  ause and the reset works correctly.
  
  However the VAGen to EGL migration tool includes the 'into' afte
  r the #sql clause, as per the example SQL statement below ie
  Function CS10F20-SEL-EMP()
    try
      get EMPLOYEE
        with #sql{
          select EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHO
  NENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COM
  M
          from EMPLOYEE T1
          WHERE EMPNO = :EMPNO
        }
        into EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO
  , HIREDATE, VAGen_JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, C
  OMM ;
      onException ERROR-HANDLER() ;
    end
  end // end CS10F20-SEL-EMP
  
  Steps to reproduce:
  a) Modify this statement ie:
  Function CS10F20-SEL-EMP()
    try
      get EMPLOYEE
        with #sql{
          select EMPNO, FIRSTNME
          from EMPLOYEE T1
          WHERE EMPNO = :EMPNO
        }
        into EMPNO, FIRSTNME;
      onException ERROR-HANDLER() ;
    end
  end // end CS10F20-SEL-EMP
  
  b) Then implement the reset the statement and the result will be
   incorrectly defined ie:
  Function CS10F20-SEL-EMP()
    try
      get EMPLOYEE
        with
       #sql{
        select
         EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO,
         HIREDA into EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.M
  IDINIT,
        EMPLOYEE.LASTNAME, EMPLOYEE.WORKDEPT, EMPLOYEE.PHONENO,
        EMPLOYEE.HIREDATE, EMPLOYEE.VAGen_JOB,
        EMPLOYEE.EDLEVEL, EMPLOYEE.SEX, EMPLOYEE.BIRTHDATE,
        EMPLOYEE.SALARY, EMPLOYEE.BONUS, EMPLOYEE.COMM, BIRTHDATE,
   SALARY, BONUS,
         COMM
        from EMPLOYEE T1
       }
        into EMPNO, FIRSTNME;
      onException ERROR-HANDLER() ;
    end
  end // end CS10F20-SEL-EMP
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  If a "get" statement includes both an into clause and a #sql
  clause, and the #sql clause comes first, performing SQL
  Statement -? Reset SQL on it will result in invalid syntax.
  

Problem conclusion

• The problem has been fixed.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  RATL BUS DEV

• Fixed component ID

  5724S5000

Applicable component levels

• R751 PSN

     UP