Help with embedded SQL in RPG. Dang thing hates the delete statement?

385 pts.
Tags:
RPG ILE
SQL
 H OPTION(*NODEBUGIO:*SRCSTMT)                             *******************************************************  FWRLDSHP   IF   E             DISK                       FWRLDSHP1  O  A E           K DISK                       D*******************************************************  D*  Working Variables                                    D*******************************************************  D                SDS                                     D PGMNAME                 1     10                       D*                                                       D myDS            ds                   occurs(60)        DXORDNU                          7a    varying           DXHIPDT                          7a                      DXSHPIN                         30a                      D*                                                       D i               s             20i 0 inz                D*                                                       D*INVDETL        E DS                  EXTNAME(WRLDSHP1)  C*                                                       C/EXEC SQL                                               C+ Set Option                                            C+     Commit    = *NONE,                                C+     CloSqlCsr = *ENDMOD                             

C/END-EXEC                                       C*                                               C                   EXSR      AAAOPEN            C*                                               C                   EXSR      GETDETAIL          C*                                               C                   EXSR      SQLCLOSE           C*                                               C                   MOVE      *ON           *INLR C*                                               C/EJECT                                          C     AAAOPEN       BEGSR                        C*                                               C*    * Execute SQL prepare and open statement   C*                                               C/EXEC SQL                                       C+  DECLARE A CURSOR FOR                         C+  SELECT *                                     C+  FROM MBMTEST01/WRLDSHP                       C/END-EXEC                                       C*                                               C/EXEC SQL                                       C+   OPEN A                                     

C/END-EXEC                                                             C*                                                                     C                   ENDSR                                              C*                                                                     C     GETDETAIL     BEGSR                                              C*                                                                     C*    * Read selected detail records                                   C*                                                                     C                   EXSR      GET                                      C     SQLCOD        DOWEQ     0                                        C*    myDS          dsply                                              C+                  DELETE FROM MBMTEST01/WRLDSHP WHERE TNCOUN  = 'USA' C                   EXSR      GET                                      C                   ENDDO                                              C                   Eval      *inlr = *on                              C                   ENDSR                                              C*                                                                     C/EJECT                                                                C*                                                                     C     GET           BEGSR                                              C*                                                                     C*    * Get invoice detail records using dealer cursor                 C*                                                                     C/EXEC SQL                                                            

C+   FETCH FROM A INTO :myDS   C/END-EXEC                     C*                             C                   ENDSR      C*                             C/EJECT                        C     SQLCLOSE      BEGSR      C*                             C*    * Execute close of cursor C*                             C/EXEC SQL                     C+   CLOSE  A                  C/END-EXEC                     C*                             C                   ENDSR      C*                            



Software/Hardware used:
iseries as/400

Answer Wiki

Thanks. We'll let you know when a new response is added.

Your DELETE statement is not within an EXEC-SQL – END-EXEC declaration block.

But that would exec the delete statement for every record in the file. The first time it would delete all the records with a TNCOUN = ‘USA’, every time after that would be wasted cpu.

If all you are trying to do is delete all the records that have a TNCOUN = ‘USA’, then you are making this too difficult. You don’t need to have an SQL cursor to delete a set of records. If this is what you are trying to do your *whole* program could be reduced to this:

******* start source *******
H OPTION(*NODEBUGIO:*SRCSTMT)
/free
Exec SQL Set Option Commit = *NONE;
Exec SQL DELETE FROM MBMTEST01/WRLDSHP WHERE TNCOUN = ‘USA';
*inlr = *on;
********end source ********

This is assuming that you are on V5R4 or higher. If you are on a lower version then you will need to surround each SQL statement with the /EXEC-SQL and /END-EXEC statements.

And if you compile your program with comit(*NONE), then you don’t even need the “Set Option” statement.

Hope this helps. :-)

Discuss This Question:  

 
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following