RPG เรียกใช้ SQL cmd

RPG เรียกใช้ SQL cmd

น.ศ. ที่จบใหม่  จะใช้ SQL statement คล่อง  เช่น  ตรวจสอบ Data ด้วย SQL แล้วนำ SQL ดังกล่าวมาเขียนโปรแกรม
แต่วิธีนี้  ใช้กับ  RPG ทั่วไปไม่ได้  (ต้องติดต่อ ทีละ File)

 ในขณะที่ IBM ได้พัฒนาการทำงานใน SQL ทำงานได้เร็วกว่า การเขียนแบบปรกติ (ฟังจากสัมนาของ IBM)    

Q:  “IBM ไม่เปิดช่องให้เขียน RPG ร่วมกับ SQL ?” 

A: ทำได้ครับ

ต้องเขียนใน  QRPGLESRC   กำหนดชนิดของ Source เป็น SQLRPGLE

                           Work with Members Using PDM                 A39BP  

File  . . . . . .   QRPGLESRC             
   Library . . . .     QGPL                  Position to  . . . . .            

Type options, press Enter.                                                   
  2=Edit         3=Copy  4=Delete 5=Display       6=Print     7=Rename        
  8=Display description  9=Save  13=Change text  14=Compile  15=Create module..

 Opt  Member      Type        Text                                            
      MYPGM       SQLRPGLE    REPORT

              

โครงสร้างการเขียน (แบบย่อ)

• เตรียม SQL statement  (ถ้า Run จะได้  Data Set)Select ได้ DataSet ออกมา -> จัดการทีละ row เรียก Cursor  (update, insert, …)
• กำหนด ต้วแปร  ชนิด Cursor  (เช่น Cur0)  ให้เรียกใช้ SQL statement(ถ้า SQL statement ยาวไป แยกเขียนได้)
• Open  ตัวแปรชนิด Cursor (Cur0)
•  กำหนด  ถ้าไม่มีข้อมูล ให้ไปตำแหน่ง Tag ที่ Close
•  Loop   จนกว่า  End of File   (SQLCOD = 0)
• (Fetch) อ่านทีละ row เข้าตัวแปร
• นำตัวแปร  ไปใช้งาน เช่น สั่งพิมพ์
• Tag : Close
• Close ตัวแปรชนิด Cursor (Cur0)

ตย.โปรแกรม  อ่านข้อมูล แล้วสั่งพิมพ์

FQPRINT    O    F  132        PRINTER OFLIND(*INOF)              
D*                                                               
D strSQL          S          10000A   VARYING  INZ               
D*                                                               
D dsField0        Ds                                             
D  E4PRSC                 1      5                               
D  NAME                   6     15                               
C* ----------------------------------------------------------- **
C     *ENTRY        PLIST                                        
C                   PARM                    Plac              2  
C* ----------------------------------------------------------- **
 /FREE                                                           
   strSQL =                 ' SELECT E4PRSC,E4PABN FROM          
   strSQL = %TRIM(strSQL) + ' TPPR1D/PE040P                      
   strSQL = %TRIM(strSQL) + ' WHERE E4PLAC = '''+PLAC+'''         
   strSQL = %TRIM(strSQL) +   ' AND E4PRSC <= ''AZ  ''            
 /END-FREE                                                        
C* ----------------------------------------------------------- ** 
C/EXEC SQL                                                        
C+ PREPARE MAINSELECT FROM :strSQL                                
C/END-EXEC                                                        
C*                                                                
C/EXEC SQL                                                        
C+ DECLARE CUR0 CURSOR FOR MAINSELECT                               
C/END-EXEC                                                        
C*
C/EXEC SQL                                                        
C+ OPEN CUR0                                                        
C/END-EXEC                                                        
C*                                                                
C/EXEC SQL                                                        
C+ WHENEVER NOT FOUND GO TO CLOSE_C0                              
C/END-EXEC                                                        
C*                                                                
C                   z-add     0             CNT#              4 0 
C                   EXCEPT    HED010                              
C*                  -----------------------Loop Until no Data     
C                   Dow       SQLCOD = 0                          
C                   EXSR      FetchC0                             
**                            --If EOF goto CLOSR_C0              
C                   EVAL      CNT# = CNT#+1                       
C   OF              EXCEPT    HED010                              
C                   EXCEPT    DTL010                              
C                   ENDDO                                         
**                                                                
C     CLOSE_C0      TAG                                           
**    -----------------                                            
C/EXEC SQL                                                         
C+ CLOSE CUR0                                                        
C/END-EXEC                                                         
C*                  ---SQL:no data---                              
C                   IF        CNT# ='0'                            
C                   EXCEPT    ERR010                               
C                   ENDIF                                          
C*                                                                 
C                   SETON                                        LR
C   LR              RETURN                                         
**----------------------------------------------------------- **   
C     FetchC0       BEGSR                                          
C*-----------------------     
C*                  -Use DS for read Var (rename)                      
C/EXEC SQL                                                         
C+ FETCH NEXT FROM CUR0 INTO :dsField0                               
C/END-EXEC                                                         
C                   ENDSR                                         
** ----------------------------------------------------------- ** 
OQPRINT    E            HED010         1  2                       
O                       plac                 2                    
O                                           30 '*** TEST SQL ***' 
O          E            DTL010         1                          
O                       E4PRSC              10                    
O                       NAME                22                    
O          E            ERR010         1                 
O                                           10 '-NO DATA-'

ทดสอบโดย   

Call    MYPGM   (‘T2’)  ส่งค่าที่ทำให้มี Data กับ ไม่มี Data

อธิบาย Code

D strSQL          S          10000A   VARYING  INZ               
ประกาศตัวแปร  strSQL  เพื่อป้อน SQL Statement

D dsField0        Ds                                             
D  E4PRSC                 1      5                               
D  NAME                   6     15                               
กำหนด DS เพื่อรับค่า  Field จาก SQL

C     *ENTRY        PLIST                                        
C                   PARM                    Plac              2  
RPG รับค่าเข้ามา  (ทดสอบ แบบมี Data หรือ ไม่มี Data)

/FREE                                                           
   strSQL =                 ' SELECT E4PRSC,E4PABN FROM          
   strSQL = %TRIM(strSQL) + ' TPPR1D/PE040P                      
   strSQL = %TRIM(strSQL) + ' WHERE E4PLAC = '''+PLAC+'''         
   strSQL = %TRIM(strSQL) +   ' AND E4PRSC <= ''AZ  ''            
 /END-FREE                                                       
เขียนแบบ Free From    ''' จะได้ ' 2 ตัว 

