RPG程序使用动态SQL无法向Display File展示学生数据问题排查

我编写了SQLRPGLE程序，通过动态SQL实现以下功能：用户输入学生ID（STUSSN）后，从WUSTDP表查询学生的地址、电话、SSN、姓名、邮箱信息，展示到Display File的DISPSTUD界面，但界面始终没有数据显示。目前仅使用WUSTDP表，未涉及其他表。相关代码如下：

显示文件（Display File）代码 #

INDARA
     A                                      CA12(12 'CANCEL')
     A                                      CA03(03 'EXIT')
     A          R STUDSEL
     A                                  1  2'Lab 12'
     A                                  1 30'Wibaux University'
     A                                  2 23'Student Registration aplication'
     A                                  1 71DATE
     A                                  6 23'Student Number'
     A                                  9 23'Type value, then enter.'
     A                                 15  3'F3=Exit  F12=Cancel'
     A            STUDNT         9D  I  6 40
     A          R DISPSTUD
     A                                  1  2'Lab 12'
     A                                  1 30'Wibaux University'
     A                                  2 23'Student Registration aplication'
     A                                  1 71DATE
     A                                  3 10'Student ID:'
     A                                  4 10'Student Name:'
     A                                  5 10'Address:'
     A                                  6 10'Phone:'
     A                                  7 10'Email:'
     A                                 16  4'F3=Exit  F12=Cancel'
     A            STUDNAME      36   B  4 30
     A            ADDRESS       39   B  5 30
     A            PHONE     R        B  6 30REFFLD(STUDREC/PHONE FLIGHT2023/WUS-
     A                                      TDP)
     A            SEMAIL    R        B  7 30REFFLD(STUDREC/SEMAIL FLIGHT2023/WU-
     A                                      STDP)
     A                                  9 10'Section Number'
     A            SECTION   R        B  9 30REFFLD(SECREC/SECTION FLIGHT2023/WU-
     A                                      SCTP)
     A            STUSSN    R        B  3 30REFFLD(STUDREC/STUSSN FLIGHT2023/WU-
     A                                      STDP)

     A          R REGCOMPLT
     A                                  1  2'Lab 12'
     A                                  1 30'Wibaux University'
     A                                  2 23'Student Registration aplication'
     A                                  1 71DATE
     A                                  3 10'Student ID:'
     A                                  4 10'Student Name:'
     A                                  5 10'Address:'
     A                                  6 10'Phone:'
     A                                  7 10'Email:'
     A                                  9 10'Class Information'
     A                                 10 10'Dept:'
     A                                 10 20'Course:'
     A                                 10 35'Section:'
     A                                 10 52'Credits:'
     A                                 11 10'Meets:'

     A                                 16  4'F3=Exit  F12=Cancel'
     A            STUSSN    R        B  3 30REFFLD(STUDREC/STUSSN FLIGHT2023/WU-
     A                                      STDP)
     A            STUDNAME      36   B  4 30
     A            ADDRESS       39   B  5 30
     A            PHONE     R        B  6 30REFFLD(STUDREC/PHONE FLIGHT2023/WUS-
     A                                      TDP)
     A            SEMAIL    R        B  7 30REFFLD(STUDREC/SEMAIL FLIGHT2023/WU-
     A                                      STDP)
     A            SECTION   R        B  9 30REFFLD(SECREC/SECTION FLIGHT2023/WU-
     A                                      SCTP)
     A            DEPT      R        B 10 15REFFLD(CRSREC/DEPT FLIGHT2023/WUCRS-
     A                                      P)
     A            COURSE    R        B 10 28REFFLD(CRSREC/COURSE FLIGHT2023/WUC-
     A                                      RSP)
     A            CREDIT    R        B 10 60REFFLD(CRSREC/CREDIT FLIGHT2023/WUC-
     A                                      RSP)
     A            SECTION1  R        B 10 44REFFLD(SECREC/SECTION FLIGHT2023/WU-
     A                                      SCTP)
     A            SECDAY    R        B 11 17REFFLD(SECREC/SECDAY FLIGHT2023/WUS-
     A                                      CTP)
     A            SECTIM    R        B 11 21REFFLD(SECREC/SECTIM FLIGHT2023/WUS-
     A                                      CTP)
     A                                 13 10'Press enter to confirm enrolment'
     A          R REGMSG
     A                                  1  2'Lab 12'
     A                                  1 30'Wibaux University'
     A                                  2 23'Student Registration aplication'
     A                                  1 71DATE
     A                                 11 25'Registration completed'
     A                                 12 21'You have been added to the section-

     A                                      .'
     A                                 18 13'Press Enter to continue'
     A                                 20  1'F3=Exit   F12 = Cancel'
     A          R ERROR
     A                                  1 28'ERROR'
     A                                  4 14'oNE OR MORE THINGS WERE ENTERED IN-
     A                                      CORRECTLY'
     A                                 10  2'F3=EXIT   F12=CANCEL'
     A          R RECORD1

     A*%%RS+<record-sequences>
     A*%%RS+ <sequence name="Untitled">
     A*%%RS+  <device type="display" width="80" height="24" />
     A*%%RS+ </sequence>
     A*%%RS </record-sequences>                                                