C/EXEC SQL                                                        
C+ PREPARE MAINSELECT FROM :strSQL                                
C/END-EXEC                                                        
C*                                                                
C/EXEC SQL                                                        
C+ DECLARE C0 CURSOR FOR MAINSELECT                               
C/END-EXEC                                                         

ใช้ /EXEC SQL  … .END-EXEC   เพื่อเรียกใช้ SQL Statement  (1 block  ต่อ 1 คำสั่ง)
(เขียนแบบนี้รู้สึกว่า  เปลืองบรรทัดยังไง ไม่รู้)
กำหนด ต้วแปร  ชนิด Cursor  (เช่น Cur0)  ให้เรียกใช้ SQL statement

C/EXEC SQL                                                        
C+ OPEN C0                                                        
C/END-EXEC                                                        
Open  ตัวแปรชนิด Cursor

C/EXEC SQL                                                        
C+ WHENEVER NOT FOUND GO TO CLOSE_C0                              
C/END-EXEC                                                        
กำหนด  ถ้าไม่มีข้อมูล ให้ไปตำแหน่ง Tag ชื่อ Close_C0

C                   z-add     0             CNT#              4 0 
C                   EXCEPT    HED010                              
C*                  -----------------------Loop Until no Data     
C                   Dow       SQLCOD = 0                          
C                   EXSR      FetchC0                             
**                            --If EOF goto CLOSR_C0              
(Fetch) อ่านข้อมูล  ถ้าไม่มีข้อมูลจะ  กระโดดไปตำแหน่ง Tag ชื่อ Close_C0
แยก Fetch ออกเป็น  Sub-Routine (ทำให้ Code อ่านง่าย)

C                   EVAL      CNT# = CNT#+1                       
C   OF              EXCEPT    HED010                              
C                   EXCEPT    DTL010                              
C                   ENDDO                                         
**                                                                
C     CLOSE_C0      TAG                                           
**    -----------------                                            
Tag ที่รับเมื่อ  ไม่มีข้อมูล,ข้อมูลหมด

C/EXEC SQL                                                         
C+ CLOSE C0                                                        
C/END-EXEC                                                         
Close (ใช้คู่กับ Open)

C*                  ---SQL:no data---                              
C                   IF        CNT# ='0'                            
C                   EXCEPT    ERR010                               
C                   ENDIF                                          
C*                                                                 
C                   SETON                                        LR
C   LR              RETURN                                         
**----------------------------------------------------------- **   
C     FetchC0       BEGSR                                          
C*-----------------------     
C*                  -Use DS for read Var (rename)                      
C/EXEC SQL                                                         
C+ FETCH NEXT FROM C0 INTO :dsField0                               
C/END-EXEC                                                         
C                   ENDSR                                          

แยก Fetch ออกเป็น  Sub-Routine (ทำให้ Code อ่านง่าย) 

ดู Complier Listing

จะเห็นว่า  Compiler เพิ่ม  "ตัวแปร" ที่เกี่ยวข้องกับ SQL  (มีหลายตัว)
และเปลี่ยน /Exec SQL เป็นชุดคำสั่ง RPG  

ลอง Debug ดู

ใช้ StrDbg cmd น๊ะครับ (ใช้ StrISDB ไม่ได้)

ผู้อ่านที่เขียน  Style แบบนี้ 

KEY   SETLL  File

KEY   READE  FILE     80

      ---init before Loop---

*IN80 DOWEQ '0'

      ---process---

KEY   READE  FILE     80

      ENDDO                                                  

จะพบว่า  Code ข้างต้น  ถ้า Fetch เทียบเท่ากับ Read  ตำแหน่งการวางต้องมี 2 บรรทัด

ในความจริง    Fetch จะทำงาน เหมือน Try ... Catch  ทำงานร่วมกับ WHEN EVER (ด้านบน)

- อ่านแล้วพบว่า End Of File จะต้องกระโดดไปที่  CLOSE_C0

ดังนั้น เมื่อ End Of File จะไม่ทำคำสั่งนี้

C                   EVAL      CNT# = CNT#+1     

สรุป

สำหรับโปรแกรมง่ายๆ  Code แบบวิธี RPG ดั้งเดิม  จะเขียนได้สั้นกว่า

แต่ถ้า  ใช้งานทีละหลาย File (ที่มี Relation)   การเขียนแบบนี้จะช่วยได้มาก
(และ สามารถนำไปทดสอบกับ SQL Editor ภายนอกก่อนได้)

วิเคราะห์

ถ้ามันดี  แล้วทำไม  ผมถึงไม่ใช้งาน ?
(ผมใช้ SQL ตรวจข้อมูลได้เร็วกว่า เขียนโปรแกรม)

ในช่วง 20 ปี ของการใช้ RPG มีการสร้าง Tool หลายตัวเข้ามาช่วยทำงาน

Tool ดังกล่าว  ไม่สามารถใช้กับ  Code ที่เขียนร่วมกับ SQL ได้  เช่น

• Tool ที่ใช้ตรวจ  program ในระบบฯ มีใช้ File อะไรบ้าง ? (DSPPGMREF cmd)
  (บริษัทฯ มีใช้ Tool ตัวนี้)
• STRISDB  cmd 
• ปัญหาเรื่องความเคยชิน  ของโปรแกรมเมอร์ ที่ทำงานมานาน (ช้า เมื่อย้ายไปเขียน  วิธีใหม่)

Data Area

Data Area

Data Area ทำงานเหมือน File ที่มีเพียง 1 Record  1 Field   ทำให้การเรียกใช้จัดการทำได้ง่าย แต่ไม่สื่อความหมาย (ไม่รู้เลยว่า ข้างในแบ่งเป็น Field ย่อยอะไรบ้าง)

งานที่นิยมใช้ - ข้อมูลขนาดกระทัดรัด

• จัดเก็บ  User Name (หลัง LogOn ผ่านแล้ว) งานอื่นๆใน Job เดียวกัน (เหมือนตัวแปร Session ของ Web)
• จัดเก็บ  สถานะ  เช่น  Update ต้องผ่าน 4 ขั้นตอน เช่น  “ABCD”  ณ ตอนนี้ทำถึง  C แล้ว (ควบคุม work flow,approve)
• จัดเก็บ  Run No เช่น  “00152”   Invoice No เลขที่ล่าสุด คือ หมายเลข 00152
• งวด/รอบบัญชี  เช่น “2013/02”  วันนี้  วันที่  2013/03/01  แต่ปี/เดือนของบัญชี  ยังเป็น 2013/02 (ยังปิดบัญชีไม่เสร็จ)

มี 2 แบบ คือ  

1. Local Var ประจำ Job ใน AS/400  (คล้าย  ตัวแปร Session ของ Web) … ถูกสร้างอัตโนมัติ และหายไปเมื่อ
2. 1. 1. จัดเก็บลง Library  ... ต้องสร้างด้วยคำสั่ง CRTDTAARA

การจัดการด้วย CL Cmd

ตัวอย่าง  การสร้าง Data Area

CRTDTAARA DTAARA(QGPL/CTRLYM)    
         TYPE(*CHAR)        LEN(6)        TEXT('DATE CONTROL YYYY/MM')

CRTDTAARA DTAARA(QGPL/RUNNO)    
         TYPE(*DEC)         LEN(5 0)      TEXT('LAST INVOICE NO')

CRTDTAARA DTAARA(QGPL/UPDSTS)    
         TYPE(*CHAR)        LEN(10)       TEXT('UPDATE STATUS')