SQLRPGLE程序代码 #

**FREE
//--------------------------------------------------------- WUSTDP, WUSCTP, WUCRSP, and WUENRLP
// AUTHOR: VICTOR HERNANDEZ
// DATE: 3/8/2024
// PROGRAM NAME: LAB12RPG
// PURPOSE: ALLOW A STUDENT TO INTERACT WITH A DISPLAY FILE IN ORDER TO SIGN UP FOR CLASSES
// --------------------------------------------------------FILES
//ctl-opt option(*nodebugio:*srcstmt) ;

DCL-F LAB12SCRN1 WORKSTN INDDS(INDICATORS);
DCL-F WUSTDP  USAGE(*OUTPUT:*INPUT);
DCL-F WUSCTP  USAGE(*OUTPUT:*INPUT);
DCL-F WUCRSP  USAGE(*OUTPUT);
DCL-F WUENRLP USAGE(*OUTPUT);
//---------------------------------------------------------VARIABLES
Dcl-ds Indicators len(99);
  Exit      Ind pos(3);    //Exit
  Cancel    Ind pos(12);   //Cancel
  FKey      Ind pos(25);   //vldcmdkey
  StudIDErr Ind pos(30);   //Student ID error
  SecNumErr Ind pos(35);   //Section number error
  Enter     Ind pos(40);   //Enter

END-DS;
//---------------------------------------------------------VARIABLES
dcl-s   SqlString char(100);
//---------------------------------------------------------DATA STRUCTURES
dcl-ds StudInfo;
  xStussn   Zoned(9:0);
  xStudname char(36);
  xAddress  char(39);
  xPhone    zoned(10:0);
  xSEmail   char(30);
END-DS;

dcl-ds ClassInfo;
  XDept    char(3);     //WUCRSP
  XCourse  zoned (3:0); //WUCRSP & WUSTDP
  XSection zoned(5:0);  //WUSCTP & WUENRLP
  XSecday  char(3);     //WUSCTP
  xsectim  zoned(4:0);  // WUSCTP
  XCredit  zoned(1:0);  //WUCRSP
END-DS;
//---------------------------------------------------------Main


  Exfmt Studsel;

  SqlString = 'SELECT STUSSN, (SFNAME CONCAT " " CONCAT SLNAME),  '+\n    '(STREET CONCAT " " CONCAT CITY CONCAT " " CONCAT STATE),     '+\n    'PHONE, SEMAIL                                                '+\n    'FROM WUSTDP                                                  '+\n    'WHERE STUSSN = ?';

  clear StudInfo;

  EXEC SQL PREPARE CUR FROM :SqlString;
  //exec sql EXECUTE CUR USING :StudInfo;

  EXEC SQL DECLARE STUDCUR CURSOR FOR CUR;

  EXEC SQL OPEN STUDCUR using :Studnt;

  EXEC SQL FETCH STUDCUR INTO :StudInfo;

  STUSSN = XSTUSSN;
  STUDNAME = XSTUDNAME;
  ADDRESS = XADDRESS;
  PHONE = XPHONE;
  SEMAIL = XSEMAIL;

  Exfmt Dispstud;

Exec SQL close studcur;
*Inlr = *On;
Return;                                                                           

排查建议 #

• 参数类型匹配检查：确认Studnt变量（输入的学生ID）的类型、长度与WUSTDP表中STUSSN字段完全一致，类型不匹配会导致查询无结果。
• 字段拼接长度验证：检查姓名和地址拼接后的总长度是否超过xStudname（36位）、xAddress（39位）的定义，过长会导致数据截断或赋值失败。
• 添加SQL错误处理：在每个SQL操作后检查SQLCODE或SQLSTATE，判断是否执行失败，示例：

  EXEC SQL FETCH STUDCUR INTO :StudInfo;
  IF SQLCODE <> 0;
    Exfmt ERROR;
    RETURN;
  ENDIF;
  

• 简化查询逻辑：单条记录查询无需使用游标，直接用EXECUTE INTO更高效，避免游标操作问题：

  EXEC SQL PREPARE STMT FROM :SqlString;
  EXEC SQL EXECUTE STMT INTO :StudInfo USING :Studnt;
  

• 显示字段一致性检查：确认DISPSTUD记录中的字段名、类型、长度与程序中赋值的变量完全匹配，避免因字段不对应导致数据无法显示。

内容的提问来源于stack exchange，提问作者Victor Hernandez