CRTDTAARA DTAARA(QGPL/BALQTY)    
         TYPE(*CHAR)        LEN(10)       TEXT('BALANCE QTY')

ตัวอย่าง  คำสั่งการเปลี่ยนค่าใน Data Area

CHGDTAARA DTAARA(QGPL/CTRLYM)
         VALUE(‘201302’)    

ตัวอย่าง  คำสั่งที่ใช้แสดงค่าใน Data Area

DSPDTAARA DTAARA(QGPL/CTRLYM)

ตัวอย่าง  คำสั่งการเปลี่ยนค่าใน Data Area  โดยระบุตำแหน่ง

ต้องการเปลี่ยนเฉพาะ ค่า "เดือน" อยู่ในตำแหน่งที่ 5 ไป 2 อักษร
                        Change Data Area (CHGDTAARA)
Type choices, press Enter.
Data area specification:
 Data area  . . . . . . . . . . > CTRLYM        Name, *LDA, *GDA, *PDA 

   Library  . . . . . . . . . . >   QGPL        Name, *LIBL, *CURLIB
 Substring specifications:
 Substring starting position  . > 5             1-2000, *ALL
 Substring length . . . . . . . > 2            1-2000
New value  . . . . . . . . . . . > '03'

แบบฝึกหัด
1. กำหนด   Data Area  เพื่อจัดเก็บ  ปีเดือน  และ  วันสุดท้ายของเดือนนั้น  
 เช่น  2013/09/30  (มี / คั่นด้วย)
2. สร้างใน Library ของตนเอง
3. บันทึก  และ   ดู   ด้วยคำสั่ง  ข้างต้น

การจัดการด้วย RPG

[สร้าง Data Area

จาก ตย ข้างต้น]

ประกาศ ตัวแปรและให้ดึงจาก Data Area (เป็นชนิดอักษร)

อ่าน   ค่า  ปี,เดือน  (โดย Lock เพื่อป้องกันคนอื่นเรียกใช้)

นำค่า มาแสดงผลบนหน้าจอ  (ให้แก้ไขบนหน้าจอ)

บันทึก  ค่า  ปี,เดือน  ใหม่ที่รับจากหน้าจอ (พร้อมทั้ง  ยกเลิก การ Lock)

C           *NAMVAR   DEFN            CTRLYM  6

C*C*--                  IN   CTRLYM                    NO PROTECT

C           *LOCK     IN   CTRLYM                    LOCKC*

C*C                     MOVELCTRLYM     W1CTYY   
C                     MOVE CTRLYM     W1CTMM
C*
C                     EXFMTDSP01W
C*
C                     OUT  CTRLYM
C                     UNLCKCTRLYM

แบบฝึกหัด (ต่อ)

1. สร้าง RPG ที่อ่าน Run No (Data Area) เรียก RPG ใน 2 job
2. เพิ่ม Code เพื่อป้องกัน (Lock) ต้องให้ Run No เสร็จก่อน อีก job จึงจะแสดงผลได้

การประกาศ ในแบบต่างๆ

ประกาศ แล้วใช้ชื่อ ตัวเแปรอื่น

เช่น Data Area = CTRLYM แต่ในโปรแกรมจะใช้ผ่าน W1YM เป็นต้น

C           *NAMVAR   DEFN CTRLYM     W1YM 

ประกาศ หลายค่าและดึงค่าพร้อมๆกัน ในครั้งเดียว

C           *NAMVAR   DEFN            CTRLYM  6

C

*NAMVAR   DEFN            RUNNO  4

C

*NAMVAR   DEFN            BALQTY 102

C*

C           *LOCK     IN   *NAMVAR

C*

C                OUT  *NAMVAR

Local Data Area 

มีลักษณะ  “คล้ายกัน” แต่จะใช้ชื่อ ตัวแปรดังนี้ "LDA" โดยมีขนาด 1,024 Tip Data Area ไม่มีโครงสร้าง   มักจะใช้ Data Structure (กำหนดใน I–Spec) ช่วยในการกำหนดโครงสร้างใน Data Area 

ICTRLYM      DS
I                                        1    8 CTLYMD 
I                                        1    4 CTLY 
I                                        5    6 CTLM
I                                        7    8 CTLD

ILDA        UDS  
I                                        1  15 EMPNO
I                                       16  45 EMPNM 

C           *NAMVAR   DEFN *LDA      LDA   
C*
C                     IN   LDA
C*

แบบฝึกหัด (ต่อ)

1. RPG-1 กำหนดให้ Local Data Area มีค่า "Say Hi"
2. ใน Job เดียวกัน RPG-2 ดึงค่า Local Data Area มาแสดงผล (ต้องมีค่าตามข้อ 1) เปิดใช้ต่าง Job เรียกใช้ RPG-2 (Data Area ต้องไม่มีค่า)